这事发生在上个月客户的线上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一看%util100%就急着加磁盘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.8await高达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都不低读写都在大幅进行这有点像某个大查询在疯狂扫表同时又有大量写入在刷盘。再看iotopmysqld自己占了绝大部分I/O其他进程基本可以忽略。至此可以初步判断瓶颈是MySQL进程自身的I/O放大而不是磁盘设备故障。设备没坏接下来就要去MySQL内部找原因。2.2 MySQL层看InnoDB状态和慢查询排查MySQL层我用的核心命令是SHOW ENGINE INNODB STATUS\G重点看这四块TRANSACTIONS是否有长事务、锁等待。BUFFER POOL AND MEMORY命中率、脏页比例。LOGredo 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 182394Free buffers0说明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;第二类是批量更新SQLUPDATE 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 全链路信息串起来到这里所有证据其实已经能拼出一条完整的因果链业务高峰触发大量报表查询和批量更新索引利用不充分每条SQL都要扫描几十万到几百万行。大量行扫描导致Buffer Pool读命中率下降Free buffers归零每次查询都产生物理随机读。批量更新产生大量脏页InnoDB后台刷脏线程和用户线程配合刷盘加上redo log容量不足导致频繁checkpoint写放大严重。大范围扫描和更新产生大量锁长事务阻塞purge线程undo膨胀反过来又加剧Buffer Pool和磁盘压力。最终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_requests59亿reads4500万算下来命中率约92.4%。这个数值在日常看似乎不低但考虑到业务是频繁扫全表的模式实际触底物理读的量可能并不小。更关键的是Free buffers0说明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_size4G10G扩大Buffer Pool减少物理读innodb_log_file_size128M1G减少checkpoint频率缓解写放大innodb_flush_log_at_trx_commit12降低每次提交的刷盘频率业务可接受秒级丢失innodb_io_capacity2001000提高后台刷脏能力上限innodb_io_capacity_max4002000配合上面参数让刷脏更积极long_query_time51收紧慢查询阈值方便观察效果max_connections500800缓解高峰期连接排队同时配合前端连接池优化innodb_buffer_pool_size调整到10G的依据服务器物理内存16G操作系统和运维基础进程占用大约2GMySQL自身线程和排序等内存占用约2G剩下的可用内存约为12GBuffer Pool设为10G相对稳妥还能留一些余量给数据库连接排序、临时表使用。innodb_log_file_size从128M调整到1G这一步在MySQL 8.0里需要停机操作因为修改redo log大小不再像5.7那样可以动态修改。我当时的操作顺序是干净关闭实例确保checkpoint完成。删除旧的ib_logfile*文件8.0中是#ib_logfile*。修改配置文件。启动实例确认redo log新文件生成并容量正确。innodb_flush_log_at_trx_commit2这个改动需要和业务方确认风险它的含义是事务提交时不强制刷磁盘而是每秒批量刷一次。如果数据库进程崩溃最多丢失1秒的数据。客户这个系统可以接受这个风险所以改了。如果业务对数据零丢失有强要求比如金融交易核心链路这个参数不建议动。4.2 SQL改写与索引优化第一类报表SQL的优化方案是新建复合索引让查询直接走索引覆盖ALTER TABLE order_info ADD INDEX idx_status_create_time (status, create_time);直接把status放在左边因为查询条件是等值匹配然后create_time的范围过滤在第二个字段上。数据分布上status1只有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已经有单列索引但更新过程中大量行被锁定且status1不是高区分度条件。考虑到批量更新本身的特性我推荐客户把单条更新拆成固定批次执行比如每次只更新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里使用ALGORITHMINPLACE在线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%iowait40%以上5%-10%磁盘%util100%30%-40%磁盘await200ms以上8ms-15msBuffer 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 典型问题速查表把这次排查过程中遇到的一些容易被带偏的问题整理成表格方便大家对照问题常见误解实际情况%util100%磁盘坏了或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按commandQuery筛选活跃查询按time倒序找到正在执行的长SQL或者通过sys.io_global_by_wait_by_latency直接看到MySQL内部各类I/O事件的总延迟排序比从processlist里抓包更系统化。第三调参不要一次性全改。每改一个或一组参数至少观察半个到一个业务周期记录前后对比。有时候一次改太多出了新问题或者恢复不明显很难判断是哪个参数生效的。这个案例里如果我先去掉慢查询治理只调参数指标也能改善一部分但SQL扫描放大还在很快还是会撞上新的瓶颈。第四批量任务必须错峰。业务高峰期跑全量报表和批量更新在数据库设计层面就是错误的。哪怕SQL都优化好了也要错峰避免同一时刻所有压力叠加。innodb_flush_log_at_trx_commit2的变更务必要和业务确认可接受的数据丢失窗口别自己擅自做决定。回到这次的故障本身我个人最大的体会是MySQL的I/O问题大多数时候不是I/O问题而是SQL和存储引擎参数之间的一种失衡状态。磁盘只是缓冲池崩溃、长事务堆积、redo log刷盘压力的“最终受害者”。排查的时候先稳住心态从系统层取证再到MySQL内部验证把证据链拉全了再动手。很多时候列完清单就会发现答案已经自己浮出来了。最后再分享一个小技巧在故障处理完成后保留当时的SHOW ENGINE INNODB STATUS输出、iostat日志和慢查询快照归档到故障复盘文档里。下一次遇到类似问题直接对照旧数据做差异分析能省掉至少一半的排查时间。
