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 优化部分没有需要优化的查询（或者需要优化的查询不是主要原因）。

  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分析

慢日志明细、慢日志统计

诊断报告、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 的执行效率：查询语句的执行效率可以通过其需要扫描的表数据行数 和 结果集数据行数 比率 来衡量。 该比率越小说明查询语句效率越高。 比如：

#访问表数据行数返回结果集行数比率说明效率1100010100平均每扫描 100 行表数据返回 1 行结果比较低220102平均每扫描 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 分析 和 慢日志等信息。