Mysql 並行,MySQL5.7 並行復制的學習

转载于 2021-03-17 07:31:38 发布 · 101 阅读

文章标签：

#Mysql 並行

本文详细介绍了MySQL 5.6及5.7版本中的并行复制机制，包括基于库级别和组提交的并行复制原理，并提供了配置示例。

MySQL 5.6 基於庫級別的並行復制

MySQL5.6的並行復制是庫(schema)級別的，從庫為每個庫(schema)分配一個線程以此來提高復制效率

在MySQL 5.6版本之前，Slave服務器上有兩個線程I/O線程和SQL線程。I/O線程負責接收二進制日志(更准確的說是二進制日志的event)，SQL線程進行回放二進制日志。

MySQL5.6開啟並行復制時，從庫SQL線程就變為了coordinator線程，coordinator線程主要負責以下兩部分的內容：

判斷可以並行執行，那么選擇worker線程執行事務的二進制日志

判斷不可以並行執行，如該操作是DDL，亦或者是事務跨schema操作，則等待所有的worker線程執行完成之后，再執行當前的日志。

這意味着coordinator線程並不是僅將日志發送給worker線程，自己也可以回放日志，但是所有可以並行的操作交付由worker線程完成。coordinator線程與worker是典型的生產者與消費者模型。

對於有多個數據庫的實例，開啟並行的執行SQL，對從庫能有較大的提升。但對單個庫，開啟多線程復制，性能可能比單線程還差。

MySQL 5.7 基於組提交(LOGICAL_CLOCK)的並行復制

MySQL5.7的並行復制是基於組提交(LOGICAL_CLOCK)，主要思想是一個組提交的事務都是可以並行回放到從，原理是基於鎖的沖突檢測，因為這些事務都已進入到事務的prepare階段，則說明事務之間沒有任何沖突(否則就不可能提交)。

MySQL5.7並行復制引入了兩個值last_committed和sequence_number。last_committed表示事務提交的時候，上次事務提交的編號，在主庫上同時提交的事務設置成相同的last_committed。如果事務具有相同的last_committed，表示這些事務都在一組內，可以進行並行的回放。

總的來說就是：並發線程執行不同的事務只要在同一時刻能夠commit(說明線程之間沒有鎖沖突)，那么master節點就可以將這一組的事務標記並在slave機器上安全的進行並發重放主庫提交的事務。所以盡可能的使所有線程能在同一時刻提交可以，可以極大的提高slave機器並發執行事務的數量使主備數據同步。

為了兼容MySQL 5.6基於庫的並行復制，5.7引入了新的變量slave-parallel-type，其可以配置的值有：

DATABASE：默認值，基於庫的並行復制方式

LOGICAL_CLOCK：基於組提交的並行復制方式

主端相關參數：

binlog_group_commit_sync_delay：表示binlog提交后等待延遲多少時間再同步到磁盤，單位是微秒，默認0，不延遲。設置延遲可以讓多個事務在用一時刻提交，提高binlog組提交的並發數和效率，從而提高slave的吞吐量。

binlog_group_commit_sync_no_delay_count：表示在等待上面參數超時之前，如果有足夠多的事務，則停止等待直接提交。單位是事務數，默認0。

從端相關參數：

slave_parallel_workers：設置為0，則MySQL 5.7退化為原單線程復制；設置為1，則SQL線程功能轉化為coordinator線程，但是只有1個worker線程進行回放，也是單線程復制，性能反而比0還要差

那么如何知道事務是否在一組中？在MySQL 5.7是將組提交的信息存放在GTID中。那么如果用戶沒有開啟GTID功能，即將參數gtid_mode設置為OFF呢？故MySQL 5.7又引入了稱之為Anonymous_Gtid的二進制日志event類型，如：

mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000005 | grep last_committed

#181228 11:26:19 server id 1 end_log_pos 219 CRC32 0x5f456ce6 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes

#181228 11:26:25 server id 1 end_log_pos 489 CRC32 0x70efcdd6 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes

#181228 11:29:25 server id 1 end_log_pos 758 CRC32 0x988d75b0 Anonymous_GTID last_committed=1 sequence_number=3 rbr_only=yes

#181228 11:29:30 server id 1 end_log_pos 1027 CRC32 0x0441d881 Anonymous_GTID last_committed=1 sequence_number=4 rbr_only=yes

#181228 11:30:23 server id 1 end_log_pos 1299 CRC32 0xacef32af Anonymous_GTID last_committed=2 sequence_number=5 rbr_only=yes

#181228 11:41:24 server id 1 end_log_pos 1578 CRC32 0xe19286ec Anonymous_GTID last_committed=3 sequence_number=6 rbr_only=yes

#181228 11:43:14 server id 1 end_log_pos 1857 CRC32 0x29a01493 Anonymous_GTID last_committed=4 sequence_number=7 rbr_only=yes

#181228 11:43:30 server id 1 end_log_pos 2139 CRC32 0x9acc9bff Anonymous_GTID last_committed=1 sequence_number=8 rbr_only=yes

#181228 11:43:31 server id 1 end_log_pos 2436 CRC32 0x1c43ff17 Anonymous_GTID last_committed=1 sequence_number=9 rbr_only=yes

