MySQL 線上故障複盤：innodb_io_capacity 設置過大導致周期性 TPS 跌零與髒頁刷盤風暴

故障現象

Sysbench 壓測 600 線程，TPS 正常 ~2000-4000，每隔 ~10 分鐘出現 TPS 驟降為 0 持續數分鐘，隨後恢復。錯誤率 0，重連 0，但響應時間飆升至 600 秒。

[1530s] threads: 600, tps: 787.59, reads: 393.19, writes: 394.39, response time: 1060.72ms (95%)
[1540s] threads: 600, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%)
...
[2140s] threads: 600, tps: 219.00, reads: 108.30, writes: 110.70, response time: 615414.74ms (95%)

根因分析：髒頁積壓觸發保護機制

• InnoDB 後台刷髒頁速度 跟不上 事務提交產生髒頁速度。
• 髒頁佔比達到 innodb_max_dirty_pages_pct (默認 75%) → 異步刷盤，不阻塞寫入。
• 髒頁佔比達到 90% (硬編碼閾值) → 同步阻塞所有寫入，專心刷盤，直到降至安全線 → TPS 歸零。

配置現場

innodb_log_file_size = 1000M
innodb_log_files_in_group = 4
innodb_max_dirty_pages_pct = 75
innodb_io_capacity = 200  -- 關鍵問題參數

為什麼不早點刷？自適應刷盤機制誤判

InnoDB 根據 innodb_io_capacity 評估磁盤能力，決定何時啟動積極刷盤。設置過大 (200) 導致高估磁盤能力，以為磁盤能扛住，推遲刷盤，致使髒頁堆積至 90% 觸發停世界。

硬件實測與官方建議

[root@t1 bin]# ./sg_vpd /dev/sda --page=0xb1
Nominal rotation rate: 7200 rpm  -- 普通機械盤

官方文檔明確建議：

For systems with individual 5400 RPM or 7200 RPM drives, you might lower the value to the former default of 100.

修正與驗證

innodb_io_capacity = 100  -- 修改後重啟/動態生效

重新壓測：TPS 曲線平穩，無歸零現象，問題完美解決。

核心參數關聯圖

經驗總結

• innodb_io_capacity 必須匹配真實磁盤 IOPS 能力，機械盤 100-200，SSD 1000-5000，NVMe 10000+。
• 過大 → 髒頁風暴；過小 → 人為限制寫入吞吐。
• 配合 innodb_max_dirty_pages_pct (75-90)、innodb_adaptive_flushing (ON) 共同控制刷盤節奏。
• 監控指標：Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total 實時髒頁比。