MySQL併發複製系列一：binlog組提交

  MySQL二階段提交流程：

            Storage Engine（InnoDB） transaction prepare階段：即sql語句已經成功執行並生成redo和undo的記憶體日誌

            Binary log日誌提提交

• write()將binary log記憶體日誌資料寫入檔案系統快取
• fsync()將binary log 檔案系統快取日誌資料永久寫入磁碟

            Storage Engine(InnoDB)內部提交

• commit階段在儲存引擎內提交( innodb_flush_log_at_trx_commit控制)使undo和redo永久寫入磁碟

    開啟Binary log的MySQL在crash recovery時：

• 當事務在prepare階段crash，資料庫recovery的時候該事務未寫入Binary log並且儲存引擎未提交，將該事務roll back。
• 當事務在Binary log日誌已經fsync()永久寫入二進位制日誌時crash，但是儲存引擎未來得及commit,此時MySQL資料庫recovery的時候將會從二進位制日誌的Xid（MySQL資料庫內部分散式事務XA）中獲取提交的資訊重新將該事務重做並commit使儲存引擎和二進位制日誌始終保持一致。

以上提到單個事務的二階段提交過程，能夠保證儲存引擎和binary log日誌保持一致，但是在併發的情況下怎麼保證儲存引擎和Binary Log提交的順序一致？當多個事務併發提交的情況，如果Binary Log和儲存引擎順序不一致會造成什麼影響？

如上圖：事務按照T1、T2、T3順序開始執行，將二進位制日誌（按照T1、T2、T3順序）寫入日誌檔案系統快取，呼叫fsync()進行一次group commit將日誌檔案永久寫入磁碟，但是儲存引擎提交的順序為T2、T3、T1。當T2、T3提交事務之後做了一個On-line的backup程式新建一個slave來做replication，那麼事務T1在slave機器restore MySQL資料庫的時候發現未在儲存引擎內提交，T1事務被roll back，此時主備資料不一致(搭建Slave時，change master to的日誌偏移量記錄T3在事務位置之後)。

結論：MySQL資料庫上層二進位制日誌的寫入順序和儲存引擎InnoDB層的事務提交順序一致，用於備份及恢復需要，如xtrabackup和innobackpex工具。
      為了解決以上問題，在早期的MySQL版本，通過prepare_commit_mutex 鎖保證MySQ資料庫上層二進位制日誌和Innodb儲存引擎層的事務提交順序一致。

圖3可以看出在prepare_commit_mutex，只有當上一個事務commit後釋放鎖，下一個事務才可以進行prepara操作，並且在每個transaction過程中Binary log沒有fsync()的呼叫。由於記憶體資料寫入磁碟的開銷很大，如果頻繁fsync()把日誌資料永久寫入磁碟資料庫的效能將會急劇下降。此時MySQL 資料庫提供sync_binlog引數來設定多少個binlog日誌產生的時候呼叫一次fsync()把二進位制日誌刷入磁碟來提高整體效能，該引數的設定作用：

• sync_binlog=0，二進位制日誌fsync()的操作基於作業系統。
• sync_binlog=1，每一個transaction commit都會呼叫一次fsync()，此時能保證資料最安全但是效能影響較大。
• sync_binlog=N，當資料庫crash的時候至少會丟失N-1個transactions。

圖3 所示MySQL開啟Binary log時使用prepare_commit_mutex和sync_log保證二進位制日誌和儲存引擎順序保持一致（通過sync_binlog來控制日誌的重新整理頻率），prepare_commit_mutex的鎖機制造成高併發提交事務的時候效能非常差而且二進位制日誌也無法group commit。

那麼如何保證MySQL開啟Binary Log日誌後使二進位制日誌寫入順序和儲存引擎提交順序保持一致並且能夠進行二進位制日誌的Group Commit？

MySQL 5.6 引入BLGC（Binary Log Group Commit）,二進位制日誌的提交過程分成三個階段，Flush stage、Sync stage、Commit stage。

那麼事務提交過程簡化為：

儲存引擎（InnoDB) Prepare    ---->    資料庫上層(Binary Log)   Flush Stage    ---->    Sync Stage    ---->    調儲存引擎（InnoDB）Commit stage.

每個stage階段都有各自的佇列，使每個session的事務進行排隊。當一個執行緒註冊了一個空佇列，該執行緒就視為該佇列的leader，後註冊到該佇列的執行緒為follower，leader控制佇列中follower的行為。leader同時帶領當前佇列的所有follower到下一個stage去執行，當遇到下一個stage並非空佇列，此時leader可以變成follower到此佇列中（注：follower的執行緒不可能變成leader）

在 Flush stage：所有已經註冊執行緒都將寫入binary log快取

在Sync stage ：binary log快取的資料將會sync到磁碟，當sync_binlog=1時所有該佇列事務的二進位制日誌快取永久寫入磁碟

在 Commit stage:leader根據順序呼叫儲存引擎提交事務。

當一組事務在進行Commit階段時，其他新的事務可以進行Flush階段，從而使group commit不斷生效。那麼為了提高group commit中一組佇列的事務數量，MySQL用binlog_max_flush_queue_time來控制在Flush stage中的等待時間，讓Flush佇列在此階段多等待一些時間來增加這一組事務佇列的數量使該佇列到Sync階段可以一次fysn()更多的事務。

MySQL 5.7 Parallel replication實現主備多執行緒複製基於主庫Binary Log Group Commit, 並在Binary log日誌中標識同一組事務的last_commited=N和該組事務內所有的事務提交順序。為了增加一組事務內的事務數量提高備庫組提交時的併發量引入了binlog_group_commit_sync_delay=N 和binlog_group_commit_sync_no_delay_count=N (注：binlog_max_flush_queue_time 在MySQL的5.7.9及之後版本不再生效)引數，MySQL等待binlog_group_commit_sync_delay毫秒直到達到binlog_group_commit_sync_no_delay_count事務個數時，將進行一次組提交。

Reference:http://mysqlmusings.blogspot.kr/2012/06/binary-log-group-commit-in-mysql-56.html