MySQL效能最佳化(一)

標籤：

MySQL參數:innodb_flush_log_at_trx_commit和sync_binlog

innodb_flush_log_at_trx_commit和sync_binlog是MySQL的兩個配置參數，前者是InnoDB引擎特有的。在實際應用中，它們的配置對於MySQL的效能有很大影響。

 

1.innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit參數指定了InnoDB在事務提交後的日誌寫入頻率。這麼說其實並不嚴謹，不同取值具有不同的意義和表現。

①.當innodb_flush_log_at_trx_commit取值為0的時候，log buffer會每秒寫入到記錄檔並刷寫（flush）到磁碟。但每次事務提交不會有任何影響，也就是log buffer的刷寫操作和事務提交操作沒有關係。在這種情況下，MySQL效能最好，但如果mysqld進程崩潰，通常會導致最後1s的日誌丟失。

②.=1時，每次事務提交時，log buffer會被寫入到記錄檔並刷寫到磁碟。這也是預設值。這是最安全的配置，但由於每次事務都需要進行磁碟I/O，所以也最慢。

③.=2時，每次事務提交會寫入記錄檔，但並不會立即刷寫到磁碟，記錄檔會每秒刷寫一次到磁碟。這時如果mysqld進程崩潰，由於日誌已經寫入到系統緩衝，所以並不會遺失資料；在作業系統崩潰的情況下，通常會導致最後 1s 的日誌丟失。

上面說到的「最後1s」並不是絕對的，有的時候會丟失更多資料。有時候由於調度的問題，每秒刷寫（once-per-second flushing）並不能保證 100% 執行。對於一些資料一致性和完整性要求不高的應用，配置為2就足夠了；如果為了最高效能，可以設定為 0。有些應用，如支付服務，對一致性和完整性要求很高，所以即使最慢，也最好設定為

 

2.sync_binlog

sync_binlog是MySQL的二進位日誌（binary log）同步到磁碟的頻率。MySQL server在binary log每寫入sync_binlog次後，刷寫到磁碟。

如果autocommit開啟，每個語句都寫一次binary log，否則每次事務寫一次。

①.預設值是=0，不主動同步，而依賴作業系統本身不定期把檔案內容flush到磁碟。設為1最安全，在每個語句或事務後同步一次binary log，即使在崩潰時也最多丟失一個語句或事務的日誌，但因此也最慢。

②.大多數情況下，對資料的一致性並沒有很嚴格的要求，所以並不會把sync_binlog配置成1. 為了追求高並發，提升效能，可以設定為 100 或直接用0. 而和innodb_flush_log_at_trx_commit一樣，對於支付服務這樣的應用，還是比較推薦sync_binlog=1.

 

MySQL效能最佳化(一)