RDS for MySQL CPU 性能问题浅析

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
数据管理 DMS,安全协同 3个实例 3个月
推荐场景:
学生管理系统数据库
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: RDS for MySQL CPU 性能问题浅析


RDS for MySQL CPU 性能问题浅析

1. 原因

1.1 应用负载高

1.2 查询执行成本高

2. 解决方法

2.1 相关工具

2.2 应用负载高

2.3 查询语句执行成本高

3. 避免出现的一般原则


RDS for MySQL 实例在日常使用中,会碰到 CPU 使用率达到 100% 的情况。比如:

1. 原因

根本原因:
应用提交的查询访问的 逻辑读(逻辑 IO) 总量 (需要访问的 表 数据) 过高。
大量逻辑读会导致数据缓存 Buffer Pool 中用于维护数据一致性的 Latch 和 Mutex 争抢过于频繁,进而大量消耗 CPU 资源。

背景知识:
  • 物理读 - 当执行一个查询时,为了返回满足查询的结果集,系统必须访问 表 中的数据。这些数据以 16 KB 大小的数据页(Page,Oracle DB 中称之为 Block)形式存储在磁盘上。当查询需要访问该数据时,如果该数据 不在 InnoDB Buffer Pool 中,则系统会将该页从磁盘上的数据文件中加载到 InnoDB Buffer Pool 中,每一个 16 KB 页的加载动作被称之为一个物理读(物理 IO)。
  • 逻辑读 - 档执行一个查询时,为了返回满足查询的结果集,系统必须访问 表 中的数据。这些数据以 16 KB 大小的数据页(Page,Oracle DB 中称之为 Block)形式存储在磁盘上。当查询需要访问该数据时,如果该数据 在 InnoDB Buffer Pool 中,则对每一个 16 KB 页的内存访问称之为一个逻辑读(逻辑 IO)。
  • TPS - Transaction Per Second, 每秒的事务数。
  • QPS - Query Per Second,每秒的查询数。

    物理读涉及到 IOPS 资源的消耗,逻辑读涉及到 CPU 资源的消耗。

注:本文不排除由于其他原因(比如大量行锁冲突、行锁等待)导致的实例 CPU 使用率高,但这种情况出现的概率非常低,在此不做讨论。

通过一个简化的公式来说明 CPU资源、语句执行成本 以及 QPS 之间的关系:

条件应用模型恒定

avg_lgc_io:每条查询执行需要的平均逻辑 IO ,可以简化为 查询 需要访问 的 表 数据行数。

total_lgc_io实例 CPU 资源单位时间能够处理的 逻辑IO 总量

公式:

total_lgc_io = avg_lgc_io x QPS 
单位时间 CPU 资源 = 查询执行平均成本 x 单位时间执行的查询数量

 两种典型场景:

1.1 应用负载高

特征:实例的 QPS 高,查询比较简单、单个SQL执行成本低(逻辑读低,需要访问的数据量小)、优化余地小。

表现:没有出现慢查询(或者慢查询不是问题主要原因),QPS 和 CPU 使用率曲线变化吻合。

常见于应用优化过的在线事务交易系统(比如订单系统)、高读取率的热门Web网站应用、第三方压力工具测试中(Sysbench)等:

CPU:

QPS/TPS:

在诊断报告中,没有对应的 慢查询(或者该慢查询不是主要原因),并且 QPS/TPS 曲线和 CPU 曲线变化吻合 
控制台  登录数据库  DMS  实例信息  诊断报告 :

cpu_dms_01.png

SQL 优化部分没有需要优化的查询(或者需要优化的查询不是主要原因)。

cpu_dms_02.png

  CPU 使用率变化曲线和 QPS and TPS 变化曲线吻合。

1.2. 查询执行成本高

特征:QPS 不高;查询执行成本高、优化余地大。

表现:存在慢查询,QPS 和 CPU 使用率曲线变化不吻合。

查询执行成本高,为了获得结果集需要访问大量的数据(平均逻辑读高),在 QPS 并不高的情况下,RDS 实例的 CPU 使用率高。

注:由于查询成本高导致实例 CPU 使用率高是 RDS for MySQL 非常常见的问题。 

cpu_dms_08.png

2 解决方法 

2.1 相关工具

DMS 和 RDS 产品提供了几种不错的工具来辅助排查解决实例性能问题。
DMS主要有:
  • 实例诊断报告

  • SQL窗口提供的查询优化建议 和 查看执行计划

  • 实例会话

