mysql group by_技術分享 | 回顧 MySQL 的 MTS

作者：洪斌

愛可生南區負責人兼技術服務總監，MySQL ACE，擅長資料庫架構規劃、故障診斷、性能優化分析，實踐經驗豐富，幫助各行業客戶解決 MySQL 技術問題，為金融、營運商、網際網路等行業客戶提供 MySQL 整體解決方案。

本文來源：轉載自公衆号-玩轉MySQL

*愛可生開源社群出品，原創内容未經授權不得随意使用，轉載請聯系小編并注明來源。

MySQL 并行回放在一路改進，從 5.6 的 schema 并行，到 5.7 的 group commit，再到 8.0 的 write set。

MTS based on schema

這種方式不用多說，涉及不同 schema 的 DML 操作，在 slave 端可以按 schema 粒度并行回放，弱點也很明顯，如果執行個體中的 schema 較少，并行回放效果并不理想。

其優化方式也比較簡單 slave_parallel_workers 小于等于 master 的 schema 數量。

LOGICAL_CLOCK

MySQL 5.7 增加了基于 group commit 的并行回放政策，使得 slave 可以接近 master 同樣并發回放事務，master 并發越高，slave 并行回放效果越明顯。

在 binlog 中每個事務會有多出兩個标簽

sequence_number：

随每個事務遞增的自增 ID，每次新的 binlog 會從 1 開始

last_committed：

目前事務所依賴的上次事務的 sequence_number，每次新的 binlog 會從 0 開始

last_committed 相同值的事務代表同時送出的，可以并行回放。

#180105 20:08:33 ... last_committed=7201 sequence_number=7203
#180105 20:08:33 ... last_committed=7203 sequence_number=7204
#180105 20:08:33 ... last_committed=7203 sequence_number=7205
#180105 20:08:33 ... last_committed=7203 sequence_number=7206
#180105 20:08:33 ... last_committed=7205 sequence_number=7207
           

1. 7203 事務依賴 7201

2. 7204、7205、7206 事務依賴 7203，可以并行送出

3. 7207 事務依賴 7205，由于 7205 依賴 7203，那麼在 7205 執行完後，7207 可以和 7206 并行執行

優化方式通過調整 master group commit size 和 slave 的并行 work 線程數，提升并行效率。

master group commit size 和并發壓力，以及下面兩個參數相關

binlog_group_commit_sync_delay 表示 binlog 送出事務前等待多少微秒

binlog_group_commit_sync_no_delay_count 表示同步隊列最大允許的事務數，當等待送出的線程達到多少時, 就不在等待

在 master 低并發的負載下，并行回放效果就不好了，如果想要提高并行度，需要增加 binlog_group_commit_sync_delay，積累較多的分組大小，副作用是拉低 master 吞吐量。

Write set

MySQL 8.0.1 & 5.7.22 在 group commit 基礎上進一步改善了并行複制效率，增加了新的跟蹤事務依賴關系的機制。相對于基于事務 commit timestamp，即使 master 低并發場景下也能使 slave 根據事務依賴關系并行重放，充分利用硬體資源，不需要像 MySQL 5.7 增加 binlog_group_commit_sync_delay 延遲的方式，增加可并行的事務，降低複制延遲。

在 master 上滿足以下條件：

1. binlog_format=row

2. 開啟 transaction_write_set_extraction=XXHASH64

3. 更新表必須有主鍵，如果更新事務包含外鍵，則退回 commit_order 方式

4. binlog_transaction_dependency_tracking = [COMMIT_ORDER | WRITESET | WRITESET_SESSION]

slave 上開啟 slave_parallel_workers

COMMIT_ORDER 基于 commit timestamp

不同會話在相同時間執行可以并行回放

WRITESET 變更不同行的操作都可以并行

• 無主鍵 退回 commit_order 模式

