MySQL IO 性能优化

核心概念

IO 是数据库性能的主要瓶颈，优化 IO 性能对提升数据库性能至关重要。

IO 瓶颈表现

IO 类型

MySQL IO 操作：
1. 数据文件 IO（最频繁）
   - 数据页读取
   - 数据页写入

2. 日志文件 IO（最关键）
   - redo log
   - binlog
   - undo log

3. 临时文件 IO
   - 排序临时表
   - 分组临时表

磁盘 IO 优化

磁盘类型选择

IO 调度算法

# Linux IO 调度算法
# none: 无调度（NVMe 推荐）
# mq-deadline: 截止时间（SSD 推荐）
# bfq: 预算公平队列（HDD 推荐）
# kyber: 轻量级调度器

# 查看当前调度算法
cat /sys/block/sda/queue/scheduler

# 修改调度算法（临时）
echo mq-deadline > /sys/block/sda/queue/scheduler

# 修改调度算法（永久）
# /etc/grub.conf 添加
# elevator=mq-deadline

文件系统优化

# 推荐使用 XFS 文件系统
# 格式化
mkfs.xfs -f /dev/sda

# 挂载选项优化
# /etc/fstab
/dev/sda1  /var/lib/mysql  xfs  noatime,nodiratime  0  0

# noatime: 不更新访问时间
# nodiratime: 不更新目录访问时间

RAID 配置

InnoDB IO 优化

Buffer Pool 优化

[mysqld]
# 增大 Buffer Pool，减少磁盘 IO
innodb_buffer_pool_size = 8G

# 多实例，减少锁竞争
innodb_buffer_pool_instances = 8

# 预读优化
innodb_read_ahead_threshold = 56
innodb_read_ahead_pages = 64

IO Capacity 配置

[mysqld]
# IO 能力（根据磁盘性能调整）
# HDD: 200, SSD: 2000, NVMe: 10000+
innodb_io_capacity = 2000

# 后台刷盘 IO 能力
innodb_io_capacity_max = 4000

# 刷盘比例
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 50

刷盘策略优化

[mysqld]
# Redo Log 刷新策略
innodb_flush_log_at_trx_commit = 1
# 0: 每秒刷盘，性能最好，可能丢失 1 秒数据
# 1: 每次提交刷盘，最安全（推荐）
# 2: 每次提交写 OS 缓存，每秒刷盘

# 双写缓冲
innodb_doublewrite = 1
# 防止页分裂，提升安全性

# 刷新邻居页
innodb_flush_neighbors = 0
# SSD 建议关闭，HDD 建议开启

日志 IO 优化

Redo Log 优化

[mysqld]
# Redo Log 文件大小
innodb_log_file_size = 512M
# 太小：频繁 checkpoint
# 太大：恢复时间长

# Redo Log 文件数量
innodb_log_files_in_group = 2

# Log Buffer 大小
innodb_log_buffer_size = 64M
# 大事务适当调大

监控：

-- 查看 Redo Log 使用
SHOW ENGINE INNODB STATUS\G

-- Log 部分：
-- Log sequence number: 当前 LSN
-- Log buffered up to: 缓冲的 LSN
-- Last checkpoint at: 最近 checkpoint LSN
-- Pending checkpoint writes: 等待写入的 checkpoint

Binlog 优化

[mysqld]
# Binlog 格式
binlog_format = ROW

# Binlog 刷盘策略
sync_binlog = 1
# 0: 不主动刷盘，性能最好，可能丢失
# 1: 每次提交刷盘，最安全（推荐）
# N: 每 N 次提交刷盘

# Binlog 大小
max_binlog_size = 512M

# Binlog 过期时间
binlog_expire_logs_seconds = 604800  # 7 天

Undo Log 优化

[mysqld]
# Undo Log 表空间数量
innodb_undo_log_truncate = ON
innodb_undo_tablespaces = 2

# Undo Log 清理阈值
innodb_max_undo_logs = 128

IO 监控与诊断

IO 监控工具

# iostat - IO 统计
iostat -x 1  # 每秒刷新

# 关键指标：
# %iowait: CPU 等待 IO 时间
# %util: 磁盘利用率
# await: 平均 IO 等待时间
# svctm: 平均服务时间

# iotop - 查看进程 IO
iotop -o

# pidstat - 进程 IO 统计
pidstat -d 1

# blktrace - 块设备 IO 追踪
blktrace -d /dev/sda -o - | blktrace -a complete

MySQL IO 监控

-- 查看 IO 等待
SELECT 
    event_name,
    count_read,
    sum_number_of_bytes_read,
    sum_timer_read,
    count_write,
    sum_number_of_bytes_write,
    sum_timer_write
FROM performance_schema.file_summary_by_event_name
ORDER BY sum_timer_read + sum_timer_write DESC;

-- 查看 IO 延迟
SHOW GLOBAL STATUS LIKE 'Innodb_data%';

-- Innodb_data_reads: 总读取次数
-- Innodb_data_writes: 总写入次数
-- Innodb_data_fsyncs: fsync 次数

慢 IO 查询

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 查看慢查询
SELECT * FROM mysql.slow_log;

-- 使用 pt-query-digest 分析
pt-query-digest /var/lib/mysql/slow.log

IO 优化实践

优化步骤

1. 监控 IO 指标
   iostat -x 1

2. 识别瓶颈
   - %iowait > 30%
   - await > 50ms
   - %util > 90%

3. 优化配置
   - 增大 Buffer Pool
   - 调整 IO Capacity
   - 优化刷盘策略

4. 优化 SQL
   - 添加索引
   - 减少全表扫描
   - 避免大事务

5. 硬件升级
   - HDD → SSD
   - SSD → NVMe

配置示例

# NVMe SSD 优化配置
[mysqld]
# Buffer Pool
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 16

# IO Capacity
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000

# 刷盘策略
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

# 刷新邻居页（SSD 关闭）
innodb_flush_neighbors = 0

# Log Buffer
innodb_log_buffer_size = 128M
innodb_log_file_size = 1G

常见问题解决

问题 1：IO 等待高

症状：
- iowait > 50%
- 查询慢

解决：
1. 增大 innodb_buffer_pool_size
2. 优化 SQL，减少全表扫描
3. 升级 SSD

问题 2：Redo Log 瓶颈

症状：
- Log sequence number 增长快
- Checkpoint 频繁

解决：
1. 增大 innodb_log_file_size
2. 增大 innodb_log_buffer_size
3. 调整 innodb_io_capacity

问题 3：临时表 IO 高

症状：
- Created_tmp_disk_tables 多
- 排序慢

解决：
1. 增大 tmp_table_size
2. 优化 ORDER BY / GROUP BY
3. 添加索引避免排序

最佳实践

配置检查清单

[ ] 使用 SSD 或 NVMe 磁盘
[ ] innodb_buffer_pool_size = 物理内存的 70%
[ ] innodb_io_capacity 根据磁盘类型设置
[ ] innodb_flush_log_at_trx_commit = 1
[ ] sync_binlog = 1
[ ] innodb_flush_neighbors = 0 (SSD)
[ ] 使用 XFS 文件系统 + noatime
[ ] RAID 10 配置

参考资料

• MySQL 官方文档 - InnoDB IO
• 《高性能 MySQL》第 8 章：MySQL 服务器性能剖析
• Percona Blog: InnoDB IO Optimization