MySQL 5.7並行複製

MySQL 5.7並行複製原理

MySQL 從 5.6 開始引入了多庫並行主從複製，但是其並行只是基於  Schema 的，也就是基於庫的。如果使用者的 MySQL 資料庫例項中存在多個  Schema，對於從機複製的速度的確可以有比較大的幫助。MySQL 5.6 並行複製的架構如下所示：

在上圖的紅色框框部分就是實現並行複製的關鍵所在。在 MySQL 5.6 版本之前，Slave 伺服器上有兩個執行緒 I/O 執行緒和 SQL 執行緒。I/O 執行緒負責接收二進位制日誌(更準確的說是二進位制日誌的 event )，SQL 執行緒進行回放二進位制日誌。如果在 MySQL 5.6 版本開啟並行複製功能，那麼SQL執行緒就變為了  Coordinator 執行緒， Coordinator 執行緒主要負責以前兩部分的內容：

• 若判斷可以並行執行，那麼選擇  Worker 執行緒執行事務的二進位制日誌。

• 若判斷不可以並行執行，如該操作是  DDL，亦或者是事務跨  Schema 操作，則等待所有的  Worker 執行緒執行完成之後，再執行當前的日誌。

這意味著  Coordinator 執行緒並不是僅將日誌傳送給  Worker 執行緒，自己也可以回放日誌，但是所有可以並行的操作交付由  Worker 執行緒完成。 Coordinator 執行緒與  Worker 是典型的生產者與消費者模型。

上述機制實現的基於  Schema 的並行複製存在兩個問題，首先是  Crash Safe 功能不好做，因為可能之後執行的事務由於並行複製的關係先完成執行，那麼當發生 Crash 的時候，這部分的處理邏輯是比較複雜的。從程式碼上看，5.6 這裡引入了  Low-Water-Mark 標記來解決該問題，從設計上看，其是希望藉助於日誌的冪等性來解決該問題，不過 5.6 的二進位制日誌回放還不能實現冪等性。另一個最為關鍵的問題是這樣設計的並行複製效果並不高，如果使用者例項僅有一個庫，那麼就無法實現並行回放，甚至效能會比原來的單執行緒更差。而單庫多表是比多庫多表更為常見的一種情形。

MySQL 5.7 才可稱為真正的並行複製，這其中最為主要的原因就是 Slave 伺服器的回放與主機是一致的即 Master 伺服器上是怎麼並行執行的 Slave 上就怎樣進行並行回放。不再有庫的並行複製限制，對於二進位制日誌格式也無特殊的要求（基於庫的並行複製也沒有要求）。

從 MySQL 官方來看，其並行複製的原本計劃是支援表級的並行複製和行級的並行複製，行級的並行複製通過解析 ROW 格式的二進位制日誌的方式來完成。但是最終出現的是在開發計劃中稱為： MTS: Prepared transactions slave parallel applier。

該並行複製的思想最早是由 MariaDB 的 Kristain 提出，並已在 MariaDB 10 中出現，MySQL 5.7 並行複製的思想簡單易懂，一言以蔽之：一個組提交的事務都是可以並行回放，因為這些事務都已進入到事務的  Prepare 階段，則說明事務之間沒有任何衝突（否則就不可能提交）。

為了相容 MySQL 5.6 基於庫的並行複製，5.7 引入了新的變數  slave-parallel-type，其可以配置的值有：

• DATABASE：預設值，基於庫的並行複製方式。

• LOGICAL_CLOCK：基於組提交的並行複製方式。

