1. 项目背景与问题定位上周五凌晨2点37分生产环境监控系统突然发出刺耳的告警声——MySQL数据库服务器的I/O等待飙升至98%系统负载突破40。作为DBA团队负责人我立刻通过SSH连接到服务器展开排查。这是一套运行在CentOS 7.6上的MySQL 5.7集群承载着公司核心订单系统的数据存储。通过top命令观察发现mysqld进程的CPU使用率并不高约15%但waI/O等待指标长期维持在80%以上。更令人警惕的是vmstat显示procs下的b列不可中断睡眠进程数量持续在8-12之间波动。这种典型的I/O瓶颈特征直接导致前端应用出现大量org.postgresql.util.PSQLException: An I/O error occurred类报错——虽然错误信息显示是PostgreSQL但实际上是因为应用连接MySQL超时后抛出的误导性异常。2. 全链路故障诊断过程2.1 存储层排查首先使用iostat -x 1检查磁盘I/O状况发现sdb设备的util持续100%await高达300ms以上。这套系统采用的是RAID10配置的SAS机械硬盘阵列理论上不应该出现如此严重的延迟。进一步通过smartctl检查磁盘健康状态所有SMART参数均显示正常。关键发现来自iotop命令一个名为mysqld的进程正以约200MB/s的速度持续写入临时文件。这显然不正常——正常情况下我们的MySQL实例写入量应该稳定在20MB/s左右。2.2 MySQL层分析登录MySQL执行SHOW PROCESSLIST发现大量处于Copying to tmp table状态的连接。查询information_schema发现有3个会话正在执行包含多表JOIN且没有合适索引的复杂报表查询每个查询都扫描超过500万行数据。通过SHOW ENGINE INNODB STATUS查看更详细的信息在TRANSACTIONS段发现大量锁等待而在FILE I/O段显示有超过15个pending的fsync操作。这证实了I/O子系统已经不堪重负。2.3 系统层检查使用pidstat -d命令定位到具体线程级别的I/O情况发现几个MySQL线程的kB_rd/s和kB_wr/s指标异常高。结合free -m查看内存使用虽然总内存128GB但buffers/cache可用仅剩2GB且swap开始被使用。最关键的证据来自perf工具采集的系统调用统计# perf top -e block:block_rq_issue 49.32% [kernel] [k] blk_peek_request 31.15% mysqld [.] os_file_write_func 8.77% [kernel] [k] __blk_run_queue这表明I/O瓶颈确实集中在MySQL的磁盘写入操作上。3. 优化方案设计与实施3.1 紧急处理措施通过SET GLOBAL long_query_time1临时降低慢查询阈值使用KILL QUERY终止正在运行的三个问题查询调整innodb_io_capacity从默认200提升至1000设置innodb_flush_neighbors0关闭相邻页刷新这些操作在5分钟内将I/O等待从98%降至45%系统负载降到15左右。3.2 中长期优化方案3.2.1 查询优化为所有报表查询添加复合索引重写SQL避免全表扫描。例如将SELECT * FROM orders JOIN users ON orders.user_id users.id WHERE create_time 2023-01-01优化为SELECT /* INDEX(orders idx_user_create) */ o.id, o.amount, u.name FROM orders o FORCE INDEX (idx_user_create) JOIN users u ON o.user_id u.id WHERE o.create_time 2023-01-013.2.2 参数调整修改my.cnf关键参数innodb_buffer_pool_size 96G # 总内存的75% innodb_io_capacity_max 2000 innodb_lru_scan_depth 256 innodb_flush_method O_DIRECT innodb_read_io_threads 16 innodb_write_io_threads 163.2.3 架构改进将报表查询迁移到专用的从库执行增加Redis缓存层缓存常用查询结果对临时表空间使用tmpfs文件系统4. 效果验证与监控加固优化后连续72小时监控数据显示平均I/O等待从78%降至12%查询平均响应时间从3.2s缩短到0.4s临时表创建次数减少90%新增的监控项包括Grafana面板跟踪performance_schema.file_summary_by_event_name每分钟采集iostat -dxm数据对information_schema.INNODB_TRX进行15秒间隔采样5. 经验总结与避坑指南临时表陷阱MySQL在处理复杂查询时若内存不足会创建磁盘临时表。通过EXPLAIN查看Extra列中的Using temporary可以提前发现这类问题。I/O容量设置机械硬盘阵列的innodb_io_capacity不应低于500SSD阵列建议设置在2000以上。这个参数直接影响InnoDB的后台刷脏页速度。监控盲区常规监控容易忽略线程级I/O统计。建议定期使用performance_schema.threads结合pidstat进行深度检查。O_DIRECT争议虽然O_DIRECT可以绕过系统缓存但在某些内核版本可能导致额外的锁竞争。我们最终在Linux 3.10内核上保持默认的fsync方式。索引优化技巧对于报表查询创建包含所有查询字段的覆盖索引比单列索引更有效。但要注意索引维护成本我们采用pt-index-usage工具定期清理无用索引。这次故障给我们的重要启示是MySQL的I/O问题往往是多个因素共同作用的结果需要从查询、配置、硬件、架构四个维度进行综合分析和优化。单纯的参数调整或硬件升级都难以彻底解决问题。