凌晨两点十六分监控大屏上的MySQL IOPS曲线突然拉成一条垂直的直线告警声把值班室的安静撕得粉碎。那条从10点开始缓慢抬升的紫色线条在那一刻直接冲上了磁盘性能的上限刻度数据库的活跃会话数同步飙到400大量业务请求开始排队等待前端接口响应时间从几十毫秒瞬间恶化到十秒级别。我拔掉耳机、切到工位准备面对的不仅仅是一个SQL慢查询而是一场典型的MySQL高负载I/O故障。这类问题最磨人的地方在于现象非常一致但引发原因的路径却五花八门——可能是某条被漏掉索引的SQL也可能是连接池爆炸后带来的临时表风暴还可能是InnoDB刷盘策略在特定业务模型下的失配。这篇文章我会按当时的排查手法从操作系统到MySQL内部再到SQL语句完整拆解一遍全链路定位过程和最终落地的优化方案把能够直接抄作业的命令、参数和判断逻辑都留下来。这类故障适合所有正在维护MySQL生产环境的DBA、后端开发以及负责基础设施的运维同学。我的建议是不要跳着读因为每一步排查都是下一步分析的判据缺失一步就可能把方向带偏。1. 故障初现高负载 I/O 故障的前20分钟1.1 我看到的报警数据和第一反应监控面板上的第一波告警来自三个层面云监控的主机磁盘IOPS达到每秒32000接近云盘规格的写入上限MySQL自身的Threads_running持续超过300业务监控显示订单查询接口P99耗时从120ms涨到8.7秒。这三个信号同时出现时我第一反应不是去看慢查询日志而是先确认一件事——数据库是不是还活着应用侧是否已经在堆积请求。如果应用不熔断后面的排查动作再快都会被持续涌入的流量干扰。我快速执行了一组基础命令确认当前状态mysql SHOW GLOBAL STATUS LIKE Threads_running; mysql SHOW GLOBAL STATUS LIKE Threads_connected; mysql SHOW ENGINE INNODB STATUS\G当时输出里Threads_running是342Threads_connected接近1800InnoDB的History List Length已经超过12万。这三个数字基本可以定性连接数被占满有大量请求在短暂排队而InnoDB的undo日志积累说明可能存在长事务阻塞了purge线程。更糟糕的是History List Length偏高自身的后台任务也在增加I/O消耗等于火上浇油。此时我做的第一件事不是分析慢SQL而是把连接数上限临时调大并让应用侧把非核心业务的定时任务停掉。这不是根治但能先把业务请求的拥堵面控制住。就像你家里水管爆了第一件事不是研究漏水原因而是先关掉总阀。停掉几个后台统计任务后Threads_running回落到120左右虽然还是很高但至少给了我一个相对安静的环境做下一步反推。1.2 快速圈定问题范围先“止血”再“断根”故障处理有个原则先恢复后定位。因为在高负载状态下你看到的性能数据很多都是“果”而不是“因”。比如I/O打满可能是某个大查询把数据页全部读进缓冲池把其他正常业务的缓存全部挤掉导致后续查询全部走磁盘。这时候如果盯着I/O指标去优化磁盘性能方向就错了。我在止血阶段的动作供大家参考临时调大thread_cache_size和max_connections避免新连接直接被拒。暂停非核心的报表、定时任务、消息队列消费进程。把应用侧的数据库连接池最大等待时间从5秒缩短到2秒快速失败防止雪崩。抓取当前正在执行的SQL跳过慢查询日志直接看实时会话。SELECT id, user, host, db, command, time, state, LEFT(info, 200) AS sql_text FROM information_schema.processlist WHERE command ! Sleep AND time 10 ORDER BY time DESC LIMIT 30;就是这个实时查询让我看到了一个熟悉的面孔一个用于后台数据补录的长事务正在更新一张1600万行的历史订单表UPDATE条件里的时间范围字段没有走索引导致InnoDB需要扫描大量数据页并频繁加锁。更麻烦的是这个长事务一直不提交积压的undo信息直接让History List Length疯涨purge线程跟不上I/O又被事务刷盘和读取双重施压。到这里故障范围基本锁定到“慢SQL触发的连锁反应”而不是存储层或硬件故障。2. 全链路排查思路从硬件到 SQL 的分层剥离2.1 第一层操作系统与存储的 I/O 能力基线很多人遇到MySQL I/O高负载直接就去分析SQL其实应该先花两分钟确认底层没有被“架空”。所谓底层就是操作系统和存储是否真的达到了能力上限。如果你的系统是物理机要先看磁盘是不是有坏道或RAID重建如果是云盘要看有没有突发流量被限流如果是虚拟机还要排除宿主机邻居的干扰。我当时在服务器上跑了这三组命令iostat -x 1 5 vmstat 1 5 pidstat -d 1 5关键指标有三个util、await、svctm。util接近100%说明磁盘确实忙不过来await超过30ms说明请求排队严重svctm如果远小于await说明瓶颈在请求队列而非磁盘介质本身。当时我的机器iostat输出里sda的util是99.8%await高达85ms但svctm只有2.3ms。这个数据非常关键——它告诉我磁盘硬件本身不慢慢在请求堆积数量实在太多I/O调度器把队列塞满了。这个判断直接排除了“换磁盘”这个错误方向把问题的根因继续压回MySQL和SQL层。顺带说一句用pidstat看进程I/O时要重点看mysqld的每秒读写字节数和每秒读写次数如果读次数远大于写次数那基本是缓存命中率出了问题大量读请求落盘如果写次数高则优先怀疑刷盘策略、双写缓冲、binlog写入等链路。2.2 第二层MySQL 内部的 I/O 压力分布确认磁盘不背锅后下一步要看I/O压力究竟集中在InnoDB的哪个环节。MySQL提供的信息非常丰富但别一上来就翻Performance Schema全套先看几个最直接的计数器。SHOW GLOBAL STATUS LIKE Innodb_data_reads; SHOW GLOBAL STATUS LIKE Innodb_data_reads; SHOW GLOBAL STATUS LIKE Innodb_pages_read; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads;通过Innodb_buffer_pool_read_requests逻辑读和Innodb_buffer_pool_reads物理读的比值可以快速算出缓冲池命中率。公式是(1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100。正常情况下这个值应该稳定在99%以上如果跌到95%以下说明巨大量的数据请求没有命中缓存全部落到磁盘。故障发生时这个命中率一度只有91%这已经是非常严重的信号。还要看InnoDB的日志写入量SHOW GLOBAL STATUS LIKE Innodb_os_log_written; SHOW GLOBAL STATUS LIKE Innodb_log_write_requests;当时的log写入量吓人平均每秒超过45MB说明大量UPDATE/DELETE操作在写redo log而且是低频大范围更新的典型特征。再加上之前看到的History List Length偏高我基本断定有一个大事务长时间不提交改了很多行又不释放锁导致其他写入事务需要等待InnoDB层为了保障持久性不断刷redo。刷盘、读数据页、做undo清理三股I/O挤在一起。这个阶段的结论是MySQL内部I/O压力主要来自“物理读增加”和“日志写入放大”这两个问题都能从SQL层面找到源头。2.3 第三层SQL 语句与索引效率审计回到最开始抓到的那个UPDATE语句我把它单独拿出来做执行计划分析EXPLAIN UPDATE order_archive SET process_status 3, retry_count retry_count 1 WHERE create_time BETWEEN 2024-11-01 00:00:00 AND 2024-11-30 23:59:59 AND source_channel API;结果中type是ALLrows估算1240万行filtered只有0.05%Extra里出现了Using where。这意味着MySQL要逐行扫描一千多万行数据然后判断两个条件是否满足。更糟的是这个表上有idx_create_time索引但SQL写法让优化器放弃了这个索引——原因在于source_channel API的可选择性被分析器认为太低同时两个条件本身存在一定的相关性优化器自己“算了一笔账”认为扫全表比走索引再回表更划算。这种选择在数据分布正常时可能是对的但配合长时间不提交、行级锁积累、undo膨胀就会演变成一场灾难。我还看了一遍这只表上的索引列表发现一个特别典型的通病索引数量有12个其中有3个是冗余索引比如idx_create_time和idx_channel_create_time后者完全能覆盖前者。这些冗余索引平时只拖慢写入速度但在这次故障中因为那个UPDATE要更新非索引列所有二级索引都要跟着维护每改一行就得写好几个索引页I/O放大倍数非常可观。删除冗余索引对写入型I/O的缓解立竿见影。SQL审计这块我建议每半年做一次全量慢查询日志分析找出来两类语句一类是执行次数不多但单次消耗恐怖的“胖查询”另一类是单次消耗小但调用频率极高的“瘦循环”。这次故障的元凶就属于前者而后者的危害往往在连接数和CPU上先爆发最终也会传导成I/O压力。3. 根因定位与优化落地3.1 最大的坑一个低频业务查询把缓冲池“洗”了一遍真正让我额头冒汗的不是那个长UPDATE本身——毕竟它更新完几百行后等不到锁就直接报错回滚了。真正的问题出在它回滚之后由于事务回滚需要把之前写入的undo页重新读出来做逆向操作这个过程会读取海量历史数据页直接把这些数据页“洗”进缓冲池把原本缓存的高热业务数据全部淘汰出去。这就是缓冲池抖动。缓冲池抖动造成的最直接后果是之前命中缓存的订单查询、用户信息查询等高频操作在一瞬间全部失去缓存每一个请求都要去磁盘读数据页。而当前连接池又特别大几百个请求同时去读I/O队列瞬间打满。这种“故障演变成故障”的连锁反应比原始SQL的危害大得多。为了验证这个判断我查了Performance Schema中的等待事件汇总SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_seconds FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME LIKE wait/io/file/innodb/% ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;结果中文件读取等待时间排名第一占比接近72%这印证了“大量物理读导致磁盘队列堵塞”的判断。InnoDB的缓冲池命中率也在持续下降一度跌破90%。到这一步故障的完整链路基本闭合低效UPDATE → 全表扫描 → 长时间不提交 → undo膨胀 → 回滚时大量读页 → 缓冲池被污染 → 全局缓存命中率暴跌 → 高频业务SQL全部落盘 → 磁盘I/O打满 → 整个数据库响应恶化。这个链路里最值得记笔记的是优化SQL不能只看执行计划的type是不是ALL还要考虑这条SQL对InnoDB缓冲池的影响半径。有的SQL本身执行只要几秒但它引发的缓存抖动可能让整个实例几分钟内都缓不过来。3.2 索引与连接池的双重治理根因确认后我在变更窗口里按顺序执行了以下优化动作整个过程中没有重启数据库第一步终止并回滚那个长事务。由于回滚本身就是负担我采用了渐进式终止策略——先从performance_schema中找到该事务对应的thread_id然后通过KILL QUERY终止正在执行的语句宁可让它慢慢回滚也不直接KILL连接。直接KILL连接有时也会触发崩溃恢复场面更不可控。第二步重建索引。删掉了两个冗余索引idx_create_time和idx_source_channel保留idx_channel_create_time作为联合索引。同时把应用侧SQL的条件顺序调整为source_channel API AND create_time BETWEEN ...让优化器能更平滑地使用联合索引的最左前缀。第三步优化连接池。应用侧使用的是HikariCPmaximum-pool-size原本配到了200对单机MySQL来说这个数字高得离谱。连接数越大并发争用越多且MySQL内部有些锁容易在400个线程同时干活时爆发。我把连接池调整为maximum-pool-size50minimum-idle10并开启连接泄漏检测。对于多实例部署的应用50并不是固定的标准值核心是“够用且有冗余”——通常按高峰并发数的两倍再加一点余量来估算但这个余量不能把数据库直接打成过载。这三步做完之后Threads_running回落到个位数物理读数量断崖式下降。不过立即把连接池从200调到50初期会有少量请求因为等待获取连接而超时所以这种调整建议放在业务低峰期做而且要和应用开发同步确认连接池的最大等待时间可以容忍。3.3 写入链路的参数调整优化完SQL和索引I/O还是有一些周期性尖刺。我继续做了参数层面的深挖这次的目标是InnoDB的刷盘和日志策略。原生产环境使用的是普通SATA SSD但故障期间的写入模式非常激进innodb_flush_log_at_trx_commit1。这个参数意味着每次事务提交都要把redo log刷到磁盘保证可靠性但增加I/O。业务允许极端情况下丢失1秒数据所以我把该参数调整为2让MySQL在提交时只把日志写入操作系统缓存每秒由后台刷一次盘。别小看这个改动对高并发写入场景它能减少70%以上的磁盘sync次数。另一个被多数人忽略的参数是innodb_io_capacity和innodb_io_capacity_max。默认值分别是200和2000但如果你的磁盘本身能支持10000 IOPSMySQL的刷页线程会变得异常保守——它以为自己只有200的I/O能力宁可让脏页堆积也不积极刷新。脏页堆积到阈值后又会触发compulsory flush风暴然后就是间歇性的I/O尖峰。我把它们分别调整到1000和4000同时把innodb_buffer_pool_size从8GB扩大到14GB服务器可用内存32GB给缓冲池更多空间。参数调整不是越多越好。我特意没有碰innodb_doublewrite和innodb_flush_method因为这台服务器的存储层和数据安全要求禁不起这两种参数的误调。如果未来有条件做存储层改造我会优先把redo log放到独立的NVMe盘上这样才能从物理层面彻底隔离日志写入和随机读。4. 优化后的效果与实测数据4.1 优化前后关键指标对比故障处理结束后的第三天我拉了一周的监控数据做前后对比。最直观的变化是I/O负载曲线磁盘util从持续98%以上降到35%左右IOPS峰值从32000降到7000磁盘await从85ms降到6ms以下。这些数字说明I/O不再是瓶颈MySQL的请求队列终于能快速消化。缓冲池命中率重新回到99.4%左右。这一点对业务性能的影响比I/O还大因为一旦命中率恢复绝大部分查询根本不需要触达磁盘响应时间自然垮不下来。Threads_running稳定在20以内Threads_connected维持在60左右连接池几乎没有出现排队。慢查询也是个有效的体检指标。优化前慢查询日志每十分钟就能扫出近200条长SQL优化后超过1秒的SQL基本消失只剩几条凌晨批处理的定时任务偶尔超过500ms。整体效果是可以明确量化的指标故障时优化后磁盘util99.8%32%IOPS峰值320007000awaitms855.8缓冲池命中率90.6%99.4%Threads_running34218P99接口耗时ms8700964.2 业务侧感知变化对业务方来说最明显的感知不是某个数字的降低而是“夜间脚本任务不再互相打架了”。以前每逢整点数据对账订单查询接口就会出现明显的毛刺我都以为是对账本身数据量大应该加机器。优化之后发现根源其实就是那个低频UPDATE把缓存污染了连带整点对账的所有查询全部打到磁盘。削掉这个毒瘤后同样的对账任务执行时间从原来的40分钟降到6分钟而且业务侧零感知。这个案例也让我得到一个经验业务侧感知的“慢”往往是聚合后的表象DBA不能只看平均值要看峰值期间的缓存命中率和I/O队列长度。曾经有开发同事提过来“加一台只读从库分摊读压力”被我劝住了。因为主库的读压力根本不是正常业务读导致的而是异常SQL引发的缓存抖动加从库只会让架构复杂度变高纯属花冤枉钱。5. 高负载 I/O 故障的预防与巡检清单5.1 日常巡检命令与阈值建议没有故障预案的巡检等于裸奔。我把自己常用的巡检三件套分享出来每两天跑一次只需五分钟就能发现问题苗头。第一件套是慢查询日志聚合。通过pt-query-digest对慢查询日志做按周聚合重点看rows_examined和rows_sent的比例。如果某条SQL扫描行数和返回行数的比值超过1万就要警惕它是不是潜在的缓存炸弹。这个比值里藏着全表扫描的隐患。第二件套是performance_schema的磁盘I/O等待统计。用前面说的等待事件聚合SQL按小时汇总当某个wait/io/file/innodb事件的总等待时间占所有等待事件的50%以上就进入重点关注列表。第三件套是InnoDB缓冲池状态。每两小时记录一次Innodb_buffer_pool_pages_dirty和Innodb_buffer_pool_pages_flushed。如果脏页比例持续超过75%而没有回落的趋势说明刷盘线程配置或I/O能力有问题需要在它变成I/O风暴前提前干预。我给自己定的阈值是磁盘util超过80%持续10分钟、await超过30ms持续5分钟、缓冲池命中率低于95%、Threads_running超过CPU核数的4倍、History List Length超过5万。任何一个阈值触发都会自动生成语音告警并带上当时的processlist快照避免半夜爬起来一脸懵。5.2 监控体系与变更管理优化参数比优化SQL有风险所以我后来专门建立了一套参数变更流程先在预发环境用同一业务模型压测记录变更前后的SHOW GLOBAL STATUS快照生产环境变更前备份配置文件变更后观察一个业务周期。这套流程看起来繁琐但能拦住90%的“好心办坏事”。监控体系方面我只推荐最基础也最耐用的组合Prometheus node_exporter mysqld_exporter Grafana。mysqld_exporter里加三个自定义查询分别采集information_schema.processlist活动会话数、global_status里的关键计数器、以及performance_schema里的等待事件汇总。不要一上来就接APM全链路先把数据库自己的核心指标看明白APM才能有效辅助。5.3 我踩过最深的坑一次“优化”引发的雪崩最后说一个反面教材。我曾在一台压力很大的MySQL上调整innodb_flush_log_at_trx_commit2当时只看了每秒写入次数的下降忽略了这台服务器还承担了清算业务。凌晨对账时突然发生一次操作系统重启最后几十秒的redo日志丢了一半导致对账数据不完整。从那之后我得出一个铁律凡是改动可靠性参数必须先跟业务方确认“丢多少数据能接受”而不是默认业务方对数据丢失无感。6. 这类问题还能怎么继续排查经过这个案子我把高负载I/O故障的完整排查顺序写成了一段固定套路在这里再完整复盘一次先确认操作系统和存储的底层能力再看MySQL内部I/O计数器然后跳到执行计划找SQL问题最后检查缓冲池抖动和连接池规模。每一步的判据都要写进值班手册里新人照着顺序操作至少能避免在故障时东一榔头西一棒子。后续我还在做两件事一是给所有核心表的information_schema.statistics做冗余索引自动发现每周生成一次“可删除索引”清单二是把慢查询追踪从日志方式切到Performance Schema的digest汇总这样能历史回溯每条SQL的资源消耗趋势真正做到“全链路可观测”。根据我个人经验MySQL高负载I/O故障极少是单点原因大多是“低效SQL 缓冲池抖动 连接池过载 刷盘配置保守”的组合拳。排查时切忌只盯一个指标把链路从底到顶拆开看每一层都有自己的证据链。希望这个案例能帮你在下次数据库报警时少流点汗多一份从容。