如何知道事務是否在一組中，又是一個問題，因為原版的 MySQL 並沒有提供這樣的資訊。在 MySQL 5.7版本中，其設計方式是將組提交的資訊存放在  GTID 中。那麼如果使用者沒有開啟  GTID 功能，即將引數  gtid_mode 設定為 OFF 呢？故 MySQL 5.7 又引入了稱之為  Anonymous_Gtid 的二進位制日誌  event 型別，如：

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000011';
| mysql-bin.000011 | 123 | Previous_gtids | 88 | 194 | f11232f7-ff07-11e4-8fbb-00ff55e152c6:1-2 |
| mysql-bin.000011 | 194 | Anonymous_Gtid | 88 | 259 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000011 | 259 | Query | 88 | 330 | BEGIN |
| mysql-bin.000011 | 330 | Table_map | 88 | 373 | table_id: 108 (aaa.t) |
| mysql-bin.000011 | 373 | Write_rows | 88 | 413 | table_id: 108 flags: STMT_END_F |
......

這意味著在 MySQL 5.7 版本中即使不開啟  GTID ，每個事務開始前也是會存在一個  Anonymous_Gtid ，而這  GTID 中就存在著組提交的資訊。

組提交是個比較好玩的方式，我們根據 MySQL 的  binlog 可以發現較之原來的二進位制日誌內容多了  last_committed 和  sequence_number 。

$ mysqlbinlog mysql-bin.000011 |grep last_committed
#170607 11:24:57 server id 353306 end_log_pos 876350 CRC32 0x92093332 GTID last_committed=654 sequence_number=655
#170607 11:24:58 server id 353306 end_log_pos 880406 CRC32 0x344fdf71 GTID last_committed=655 sequence_number=656
#170607 11:24:58 server id 353306 end_log_pos 888700 CRC32 0x4ba2b05b GTID last_committed=656 sequence_number=657

上面是沒有開啟組提交的一個日誌，我們可以看得到  binlog 當中有兩個引數  last_committed 和  sequence_number，我們可以看到，下一個事務在主庫配置好組提交以後， last_committed 永遠都和上一個事務的  sequence_number 是相等的。這也很容易理解，因為事務是順序提交的。

下面看一下組提交模式的事務：

$ mysqlbinlog mysql-bin.000012|grep last_commit
#170609 10:11:07 server id 353306 end_log_pos 75629 CRC32 0xd54f2604 GTID last_committed=269 sequence_number=270
#170609 10:13:03 server id 353306 end_log_pos 75912 CRC32 0x43675b14 GTID last_committed=270 sequence_number=271
#170609 10:13:24 server id 353306 end_log_pos 76195 CRC32 0x4f843438 GTID last_committed=270 sequence_number=272

我們可以看到最後兩個事務的  last_committed 是相同的，這意味著這兩個事務是作為一個組提交的，兩個事務在  Perpare 階段獲取相同的  last_committed 而且相互不影響，最終是會作為一個組進行提交。這就是所謂的組提交。組提交的事務是可以在從機進行並行回放的。

上述的  last_committed 和  sequence_number 代表的就是所謂的  LOGICAL_CLOCK 。

配置MySQL並行複製

環境準備

這裡一共使用了二臺機器，MySQL 版本都為 5.7.18。

機器名	IP地址	MySQL角色
dev-master-01	192.168.100.210	MySQL 主庫
dev-node-02	192.168.100.212	MySQL 從庫

安裝MySQL

MySQL 安裝比較簡單，在 「 MySQL 5.7多源複製實踐」一文中我們也講了，這裡就不在重複講了。如果你還不會安裝，可以先參考此文安裝好 MySQL 。

啟用MySQL並行複製

MySQL 5.7的並行複製建立在組提交的基礎上，所有在主庫上能夠完成  Prepared 的語句表示沒有資料衝突，就可以在 Slave 節點並行複製。

關於 MySQL 5.7 的組提交，我們要看下以下的引數：

mysql> show global variables like '%group_commit%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 0     |
| binlog_group_commit_sync_no_delay_count | 0     |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)

要開啟 MySQL 5.7 並行複製需要以下二步，首先在主庫設定  binlog_group_commit_sync_delay 的值大於0 。

mysql> set global binlog_group_commit_sync_delay=10;

這裡簡要說明下  binlog_group_commit_sync_delay 和  binlog_group_commit_sync_no_delay_count 引數的作用。

