MySQL高负载I/O故障根因分析：从系统层到InnoDB的排查与优化

这事发生在上个月，客户的线上MySQL实例连续两天在业务高峰时段崩溃报警，从应用侧看就是大量请求超时，接口P99延迟从原本的80ms直接飙到3s以上。我看了一眼监控面板，CPU 80%以上，磁盘I/O util触顶100%，iowait一度超过40%，典型的MySQL高负载I/O故障。

这类问题的麻烦之处在于，表面是“磁盘扛不住”，但根子通常不在磁盘本身。我先把这个案例的完整排查过程、根因链路、优化动作和踩过的坑全部记录下来，尽量还原当时的判断逻辑和操作细节，给遇到类似场景的朋友一个可以参照的分析路径。

1. 故障现象与影响范围

1.1 业务侧表现

客户业务是典型的互联网SaaS应用，MySQL单实例，8核16G内存，数据量约200G，跑在云厂商的SSD云盘上。高峰期连接数大概300左右，读多写少。故障发生的时间点集中在每天上午10:00到11:30，以及下午14:00到16:00，恰好是业务方批量任务和数据报表的集中时段。

现象分几层：

• 应用侧：接口超时，报错日志出现大量Lock wait timeout exceeded; try restarting transaction以及Deadlock found when trying to get lock。
• 数据库侧：show processlist看到大量Waiting for table level lock和Updating状态的会话，慢查询日志每分钟刷出上百条执行时间超过5s的SQL。
• 系统侧：top显示CPU us和wa双高，iostat确认磁盘%util长期100%，await达到200ms以上，而正常情况下这个值应该在10ms以内。

这里有个容易被忽略的点：很多DBA一看%util=100%就急着加磁盘IOPS、加机器，但实际上一张SSD的随机读写能力在满压力下也不至于把200G的业务库打成这样。问题一定出在“SQL层把I/O请求放大了”或者“MySQL内部刷脏机制异常”，磁盘只是个背锅的。

1.2 影响评估与优先级判断

我当时的处理顺序是：先止血，再排查根因。所谓止血，就是先把业务影响降下来，比如把批量任务错峰、临时杀掉长时间挂起的慢查询、调整部分只读流量到备库。这个案例比较幸运，客户有一套只读备库，我直接把报表类查询切到备库，主库的压力立刻下降了一部分。

但临时切流量只是缓兵之计，因为主库自身的写入链路还是有问题，不把根因找到，批量任务一恢复，故障马上又会反弹。所以切完流量后，我立刻开始做全链路排查。全链路的意思是：系统的CPU、内存、磁盘、网络、MySQL的Buffer Pool、redo log、锁、SQL执行计划，全部串起来看，缺一环都可能会错判方向。

具体排查节点和判断标准，下面分开写。

2. 全链路排查：从操作系统到MySQL内部

2.1 系统层：先确认I/O瓶颈的性质

排查的第一步是确认瓶颈到底是“读”还是“写”，是“随机”还是“顺序”。判断依据主要来自iostat和vmstat。

我在现场执行了：

iostat -x 1 10 vmstat 1 10 iotop -o top -H -p $(pgrep -x mysqld)

关键输出摘录（节选）：

Device rrqm/s wrqm/s r/s w/s rkB/s wkB/s await svctm %util vda 0.00 12.5 312.3 215.6 5632.1 9182.4 216.3 3.2 99.8

await高达216ms，但svctm只有3.2ms，这个数据非常关键。svctm反映的是设备自身处理一个I/O请求的时间，3.2毫秒说明磁盘硬件本身并没有坏，慢的原因是I/O请求在排队。也就是说，磁盘的队列被塞满了，请求在队列里等待的时间远大于实际处理时间。

接着看vmstat的r列（运行队列）和b列（阻塞进程）：

procs -------memory------- ---swap-- ---io---- --system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 9 3 0 1024000 204800 4194304 0 0 8332 12800 18000 42000 38 12 30 20

