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 实例信息 诊断报告 ：
SQL 优化部分没有需要优化的查询（或者需要优化的查询不是主要原因）。
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分析
- 慢日志明细、慢日志统计
诊断报告、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小节中的例子）：
对于查询时间长、运行状态（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 分析 和 慢日志等信息。
