mysql基于組提交的并發(fā)復(fù)制小結(jié)

一：MySQL 5.7并行復(fù)制初理解

我們知道MySQL 5.7并行復(fù)制引入了兩個(gè)值last_committed和sequence_number。last_committed表示事務(wù)提交的時(shí)候，上次事務(wù)提交的編號(hào)，在主庫(kù)上同時(shí)提交的事務(wù)設(shè)置成相同的last_committed。如果事務(wù)具有相同的last_committed，表示這些事務(wù)都在一組內(nèi)，可以進(jìn)行并行的回放。

查看組提交的事務(wù)個(gè)數(shù),其中9892是last_commited的值，相同的last_commited值，后面的sequnen ce_number是可以并行復(fù)制的事務(wù)的一個(gè)序列號(hào)，新的binlog文件是從1開始的，給gtid的xid沒有關(guān)系！

cat binlog.log | grep GTID | grep last_committed | grep 19408

二：造成延遲的原因：

表沒有主鍵，事務(wù)太大，并行力度不夠,參數(shù)設(shè)置不合適，從庫(kù)硬件差

二：Mysql 5.7并行復(fù)制相關(guān)的參數(shù)

一般主從延遲太大，要不是沒有主鍵，要不是配置的不合理

依次介紹些這些參數(shù)的作用，以及設(shè)置途徑

binlog_group_commit_sync_delay

binlog_group_commit_sync_no_delay_count

max_allowed_packet

slave_max_allowed_packet

slave_preserve_commit_order

slave_pending_jobs_size_max

一：binlog_group_commit_sync_delay ：

Property	Value
Command-Line Format	--binlog-group-commit-sync-delay=#
System Variable	binlog_group_commit_sync_delay
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	0
Minimum Value	0
Maximum Value	1000000

1）該參數(shù)控制mysql 組提交之前等待的微秒數(shù)，設(shè)置該參數(shù)可以讓組提交的每個(gè)組里面的事務(wù)數(shù)更多（因?yàn)樗却薾微妙），因?yàn)槊總€(gè)組里面的事務(wù)可以在從庫(kù)并行的去重放，所以設(shè)置該參數(shù)大于0，有助于提高從庫(kù)應(yīng)用日志的速度，減少?gòu)膸?kù)延遲

設(shè)置binlog_group_commit_sync_delay可以增加從庫(kù)的并行提交事務(wù)數(shù)，因此可以增加復(fù)制從屬服務(wù)器上的并行執(zhí)行能力。要受益于此效果，從屬服務(wù)器必須設(shè)置slave_parallel_type=LOGICAL_CLOCK，并且在還設(shè)置binlog_transaction_dependency_tracking=COMMIT_ORDER時(shí)效果更顯著。在調(diào)整binlog_group_commit_sync_delay的設(shè)置時(shí)，必須同時(shí)考慮主設(shè)備的吞吐量和從設(shè)備的吞吐量。

2）但是設(shè)置binlog_group_commit_sync_delay會(huì)增加主服務(wù)器上事務(wù)的延遲，這可能會(huì)影響客戶端應(yīng)用程序。此外，在高度并發(fā)的工作負(fù)載上，可能會(huì)增加爭(zhēng)用，從而降低吞吐量，導(dǎo)致數(shù)據(jù)庫(kù)性能降低，還能在錯(cuò)誤日志中看到很多RECORD LOCK信息，

3）當(dāng)設(shè)置sync_binlog=0或sync_binlog=1時(shí)，binlog_group_commit_sync_delay指定的延遲將作用于每個(gè)事務(wù)組提交。當(dāng)sync_binlog設(shè)置為大于1的值n時(shí)，該參數(shù)的延遲作用于每n個(gè)二進(jìn)制日志提交組提交之間；

建議：該參數(shù)設(shè)置一定要結(jié)合實(shí)際情況，評(píng)估出mysql的并發(fā)量，計(jì)算出單位時(shí)間內(nèi)的并發(fā)量，然后合理設(shè)置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count（單位時(shí)間每組的事務(wù)數(shù)），一定要限制每個(gè)組的事務(wù)個(gè)數(shù)，因?yàn)槿绻辉O(shè)置的話，如果你的實(shí)際并發(fā)量挺大，這就會(huì)導(dǎo)致你的每個(gè)組的事務(wù)數(shù)可能非常多，然后我們知道組提交的commit階段是分三個(gè)步驟的，每個(gè)階段都是有隊(duì)列的，如果每個(gè)組太多事務(wù)，就會(huì)導(dǎo)致內(nèi)存隊(duì)列不夠用，而使用臨時(shí)文件，這樣就降低了commit的性能，造成更多的延遲，所以要使用 binlog_group_commit_sync_no_delay_count 來限制你的每個(gè)組的事務(wù)數(shù)，這樣就能盡可能多的把同一個(gè)組的事務(wù)數(shù)增多，但是也不至于太多，限制在一個(gè)合理的范圍，這些事務(wù)可以在從庫(kù)并發(fā)執(zhí)行，而如果不設(shè)置的話，那么這些事務(wù)可能有很多是串行的，也有可能事務(wù)太多導(dǎo)致性能降低而產(chǎn)生更多的延遲！