运行队列9，说明CPU已经过载；但wa只有20%，不是纯I/O等待——这就说明问题不只是磁盘，CPU资源也吃紧，二者是叠加的。bi（读入块）8332KB/s和bo（写出块）12800KB/s都不低，读写都在大幅进行，这有点像某个大查询在疯狂扫表，同时又有大量写入在刷盘。

再看iotop：mysqld自己占了绝大部分I/O，其他进程基本可以忽略。至此可以初步判断：瓶颈是MySQL进程自身的I/O放大，而不是磁盘设备故障。设备没坏，接下来就要去MySQL内部找原因。

2.2 MySQL层：看InnoDB状态和慢查询

排查MySQL层，我用的核心命令是SHOW ENGINE INNODB STATUS\G，重点看这四块：

• TRANSACTIONS：是否有长事务、锁等待。
• BUFFER POOL AND MEMORY：命中率、脏页比例。
• LOG：redo log的使用和刷盘情况。
• ROW OPERATIONS：正在执行的row操作数量，辅助判断是否有大片扫描。

故障期抓到的关键片段：

BUFFER POOL AND MEMORY ---------------------- Buffer pool size 1048576 Free buffers 0 Database pages 1048562 Old database pages 0 Modified db pages 182394

Free buffers=0说明Buffer Pool全部页面都被用完了，Modified db pages（脏页）还有18万，意味着有大量修改还没刷到磁盘。InnoDB的后台线程此时一定在拼命刷盘，这会直接造成写放大。

TRANSACTIONS部分出现了大量的锁等待记录，有些事务打开时间超过5分钟。这种长事务会堵住purge线程，导致undo log膨胀，历史版本不能被清理，进一步加剧Buffer Pool压力。

慢查询日志是另一个突破口。我开了slow_query_log，并临时把long_query_time调到0.5s抓采样，结果发现两类SQL占了80%以上：

第一类是报表统计SQL，典型长这样：

SELECT COUNT(*) FROM order_info WHERE status = 1 AND create_time BETWEEN '2024-11-01 00:00:00' AND '2024-11-30 23:59:59';

第二类是批量更新SQL：

UPDATE order_info SET status = 2 WHERE user_id = 12345 AND status = 1;

这两类SQL乍一看都很正常，有where条件，但实际上都踩了大坑。第一类的create_time虽然建了单列索引，但status没索引，MySQL的执行计划选择的是create_time索引回表，然后逐行过滤status，几百万行回表就是几百万次随机读。第二类的user_id和status建了联合索引吗？没有，只有user_id单列索引，于是更新时先把user_id对应的所有行都捞出来，再逐行过滤status，同样是大范围扫描。

这种“索引设计不合理 + 大范围扫描 + 写操作”的组合，会在同一时间窗口内产生大量内存页被修改、大量脏页、大量随机读，直接把I/O打爆。

2.3 全链路信息串起来

到这里所有证据其实已经能拼出一条完整的因果链：

1. 业务高峰触发大量报表查询和批量更新，索引利用不充分，每条SQL都要扫描几十万到几百万行。
2. 大量行扫描导致Buffer Pool读命中率下降，Free buffers归零，每次查询都产生物理随机读。
3. 批量更新产生大量脏页，InnoDB后台刷脏线程和用户线程配合刷盘，加上redo log容量不足导致频繁checkpoint，写放大严重。
4. 大范围扫描和更新产生大量锁，长事务阻塞purge线程，undo膨胀反过来又加剧Buffer Pool和磁盘压力。
5. 最终CPU和磁盘双双过载，应用超时。

这条链路里，任何一个环节单拎出来都不是大问题，但叠加在一起就形成了恶性循环。这也解释了为什么很多人在遇到类似问题时只优化SQL或只换磁盘都没用——因为不是单一原因。

3. 根因定位：三个关键问题的联动效应

3.1 Buffer Pool命中率过低

排查过程中我计算了Buffer Pool的命中率，公式很简单：

