RDS for MySQL CPU 性能问题浅析-优快云博客

本文探讨了RDSforMySQL实例CPU使用率达到100%的原因及解决方案，包括应用负载高和查询执行成本高的场景，并提供了实用的工具和技术手段。

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分析
慢日志明细、慢日志统计

诊断报告、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 分析和慢日志等信息。