解決 MySQL 佔用 IO 過高：關鍵參數調優實戰

問題背景

線上 MySQL 實例磁盤 IO 利用率長期 90%+，導致查詢延遲抖動、備份緩慢、甚至觸發告警。經排查，主要由二進制日志 (Binlog) 同步頻率過高 和 InnoDB Redo Log 刷盤策略過於激進 引起。

核心優化參數

1. sync_binlog：控制 Binlog 刷盤頻率

SET GLOBAL sync_binlog = 500;

• 默認值 1：每次事務提交強制 fsync 刷盤，最安全但 IO 開銷最大。
• 設為 500：累積 500 次事務提交才刷盤一次，大幅減少 IOPS。
• 風險：崩潰時最多丟失 500 個事務的 binlog。若業務可接受秒級數據風險 (如日志、統計類)，收益巨大。

2. innodb_flush_log_at_trx_commit：控制 Redo Log 刷盤

SET GLOBAL innodb_flush_log_at_trx_commit = 2;

推薦生產環境設為 2：折中方案，僅操作系統級宕機才丟數據，MySQL 進程崩潰不丟，性能提升明顯。

持久化配置

SET GLOBAL 僅運行時生效，重啟失效。需寫入 my.cnf：

[mysqld]
sync_binlog = 500
innodb_flush_log_at_trx_commit = 2

修改後 systemctl restart mysqld 生效。

日志類型對 IO 的影響對比

驗證優化效果

1. iostat -x 1 觀察 %util、await、svctm 下降。
2. SHOW GLOBAL STATUS LIKE 'Innodb_os_log_fsyncs'; 對比調整前後 fsync 次數增長速度。
3. 應用端監控：查詢 P99 延遲、寫入吞吐是否改善。

總結

• Binlog 同步頻率 (sync_binlog) 和 Redo Log 刷盤策略 (innodb_flush_log_at_trx_commit) 是控制 MySQL 寫入 IO 的兩大核心桿槓。
• 根據業務對數據「零丟失」的敏感度，在 性能與安全 間權衡：核心交易庫建議 sync_binlog=1, flush_log=1；日志/統計/非核心業務可大膽設 500/2。
• 配置修改務必寫入 my.cnf 並重啟生效，避免重啟後配置回滾導致故障複發。