命中率 = (Pages_read - Pages_created - Pages_written差量) / Pages_read，实际运维中更常用的是： 命中率 = (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests

通过SHOW GLOBAL STATUS拿到两个关键计数：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

当时的两个值大约是：read_requests=59亿，reads=4500万，算下来命中率约92.4%。这个数值在日常看似乎不低，但考虑到业务是频繁扫全表的模式，实际触底物理读的量可能并不小。更关键的是Free buffers=0，说明Buffer Pool已经一点空闲空间都没有了，所有空闲页都被挤占，每次载入新页时必须淘汰旧页，导致“读—淘汰—再读”的颠簸。

另一个指标是Innodb_buffer_pool_wait_free，如果这个值很大，说明后台刷脏跟不上用户线程分配页面的速度，用户线程会被迫等待刷脏完成。这个案例里这个值在故障阶段持续增长，是明确的信号。

3.2 redo log容量过小导致频繁checkpoint

这是这个案例里最容易被忽略的问题。客户的innodb_log_file_size只有128MB——这其实是很多MySQL安装包和云数据库的默认配置。当业务产生大量写入时，128MB的redo log非常容易写满，InnoDB只能强制推进checkpoint来覆盖旧日志，每次checkpoint都要把对应脏页刷到磁盘，刷不过来时就会阻塞用户线程。

用一个粗算来理解这个容量问题：假设一批批量更新每小时产生约2GB的写入，那么平均每秒写入约560KB，在峰值时每秒可达5MB以上。而128MB的redo log容量，即使全部处于可复用状态，大约几十秒就会被写满，如果脏页刷盘速度跟不上，整个写入链路就会出现周期性停顿。

复现问题很简单，SHOW ENGINE INNODB STATUS里查看LOG部分，如果看到类似Log sequence number和Log flushed up to之间的差值，长期维持在log_file_size的70%以上，或者出现的checkpoint age经常显示达到配置上限，就能确认redo log偏小。

3.3 索引设计缺陷造成扫描放大

索引问题前面已经提到了两个典型场景，我再补一个当时挖出来的细节：报表SQL里的status和create_time条件，在MySQL优化器眼里存在选择性误判。

order_info表当时的数据量约1200万行，status字段的分布是：1表示待处理，约100万行；2表示处理中，约50万行；3表示已完成，约1050万行。优化器基于show index的区分度估算，认为通过create_time过滤能减少更多行，就选择了create_time索引。但实际上很多用户在月初批量创建订单，create_time在一个月内的数据分布极不均匀，11月的某一周就占了500万行，于是这个选择直接导致了500万行回表。

这种情况靠优化器自己修正很难，因为它用的是基数统计估算，不是实际数据分布。最有效的做法就是给出更好的复合索引，让优化器“被迫”走我们设计好的路径。

3.4 三因素联动形成“I/O风暴”

当上面三个问题同时存在时的表现是：大查询和大更新并行涌入，Buffer Pool颠簸，损坏的redolog触发频繁checkpoint，锁等待加剧长事务，长事务又导致undo无法清理，undo占用Buffer Pool空间，进一步加剧颠簸。整个系统等于同时发生了“读放大”“写放大”“锁等待”三层叠加。这就是为什么系统层看上去是I/O问题，但只加IOPS完全无效——因为瓶颈已经不在磁盘吞吐，而在MySQL内部对磁盘请求的无限放大。

4. 优化实施：参数调优与SQL改写的完整过程

4.1 参数调整的具体计算和配置

优化分两大部分，一部分是实例参数调整，另一部分是SQL和索引改写。参数调整我找客户沟通后，按冷热分批处理，避免一次性大改导致难以评估效果。

参数调整的核心项如下：

参数名	原值	调整后	调整原因
innodb_buffer_pool_size	4G	10G	扩大Buffer Pool，减少物理读
innodb_log_file_size	128M	1G	减少checkpoint频率，缓解写放大
innodb_flush_log_at_trx_commit	1	2	降低每次提交的刷盘频率（业务可接受秒级丢失）
innodb_io_capacity	200	1000	提高后台刷脏能力上限
innodb_io_capacity_max	400	2000	配合上面参数，让刷脏更积极
long_query_time	5	1	收紧慢查询阈值，方便观察效果
max_connections	500	800	缓解高峰期连接排队（同时配合前端连接池优化）

innodb_buffer_pool_size调整到10G的依据：服务器物理内存16G，操作系统和运维基础进程占用大约2G，MySQL自身线程和排序等内存占用约2G，剩下的可用内存约为12G，Buffer Pool设为10G相对稳妥，还能留一些余量给数据库连接排序、临时表使用。

innodb_log_file_size从128M调整到1G，这一步在MySQL 8.0里需要停机操作，因为修改redo log大小不再像5.7那样可以动态修改。我当时的操作顺序是：

1. 干净关闭实例，确保checkpoint完成。
2. 删除旧的ib_logfile*文件（8.0中是#ib_logfile*）。
3. 修改配置文件。
4. 启动实例，确认redo log新文件生成并容量正确。

innodb_flush_log_at_trx_commit=2这个改动需要和业务方确认风险，它的含义是事务提交时不强制刷磁盘，而是每秒批量刷一次。如果数据库进程崩溃，最多丢失1秒的数据。客户这个系统可以接受这个风险，所以改了。如果业务对数据零丢失有强要求，比如金融交易核心链路，这个参数不建议动。

4.2 SQL改写与索引优化

第一类报表SQL的优化方案是新建复合索引，让查询直接走索引覆盖：

ALTER TABLE order_info ADD INDEX idx_status_create_time (status, create_time);

直接把status放在左边，因为查询条件是等值匹配，然后create_time的范围过滤在第二个字段上。数据分布上status=1只有100万行，相比原来走create_time索引回表5万到500万行不等，扫描量少了一个数量级。而且如果查询只需status和create_time两个字段，等于可以做覆盖索引扫描，连回表都省了。

第二类批量更新SQL的优化方案是：

UPDATE order_info SET status = 2 WHERE user_id = 12345 AND status = 1;

这条SQL的坑在于user_id已经有单列索引，但更新过程中大量行被锁定，且status=1不是高区分度条件。考虑到批量更新本身的特性，我推荐客户把单条更新拆成固定批次执行，比如每次只更新500行：

UPDATE order_info SET status = 2 WHERE user_id = 12345 AND status = 1 LIMIT 500;

循环执行，每批次之间sleep 50ms到100ms。这样做的核心价值是：把一次大事务拆成多个小事务，缩短锁持有时间，降低redo log瞬时压力，也让binlog同步不会在备库上产生堆积。

除了改写SQL，还在where条件上增加了create_time BETWEEN ... AND ...的缩圈条件，让更新范围进一步缩小。这个改动需要业务方确认数据口径，因为批量任务有明确的时间窗口。

4.3 实施顺序与灰度方案

我做这类优化有一条原则：先把能止血的做了，再把需要验证的做了，最后做需要停机的。实际执行顺序如下：

第一步，先把只读流量切到备库，主库的查询压力立刻下降30%。这一步用了大约5分钟。

第二步，创建新索引idx_status_create_time。这个操作在MySQL 8.0里使用ALGORITHM=INPLACE，在线DDL不锁表，不会阻塞业务，但会消耗一定I/O。选择在业务低谷期执行，耗时约3分钟。执行期间同步观察I/O和复制延迟，确认没有明显影响。

第三步，批量更新脚本改造。这个需要业务代码配合，按前面说的分批次带sleep方式改造，然后灰度跑一个批次任务验证。

第四步，参数调整中需要停机的innodb_log_file_size、需要重启的innodb_buffer_pool_size，放在凌晨2点到3点的维护窗口执行。

参数调整的先后顺序也有讲究：先扩Buffer Pool，再调redo log，因为扩Buffer Pool后脏页容量大了，如果redo log还很小，会导致更频繁的checkpoint，反而放大写压力。所以这两个参数必须一起调整。

5. 优化效果验证与监控告警完善

5.1 优化前后的指标对比

优化完成后观察了一周，核心指标对比如下：

指标	优化前（高峰）	优化后（高峰）
CPU使用率	80%-95%	35%-50%
iowait	40%以上	5%-10%
磁盘%util	100%	30%-40%
磁盘await	200ms以上	8ms-15ms
Buffer Pool命中率	92.4%	99.6%
慢查询（>1s）	每分钟上百条	每分钟0-2条
应用P99延迟	3s以上	100ms以内

%util从100%降到30%-40%，await回到10ms左右，这已经恢复了正常的SSD水平。性能上还有一个很直观的体现：原本每天上午10点的批量任务要跑40分钟，优化后只需要9分钟。

通过监控对比可以确认，之前判断的“I/O风暴”确实是由SQL扫描放大和redo log容量不足共同引发的。磁盘设备和云盘能力从始至终都不是瓶颈，这也验证了排查思路的正确性。

5.2 监控体系的持续完善

故障处理完了，监控也得跟上，否则下次还会在同样的问题上栽跟头。我给客户补齐了以下监控项：

• Free buffers低于10%时触发告警。
• Innodb_buffer_pool_wait_free连续5分钟大于0时触发告警。
• Threads_running超过50时触发告警。
• 磁盘await超过20ms持续10分钟触发告警。
• checkpoint age高于redo log容量限制的70%时触发告警。
• 慢查询数量每分钟超过10条时触发告警。

Threads_running这个监控项特别值得提，很多团队只盯CPU和连接数，但Threads_running反映的是MySQL当前真正在执行任务的线程数，如果长期大于CPU核数的2倍，说明SQL队列拥堵严重，这个指标比连接数更能反映数据库健康度。

6. 常见问题与排查技巧实录

6.1 典型问题速查表

把这次排查过程中遇到的一些容易被带偏的问题整理成表格，方便大家对照：

问题	常见误解	实际情况
%util=100%	磁盘坏了或IOPS不够	往往是SQL层请求放大导致排队
await很高	磁盘老化	可能是I/O请求在队列中等待
Buffer Pool命中率92%	已经足够	高扫描场景下需要追踪Free buffers和wait_free
慢查询多	加索引就行	需要先看执行计划，避免索引被绕过
redo log偏小	不是问题	批量写入场景下会引发频繁checkpoint，放大写压力
杀死慢查询	恢复即可	如果事务被回滚，undo清理和回滚本身会加剧压力

6.2 几个实际操作心得

最后一个部分，分享几个我在这次故障处理过程中得到的具体经验：

第一，遇到I/O故障先看svctm和await的关系，再判断是设备问题还是排队问题。如果svctm正常但await高，基本可以排除硬件故障，不用先折腾云盘。

第二，启用performance_schema和sys库，很多工作可以做得更轻松。比如通过sys.session按command='Query'筛选活跃查询，按time倒序找到正在执行的长SQL，或者通过sys.io_global_by_wait_by_latency直接看到MySQL内部各类I/O事件的总延迟排序，比从processlist里抓包更系统化。

第三，调参不要一次性全改。每改一个或一组参数，至少观察半个到一个业务周期，记录前后对比。有时候一次改太多，出了新问题或者恢复不明显，很难判断是哪个参数生效的。这个案例里，如果我先去掉慢查询治理只调参数，指标也能改善一部分，但SQL扫描放大还在，很快还是会撞上新的瓶颈。

第四，批量任务必须错峰。业务高峰期跑全量报表和批量更新，在数据库设计层面就是错误的。哪怕SQL都优化好了，也要错峰，避免同一时刻所有压力叠加。innodb_flush_log_at_trx_commit=2的变更，务必要和业务确认可接受的数据丢失窗口，别自己擅自做决定。

回到这次的故障本身，我个人最大的体会是：MySQL的I/O问题大多数时候不是I/O问题，而是SQL和存储引擎参数之间的一种失衡状态。磁盘只是缓冲池崩溃、长事务堆积、redo log刷盘压力的“最终受害者”。排查的时候先稳住心态，从系统层取证，再到MySQL内部验证，把证据链拉全了再动手。很多时候，列完清单就会发现答案已经自己浮出来了。

最后再分享一个小技巧：在故障处理完成后，保留当时的SHOW ENGINE INNODB STATUS输出、iostat日志和慢查询快照，归档到故障复盘文档里。下一次遇到类似问题，直接对照旧数据做差异分析，能省掉至少一半的排查时间。