1. 项目背景与问题定位
上周五凌晨2点37分，生产环境监控系统突然发出刺耳的告警声——MySQL数据库服务器的I/O等待飙升至98%，系统负载突破40。作为DBA团队负责人，我立刻通过SSH连接到服务器展开排查。这是一套运行在CentOS 7.6上的MySQL 5.7集群，承载着公司核心订单系统的数据存储。
通过top命令观察发现，mysqld进程的CPU使用率并不高（约15%），但wa（I/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_time=1临时降低慢查询阈值 - 使用
KILL QUERY终止正在运行的三个问题查询 - 调整
innodb_io_capacity从默认200提升至1000 - 设置
innodb_flush_neighbors=0关闭相邻页刷新
这些操作在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-01'3.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不应低于500，SSD阵列建议设置在2000以上。这个参数直接影响InnoDB的后台刷脏页速度。监控盲区：常规监控容易忽略线程级I/O统计。建议定期使用
performance_schema.threads结合pidstat进行深度检查。O_DIRECT争议：虽然O_DIRECT可以绕过系统缓存，但在某些内核版本可能导致额外的锁竞争。我们最终在Linux 3.10内核上保持默认的fsync方式。
索引优化技巧：对于报表查询，创建包含所有查询字段的覆盖索引比单列索引更有效。但要注意索引维护成本，我们采用pt-index-usage工具定期清理无用索引。
这次故障给我们的重要启示是：MySQL的I/O问题往往是多个因素共同作用的结果，需要从查询、配置、硬件、架构四个维度进行综合分析和优化。单纯的参数调整或硬件升级都难以彻底解决问题。
