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

前言

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

怎么看懂 IO 指标

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

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	16000	定义了后台任务可用的 IOPS 量
innodb_io_capacity_max	32000	定义了后台任务可用的最大 IOPS 量
innodb_flush_log_at_trx_commit	1	控制事务的提交策略，具体信息请参考官方文档
sync_binlog	1	控制 binlog 落盘的频率，具体信息请参考官方文档

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 性能：

类型	IOPS	吞吐量（MB）
随机读	121959	1905
随机写	98326	1536
随机读写（读部分）	47129	750
随机读写（写部分）	47152	754

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

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

IO 分析

纯写入

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

这种类型的指标有一个明显的特点：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：

代码语言：txt复制

[ 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

纯读取

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

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

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

读写混合

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

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

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

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

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

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

如果：

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

参数名	意义	备注
Innodb_data_pending_fsyncs	当前阻塞的 fsync 操作	一般为 0，比较高的话，看一下 innodb_flush_method 的设置
Innodb_data_pending_reads	当前阻塞的 read 操作	一般为 0，如果指标较高且影响业务的话，参考读压力的应对方式
Innodb_data_pending_writes	当前阻塞的 write 操作	一般为 0，如果指标较高且影响业务的话，参考写压力的应对方式
Innodb_os_log_pending_fsyncs	写 redo log 时，当前阻塞的 fsync 操作	一般为 0，如果大于 0 的话，通常就是 IO 设备的瓶颈，考虑把 redo log 迁移到 SSD 或者做 IO 隔离，独占 IO 设备的性能
Innodb_os_log_pending_writes	写 redo log 时，当前阻塞的 write 操作	一般为 0，如果指标较高且影响业务的话，参考写压力的应对方式