如何理解MySQL中binlog和innodb_flush_log_at_trx_commit

如何理解MySQL中binlog和innodb_flush_log_at_trx_commit ，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

 innodb_flush_log_at_trx_commit和sync_binlog 兩個參數(shù)是控制MySQL 磁盤寫入策略以及數(shù)據(jù)安全性的關鍵參數(shù)。本文從參數(shù)含義，性能，安全角度闡述兩個參數(shù)為不同的值時對db 性能,數(shù)據(jù)的影響.

一 參數(shù)意義

innodb_flush_log_at_trx_commit

如果innodb_flush_log_at_trx_commit設置為0，log buffer將每秒一次地寫入log file中，并且log file的flush(刷到磁盤)操作同時進行.該模式下，在事務提交的時候，不會主動觸發(fā)寫入磁盤的操作。
如果innodb_flush_log_at_trx_commit設置為1，每次事務提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file，并且flush(刷到磁盤)中去.
如果innodb_flush_log_at_trx_commit設置為2，每次事務提交時MySQL都會把log buffer的數(shù)據(jù)寫入log file.但是flush(刷到磁盤)操作并不會同時進行。該模式下,MySQL會每秒執(zhí)行一次 flush(刷到磁盤)操作。

注意：

  由于進程調(diào)度策略問題,這個“每秒執(zhí)行一次 flush(刷到磁盤)操作”并不是保證100%的“每秒”。

sync_binlog

sync_binlog 的默認值是0，像操作系統(tǒng)刷其他文件的機制一樣，MySQL不會同步到磁盤中去而是依賴操作系統(tǒng)來刷新binary log。

當sync_binlog =N (N>0) ，MySQL 在每寫 N次 二進制日志binary log時，會使用fdatasync()函數(shù)將它的寫二進制日志binary log同步到磁盤中去。

注:

   如果啟用了autocommit，那么每一個語句statement就會有一次寫操作；否則每個事務對應一個寫操作。

   根據(jù)上述描述，我做了一張圖，可以方便大家查看。
 

二 性能

    兩個參數(shù)在不同值時對db的純寫入的影響表現(xiàn)如下：

 測試場景1 

  innodb_flush_log_at_trx_commit=2 

  sync_binlog=1000

 測試場景2 

  innodb_flush_log_at_trx_commit=1 

  sync_binlog=1000

 測試場景3 

  innodb_flush_log_at_trx_commit=1 

  sync_binlog=1

 測試場景4

  innodb_flush_log_at_trx_commit=1

  sync_binlog=1000

 測試場景5 

  innodb_flush_log_at_trx_commit=2 

  sync_binlog=1000 

由此可見，當兩個參數(shù)設置為雙1的時候，寫入性能最差，sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 時，(在當前模式下)MySQL的寫操作才能達到最高性能。

三 安全

當innodb_flush_log_at_trx_commit和sync_binlog  都為 1 時是最安全的，在mysqld 服務崩潰或者服務器主機crash的情況下，binary log 只有可能丟失最多一個語句或者一個事務。但是魚與熊掌不可兼得，雙11 會導致頻繁的io操作，因此該模式也是最慢的一種方式。

當innodb_flush_log_at_trx_commit設置為0，mysqld進程的崩潰會導致上一秒鐘所有事務數(shù)據(jù)的丟失。
當innodb_flush_log_at_trx_commit設置為2，只有在操作系統(tǒng)崩潰或者系統(tǒng)掉電的情況下，上一秒鐘所有事務數(shù)據(jù)才可能丟失。

雙1適合數(shù)據(jù)安全性要求非常高，而且磁盤IO寫能力足夠支持業(yè)務，比如訂單,交易,充值,支付消費系統(tǒng)。雙1模式下，當磁盤IO無法滿足業(yè)務需求時 比如11.11 活動的壓力。推薦的做法是 innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N為500 或1000) 且使用帶蓄電池后備電源的緩存cache，防止系統(tǒng)斷電異常。

    系統(tǒng)性能和數(shù)據(jù)安全是業(yè)務系統(tǒng)高可用穩(wěn)定的必要因素。我們對系統(tǒng)的優(yōu)化需要尋找一個平衡點，合適的才是最好的，根據(jù)不同的業(yè)務場景需求，可以將兩個參數(shù)做組合調(diào)整，以便是db系統(tǒng)的性能達到最優(yōu)化。

看完上述內(nèi)容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業(yè)資訊頻道，感謝您對億速云的支持。