AWS RDS MySQL 优化

关系数据库作为生产环境中的 OLTP，承载着重要的使命，说它是整个业务系统的最重要的部分都不为过。AWS RDS MySQL 作为当前很受欢迎并且使用量很大的托管服务，在一定程度下降低了运维和 DBA 的负担。但是我们仍然需要面对线上出现的各种复杂的 RDS MySQL 性能问题，由于 MySQL 的性能问题是一个全方位立体的问题，上到整个业务系统的架构，读写节点的拓扑，节流策略的设置，业务侧分库分表，下到表的关系建立，表结构设计，SQL 语句优化，MySQL 服务器的参数调优，实时的监控 MySQL 的指标并反馈等等。这篇文章根据自己遇到的各种客户问题从 SQL latency 的角度切入来谈一下 latency 问题的定位，RDS MySQL 侧的监控以及一些优化的方法。

我们只有确定了问题很可能出在哪里，然后采取对应的措施才可能解决。否则一顿 RDS 实例升级操作可能根本没有解决问题还白花钱。但是一般线上分析可能对业务有影响，而且可能捕捉不到问题现场，所以多采用事后分析；而出现 RDS MySQL 性能恶化的情况下需要一种应急措施来维持线上业务，那么建议在业务侧那边采取限流策略或者对 RDS MySQL 来降级(比如从读写变为只读)，极端情况下，业务侧那边要采用熔断机制暂时不把新的请求发给 RDS MySQL，不要让一个已经很糟糕的 server 变得更糟糕。升级 RDS 实例做为可选的最后一步应急措施！

接下来我们按照先分析问题，然后解决问题的顺序来讲，最后还给出一些如何监控 SQL 层面以及收集信息的建议。

1. 分析问题：

1.1 从业务侧看到 SQL 比较慢，latency 不外乎由如下几部分组成（通过枚举请求处理的各个路径即可获知，全链路监控的重要性就体现在这里了）：

业务侧收发请求带入的 latency；

业务侧的 runtime 环境带入的 latency（比如业务侧跑在 EC2 虚机上，那么该虚机所在的物理主机当前的 load 可能引起的抖动会对 latency 有贡献），

业务侧与 RDS MySQL 实例之间的网络带入的 latency；

RDS MySQL server 本身，它所在的物理主机的 load，以及 RDS MySQL 的拓扑结构带入的 latency；

RDS MySQL 用到的 EBS 卷带入的 IO latency。

1.2 Latency 有两种：

一种是 CPU 消耗性的即所谓的 CPU bound（比如没有使用 index 的 SQL 会进行全表扫描一般会有比较高的 CPU 使用率，尤其是数据绝大部分都在 innodb 的 buffer pool 的话）；

一种是非 CPU 消耗性的，主要有 IO 消耗性的所谓的 IO bound 包括 disk IO 与 network IO（比如有频繁的写操作而且需要尽快落盘）和对共享资源的争用导致的延迟（比如数据库级别的锁和 OS 级别的锁，并发进入 innodb 的线程数超过设置的阈值，这些都可以让当前线程挂起并放弃 CPU）。

可以查看 RDS MySQL CPU 使用率以及可用物理内存的监控图，如果 CPU 使用率比较高，打开 RDS enhanced monitoring（因为这个要收费，所以只是在出现问题的时候在 enable。另外，它只是收集系统的一些统计信息，所以它对 mysqld 进程的性能影响几乎很小可以忽略），把能选上的选项都勾选，目的是查看 CPU 高是不是真的是 mysqld 进程占用了绝大部分。

根据出问题的时间段，查看该时间段的 RDS 的 disk write/read latency 平均值和最大值监控图，以及这个时间段的 read/write IOPS 监控图。如果该时间段 RDS 的 disk average latency 比平时高很多，而 IOPS 也很大接近了该 EBS 卷的 IOPS capacity，可以考虑在线提升 EBS 的 IOPS capacity。

1.3 如果排除了其他的原因比如业务侧，网络和 EBS IO 的问题，那么问题就 narrow down 到 MySQL 层面了。

如果是 mysqld 占用的 CPU 高，常见的有如下几种情况：

• SQL 没有使用索引导致的全表扫描；

• 大表的排序操作或者 group by 操作；