[email protected] ~/w/s/msb_8_0_3> mysqlbinlog data/MBP-bin.000013 |grep last_ |sed -e 's/server id.*last/[...] last/' -e 's/.rbr_only.*/ [...]/'
#180105 21:19:31 [...] last_committed=0    sequence_number=1 [...] create table t1 (id);
#180105 21:19:50 [...] last_committed=1    sequence_number=2 [...] insert t1 value(1);
#180105 21:19:52 [...] last_committed=2    sequence_number=3 [...] insert t1 value(2);
#180105 21:19:54 [...] last_committed=3    sequence_number=4 [...] insert t1 value(3);
           

• 有主鍵 單個會話也可以并行

#180105 21:23:58 [...] last_committed=4    sequence_number=5 [...] create table t2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, str VARCHAR(80) NOT NULL UNIQUE);
#180105 21:24:19 [...] last_committed=5    sequence_number=6 [...] session1: insert t2(str) value('a');
#180105 21:24:21 [...] last_committed=5    sequence_number=7 [...] session1: insert t2(str) value('b');
#180105 21:24:25 [...] last_committed=5    sequence_number=8 [...] session1: insert t2(str) value('c');
           

WRITESET_SESSION 相同會話不會被重新排序，不同會話可并行

在相同會話中執行

#180106 13:15:26 [...] last_committed=0    sequence_number=1 [...] session1：insert t2(str) value('h');
#180106 13:15:31 [...] last_committed=1    sequence_number=2 [...] session1：insert t2(str) value('l');
           

在不同會話中執行

#180106 13:17:08 [...] last_committed=1    sequence_number=3 [...] session1: insert t2(str) value('q');
#180106 13:17:09 [...] last_committed=1    sequence_number=4 [...] session2: insert t2(str) value('w');
           

如何觀測并行回放

如何評估 slave_parallel_workers 設定多少合适？如果設定小了，任務會有積壓，如果設定大了，多餘的線程是空閑的。

計算機領域有這麼個公式專門來研究系統資源配置設定公平性。

出自論文《A Quantitative Measure Of Fairness And Discrimination For Resource Allocation In Shared Computer Systems》引用數 4000+

這個公式稱為 jain's index 計算一系統共享資源配置設定公平性的指數，指數值在 0<j<1 之間，越接近 1 表示資源配置設定更公平。

借用此公式是不是可以來計算回放線程任務配置設定的公平性，如果 slave_parallel_workers 設定過大，反應任務配置設定公平性較差。找到合适的 slave_parallel_workers。

這裡需要啟用 performance_schema 的 transaction 級的 instrument，搜集線程事務送出資訊。

call sys.ps_setup_enable_consumer('events_transactions%');
call sys.ps_setup_enable_instrument('transaction');
           

将公式轉換為 SQL 語句如下

select ROUND(POWER(SUM(trx_summary.COUNT_STAR), 2) /
(@@GLOBAL.slave_parallel_workers * SUM(POWER(trx_summary.COUNT_STAR, 2))), 2) AS replica_jain_index
from performance_schema.events_transactions_summary_by_thread_by_event_name as trx_summary join performance_schema.replication_applier_status_by_worker as applier
on trx_summary.THREAD_ID = applier.THREAD_ID
           

在 slave 上觀測，如果該指數值越接近 0 說明目前 applier 線程越空閑率高，如果該指數值越接近 1 說明目前 applier 線程空閑率越低。動手試試吧！

參考：

https://www.percona.com/blog/2016/02/10/estimating-potential-for-mysql-5-7-parallel-replication/

https://www.slideshare.net/JeanFranoisGagn/fosdem-2018-premysql-day-mysql-parallel-replication

https://jfg-mysql.blogspot.hk/2017/02/metric-for-tuning-parallel-replication-mysql-5-7.html

https://jfg-mysql.blogspot.com/2018/01/write-set-in-mysql-5-7-group-replication.html?m=1

http://mysqlhighavailability.com/improving-the-parallel-applier-with-writeset-based-dependency-tracking/