拓展參數(shù) binlog_transaction_depandency_tracking：

控制檢測(cè)是不是可以并行復(fù)制的方式，

基于主鍵的沖突檢測(cè)(binlog_transaction_depandency_tracking = COMMIT_ORDERE|WRITESET|WRITESET_SESSION, 修改的row的主鍵或非空唯一鍵沒有沖突，即可并行)

5.7.22 也支持了 write-set 機(jī)制

事務(wù)依賴關(guān)系： binlog_transaction_depandency_tracking = COMMIT_ORDERE|WRITESET|WRITESET_SESSION

COMMIT_ORDERE: 繼續(xù)基于組提交方式

WRITESET: 基于寫集合決定事務(wù)依賴（有問題?。?/div>

WRITESET_SESSION: 基于寫集合，但是同一個(gè)session中的事務(wù)不會(huì)有相同的last_committed

事務(wù)檢測(cè)算法： transaction_write_set_extraction = OFF| XXHASH64 | MURMUR32

MySQL會(huì)有一個(gè)變量來存儲(chǔ)已經(jīng)提交的事務(wù)HASH值，所有已經(jīng)提交的事務(wù)所修改的主鍵（或唯一鍵）的值經(jīng)過hash后都會(huì)與那個(gè)變量的集合進(jìn)行對(duì)比，來判斷改行是否與其沖突，并以此來確定依賴關(guān)系

這里說的變量，可以通過這個(gè)設(shè)置大小： binlog_transaction_dependency_history_size

這樣的粒度，就到了 row級(jí)別了，此時(shí)并行的粒度更加精細(xì)，并行的速度會(huì)更快，某些情況下，說slave的并行度超越master也不為過(master是單線程的寫，slave也可以并行回放)

二：binlog_group_commit_sync_no_delay_count

Property	Value
Command-Line Format	--binlog-group-commit-sync-no-delay-count=#
System Variable	binlog_group_commit_sync_no_delay_count
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	0
Minimum Value	0
Maximum Value	1000000

該參數(shù)必須和binlog_group_commit_sync_delay一起使用才有意義，當(dāng)已經(jīng)處于延遲的事務(wù)個(gè)數(shù)達(dá)到該參數(shù)設(shè)置的數(shù)的時(shí)候，就終止了binlog_group_commit_sync_delay參數(shù)設(shè)置的微妙延遲（不需要一定要延遲參數(shù)binlog_group_commit_sync_delay設(shè)置的微妙數(shù)），也就是該參數(shù)保證同一個(gè)組提交的組里面的事務(wù)數(shù)不能太多！

三：slave_preserve_commit_order

Property	Value
Command-Line Format	--slave-preserve-commit-order[={OFF\|ON}]
System Variable	slave_preserve_commit_order
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	OFF

1）對(duì)于多線程從機(jī)，此變量的設(shè)置1確保事務(wù)在主庫(kù)提交的順序與它們?cè)趶膸?kù)中繼日志中顯示的順序相同，并防止從庫(kù)中繼日志執(zhí)行的事務(wù)序列中出現(xiàn)間隙。此變量對(duì)未啟用多線程的從機(jī)沒有影響。請(qǐng)注意，slave_preserve_commit_order=1不保留非事務(wù)性DML更新的順序，因此這些更新可能在中繼日志中它們之前的事務(wù)之前提交，這可能會(huì)導(dǎo)致間隙；

2）slave_preserve_commit_order=1要求在從機(jī)上啟用--log bin和 --log-slave-updates ，并且slave_parallel_type設(shè)置為 LOGICAL_CLOCK。在更改此變量之前，必須停止所有復(fù)制線程（如果使用多個(gè)復(fù)制通道，則為所有復(fù)制通道）

3）在啟用slave_preserve_commit_order的情況下，sql線程在提交之前等待所有以前的事務(wù)提交，

當(dāng)從線程等待其他工作線程提交其事務(wù)時(shí)，它會(huì)將其狀態(tài)報(bào)告為:Waiting for preceding transaction to commit,所以如果啟用該參數(shù)，那么可能會(huì)加劇主從延遲！

4)如果設(shè)置slave_preserve_commit_order=0，則slave并行應(yīng)用的事務(wù)可能會(huì)無序提交。因此，檢查最近執(zhí)行的事務(wù)并不能保證來自主服務(wù)器的所有以前的事務(wù)都在從服務(wù)器上已經(jīng)執(zhí)行（也就是說某你在從庫(kù)看到的不是當(dāng)前的狀態(tài)，這不是延遲導(dǎo)致的，而是提交順序不一樣導(dǎo)致的不一致狀態(tài)）。在從機(jī)的中繼日志中執(zhí)行的事務(wù)序列中可能存在間隙。這對(duì)使用多線程應(yīng)用的從庫(kù)的日志記錄和恢復(fù)有影響。

