MySQL 最佳实践：分析和应对 MySQL 的 IO 问题

前言

在日常工作中，有时候会发现 MySQL 的状态不太对劲，这时候就会看看监控指标，可能会发现：写入 QPS 开始出现毛刺，或者 IO 的指标很高。本文会从 Linux 层面入手，根据不同的 IO 特点来分析 MySQL 数据库可能遇到的问题，并给出一些可参考的优化/缓解思路。

怎么看懂 IO 指标

检查 IO 的问题会使用iostat这个命令，这里展示一下命令的效果（iostat -x 1 -m，debian 10.2）：

iostat

avg-cpu 自然就是 CPU 相关的指标，判断 IO 问题时可以关注 %iowait，其他指标的意义如下：

• r/s 和 w/s：合并过后的读请求和写请求的每秒请求数，可以当做 IOPS 来理解。
• rMB/s 和 wMB/s：磁盘的读写吞吐量。
• rrqm/s 和 wrqm/s：每秒合并的读请求和写请求数量。
• %rrqm 和 %wrqm：合并的读请求和写请求百分比。
• r_await 和 w_await：读请求和写请求的平均响应时间，包含真正的处理时间和队列中的等待时间（ms）。
• aqu-sz：平均队列深度。
• rareq_sz 和 wareq_sz：一个读请求和写请求的平均物理大小（KB）。
• scvtm：计算出来的平均 IO 响应时间，目前已经不准确，不用再关注。
• %util：如果使用了 RAID 或者 SSD，则忽略这个指标，仅在单块机械盘上准确。

一般来说，评价一块 IO 设备（忽略机械盘的情况，没有评价的意义）是否达到了高负载情况，可以看这几个指标：r/s，w/s，rMB/s，wMB/s，r_await，w_await，aqu-sz。

MySQL 与 IO

由于 MySQL 涉及到 IO 相关的参数会比较多，因此这里仅一部分经常用到的参数以及在测试&模拟中使用默认设置：

innodb_io_capacity 和 innodb_io_capacity_max 是最直接限制 IOPS 的指标，大多数时候，SSD 可以设置成 16000 或者更高的数值，如果是云主机或者其他的共享存储设备，则需要了解一下详细的 IOPS 上限再具体调整。trx_commit 和 sync_binlog 这两个参数也放进来的原因是不同的参数组合对 IO 的压力也会有区别。通常的用法是双 1 或者 20（二零），参考官方文档的描述，双 1 在每次提交事务的时候都会刷盘，对 IO 的压力要高不少；20 则是滞后刷盘，对 IO 的压力会较小，因此写入 QPS 会高一些。

另外，可以关注到一个细节，innodb_io_capacity 的描述对象是：后台任务。这代表着 MySQL 后台的 flush，purge 操作会受到这个参数设置的限制。

测试环境

本次测试使用腾讯云服务器的高 IO 型 IT3 实例，自带了 3TB 的本地 NVME。由于腾讯云平台限制了系统版本（debian 9），因此 iostat 在输出内容上稍有差异，但是不影响分析，简单用 fio 跑了一下 16k（innodb_page_size 的默认配置） 的 IO 性能：

为什么测试环境要用一个完全不会有 IO 瓶颈的？

答：方便展示调整 MySQL 之后的效果。如果整套系统的 IO 设备负载长期处于高水位的话，最佳优化策略是升级 IO 设备，而不是调整 MySQL。因此所有的分析和应对的场景都属于中、短时间内的高 IO 负载。

IO 分析

纯写入

先看一种比较纯粹，但是较少出现的 IO 负载场景：

iostat_wo

这种类型的指标有一个明显的特点：IO 负载中没有，或者几乎没有读取相关的压力。这种负载的特征一般是缓存足够放下所有的数据，因此不需要从磁盘上读数据，压力全部在写入上。

首先能想到的，显然是trx_commit 和 sync_binlog 这两个参数，把双 1 改成 20 的配置，产生 QPS 变化的原因也比较好理解：原本一个事务需要刷一次磁盘，变成多个事务刷盘操作合并到了一起，就像是提高了每个 IOPS 的“事务处理效率”，比如从 1 事务/IOPS 变成了 N 事务/IOPS。

除了提高“每个 IOPS 的事务处理效率”以外，其实还会有另外一种思路：适当限制后台任务的 IOPS。实际上 MySQL 的写入会涉及到非常多的 buffer，log，并产生后台任务相关的数据，出现中等时间的高写入场景时，后台任务一般会慢慢堆积需要 flush 和 purge 的数据，如果 innodb_io_capacity 和 innodb_io_capacity_max 的参数设置得比较高，可能会让后台任务消耗过多的 IO 资源，这时候适当调低一些可以在一段时间内稳住写入 QPS，等高写入的压力过去之后再回滚设置。

PS：如果有更加精细化的调整方式，应该会有更好的效果，目前只能靠这个参数一刀切，不过不要改得太低，因为当后台任务堆积的数据过多，触发强制刷脏/checkpoint 等机制时，会大幅度的侵占 IO 资源，导致非常剧烈的写入 QPS 波动，这一点需要注意。

这里给出“反向调整”的效果，日志数据取自于某一个 sysbench 客户端，在 2050s 左右的时候大幅度调高了 io_capacity：

