RDS For Mysql cpu飙高

原因

1.锁冲突

1)元锁(事物A正在对一个表进行操作，事物B对表进行DDL操作时会造成元锁的产生.详情请参考:MySQL_元锁)

元锁针对的是DDL操作
查询元锁可通过以下语句
select * from information_schema.processlist where state = 'Waiting for table metadata lock'

2)行锁(行锁请参考:MySQL_innodb行锁) 可通过RDS控制台创建的诊断报告得知有无行锁

3)死锁 可通过RDS控制台创建的诊断报告得知有无死锁

2.应用负载(QPS)

3.慢查询

分析

应用负载(QPS)高

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

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

CPU:

QPS:

CPU 使用率变化曲线和 QPS 变化曲线吻合。

查询执行成本(查询访问表数据行数 avg_lgc_io)高

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

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

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

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

CPU监控图

飙高时间段 2018-08-29 16:00-17:00 最高飙至98%，并持续20分钟

TPS\QPS监控图

CPU曲线图和QPS曲线图不吻合

从检测报告可知，无死锁，行锁产生，无元锁 从CPU和QPS曲线图不吻合可知，本次CPU飙高不是由于应用负载引起的 从慢查询监控图和CPU图可知，CPU飙高之前有大量慢SQL执行，从而引起CPU飙高进而会话堵塞。

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

解决方法

应用负载(QPS)高

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

• 升级实例规格，增加 CPU 资源。 增加只读实例，将对数据一致性不敏感的查询(比如商品种类查询、列车车次查询)转移到只读实例上，分担主实例压力。 使用阿里云 DRDS 产品，自动进行分库分表，将查询压力分担到多个 RDS 实例上。
• 使用阿里云 Memcache 或者云 Redis 产品，常用的查询结果尽量从缓存中获取，减轻 RDS 实例压力。
• 使用阿里云 Memcache 或者云 Redis 产品，常用的查询结果尽量从缓存中获取，减轻 RDS 实例压力。 对于查询数据比较静态、查询重复度高、查询结果集小于 1 MB 的应用，考虑开启查询缓存(Query Cache)。 定期归档历史数据、采用分库分表或者分区的方式减小查询访问的数据量。 尽量优化查询，减少查询的执行成本(逻辑IO，执行需要访问的表数据行数)，提高应用可扩展性。

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

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

Step 1

如果当前 CPU 使用率比较高，可以通过 show processlist; 、show full processlis**t; 命令或者 DMS > 实例信息 > 实例会话** 来查看当前执行的查询

对于查询时间长、运行状态(State 列)是"Sending data"，"Copying to tmp table"、"Copying to tmp table on disk"、"Sorting result"、"Using filesort" 等都是可能有性能问题的查询(SQL)。

sending data

表示:sql正在从表中查询数据，如果查询条件没有适当的索引，则会导致sql执行时间过长
可以通过执行类似 kill 101031643; 命令来终止长时间执行的会话，注:除非客户要求，否则不要终止客户会话

从控制台慢SQL监控可以看到执行的SQL语句

查询执行效率低，为了获得预期的结果集需要访问大量的数据，优化余地大

对于CPU使用率高的问题，建议关注诊断报告的 "SQL优化"、"会话列表"、"慢SQL汇总" 部分。 注1:诊断报告同样适用于排查历史实例 CPU 使用率高的问题。
注2:对于 QPS 高和查询效率低的混合模式导致的 CPU 使用率高问题，建议从优化查询入手。