如何設置innodb_log_file_size

在這片文章中，我會提供一些關于如何設置 MySQL的 innodb_log_file_size 參數的一些建議。

跟其他數據庫管理系統一樣，MySQL通過日志來實現數據的持久性（在使用InnoDB存儲引擎的前提下）。這確保了當一個事務提交后，其相關數據在崩潰或者服務器掉電的情況下不會丟失。

MySQL的InnoDB 存儲引擎使用一個指定大小的Redo log空間（一個環形的數據結構）。Redo log的空間通過innodb_log_file_size和innodb_log_files_in_group（默認2）參數來調節。將這倆參數相乘即可得到總的可用Redo log 空間。盡管技術上并不關心你是通過innodb_log_file_size還是innodb_log_files_in_group來調整Redo log空間，不過多數情況下還是通過innodb_log_file_size 來調節。

為InnoDB引擎設置合適的Redo log空間對于寫敏感的工作負載來說是非常重要的。然而，這項工作是要做出權衡的。你配置的Redo空間越大，InnoDB就能更好的優化寫操作；然而，增大Redo空間也意味著更長的恢復時間當出現崩潰或掉電等意外時。

關于恢復時間，并不好預測對于一個指定的 innodb_log_file_size 值出現崩潰是需要多長的恢復時間--他取決于硬件能力、MySQL版本以及工作負載等因素。然而，一般情況下我們可以按照每1GB的Redo log的恢復時間大約在5分鐘左右來估算。如果恢復時間對于你的使用環境來說很重要，我建議你做一些模擬測試，在正常工作負載下（預熱完畢后）模擬系統崩潰，來評估更準確的恢復時間。

雖然恢復時間可以作為一個限制innodb_log_file_size的參考因素，也還有一些別的方式可以觀察該參數設置是否“合理”（尤其是如果你安裝了PMM： Percona Monitoring and Management）

檢查Percona Monitoring and Management的“MySQL InnoDB Metrics”儀表盤，如果你看到如下的圖像：

圖中 Uncheckpointed Bytes 已經非常接近 Max Checkpoint Age，那么你幾乎可以確定當前的 innodb_log_file_size 值因為太小已經某種程度上限制了系統性能。增加該值可以較為顯著的提升系統性能。

而如果你看到的類似下圖：

該圖中 Uncheckpointed Bytes 遠小于 Max Checkpoint Age，這種情況下再增加 innodb_log_file_size 就不會有明顯性能提升。

注意：很多MySQL設置都是相互關聯的，雖然一個特定的Redo log 空間對于一個較小的InnoDB Buffer Pool值來說可能已經足夠，但是較大的InnoDB Buffer Pool值還是期望更大的Redo log 空間以達到更好的表現。

另一件需要記住的事：我們之前說的恢復時間，取決于 Uncheckpointed Bytes 而不是總的Redo log空間。如果你在增加了innodb_log_file_size之后并未觀察到恢復時間的增加，那可能就是之前的配置在你當前的工作負載下已經夠用，你增加的空間并未被完全利用。

另一個觀察innodb_log_file_size的途徑是 Redo log空間的使用情況：

這張圖片展示了每小時寫入日志文件的總數據量和innodb_log_file_size的值。上圖中，我們有2G的Redo log空間但是每小時卻有12G多的數據被寫入日志文件。這意味著Redo空間差不多每十分鐘就輪轉一次。

而InnoDB 在每次Redo log空間輪轉時都要將innodb buffer pool中的每個臟頁都刷新到磁盤上。當這個操作出現越少時InnoDB越能得到更好的表現（對SSD硬盤的磨損也越少）。我希望看到這個操作的頻率能達到至少15分鐘一次，當然越少越好。

關于Redo 空間的使用情況，如果沒有安裝PMM的話，也可以通過下面的命令來觀察每小時的寫入量（MB）：

a=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); sleep 60; b=$(mysql -uuser -p'passwd' -e "show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'); let "res=($b-$a)*60/1024/1024";echo $res

總結：
設置合適的innodb_log_file_file_size對于平衡性能和恢復時間來說非常重要。但是記住，你的場景下的恢復時間由于受所方面因素影響，并不能完全準確的預估出來。我希望本文中討論的幾點能幫助你設置更合理的innodb_log_file_file_size。