关于RDS MySQL CPU使用率高情况的原因和解决办法

我们知道，RDS MySQL 实例在日常使用中，有时会出现CPU使用100%的情况。

　　比如：

　　1.原因：

　　出现 CPU 使用率高的根本原因，是应用提交的查询(包括数据修改操作)导致的系统逻辑读(或者称为逻辑IO，执行查询所需访问的表的数据行数)数量多，系统需要消耗大量的CPU资源用于维护从磁盘系统读取到内存中的数据一致性。

　　提别提醒：本文不排除由于RDS MySQL 自身原因或后台任务原因导致的实例CPU使用率高，但这种情况出现的概率是非常低的，几乎为零，所以在此不做讨论。

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

　　假设在应用模型恒定的情况下，每条查询需要访问的平均逻辑IO是 avg_lgc_io，实例CPU资源单位时间能够处理的逻辑IO总量是 total_lgc_io， 那么可以简单的得到公式：

　　total_lgc_io = avg_lgc_io x QPS

　　即： 单位时间 CPU 资源 = 查询执行平均成本 x 单位时间执行的查询数量

　　因此在下面2种典型场景下，容易出现CPU 使用率100% 的情况：

　　1.1. 查询语句执行效率低、执行成本(查询访问表数据行数 avg_lgc_io)高

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

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

　　1.2应用负载(QPS)高

　　体现在 RDS 实例的 QPS(每秒执行的查询次数)比较高，查询比较简单、执行效率高、优化余地小;这种情况常见于在线事务交易系统(比如订单系统)、高读取率的热门Web网站应用、第三方压力工具测试中(比如Sysbench)等等。

　　比如：

　　对应的QPS：

　　控制台=》登陆数据库 =》 DMS =》实例信息 =》诊断报告 ：

　　SQL 优化部分没有需要优化的查询(或者需要优化的查询不是主要原因)，如下图：

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

　　2 解决方法

　　DMS 工具提供了几种不错的功能来辅助排查解决实例性能问题，主要有：

　　实例诊断报告

　　实例会话

　　SQL窗口提供的查询优化建议 和 查看执行计划

　　其中实例诊断报告，是排查和解决 RDS MySQL 实例性能问题的最佳和最快捷工具。无论何种原因导致的性能问题，建议首先参考下实例诊断报告，尤其建议关注诊断报告的 "SQL优化"、"会话列表"、"慢SQL汇总" 部分(请参考2.3小节)。

　　2.1 查询语句执行效率低，执行成本(查询访问表数据行数)高

　　这种情况解决的原则是定位效率低的查询，优化查询的执行效率，降低查询执行的成本。

　　2.1.1 在碰到RDS MySQL 实例 CPU 使用率高的问题时，首先判断是否是由于应用负载上升导致 CPU 使用率上升。

　　2.1.2 如果当前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" 等都是可能有性能问题的查询(SQL)。

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

　　注：关于长时间执行会话的管理，请参考 RDS 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;

　　通过 "Kill 会话" 按钮，可以终止正在执行的查询。

　　点击"SQL"列中的查询文本，可以显示完整的查询和其执行计划。

　　可以看到该查询的执行计划中，对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窗口的"执行计划"子标签页获取。

　　2.2.3 得到需要优化的查询后，可以通过 DMS =》SQL 窗口 =》优化按钮 来获取查询的优化建议：

　　参考诊断报告给出的优化建议，添加索引后查询执行的逻辑IO大幅减少(从约900亿行减小到约30万行，查询成本降低30万倍)，相应实例CPU使用率100%的问题解决。

　　2.2.4 同时也可以通过实例诊断报告(DMS =》实例信息 =》诊断报告)来达到优化的目的。

　　点击"发起诊断" 按钮，可以创建一个针对当前实例运行情况的报告。

　　对于CPU使用率高的问题，建议关注诊断报告的 "SQL优化"、"会话列表"、"慢SQL汇总" 部分(再次强调下)。

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

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

　　2.2 应用负载(QPS)高

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

　　如问题还未解决,请联系售后技术支持。