如何設(shè)置innodb_log_file_size

在這片文章中，我會提供一些關(guān)于如何設(shè)置 MySQL的 innodb_log_file_size 參數(shù)的一些建議。

跟其他數(shù)據(jù)庫管理系統(tǒng)一樣，MySQL通過日志來實(shí)現(xiàn)數(shù)據(jù)的持久性（在使用InnoDB存儲引擎的前提下）。這確保了當(dāng)一個(gè)事務(wù)提交后，其相關(guān)數(shù)據(jù)在崩潰或者服務(wù)器掉電的情況下不會丟失。

MySQL的InnoDB 存儲引擎使用一個(gè)指定大小的Redo log空間（一個(gè)環(huán)形的數(shù)據(jù)結(jié)構(gòu)）。Redo log的空間通過innodb_log_file_size和innodb_log_files_in_group（默認(rèn)2）參數(shù)來調(diào)節(jié)。將這倆參數(shù)相乘即可得到總的可用Redo log 空間。盡管技術(shù)上并不關(guān)心你是通過innodb_log_file_size還是innodb_log_files_in_group來調(diào)整Redo log空間，不過多數(shù)情況下還是通過innodb_log_file_size 來調(diào)節(jié)。

為InnoDB引擎設(shè)置合適的Redo log空間對于寫敏感的工作負(fù)載來說是非常重要的。然而，這項(xiàng)工作是要做出權(quán)衡的。你配置的Redo空間越大，InnoDB就能更好的優(yōu)化寫操作；然而，增大Redo空間也意味著更長的恢復(fù)時(shí)間當(dāng)出現(xiàn)崩潰或掉電等意外時(shí)。

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

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

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

圖中 Uncheckpointed Bytes 已經(jīng)非常接近 Max Checkpoint Age，那么你幾乎可以確定當(dāng)前的 innodb_log_file_size 值因?yàn)樘∫呀?jīng)某種程度上限制了系統(tǒng)性能。增加該值可以較為顯著的提升系統(tǒng)性能。

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

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

注意：很多MySQL設(shè)置都是相互關(guān)聯(lián)的，雖然一個(gè)特定的Redo log 空間對于一個(gè)較小的InnoDB Buffer Pool值來說可能已經(jīng)足夠，但是較大的InnoDB Buffer Pool值還是期望更大的Redo log 空間以達(dá)到更好的表現(xiàn)。

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

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

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

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

關(guān)于Redo 空間的使用情況，如果沒有安裝PMM的話，也可以通過下面的命令來觀察每小時(shí)的寫入量（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

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