• 多个表的 Join 操作；

• 复杂的嵌套子查询操作；

• 1.3.1 对于已经执行完的事务，可以通过查看 slow query 日志来分析:

慢查询日志的分析有很多开源工具比如 pt-query-digest（percona 有很多工具很好用比如 pt-mysql-summary），MySQL 自带的 mysqldumpslow 也可以分析。

通过慢查询分析工具找到慢查询的 SQL 以后可以通过 explain 或 explain extended 来看该 SQL 的执行计划来分析一下该 SQL 的索引使用情况，进而可以推断。

举几个简单例子来看执行计划：

explain select * from test;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | SIMPLE      | test | ALL  | NULL          | NULL | NULL    | NULL | 10000 | NULL  |+----+-------------+-------+------+---------------+------+---------+------+------+-------+

上面输出表示这个 SQL 要执行全表扫描（type 为 ALL），扫描的行数大概是 10000（rows 为 10000），显然这个 SQL 执行的慢。

explain select  t1  from test\G*************************** 1. row ***************************            id: 1   select_type: SIMPLE          table: test           type: index possible_keys: NULL key: idx_t1        key_len: 8            ref: NULL           rows: 10000          Extra: Using index

上面的输出中尽管使用了索引（Extra 字段显示 Using index），但是注意这里的 type 是 index 表示的是基于索引的全扫描方式返回数据。同样从扫描的行数 rows 仍然为 10000 看到该 SQL 依然执行很慢。

explain select * from test where id =1 ;+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra |+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+|  1 | SIMPLE      | test           | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------+

上面的输出中的 type 为 const，表示最多只有一行匹配，用于主键或者唯一索引比较时。同样从扫描的行数 rows 也能看出只扫描了一行。

分析慢查询 SQL 的时候，通过 explain 来查看执行计划是非常重要的一环，而且不同版本的 MySQL 对同一个 SQL 的执行计划可能不同，因此一定要查看执行计划。explain 的输出中每个字段的含义具体请参考 MySQL 的官网：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

1.3.2 还可以把这个找到的 SQL 利用 MySQL 的 profiling 工具来分析一下：

Set profiling =1；（Profiling is enabled per session）

执行这些 SQL；

Show profiles；———–显示有哪些 query id；

Show profile for query xx；—–显示某个 query 的具体执行细节

举个例子，我们要查看 query id 为 2 的 SQL 的执行细节如下:

show profile for query 2;+----------------------+----------+| Status              | Duration |+----------------------+----------+| starting            | 0.000147 || checking permissions | 0.000023 || Opening tables      | 0.000047 || init                | 0.000081 || System lock          | 0.000031 || optimizing          | 0.000034 || statistics          | 0.001650 || preparing            | 0.000046 || executing            | 0.000018 || Sending data        | 2.460588 || end                  | 0.000041 || query end            | 0.000019 || closing tables      | 0.000022 || freeing items        | 0.000055 || cleaning up          | 0.000085 |+----------------------+----------+

很明显上面的输出中“Sending data”这个状态占了 2 秒多，这个状态表示 mysql 线程对 select 语句执行 read，process rows 和 send data to client 整个过程。一般这个过程会涉及到大量的硬盘读（如果数据不再 memory 的话）。

在 MySQL 5.7 版本以后，profiling 会逐渐废弃，推荐使用 performance schema。

关于 profiling 参考官网：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html；关于thread status 参考官网：https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

1.3.3 还可以通过 Performance_schema 系统库中的统计信息来查看：

Performance_schema 是默认关闭的并且是静态参数，可以考虑在自定义参数组中把它 enable 并重启。由于 Performance_schema 中收集信息的 table 在开启后会对 MySQL 的性能有一些影响，所以一般平时不要 enable 那些 table，在需要 troubleshooting 的时候在 enable 具体的 table，然后收集一小段时间后，确认收集到需要的信息后在把这些 table disable。

举个例子来看如何查看 SQL 语句的细节：

1.3.3.1 首先 enable events_statements_*和 events_stages_*这些 consumers：

UPDATE performance_schema.setup_consumers       SET ENABLED = 'YES'       WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers       SET ENABLED = 'YES'       WHERE NAME LIKE '%events_stages_%';

