選擇合適的 innodb_log_file_size

為什麼 innodb_log_file_size 很重要

若對 InnoDB 資料表有大量寫入操作，選擇合適的 innodb_log_file_size 對提升 MySQL 效能至關重要。然而設定過大會增加恢復時間，導致 MySQL 崩潰或斷電後需花很長時間才能恢復服務。

恢復過程分析

Redo 階段（重做階段）

InnoDB 崩潰重啟時，會掃描日誌檔案尋找只應用到記憶體且不存在於表空間的日誌記錄，將這些修改記錄應用到表空間。此過程稱為「重做階段」。所需時間取決於多個因素：

• 列數多寡（較小日誌記錄意味著相同大小日誌可存更多記錄）
• 資料修改的隨機程度（隨機更新需更多隨機 I/O 檢查頁面是否最新）
• InnoDB 緩衝池中未刷新的頁面數量及其大小
• I/O 子系統效能

因因素眾多，難以給出通用準則（如「每 10 分鐘恢復 1GB」）。建議在典型應用負載下測試：讓 MySQL 在負載中崩潰，觀察恢復時間，多次測試後估算並適當調整日誌大小。好消息是重做階段與日誌檔案大小近似成正比，1GB 日誌恢復時間約為 512MB 的 2 倍。

Undo 階段（撤銷階段）

重做階段只是恢復階段之一。另一重要階段是「撤銷階段」：日誌檔案應用完畢、資料庫處於「物理一致」狀態後，InnoDB 需回滾未提交但已寫入資料庫的事務。與「重做」階段不同，「撤銷」階段無法透過調整日誌大小縮短。甚至較小的日誌檔案可能導致撤銷階段變慢。若事務較長（如單一事務刪除 1000 萬列並崩潰），撤銷階段會耗費相當長時間。唯一減少「撤銷」階段的方法是適當調整事務大小，使更新/插入/刪除影響有限列數。

好消息是 MySQL 5.0 起，「撤銷」階段可在背景執行。但背景回滾影響的列直到回滾完成前不可修改。

到底需要多大的日誌檔案？

可執行基準測試比較 1GB 與 2GB 日誌檔案的效能差異。日誌檔案增加到一定大小後，不一定會大幅提升效能，這同樣取決於配置與工作負載。

注意：目前允許的 InnoDB 日誌檔案最大總大小為 4GB，這對大多數配置已足夠。