[ 2000s ] thds: 32 tps: 3992.98 qps: 15972.54 (r/w/o: 0.00/15972.54/0.00) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00
[ 2010s ] thds: 32 tps: 3972.08 qps: 15888.81 (r/w/o: 0.00/15888.81/0.00) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00
[ 2020s ] thds: 32 tps: 3882.72 qps: 15530.09 (r/w/o: 0.00/15529.99/0.10) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 2030s ] thds: 32 tps: 4015.60 qps: 16062.50 (r/w/o: 0.00/16062.50/0.00) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 2040s ] thds: 32 tps: 4014.08 qps: 16056.33 (r/w/o: 0.00/16056.33/0.00) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 2050s ] thds: 32 tps: 3572.21 qps: 14288.15 (r/w/o: 0.00/14288.15/0.00) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 2060s ] thds: 32 tps: 3826.92 qps: 15309.47 (r/w/o: 0.00/15309.47/0.00) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 2070s ] thds: 32 tps: 3869.60 qps: 15478.00 (r/w/o: 0.00/15477.90/0.10) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 2080s ] thds: 32 tps: 3692.36 qps: 14769.64 (r/w/o: 0.00/14769.64/0.00) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 2090s ] thds: 32 tps: 3844.62 qps: 15376.90 (r/w/o: 0.00/15376.90/0.00) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 2100s ] thds: 32 tps: 3720.92 qps: 14883.68 (r/w/o: 0.00/14883.68/0.00) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 2110s ] thds: 32 tps: 3720.37 qps: 14882.09 (r/w/o: 0.00/14882.09/0.00) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 2120s ] thds: 32 tps: 3835.51 qps: 15342.52 (r/w/o: 0.00/15342.52/0.00) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00

纯读取

另外一种比较纯粹的场景，自然就是纯读取了，例如：

iostat_ro

纯读取的 IO 特征说明缓存不够大，需要从磁盘读取热数据。那么增加内存和调高 innodb_buffer_pool_size，把更多的数据放到内存中就是最好的解决方案。至于需要加多少内存，可以结合实际业务 SQL 的响应时间（做好索引优化之后）和 buffer_pool 的命中率，从经验值来看，命中率（show engine innodb status里面）高于 99.5% 是比较理想的，如果实际 SQL 的响应时间不满足业务的需求，那么就可以根据实际命中率来估算需要的内存大小。

由于从 5.7 开始，MySQL 支持动态调整 innodb_buffer_pool_size 这个参数了，因此变更带来的影响相对小了很多，不过调整还是有代价的，尽量在业务低峰期操作。

读写混合

最常见的肯定是读写混合的场景，比如像这样子的：

iostat_rw

分析起来会相对复杂一点，但是结合纯读取和纯写入的分析之后，可以比较容易想到如下的可能性：

1. 读写混合的场景。
2. 纯写入的场景，但是内存放不下所有的数据，需要从磁盘读取之后再修改。

先看比较简单的场景2，本质上还是类似于纯写入场景，但是由于内存不够大，因此在排查 MySQL 的读写 SQL 比例（global status 中的 com_xxx 系列数据）之后，可以参考纯写入这个章节的内容进行分析处理。

虽然场景 1 会复杂一些，但是结合纯写和纯读的内容，分析的思路就有了，比如依次思考如下问题：

1. 业务读写比例大概是多少？
2. IO 系统的读性能问题比较大还是写性能问题比较大？

如果：

1. 业务读的比例高（例如 >4:1），IO 系统读的性能问题比较大：那么参考纯读取的内容，调高 buffer_pool_size 。
2. 业务读的比例高（例如 >4:1），IO 系统写的性能问题比较大：那么参考纯写入的内容，调整事务提交策略或者 io_capacity。另外，此类场景可能是因为在大批量变更数据，也可以考虑一下优化这种业务行为。
3. 业务写的比例高（例如 <4:1），IO 系统读的性能问题比较大：那么参考纯读取的内容。
4. 业务写的比例高（例如 <4:1），IO 系统写的性能问题比较大：那么参考纯写入的内容。
5. 业务的读写比例没有什么明显的特点，IO 系统读写的性能问题都比较严重：考虑以上所有的方法，包括升级硬件。

相关的内容

吞吐量，IOPS 和一些分散读写压力的手段

吞吐量和 IOPS ，一般情况下衡量 IO 系统性能最直观的指标，并没有特别的提及，主要原因还是判断起来很简单：如果iostat的指标已经达到或者接近了实际硬件的指标（比如达到了 75%），那么根据业务量增长的情况及早规划硬件升级或者其他的手段来分散读写压力。

常规的手段，可以简单的遵循以下场景来酌情使用：读多写少读写分离，写多读少拆库拆表加缓存。

判断 MySQL IO 情况的指标

如果 MySQL 在 IO 方面出现了阻塞的现象，那么可以观察以下几个指标：

InnoDB 还有很多其他的 read 和 write 的指标，通过show global status like '%innodb%read%'之类的操作都可以看到，但是这类指标一般是累计值，需要对比上一个取值时间的差值才能有比较实际的作用，通常也是用来判断 MySQL 的读写比例用，结合上表的 pending 数据和其他的系统指标来综合判断 IO 系统的负载。这些指标也是建议监控起来的。

总结一下

解决 IO 问题的手段是多样化的：最省事的升级硬件；最快捷的调整 MySQL（本文主要内容）；比较常用的架构调整手段（读写分离，拆库拆表）；结合实际情况来优化业务的行为（合并单行操作的 DML，拆分单个大量更新数据的 DML 语句等）。

虽然不能对上述手段进行全面的介绍，但是iostat提供的信息在分析 MySQL 瓶颈时还是非常有用的，本文仅从硬件的负载特点出发，简述了调整 MySQL 的一些思路。实际上需要多种手段结合起来才能比较好的应对 IO 方面的问题。