1.3.3.2 然后执行一个 SQL 查询：

SELECT * FROM employees.employees WHERE emp_no = 10001;+--------+------------+------------+-----------+--------+------------+| emp_no | birth_date | first_name | last_name | gender | hire_date |+--------+------------+------------+-----------+--------+------------+|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |+--------+------------+------------+-----------+--------+------------+

1.3.3.3 接着通过查询 events_statements_history_long 表来找到上面那个 SQL 查询的 EVENT_ID（这里通过表的 emp_no 字段的值 10001 来找 EVENT_ID）。

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT       FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%10001%';+----------+----------+--------------------------------------------------------+| event_id | duration | sql_text                                               |+----------+----------+--------------------------------------------------------+|       31 | 0.028310 | SELECT * FROM employees.employees WHERE emp_no = 10001 |+----------+----------+--------------------------------------------------------+

1.3.3.4 最后通过 EVENT_ID 从 events_stages_history_long 中查看对应 SQL 对应的各个子步骤的执行时长。

SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration       FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=31;+--------------------------------+----------+| Stage                          | Duration |+--------------------------------+----------+| stage/sql/starting             | 0.000080 || stage/sql/checking permissions | 0.000005 || stage/sql/Opening tables       | 0.027759 || stage/sql/init                 | 0.000052 || stage/sql/System lock          | 0.000009 || stage/sql/optimizing           | 0.000006 || stage/sql/statistics           | 0.000082 || stage/sql/preparing            | 0.000008 || stage/sql/executing            | 0.000000 || stage/sql/Sending data         | 0.000017 || stage/sql/end                  | 0.000001 || stage/sql/query end            | 0.000004 || stage/sql/closing tables       | 0.000006 || stage/sql/freeing items        | 0.000272 || stage/sql/cleaning up          | 0.000001 |+--------------------------------+----------+

具体可以参考官网：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html，https://dev.mysql.com/doc/refman/5.7/en/performance-schema-examples.html

慢查询日志分析和 performance schema 同样也适合分析 CPU 不高的场景。

1.3.4 对于正在执行中的事务，可以通过查看 show full processlist 看具体的线程当前正在做什么：

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

另外，需要利用 show global status 来计算 QPS，在 QPS 高导致 CPU 使用率高的场景中，查询执行时间通常比较短，show full processlist 可能不容易捕捉到当前执行的查询。

2. 解决问题：

通过上面的思路和分析很可能就找到了 Root cause，那么接下来就是去 fix 或者 optimization 的时候了。下面的前两条主要是根据分析的结果来解决当前 SQL 慢的方法，剩下的是那几条则是更多的从日常优化的角度。

2.1 如果是 SQL 语句本身导致的，那么可以考虑从 SQL 层面，索引层面，表结构层面甚至是表的关系建立方面来着手。

2.1.1 不同的关系数据库对同一个复杂的 SQL 的执行计划可能都不同，所以可能在 Oracle 上执行很快的 SQL 语句直接放在 MySQL 上执行就会很慢，这个时候需要对该 SQL 语句进行变形甚至拆分多几个 SQL 语句来迎合 MySQL。

2.1.2 如果是因为执行过程没有使用索引导致，那么考虑建立合适的索引来加速。

2.1.3 如果是因为表太大，可以考虑进行分区或者分表的方式。

2.1.4 如果是因为太多表 join 比如 10 个表 join 操作导致的，那么需要考虑是否应该把这样的 join 语句放在 MySQL 上跑。一般这种情况下，可以考虑把对应的数据抽取到一个 OLAP 上去做分析。

2.2 如果是因为高 QPS 或者高并发导致的，那么可以考虑从数据库可调参数，读写拓扑，负载均衡，业务侧限流，cache 前置，分库分表，分布式 sharding，甚至业务侧划分为多个独立的集群以及建模范式来着手。

2.2.1 你的业务场景是否用 NoSQL 来建模，NoSQL 是去关系的，天然的并发比关系数据库好。

2.2.2 随着你的业务的增长，可能 QPS 会更高，那么就要考虑是否应该从业务侧分库分表或者部署分布式 sharding 集群，或者直接从业务侧划分多个独立的集群来拆分 QPS。

