mysql磁盘io过高如何排查解决？

MySQL磁盘IO过高是一个常见的性能问题，需要进行系统性排查。以下是详细的排查和解决步骤：

一、快速诊断

1. 确认IO问题来源

# 查看系统整体IO情况
iostat -x 1
# 重点关注：%util、await、svctm

# 查看进程级IO
iotop  # 或 pidstat -d 1

# 查看MySQL进程的IO
pidstat -d -p <mysql_pid> 1

2. MySQL内部状态检查

-- 查看当前运行查询
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

-- 查看关键性能指标
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_data_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';

二、深入排查

1. 计算缓冲池命中率

-- 缓冲池命中率 = (1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%
-- 如果低于99%，说明缓冲池不足
SELECT 
  (1 - variable_value / 
    (SELECT variable_value 
     FROM performance_schema.global_status 
     WHERE variable_name = 'Innodb_buffer_pool_read_requests')
  ) * 100 as hit_rate
FROM performance_schema.global_status 
WHERE variable_name = 'Innodb_buffer_pool_reads';

2. 识别高IO操作

-- 查看当前锁情况
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看表统计信息
SELECT 
  table_schema,
  table_name,
  data_length,
  index_length,
  data_free
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY data_length + index_length DESC;

三、常见原因及解决方案

1. 缓冲池不足

# my.cnf配置优化
[mysqld]
# 设置为物理内存的50%-70%
innodb_buffer_pool_size = 8G

# 预热缓冲池
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON

2. 索引问题

-- 查找全表扫描
EXPLAIN SELECT * FROM your_table WHERE condition;

-- 查找缺失索引
-- 使用sys库（MySQL 5.7+）
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.statements_with_full_table_scans;

3. 查询优化

避免SELECT *，只选择需要的列
优化JOIN语句，确保关联字段有索引
分批处理大数据量操作
合理使用LIMIT

4. 写入优化

# 调整刷盘策略
innodb_flush_log_at_trx_commit = 2  # 从1调整为2（在可接受数据丢失风险时）
sync_binlog = 1000  # 从1调整为1000

# 增加日志文件大小
innodb_log_file_size = 1G
innodb_log_files_in_group = 3

5. 表结构优化

-- 分区大表
ALTER TABLE large_table 
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

-- 定期优化表
OPTIMIZE TABLE fragmented_table;

四、高级诊断工具

1. Performance Schema监控

-- 启用文件IO监控
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'wait/io/file/%';

-- 查看IO等待事件
SELECT 
  event_name,
  count_star,
  sum_timer_wait/1000000000 as total_latency_s
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE 'wait/io/file/%'
ORDER BY sum_timer_wait DESC
LIMIT 10;

2. 使用pt工具

# 分析慢查询
pt-query-digest mysql-slow.log

# IO分析
pt-ioprofile --profile-pid=<mysql_pid>

# 磁盘使用分析
pt-duplicate-key-checker --databases your_db

五、硬件和系统优化

1. 文件系统优化

# 使用XFS或ext4，禁用访问时间记录
/dev/sdb1 /data xfs defaults,noatime,nodiratime 0 0

# 调整IO调度器（SSD使用noop，HDD使用deadline）
echo noop > /sys/block/sdb/queue/scheduler

2. RAID优化

使用RAID 10而不是RAID 5
确保RAID卡有足够的缓存和电池

六、监控告警设置

1. 关键监控指标

- 磁盘利用率 > 80%
- IO等待时间 > 20ms
- 缓冲池命中率 < 95%
- 每秒读写次数异常波动

2. 自动优化脚本

#!/bin/bash
# 自动收集诊断信息
mysql -e "SHOW ENGINE INNODB STATUS\G" > /tmp/innodb_status.log
mysql -e "SHOW FULL PROCESSLIST" > /tmp/processlist.log
iostat -x 1 10 > /tmp/iostat.log

七、紧急处理步骤

立即缓解

-- 终止问题查询
KILL [CONNECTION] query_id;

-- 设置查询超时
SET GLOBAL max_execution_time = 30000;  -- 30秒

临时降低IO

-- 降低刷盘频率
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 0;

连接控制

-- 限制用户连接
ALTER USER 'app_user' WITH MAX_USER_CONNECTIONS 50;

总结排查流程

1. 使用系统工具确认IO问题
2. 识别是读IO还是写IO高
3. 检查MySQL缓冲池命中率
4. 分析慢查询和全表扫描
5. 检查锁和事务问题
6. 优化配置参数
7. 考虑硬件升级

建议按照上述步骤逐步排查，通常80%的IO问题可以通过优化查询、增加缓冲池和调整配置参数解决。如果问题持续存在，考虑升级到SSD存储。