其中实例诊断报告,是排查和解决 RDS for MySQL 实例性能问题的快捷工具。
出现性能问题时,建议首先参考下实例诊断报告,尤其建议关注诊断报告的 "SQL优化"、"会话列表"、"慢SQL汇总"  部分(请参考 2.3 小节)

RDS 控制台主要有:

  • 诊断报告

  • SQL分析

  • 慢日志明细、慢日志统计

诊断报告、SQL 分析 和 慢日志 等工具方便定位导致性能问题的具体 SQL 。

2.2 应用负载高

这种情况 SQL 优化的余地不大,建议考虑从应用架构、实例规格等方面来解决:

  • 升级实例规格,增加 CPU 资源。

  • 增加只读实例,将对数据一致性不敏感的查询(比如商品种类查询、列车车次查询)转移到只读实例上,分担主实例压力。

  • 使用阿里云 DRDS 产品,自动进行分库分表,将查询压力分担到多个 RDS 实例上。

  • 使用云 Redis 或 云 Memcache 产品,静态重复性查询尽量依靠缓存处理,减轻 RDS 实例压力。

  • 对于数据比较静态、查询重复度高、查询结果集小于 1 MB 的应用,考虑开启查询缓存(Query Cache)。

  • 定期归档历史数据、采用分库分表或者分区的方式减小查询访问的数据量。

  • 定期优化查询,减少其执行成本(执行需要访问的表数据行数),提高应用可扩展性。

  注:能否从开启查询缓存(Query Cache)中获益需要经过测试,具体设置请参考 RDS for MySQL 查询缓存(Query Cache)的设置和使用

2.3 查询语句执行成本高

解决的原则
定位高成本查询(通常是慢查询),优化其执行效率,降低其执行成本。
背景知识 - 如何衡量 SQL 的执行效率:
查询语句的执行效率可以通过其需要扫描的表数据行数 和 结果集数据行数 比率 来衡量。
该比率越小说明查询语句效率越高。
比如:

# 访问表数据行数 返回结果集行数 比率 说明 效率
1 1000 10 100 平均每扫描 100 行表数据返回 1 行结果 比较低
2 20 10 2 平均每扫描 2 行表数据返回 1 行结果 很高

2.2.1  

如果 当前 CPU 使用率比较高,可以通过 show processlist; show full processlist; 命令或者 DMS  实例信息  实例会话 来查看当前执行的查询(继续1.2小节中的例子):

cpu_12.png

对于查询时间长、运行状态(State 列)是"Sending data","Copying to tmp table"、"Copying to tmp table on disk"、"Sorting result"、"Using filesort" 、“Creating Sort Index”等都是可能有性能问题的查询。

可以通过执行 kill 101031643; 命令来终止该长时间执行的会话。

注:关于长时间执行会话的管理,请参考 RDS for MySQL 管理长时间运行查询。

cpu_dms_09.png

可以看到有 10 个会话在执行下面这个查询:

select b.*
  from perf_test_no_idx_01 a,
       perf_test_no_idx_02 b
 where a.created_on>= '2015-01-01'
   and a.detail= b.detail;

 点击 "SQL" 列中的查询文本,可以显示完整的查询和其执行计划。

cpu_dms_10.png

通过执行计划可以看到,对 2 张约为 30 万行数据表执行了全表扫描。
由于 2 张表是联接操作,因此这个查询的执行成本 约为 298267 x 298839 = 大约 900 亿,因此查询会执行相当长的时间并且多个会话会导致实例 CPU 使用率达到 100%。
对比 1.1 小节中的截图,同样规格的实例对于优化良好的查询,QPS 可以达到 25000;而当前 QPS 仅为 5。

注:
在 QPS 高导致 CPU 使用率高的场景中,查询执行时间通常比较短,show processlist; 或实例会话中可能会不容易捕捉到当前执行的查询。

也可以通过命令

explain select b.* 
from perf_test_no_idx_01 a, perf_test_no_idx_02 b 
where a.created_on >= 2015-01-01 
and a.detail = b.detail 

来获取该查询 SQL 的执行计划,或者在 SQL 窗口的"执行计划"子标签页获取。

2.2.2

得到需要优化的查询后,可以通过 DMS  SQL 窗口  优化按钮 来获取查询的优化建议:

 

根据诊断报告的优化建议,添加索引后查询执行成本大幅减少,从 900 亿行减小到 30 万行,查询成本降低 30 万倍,CPU 使用率 100% 的问题解决。

cpu_console_s_04.png

2.2.3 

对于非当前的负载问题,可以通过 实例诊断报告DMS  实例信息  诊断报告)获取优化建议,来达到优化的目的。