2.2.3 单个关系数据库实例本身能承载的并发都是有限的，由于具体实现的复杂性以及并发模型 BIO/NIO 的选择，共享数据的争用从而不可能很大。

这里做个广告，AWS RDS Aurora for MySQL 同等配置下要比 RDS MySQL 的性能好，区别于 MySQL 的 BIO 模型，Aurora 采用基于 epoll 的 NIO 的基于事件驱动的 React 方式来提升并发性能。Aurora 的优点远不止这些，有兴趣可以参考 AWS Aurora 相关博客和文档。

这个时候，考虑是读多还是写多，读多的话是否可以容忍弱一致性，考虑建立更多的一级或者多级 read replica 来着手（只所以会用到级联复制，是因为同一个 master 的 slave 越多，那么 master 上的多个事务提交线程（需要 binlog 文件写锁）与多个 slave 对应的 master 上的多个 dump thread（需要 binlog 文件读锁）对 binlog 文件锁的争用就越剧烈，从而影响 master 和 slave 的性能。这个也是不建议主库有很多 slave 的原因。），同时对多个 read replica 前置对 MySQL 协议感知的中间件比如 Mycat 或者对集群拓扑感知的 client library 来做负载均衡读请求；写多的话，可能需要分区，分库分表，sharding 甚至划分独立集群。

2.2.4 前置的 cache 比如 redis 是关系数据库的最佳搭档，尽量要部署，让更多的弱一致性的读请求不要到达关系数据库。

2.2.5 业务侧的限流机制非常重要，可以缓解数据库的负载，另外，客户端库的指数退让重试机制非常适合和有状态的服务比如关系数据库来交互的。目的就是让数据库不要进一步恶化直到崩溃。

2.2.6 RDS MySQL 有很多参数可以用来调整：

比如一般建议把 MySQL 的 query cache 禁用（现在一般生产环境不怎么使用 query cache，query cache 本身全局锁容易成为瓶颈，每次 DML commit 还要干掉对应的 query cache 中的旧的内容，还有如果 query cache 的 hit 不高更要命）。

如果可以容忍很少的数据丢失和/或者主从数据不一致，可以不用把 sync_binlog 和 innodb_flush_log_at_trx_commit 设置为 1 即每次事务 commit 都需要落盘。

比如设置合理的 wait_timeout 不让淘气的客户端不干事还占用资源。

MySQL 很多参数之间有着错综复杂的关系，在确认你对这些关系很清楚前，不要轻易修改默认的参数。

在这个场景下，升级 RDS 实例的机型可能对 QPS 和高并发有帮助。

2.3 利用 SHOW ENGINE INNODB STATUS 查看 innodb 的一些 metric：

Semphore section 有 metric “Spin rounds per wait”，这个 metric 如果比较大，表示当前很多线程需要等待一个 OS 级别的锁，在这样的情况下最好降低 innodb_thread_concurrency（这个参数默认是 0，建议可以修改为 128 试试）。

2.4 Information_schema.INNODB_TRX 是当前运行的所有事务的信息：

SELECT trx.trx_id, trx.trx_started, trx.trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX trx WHERE trx.trx_started < CURRENT_TIMESTAMP – INTERVAL 1 SECOND\G

上面是查看在 1 秒前开始运行还没有结束的事务。如果发现某个事务很久还没有结束并且不是期望的行为，那么可以考虑 kill 掉该事务。

应该及时提交长时间的事务，如果一直有事务没有提交可以考虑把这个事务给 kill 了。

2.5 对于 table 经常修改（增删改）的情况，最好定期比如 1 周用 Analyze table 命令来更新表的统计信息（统计信息对执行计划评估有很大影响，执行计划不好就会导致 SQL 性能下降）。

2.6 硬盘碎片空间可以被 MySQL 重复利用，但会引发查询性能不可控地下降。

对于 MySQL 的硬盘空间的回收只能对独立的表空间有用（每个表的数据保存为一个文件，因此就可以通过拷贝数据删掉旧的文件来把硬盘空间归还给 OS），共享的表空间是所有的表共同保存在几个文件中，没有办法进行类似的操作。

