mysql sync_binlog和 innodb_flush_log_at_trx_commit

賀子_DBA時代發表於2017-08-14
MySqlMIT
今天聊一聊關於mysql innodb儲存引擎中一個事務的完整流程：
首先說下innodb的事務日誌概念：
ib_logfile檔案就是innodb的事務日誌，可以理解是INNODB的REDO日誌，當資料庫異常關閉的時候，innodb儲存引擎下的mysql藉助事務日誌來完成例項恢復，即前滾和回滾來保證資料庫一致性；
區別於binlog日誌又叫二進位制日誌檔案，它會將mysql中所有修改資料庫資料的Query以二進位制的形式記錄到日誌檔案中,如：create,insert,drop,update等;（對於select操作則不會被記錄到binlog裡，因為它並沒有修改資料庫的資料），binlog主要是用於保證資料完整的，如主從備份，通過從binlog檔案中讀取操作Query來在salve機上進行同樣的操作，保證主從同步，同時也可以作為恢復資料的工具。
Innodb還有另外一個日誌Undo log，但Undo log是存放在共享表空間裡面的（ibdata*檔案，儲存的是check point日誌序列號）。
InnoDB 日誌緩衝區（InnoDB Log Buffer）：這是 InnoDB 儲存引擎的事務日誌所使用的緩衝區。類似於 Binlog Buffer，InnoDB 在寫事務日誌的時候，為了提高效能，也是先將資訊寫入 Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit 引數所設定的相應條件（或者日誌緩衝區寫滿）之後，才會將日誌寫到檔案（或者同步到磁碟）中。可以通過 innodb_log_buffer_size 引數設定其可以使用的最大記憶體空間；
下面重點講解 innodb_flush_log_trx_commit 引數：下圖可以清楚的展現出該引數設定成不同值時log重新整理的不同過程；

針對這張圖第一個箭頭代表著每次commit的時候，事務日誌到達的地方， 然後第二個箭頭，代表重新整理到磁碟永久儲存的過程, 後面的fsync every commit、fsync every second 、fsync every second 是在分別形容第二個箭頭重新整理的條件。
然後還需要注意的：巨集觀上寫進logfile就是寫進磁碟了。但是微觀上寫進logfile是先寫進了os cahce，然後再重新整理到raid cache(前提是做了raid)最後到磁碟。
具體分析innodb_flush_log_at_trx_commit=N的意義：
innodb_flush_log_at_trx_commit=0，每次commit時,事務日誌寫進了innodb log buffer ,然後每秒Log Thread 會將事務日誌從innodb log buffer重新整理到ib_ogfile(也就重新整理到了磁碟）。當innodb_flush_log_at_trx_commit設定為0，mysqld程式的崩潰會導致上一秒鐘所有事務資料的丟失，這是因為每次commit，事務日誌只是寫進了innodb log buffer 中，然後是每秒才將innodb log buffer 中的事務日誌重新整理到磁碟永久儲存，所以mysqld程式的崩潰時，innodb log buffer可能會有一秒的日誌沒有重新整理出來，但是在這種情況下，MySQL效能最好；
innodb_flush_log_at_trx_commit=2，每次commit時，事務日誌寫進了innodb log buffer，並同時接著寫進os cache, 也就是說每次commit，事務日誌寫進了os cache中， 然後每秒從os cache重新整理到ib_logfile(也就是重新整理到了磁碟）。當innodb_flush_log_at_trx_commit設定為2，只有在作業系統崩潰或者系統掉電的情況下，上一秒鐘所有事務資料才可能丟失，因為每次commit，事務日誌已經進入了os cache,所以mysqld崩潰，事務日誌是不會丟失的；
innodb_flush_log_at_trx_commit設定為1，這是最安全的設定，同時由於頻繁的io操作，導致效率是最差的，這時候不管是mysqld，還是作業系統崩潰，都不會丟資料，這是因為每次commit,事務日誌都重新整理到了磁碟永久儲存了；
選取的原則：
對於一些資料一致性和完整性要求不高的應用，配置為 2 就足夠了；如果為了最高效能，可以設定為 0。有些應用，如支付服務，對一致性和完整性要求很高，所以即使最慢，也最好設定為 1.。
然後介紹引數sync_binlog ：
sync_binlog =  N： 控制的是從binlog buffer中重新整理binlog到底層binlog檔案（也就是重新整理到底層磁碟）
N>0    每向二進位制日誌檔案寫入N條SQL或N個事務後，則把二進位制日誌檔案的資料重新整理到磁碟上； 
N=0    不主動重新整理二進位制日誌檔案的資料到磁碟上，而是由作業系統決定； 
推薦配置組合： 
1）innodb_flush_log_at_trx_commit=1同時sync_binlog =1
這就是所謂的雙1設定：這種配置適合資料安全性要求非常高，而且磁碟IO寫能力足夠支援業務，比如充值消費系統，銀行業務； 
2）innodb_flush_log_at_trx_commit=1同時sync_binlog =0
這種設定：保證了事務日誌是全的，也就保證可以例項恢復，即前滾和回滾，適合資料安全性要求高，磁碟IO寫能力不太富餘；
3））innodb_flush_log_at_trx_commit=2或者0同時sync_binlog =2或者m(0<m<100) </m<100)<>
這種設定：適合資料安全性有要求，允許丟失一點事務日誌；
4）innodb_flush_log_at_trx_commit=0同時sync_binlog =0
這種配置適合 ：磁碟IO寫能力有限，對資料安全要求較低，例如：日誌性登記業務； 
總結：通過對innodb_flush_log_at_trx_commit引數的學習，我知道了mysql在重新整理事務日誌的時候，還會經歷os cache這個過程，並且雙1設定可以實現很高的一致性需求；

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-2143511/，如需轉載，請註明出處，否則將追究法律責任。

相關文章