cpu_dms_06.png

点击"发起诊断" 按钮,可以创建一个针对当前实例运行情况的报告。

cpu_dms_07.png

对于CPU使用率高的问题,建议关注诊断报告的 "SQL优化"、"会话列表"、"慢SQL汇总"  部分

注:对于 QPS 高和查询效率低的混合模式导致的 CPU 使用率高问题,建议从优化查询入手。

 
2.2.4 
RDS 控制台的 诊断报告 (控制台  性能优化  诊断报告)会提供 实例整体的 SQL 执行分析,便于快速的定位到问题 SQL。

反馈存在问题嫌疑的 SQL。

3 避免出现 CPU 使用率达到 100% 影响业务的一般原则

  • 设置 CPU 使用率告警,实例 CPU 使用率保证一定的冗余度。

  • 应用设计和开发过程中,要考虑查询的优化,遵守 MySQL 优化的一般优化原则,降低查询的逻辑 IO,提高应用可扩展性。

  • 新功能、新模块上线前,要使用生产环境数据进行压力测试(可以考虑使用阿里云 PTS 压力测试工具)。

  • 新功能、新模块上线前,建议使用生产环境数据进行回归测试。

  • 建议经常关注和使用 RDS 控制台、DMS 中的诊断报告、SQL 分析 和 慢日志等信息。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6天前
|
缓存 关系型数据库 MySQL
如何优化MySQL性能?
【5月更文挑战第23天】如何优化MySQL性能?
12 1
|
9天前
|
SQL 关系型数据库 MySQL
RDS for MySQL的DDL、DML和DQL
这篇文章介绍了MySQL中处理数据的几种主要操作:DDL(Data Definition Language),DML(Data Manipulation Language)和DQL(Data Query Language)。首先讲解了如何使用CREATE DATABASE, ALTER DATABASE和DROP DATABASE语句来创建、修改和删除数据库。
19 1
|
9天前
|
SQL 存储 关系型数据库
RDS for MySQL的SQL分类与数据类型
小明需在MySQL中管理商品信息,使用SQL完成业务操作。SQL分为DQL(查询)、DML(增删改)、DDL(定义)、DCL(权限控制)和TCL(事务)五大类。DDL用于创建、修改和删除数据库结构,DML处理数据,DCL控制权限,TCL管理事务,DQL则用于查询数据。MySQL有多种数据类型,如数值型(整数、小数)、日期型和字符串型等,选择合适的数据类型是高效开发的关键。
19 0
|
12天前
|
缓存 关系型数据库 MySQL
如何优化MySQL数据库查询性能
MySQL是一款常用的关系型数据库,但在实际使用过程中,由于数据量增加和查询操作复杂度增加,会导致查询性能下降。本文将介绍一些优化MySQL数据库查询性能的方法。
|
14天前
|
存储 算法 关系型数据库
MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀
MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀
|
14天前
|
存储 SQL 关系型数据库
MySQL的优化利器⭐️索引条件下推,千万数据下性能提升273%🚀
以小白的视角探究MySQL索引条件下推ICP的优化,其中包括server层与存储引擎层如何交互、索引、回表、ICP等内容
MySQL的优化利器⭐️索引条件下推,千万数据下性能提升273%🚀
|
14天前
|
存储 关系型数据库 MySQL
MySQL字段的字符类型该如何选择?千万数据下varchar和char性能竟然相差30%🚀
本篇文章来讨论MySQL字段的字符类型选择并深入实践char与varchar类型的区别以及在千万数据下的性能测试
MySQL字段的字符类型该如何选择?千万数据下varchar和char性能竟然相差30%🚀
|
14天前
|
DataWorks 关系型数据库 MySQL
DataWorks产品使用合集之在DataWorks中配置RDS MySQL数据源的步骤如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
29 0
|
14天前
|
存储 关系型数据库 MySQL
RDS for MySQL测试
【4月更文挑战第28天】
|
14天前
|
缓存 关系型数据库 MySQL
【专栏】提升MySQL性能和高可用性的策略,包括索引优化、查询优化和事务管理
【4月更文挑战第27天】本文探讨了提升MySQL性能和高可用性的策略,包括索引优化、查询优化和事务管理。通过合理使用B-Tree和哈希索引,避免过度索引,以及优化查询语句和利用查询缓存,可以改善性能。事务管理中,应减小事务大小并及时提交,以保持系统效率。主从或双主复制可增强高可用性。综合运用这些方法,并根据实际需求调整,是优化MySQL的关键。