mysql的I/O性能优化与磁盘访问策略

2026-01-25

mysql的I/O性能优化与磁盘访问策略

innodb_io_capacity设过高会因磁盘实际IOPS不足导致fsync阻塞、脏页积压和TPS波动；应通过fio实测取90%稳定值，SSD设1000–4000，NVMe设8000–20000，HDD不超200，并配innodb_io_capacity_max为两倍。

mysql的i/o性能优化与磁盘访问策略

为什么 `innodb_io_capacity` 设太高反而拖慢写入

这个参数不是“越大越好”，它本质是告诉 InnoDB：磁盘每秒能稳定处理多少次随机 I/O（单位：IOPS）。设高了，InnoDB 会激进地提前刷脏页、合并插入缓冲、预读更多数据——但若底层磁盘实际扛不住，就会引发大量 fsync 阻塞、page cleaner 跟不上、Dirty pages 积压，最终表现为 innodb_data_pending_fsyncs 持续升高、TPS 波动剧烈。

实操建议：

用 fio 实测磁盘真实随机写 IOPS（例如：

fio --name=randwrite --ioengine=libaio --rw=randwrite --bs=16k --numjobs=4 --size=1G --runtime=60 --time_based --group_reporting

），取 90% 稳定值作为 innodb_io_capacity 初始值

SSD 常见合理范围是 1000–4000；NVMe 可设到 8000–20000；传统 SATA HDD 不建议超过 200
搭配调低 innodb_io_capacity_max（建议设为 innodb_io_capacity × 2），防止突发写入时过度抢占 I/O

`innodb_flush_method` 选 `O_DIRECT` 还是 `O_DSYNC`？

关键看是否启用文件系统缓存 + 是否有电池/电容保护的 RAID 卡。MySQL 默认的 fsync（即不显式设该参数）依赖 OS page cache，双缓存（InnoDB buffer pool + OS cache）会导致重复刷盘、内存浪费，且 crash recovery 时可能丢失未落盘的 redo log。

实操建议：

使用本地 SSD/NVMe 且无硬件写缓存保护 → 必须设为 O_DIRECT，绕过 OS cache，由 InnoDB 直接管理物理页对齐与刷盘
使用带 BBWC（Battery-Backed Write Cache）的 RAID 卡 → 可设为 O_DSYNC，让 OS 控制日志写入，RAID 卡保证掉电不丢
绝对不要在 ext4/xfs 上用 default 或空值，尤其高并发写场景下，Innodb_os_log_written 增速远超 Innodb_dblwr_writes 就是双缓存征兆

redo log 文件太小导致频繁 checkpoint 的识别与调整

当 innodb_log_file_size 总和不足，InnoDB 会频繁触发 sharp checkpoint，强制刷脏页以腾出 log space，造成 I/O 毛刺、log sequence number 增长陡峭、innodb_buffer_pool_wait_free 上升。典型错误日志里会出现：Warning: InnoDB: Log file ./ib_logfile0 is 48MB, but the log file size in ibdata1 is 128MB —— 这说明配置已改但没重建日志文件。

图星人

好用的AI生图工具，百万免费商用图库

下载

实操建议：

计算公式：innodb_log_file_size × 2 ≈ 1小时峰值写入量（字节） ÷ 3600 × 2；例如每秒平均写 5MB，则总大小建议 ≥ 36GB（即单个 18GB）
调整必须停库：先 SET GLOBAL innodb_fast_shutdown = 0，再关闭 mysqld，删旧 ib_logfile*，改配置，重启
确认生效后查 SHOW ENGINE INNODB STATUS/G 中 Log sequence number 和 Last checkpoint at 差值是否稳定在 70%–80% 的 log 总容量内

临时表和排序操作绕过磁盘的硬性开关

很多慢查询的 I/O 瓶颈其实来自隐式磁盘临时表（Created_tmp_disk_tables）或外部排序（Sort_merge_passes）。MySQL 不会自动把 tmpdir 放进内存，哪怕你有充足 RAM。

实操建议：

将 tmpdir 指向 tmpfs（Linux 内存文件系统）：
```
mount -t tmpfs -o size=2g tmpfs /var/tmp/mysql-tmp
```
，再在 my.cnf 中设 tmpdir = /var/tmp/mysql-tmp
同步调大 sort_buffer_size（每个连接独占，别设过大）和 read_rnd_buffer_size，但更关键是优化 SQL：加索引避免 filesort，用覆盖索引减少回表，限制 GROUP BY 数据量
监控是否生效：持续观察 SHOW GLOBAL STATUS LIKE 'Created_tmp%'，确保 Created_tmp_disk_tables / Created_tmp_tables

磁盘 I/O 优化最易被忽略的是「混合负载干扰」：备份脚本、监控采集、日志轮转这些看似无关的操作，只要在同一块物理盘上做顺序读，就会严重拖慢 InnoDB 的随机写响应。真正稳定的 IO 性能，永远始于磁盘隔离。

https://www.php.cn/faq/2027869.html