#181228 11:43:31 server id 1 end_log_pos 2778 CRC32 0x93632d39 Anonymous_GTID last_committed=1 sequence_number=10 rbr_only=yes

#181228 11:43:31 server id 1 end_log_pos 3210 CRC32 0xf2d0d2b3 Anonymous_GTID last_committed=1 sequence_number=11 rbr_only=yes

#181228 11:43:32 server id 1 end_log_pos 3822 CRC32 0xc1481a21 Anonymous_GTID last_committed=1 sequence_number=12 rbr_only=yes

#181228 11:43:32 server id 1 end_log_pos 4794 CRC32 0x58c1beae Anonymous_GTID last_committed=1 sequence_number=13 rbr_only=yes

#181228 11:43:33 server id 1 end_log_pos 6486 CRC32 0x4364eaf9 Anonymous_GTID last_committed=1 sequence_number=14 rbr_only=yes

#181228 11:43:33 server id 1 end_log_pos 9618 CRC32 0x2c5edb8c Anonymous_GTID last_committed=1 sequence_number=15 rbr_only=yes

#181228 11:43:34 server id 1 end_log_pos 15630 CRC32 0xa75cc5f4 Anonymous_GTID last_committed=1 sequence_number=16 rbr_only=yes

#181228 11:43:34 server id 1 end_log_pos 27437 CRC32 0xff0942d4 Anonymous_GTID last_committed=1 sequence_number=17 rbr_only=yes

#181228 11:43:34 server id 1 end_log_pos 50799 CRC32 0x1cb6f41f Anonymous_GTID last_committed=1 sequence_number=18 rbr_only=yes

#181228 11:43:35 server id 1 end_log_pos 97306 CRC32 0x5d03c9b8 Anonymous_GTID last_committed=1 sequence_number=19 rbr_only=yes

#181228 11:43:40 server id 1 end_log_pos 190103 CRC32 0x90e95ad1 Anonymous_GTID last_committed=19 sequence_number=20 rbr_only=yes

#181228 11:43:41 server id 1 end_log_pos 375445 CRC32 0x2515833b Anonymous_GTID last_committed=20 sequence_number=21 rbr_only=yes

#181228 11:43:41 server id 1 end_log_pos 745912 CRC32 0x81a72e5c Anonymous_GTID last_committed=21 sequence_number=22 rbr_only=yes

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

上面展示的日志內容可以看出，sequence_number 1~19 事物的last_committed都是1,意味着sequence_number 1~19 事物屬於同一組可以並行提交。

配置基於組提交(LOGICAL_CLOCK)的並行復制

開啟並行復制，只需在slave端加如下配置：

[mysqld]

#slave

slave_parallel_workers = 4 ###並行復制的線程數

slave_parallel_type = LOGICAL_CLOCK ###並行復制的類型，默認database

master_info_repository = table

relay_log_info_repository = table

relay_log_recovery = 1

slave端僅僅設置為LOGICAL_CLOCK也會存在問題，因為此時在slave上應用事務的順序是無序的，和relay log中記錄的事務順序不一樣，這樣數據一致性是無法保證的，為了保證事務是按照relay log中記錄的順序來回放，就需要開啟參數slave_preserve_commit_order。

開啟slave_preserve_commit_order參數后，slave_parallel_type只能是LOGICAL_CLOCK

slave_preserve_commit_order=1

開啟該參數后，worker線程將一直等待, 直到提交之前所有的事務。當從線程正在等待其他工作人員提交其事務時, 它報告其狀態為等待前面的事務提交。所以雖然slave可以並行應用relay log，但commit部分仍然是順序提交，其中可能會有等待的情況。

當開啟slave_preserve_commit_order參數后，slave_parallel_type只能是LOGICAL_CLOCK，如果你有使用級聯復制，那LOGICAL_CLOCK可能會使離master越遠的slave並行性越差。

PS: 經測試這個參數只有在MySQL 5.7.19設置才有效

基於寫集合的並行復制

writeset的思想是：不同事物修改了不同行的數據，那么可以視為同一組。MySQL 會對這個提交的事務中的一行記錄做一個 HASH值，這些 HASH 值稱為 writeset。writeset會存入一張 HASH 表。其他事務提交時會檢查這張 HASH 表中是否有相同的記錄，如果不相同，則視為同組，如果有相同，則視為不同組。怎么判斷是否同組，依然采用了last_committed。

實現基於寫集的並行復制，需要在master端添加如下配置：

[mysqld]

transaction_write_set_extraction=XXHASH64

binlog_transaction_dependency_tracking=WRITESET

這時候，我們分別執行兩次insert操作，並解析出binlog:

BEGIN

/*!*/;

# at 291

#181228 17:04:29 server id 1 end_log_pos 340 CRC32 0xa581c6c2 Table_map: `mydb`.`ttt` mapped to number 108

# at 340

#181228 17:04:29 server id 1 end_log_pos 390 CRC32 0x72728e77 Write_rows: table id 108 flags: STMT_END_F

### INSERT INTO `mydb`.`ttt`