RDS MySQL CPU使用率高情况的原因和解决

RDS MySQL 实例在日常使用中,有时会出现 CPU使用达到 100% 的情况。比如:
TB1fWedJVXXXXb_XXXXXXXXXXXX.png

1.问题原因

应用提交的查询(包括数据修改操作)执行所需大量的逻辑读(逻辑IO,执行查询所需访问的表的数据行数),系统需要消耗大量的 CPU 资源用于维护从存储系统读取到内存中的数据一致性。

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

通过一个简化的模型来说明 系统资源、语句执行成本 以及 QPS(Query Per Second 每秒执行的查询数)之间的关系:

条件:应用模型恒定(应用没有修改),

avg_lgc_io:每条查询执行需要的平均逻辑 IO

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

公式:

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

下面列出 2 种典型 CPU 使用 100% 的场景:

1.1 应用负载(QPS)高

特征:实例的 QPS(每秒执行的查询次数)高,查询比较简单、执行效率高、优化余地小。

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

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

CPU
TB14AWdJVXXXXbFXpXXXXXXXXXX.png
QPS
TB1o11fJVXXXXbqXpXXXXXXXXXX.png
控制台 => 登录数据库 => DMS => 实例信息 => 诊断报告
TB1Aah0JVXXXXaPXXXXXXXXXXXX.png
SQL 优化部分没有需要优化的查询(或者需要优化的查询不是主要原因)
TB18u0VJVXXXXaoaXXXXXXXXXXX.png
CPU 使用率变化曲线和 QPS 变化曲线吻合。

1.2. 查询执行成本(查询访问表数据行数 avg_lgc_io)高
特征:实例的 QPS(每秒执行的查询次数)不高;查询执行效率低、执行需要扫描大量表中数据、优化余地大。

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

查询执行效率低,为了获得预期的结果集需要访问大量的数据(平均逻辑IO高),在 QPS 并不高的情况下(例如网站访问量不大),也导致实例的 CPU 使用率高。

注:由于查询执行效率低(查询访问表数据行数多)而导致实例 CPU 使用率高是RDS MySQL非常常见的问题。 TB1FBR4JVXXXXaXXVXXXXXXXXXX.png

2、 解决方法

2.1 应用负载(QPS)高

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

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

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

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

使用阿里云 Memcache 或者云 Redis 产品,常用的查询结果尽量从缓存中获取,减轻 RDS 实例压力。

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

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

尽量优化查询,减少查询的执行成本(逻辑IO,执行需要访问的表数据行数),提高应用可扩展性。

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

2.2 查询语句执行成本(查询访问表数据行数)高

解决的原则:定位效率低的查询,优化查询的执行效率,降低查询执行的成本。

Step 1

如果当前 CPU 使用率比较高,可以通过 show processlist; 、show full processlist; 命令来查看当前执行的查询
TB1zbd7JVXXXXbmXVXXXXXXXXXX.png
对于查询时间长、运行状态(State 列)是"Sending data","Copying to tmp table"、"Copying to tmp table on disk"、"Sorting result"、"Using filesort" 等都是可能有性能问题的查询(SQL)。

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

注:关于长时间执行会话的管理,请参考 RDS MySQL 管理长时间运行查询
TB1sfSXJVXXXXcyXFXXXXXXXXXX.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" 列中的查询文本,可以显示完整的查询和其执行计划。
TB1oF9aJVXXXXcqXFXXXXXXXXXX.png
该查询的执行计划中,对 2 张约为 30 万行数据表执行了全表扫描;由于2张表是联接操作,因此这个查询的执行成本(逻辑IO)约为 298267 x 298839 = 89,133,812,013 (大概 900 亿),因此查询会执行相当长的时间并且多个会话会导致实例 CPU 使用率达到 100%(对比 1.1 小节中的截图,同样规格的实例对于优化良好的查询,QPS 可以达到 21000;而当前 QPS 仅为 5)。

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

注2:也可以通过命令

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 窗口的"执行计划"子标签页获取。

Step 2

得到需要优化的查询后,可以通过 DMS => SQL窗口 => 优化按钮 来获取查询的优化建议:
TB1c7CfJVXXXXXMXFXXXXXXXXXX.png
TB1sK45JVXXXXcDXVXXXXXXXXXX.png
根据诊断报告的优化建议,添加索引后查询执行成本大幅减少(从 900 亿行减小到 30 万行,查询成本降低 30 万倍),实例 CPU 使用率 100% 的问题解决。
TB13MioJVXXXXaXXFXXXXXXXXXX.png

Step 3

也可以通过实例诊断报告(DMS => 实例信息 => 诊断报告)获取优化建议,来达到优化的目的。
TB1yk5mJVXXXXXMXpXXXXXXXXXX.png
点击"发起诊断" 按钮,可以创建一个针对当前实例运行情况的报告。
TB1PPKuJVXXXXXTXXXXXXXXXXXX.png
对于CPU使用率高的问题,建议关注诊断报告的 "SQL优化"、"会话列表"、"慢SQL汇总" 部分(再次强调下)。

注1:诊断报告同样适用于排查历史实例 CPU 使用率高的问题。

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


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

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

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

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

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

建议经常关注和使用 DMS 中的诊断报告。

关于如何访问 DMS 中的诊断报告,请参考: RDS如何访问诊断报告

添加新评论