• binlog_group_commit_sync_delay

全域性動態變數，單位微妙，預設0，範圍：0～1000000（1秒）。

表示  binlog 提交後等待延遲多少時間再同步到磁碟，預設0 ，不延遲。當設定為 0 以上的時候，就允許多個事務的日誌同時一起提交，也就是我們說的組提交。組提交是並行複製的基礎，我們設定這個值的大於 0 就代表開啟了組提交的功能。

• binlog_group_commit_sync_no_delay_count

全域性動態變數，單位個數，預設0，範圍：0～1000000。

表示等待延遲提交的最大事務數，如果上面引數的時間沒到，但事務數到了，則直接同步到磁碟。若  binlog_group_commit_sync_delay 沒有開啟，則該引數也不會開啟。

其次要在 Slave 主機上設定如下幾個引數：

# 過多的執行緒會增加執行緒間同步的開銷，建議4-8個Slave執行緒。
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4

或者直接線上啟用也是可以的：

mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> set global slave_parallel_type='LOGICAL_CLOCK';
Query OK, 0 rows affected (0.00 sec)
mysql> set global slave_parallel_workers=4;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.06 sec)
mysql> show variables like 'slave_parallel_%';
+------------------------+---------------+
| Variable_name          | Value         |
+------------------------+---------------+
| slave_parallel_type    | LOGICAL_CLOCK |
| slave_parallel_workers | 4             |
+------------------------+---------------+
2 rows in set (0.00 sec)

檢查Worker執行緒的狀態

當前的 Slave 的 SQL 執行緒為  Coordinator（協調器），執行  Relay log 日誌的執行緒為  Worker(當前的 SQL 執行緒不僅起到協調器的作用，同時也可以重放  Relay log 中主庫提交的事務)。

我們上面設定的執行緒數是 4 ，從庫就能看到 4 個  Coordinator（協調器）程式。

並行複製配置與調優

開啟 MTS 功能後，務必將引數  master-info-repository 設定為 TABLE ，這樣效能可以有 50%~80% 的提升。這是因為並行複製開啟後對於  master.info 這個檔案的更新將會大幅提升，資源的競爭也會變大。

在 MySQL 5.7 中，推薦將  master-info-repository 和  relay-log-info-repository設定為 TABLE ，來減小這部分的開銷。

master-info-repository = table
relay-log-info-repository = table
relay-log-recovery = ON

並行複製監控

複製的監控依舊可以通過  SHOW SLAVE STATUS\G，但是 MySQL 5.7 在  performance_schema 架構下多了以下這些後設資料表，使用者可以更細力度的進行監控：

mysql> use performance_schema;
mysql> show tables like 'replication%';
+---------------------------------------------+
| Tables_in_performance_schema (replication%) |
+---------------------------------------------+
| replication_applier_configuration           |
| replication_applier_status                  |
| replication_applier_status_by_coordinator   |
| replication_applier_status_by_worker        |
| replication_connection_configuration        |
| replication_connection_status               |
| replication_group_member_stats              |
| replication_group_members                   |
+---------------------------------------------+
8 rows in set (0.00 sec)

參考文件

http://www.google.com
http://www.blogs8.cn/posts/2AR0c5
http://www.weidu8.net/wx/1000149002520518
https://www.kancloud.cn/thinkphp/mysql-parallel-applier
http://www.cnblogs.com/zhoujinyi/p/5614135.html
http://www.cnblogs.com/shengdimaya/p/6972278.html
http://blog.itpub.net/28218939/viewspace-1975822/

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ DB寶）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群號： 230161599 、618766405 ● 微 信群：可加我微 信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ），註明新增緣由 ● 於 2020-04-01 06:00 ~ 2020-04-30 24:00 在西安完成 ● 最新修改時間：2020-04-01 06:00 ~ 2020-04-30 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（ DB寶）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。 ........................................................................................................................

 

 

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