四：binlog_order_commits

Property	Value
Command-Line Format	--binlog-order-commits[={OFF\|ON}]
System Variable	binlog_order_commits
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	ON

該參數(shù)控制：

當(dāng)在復(fù)制主機(jī)上啟用此變量（這是默認(rèn)設(shè)置）時(shí)，向存儲(chǔ)引擎發(fā)出的事務(wù)提交指令將使用單個(gè)線程串行的提交，以便事務(wù)始終按寫入二進(jìn)制日志的相同順序提交。禁用此變量允許使用多個(gè)線程發(fā)出事務(wù)提交指令。與二進(jìn)制日志組提交結(jié)合使用，可以防止單個(gè)事務(wù)的提交速率成為吞吐量的瓶頸，因此可能會(huì)提高性能（主從都設(shè)置）

當(dāng)所有涉及的存儲(chǔ)引擎都已確認(rèn)事務(wù)已準(zhǔn)備好提交時(shí)，事務(wù)將寫入二進(jìn)制日志。然后，二進(jìn)制日志組提交邏輯在二進(jìn)制日志寫入之后提交一組事務(wù)。當(dāng)binlog_order_commits被禁用時(shí)，由于此進(jìn)程使用多個(gè)線程，提交組中的事務(wù)可能會(huì)以不同于二進(jìn)制日志中事務(wù)順序的順序提交。（來自單個(gè)客戶機(jī)的事務(wù)總是按時(shí)間順序提交）在許多情況下，這無關(guān)緊要，因?yàn)榧热唤M提交中的事務(wù)可以并行復(fù)制，說明這些事務(wù)分開執(zhí)行是會(huì)產(chǎn)生一致性的結(jié)果的，否則不能并行復(fù)制只能單獨(dú)的事務(wù)執(zhí)行了；

建議：在一些不重要的從庫(kù)上，可以關(guān)閉該參數(shù)來提高從庫(kù)的復(fù)制性能！

五：關(guān)于packet 數(shù)據(jù)包的大?。?/div>

1.slave_pending_jobs_size_max的用途：

在多線程復(fù)制時(shí)，在隊(duì)列中Pending（等待）的事件所占用的最大內(nèi)存，默認(rèn)為16M，如果內(nèi)存富余，或者延遲較大時(shí)，可以適當(dāng)調(diào)大; 注意這個(gè)值要比主庫(kù)的max_allowed_packet大，并且這個(gè)參數(shù)需要重啟restart slave才能生效，也就是stop slave,start slave才能生效

查看主庫(kù)的max_allowed_packet參數(shù)！

mysql > show variables like 'max_allowed_packet'; -- 134217728 即128M + --------------------+-----------+ | Variable_name | Value | + --------------------+-----------+ | max_allowed_packet | 134217728 | + --------------------+-----------+

2.max_allowed_packet

Property	Value
Command-Line Format	--max-allowed-packet=#
System Variable	max_allowed_packet
Scope	Global, Session
Dynamic	Yes
Type	Integer
Default Value	4194304
Minimum Value	1024
Maximum Value	1073741824

該參數(shù)控制mysql限制server接受的數(shù)據(jù)包大小，需要注意應(yīng)該設(shè)置成正1024的整數(shù)倍的k, 因?yàn)橛行┓?wù)器下的mysql由于某些原因可能無法將M轉(zhuǎn)為k。默認(rèn)是4m;最大是1g,

該參數(shù)設(shè)置過大，可能會(huì)導(dǎo)致由于某個(gè)事務(wù)過大，導(dǎo)致主從延遲加劇！

set global max_allowed_packet = 2*1024*1024*10 (20m)

3.slave_max_allowed_packet參數(shù)

此變量設(shè)置slave 的SQL和I/O線程的最大數(shù)據(jù)包大小，不會(huì)因?yàn)閺?fù)制更新超過了允許的最大數(shù)據(jù)包數(shù)，而導(dǎo)致基于行的復(fù)制的大型更新失敗。設(shè)置此變量將立即對(duì)所有復(fù)制通道生效，包括正在運(yùn)行的通道。

此全局變量的值始終是1024的正整數(shù)倍；如果將其設(shè)置為非正整數(shù)倍，則該值將向下舍入到1024的下一個(gè)最大倍數(shù)，以便存儲(chǔ)或使用；將slave_max_allowed_packet設(shè)置為0將使用1024。（在所有這些情況下都會(huì)發(fā)出截?cái)嗑?。）默認(rèn)值和最大值為1073741824（1 GB）；最小值為1024

Property	Value
Command-Line Format	--slave-max-allowed-packet=#
System Variable	slave_max_allowed_packet
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	1073741824
Minimum Value	1024
Maximum Value	1073741824