MySQL I/O性能优化实战：从故障排查到系统调优

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 紧急处理措施

1. 通过 SET GLOBAL long_query_time=1 临时降低慢查询阈值
2. 使用 KILL QUERY 终止正在运行的三个问题查询
3. 调整 innodb_io_capacity 从默认200提升至1000
4. 设置 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 = 16

3.2.3 架构改进

1. 将报表查询迁移到专用的从库执行
2. 增加Redis缓存层，缓存常用查询结果
3. 对临时表空间使用tmpfs文件系统

4. 效果验证与监控加固

优化后连续72小时监控数据显示：

• 平均I/O等待从78%降至12%
• 查询平均响应时间从3.2s缩短到0.4s
• 临时表创建次数减少90%

新增的监控项包括：

1. Grafana面板跟踪 performance_schema.file_summary_by_event_name
2. 每分钟采集 iostat -dxm 数据
3. 对 information_schema.INNODB_TRX 进行15秒间隔采样

5. 经验总结与避坑指南

1. 临时表陷阱 ：MySQL在处理复杂查询时，若内存不足会创建磁盘临时表。通过 EXPLAIN 查看Extra列中的"Using temporary"可以提前发现这类问题。

2. I/O容量设置 ：机械硬盘阵列的 innodb_io_capacity 不应低于500，SSD阵列建议设置在2000以上。这个参数直接影响InnoDB的后台刷脏页速度。

3. 监控盲区 ：常规监控容易忽略线程级I/O统计。建议定期使用 performance_schema.threads 结合 pidstat 进行深度检查。

4. O_DIRECT争议 ：虽然O_DIRECT可以绕过系统缓存，但在某些内核版本可能导致额外的锁竞争。我们最终在Linux 3.10内核上保持默认的fsync方式。

5. 索引优化技巧 ：对于报表查询，创建包含所有查询字段的覆盖索引比单列索引更有效。但要注意索引维护成本，我们采用pt-index-usage工具定期清理无用索引。

这次故障给我们的重要启示是：MySQL的I/O问题往往是多个因素共同作用的结果，需要从查询、配置、硬件、架构四个维度进行综合分析和优化。单纯的参数调整或硬件升级都难以彻底解决问题。