对于基于 innodb 存储引擎的 MySQL 数据库，要回收磁盘空间，推荐使用重置表存储引擎的方法：alter table table_name engine=innodb（重置期间所有写会话会被阻塞，读会话不受影响），重置消耗时间与已删除数据无关，仅与剩下数据呈线性相关。

Optimize table 对于 innodb 引擎来说，也会被 mapping 到 alter table 来处理（创建一个新表，拷贝数据，原子 rename 新表为旧表表名）

这些命令对在线业务有一些影响，最好在业务低峰做。

2.7 设置 autocommit=1，尤其应该检查一些 GUI 客户端（比如通过慢查询日志或者 general 日志）例如 MySQL Workbench 和 Navicat，它们极有可能在连接 MySQL 时采用 autocommit=0 的模式，应该调整过来，否则就可能会有事务忘记提交。

3. 如何更好的从 SQL 层面监控和收集有用的信息，给事后分析准备充足的“食材”：

MySQL 的性能问题很复杂，不是一句话就能解决的，需要收集到足够的信息来分析。出现 SQL 慢的时候，需要收集如下的信息：

3.1 慢查询日志中出现的 SQL；

慢查询日志相对 general 日志来说（general 日志没有什么特殊目的尽量不要打开，对性能还是有一些影响的），对 MySQL 的影响比较小，在出问题的时候可以开启（这个时候开启只能记录新的慢 SQL）。如果想一直记录慢查询的 SQL 也可以考虑一直开启，这样也方便做事后分析。

注意：有时候慢查询日志里面没有记录实际执行很慢的 SQL，但是通过审计日志或者 general 日志却能发现这些慢的 SQL，是因为这些 SQL 都没有执行成功（比如客户端在发起的 SQL 还没有执行完就关闭了 TCP 连接），所以它是不会记录到慢日志中的，但是都会记录到审计日志或者 general 日志。

3.2 可以在业务端的监控系统监控业务端发起的 SQL，如果 SQL 持续比较慢比如连续 30 秒 SQL 响应平均超过 2s 的时候则触发调用一个脚本来收集以下的信息：

3.2.1 执行一次 show global variables；

3.2.2 每间隔一秒执行一次 show full processlist， show global status，SHOW ENGINE INNODB STATUS， 一共执行比如 5 次。

3.2.3 执行一次 SELECT trx.trx_id, trx.trx_started, trx.trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX trx WHERE trx.trx_started < CURRENT_TIMESTAMP – INTERVAL 1 SECOND\G

3.2.4 执行一次 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G

3.2.5 执行一次 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G

收集上面的所有的这些的输出，如果可能的话最好在出问题的时候把慢查询日志也收集，收集完毕就可以关闭慢查询日志（慢查询日志记得一定要把 output 修改为 FILE，同时要 enable log_queries_not_using_indexes 来记录所有不使用索引的 SQL）

3.3 如果问题持续时间比较长，可以利用 performance schema 来收集更详细的信息。

3.4 最好在出问题的时候，查看 RDS console 的如下的监控图：

出问题的时间段的 RDS 的 DISK write/read latency 平均值和最大值，这个时间段的 read/write IOPS； CPU 使用的平均值和最大值以及可用内存； RDS 实例的网络 network-in 和 network-out。

把这些信息都收集完毕后，你可以自己来做分析，也可以让 AWS 来帮你分析。你选用 AWS RDS MySQL 的一个附加效果就是 AWS 就是你的 DBA。

总结

总的来说，关系数据库 RDS MySQL 的优化是一个基于技术的艺术，千变万化还可能随时间变化，但是不管如何变化，套路总是不怎么变的（这些套路对原生的 MySQL 也是适用的），希望这篇文章对你有帮助。

作者介绍：

梁宇辉

亚马逊 AWS 解决方案架构师，负责基于 AWS 的云计算方案架构的咨询和设计，同时致力于 AWS 云服务的应用和推广。现致力于数据库和人工智能相关领域的研究。在加入 AWS 之前，曾在三星做嵌入式开发，在阿里巴巴做网络服务器开发，在 Websense 做内容安全网关服务器开发。在软件开发和设计，troubleshooting 以及性能调优方面有多年的实战经验。

本文转载自 AWS 技术博客。

原文链接：

https://amazonaws-china.com/cn/blogs/china/aws-rds-mysql-optimization/