RDS for MySQL CPU 性能问题浅析
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
实例信息
诊断报告 :
SQL 优化部分没有需要优化的查询(或者需要优化的查询不是主要原因)。
CPU 使用率变化曲线和 QPS and TPS 变化曲线吻合。
1.2. 查询执行成本高
特征:QPS 不高;查询执行成本高、优化余地大。
表现:存在慢查询,QPS 和 CPU 使用率曲线变化不吻合。
查询执行成本高,为了获得结果集需要访问大量的数据(平均逻辑读高),在 QPS 并不高的情况下,RDS 实例的 CPU 使用率高。
注:由于查询成本高导致实例 CPU 使用率高是 RDS for MySQL 非常常见的问题。
2 解决方法
2.1 相关工具
DMS 和 RDS 产品提供了几种不错的工具来辅助排查解决实例性能问题。DMS主要有:
-
实例诊断报告
-
SQL窗口提供的查询优化建议 和 查看执行计划
-
实例会话
其中实例诊断报告,是排查和解决 RDS for MySQL 实例性能问题的快捷工具。
出现性能问题时,建议首先参考下实例诊断报告,尤其建议关注诊断报告的 "SQL优化"、"会话列表"、"慢SQL汇总" 部分(请参考 2.3 小节)。
RDS 控制台主要有:
-
诊断报告
-
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小节中的例子):
对于查询时间长、运行状态(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 管理长时间运行查询。
可以看到有 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" 列中的查询文本,可以显示完整的查询和其执行计划。
通过执行计划可以看到,对 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% 的问题解决。
2.2.3
对于非当前的负载问题,可以通过 实例诊断报告(DMS 实例信息
诊断报告)获取优化建议,来达到优化的目的。
点击"发起诊断" 按钮,可以创建一个针对当前实例运行情况的报告。
对于CPU使用率高的问题,建议关注诊断报告的 "SQL优化"、"会话列表"、"慢SQL汇总" 部分。
注:对于 QPS 高和查询效率低的混合模式导致的 CPU 使用率高问题,建议从优化查询入手。
2.2.4
RDS 控制台的 诊断报告 (控制台 性能优化
诊断报告)会提供 实例整体的 SQL 执行分析,便于快速的定位到问题 SQL。
反馈存在问题嫌疑的 SQL。
3 避免出现 CPU 使用率达到 100% 影响业务的一般原则
-
设置 CPU 使用率告警,实例 CPU 使用率保证一定的冗余度。
-
应用设计和开发过程中,要考虑查询的优化,遵守 MySQL 优化的一般优化原则,降低查询的逻辑 IO,提高应用可扩展性。
-
新功能、新模块上线前,要使用生产环境数据进行压力测试(可以考虑使用阿里云 PTS 压力测试工具)。
-
新功能、新模块上线前,建议使用生产环境数据进行回归测试。
-
建议经常关注和使用 RDS 控制台、DMS 中的诊断报告、SQL 分析 和 慢日志等信息。