MySQL 主從複製之多執行緒複製

misakivv發表於2024-11-21
MySql執行緒

目錄
  • 一、MySQL 多執行緒複製的背景
  • 二、MySQL 5.5 主從複製
    • 1、原理
    • 2、部署主從複製
      • 2.1、主節點安裝配置MySQL 5.5
      • 2.2、從節點安裝配置MySQL 5.5
    • 3、檢查主從庫 server_id 和 log_bin 配置
    • 4、建立主從複製使用者
    • 5、獲取主庫的二進位制日誌檔案和位置
    • 6、配置從庫連線主庫引數並啟動從庫複製程序
    • 7、驗證主從複製
      • 7.1、檢視主庫從庫資料
      • 7.2、主庫建立表並插入資料
      • 7.3、從庫執行查詢操作
    • 8、MySQL 5.5 單執行緒體現
  • 三、MySQL 5.6 基於 schema（庫級別） 的並行複製
    • 1、原理
    • 2、存在的問題
    • 3、部署主從複製
      • 3.1、主節點安裝配置 MySQL 5.6
      • 3.2、從節點安裝配置 MySQL 5.6
    • 4、檢查主從庫server_id、log_bin、 slave_parallel_workers
    • 5、建立主從複製使用者
    • 6、獲取主庫的二進位制日誌檔案和位置
    • 7、配置從庫連線主庫引數並啟動從庫複製程序
    • 8、驗證主從複製
      • 8.1、檢視主庫從庫資料
      • 8.2、主庫建立表並插入資料
      • 8.3、從庫執行查詢操作
    • 9、主節點單庫多表 sysbench 寫壓測 從節點測試延遲
      • 9.1、安裝 sysbench 壓測工具
      • 9.2、建立測試資料庫
      • 9.3、使用 sysbench 準備測試資料
      • 9.4、執行寫操作測試
      • 9.5、從庫採集延遲時間
      • 9.6、事務處理速度（TPS，Transactions Per Second）和查詢處理速度（QPS，Queries Per Second）
      • 9.7、清除測試資料
    • 10、主節點多庫多表 sysbench 寫壓測 從節點測試延遲
      • 10.1、建立測試資料庫
      • 10.2、使用 sysbench 準備測試資料
      • 10.3、執行寫操作測試
      • 10.4、從庫採集延遲時間
  • 四、MySQL 5.7 基於組提交的並行複製
    • 1、原理
    • 2、部署主從複製
      • 2.1、主節點安裝配置 MySQL 5.7
      • 2.2、從節點安裝配置 MySQL 5.7
    • 3、檢查主從庫server_id、log_bin、 slave_parallel_workers、slave-parallel-type
    • 4、建立主從複製使用者
    • 5、獲取主庫的二進位制日誌檔案和位置
    • 6、配置從庫連線主庫引數並啟動從庫複製程序
    • 7、驗證主從複製
      • 7.1、檢視主庫從庫資料
      • 7.2、主庫建立表並插入資料
      • 7.3、從庫執行查詢操作
    • 8、主節點單庫多表 sysbench 寫壓測 從節點測試延遲
      • 8.1、安裝 sysbench 壓測工具
      • 8.2、建立測試資料庫
      • 8.3、使用 sysbench 準備測試資料
      • 8.4、執行寫操作測試
      • 8.5、從庫採集延遲時間
      • 8.6、事務處理速度（TPS，Transactions Per Second）和查詢處理速度（QPS，Queries Per Second）
      • 8.7、清除測試資料
  • 五、MySQL 8.0 基於 WriteSet 的並行複製
    • 1、概述
    • 2、核心原理
      • Master 端
      • Slave 端
    • 3、MySQL 8.0 相關引數
      • 3.1、binlog_transaction_dependency_tracking
      • 3.2、transaction_write_set_extraction
      • 3.3、binlog_transaction_dependency_history_size
    • 4、WriteSet 依賴檢測條件
    • 5、基於 COMMIT_ORDER，WRITESET_SESSION，WRITESET 方案的壓測
    • 6、開啟並行複製
      • 6.1、主庫
      • 6.2、從庫

一、MySQL 多執行緒複製的背景

MySQL 的主從複製延遲一直是受開發者最為關注的問題之一，MySQL 從 5.6 版本開始追加了並行複製功能，目的就是為了改善複製延遲問題，並行複製稱為enhanced multi-threaded slave（簡稱MTS）。

  • MySQL 的複製是基於 binlog 的。
  • MySQL 複製包括兩部分，從庫中有兩個執行緒：IO 執行緒和 SQL 執行緒。
  • IO 執行緒主要是用於拉取接收 Master 傳遞過來的 binlog，並將其寫入到 relay log.
  • SQL 執行緒主要負責解析 relay log，並應用到 slave 中。
  • IO 和 SQL 執行緒都是單執行緒的，然而master卻是多執行緒的，所以難免會有延遲，為了解決這個問題，多執行緒應運而生了。
  • IO 沒必要多執行緒，因為 IO 執行緒並不是瓶頸。
  • SQL 多執行緒，目前最新的5.6，5.7，8.0 都是在 SQL 執行緒上實現了多執行緒，來提升 slave 的併發度，減少複製延遲。

二、MySQL 5.5 主從複製

1、原理

  • master 節點上的binlogdump 執行緒，在slave 與其正常連線的情況下，將binlog 傳送到slave 上。
  • slave 節點的I/O Thread ，透過讀取master 節點binlog 日誌名稱以及偏移量資訊將其複製到本地relay log 日誌檔案。
  • slave 節點的SQL Thread，該執行緒讀取relay log 日誌資訊，將在master 節點上提交的事務在本地回放，達到與主庫資料保持一致的目的。

MySQL 主從複製原理

2、部署主從複製

主庫 192.168.112.10
從庫 192.168.112.20
MySQL版本 5.5.62

2.1、主節點安裝配置MySQL 5.5

cd /usr/local
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.5.62-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
cd /usr/local/mysql
chown -R mysql:mysql .
mkdir -p /usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql/data
./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
cat > /etc/my.cnf << EOF
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
user = mysql
port = 3306
# 二進位制日誌配置
server-id = 1
log-bin = mysql-bin
binlog-format = row
expire_logs_days = 10

# 主從複製配置
log-slave-updates = 1
read-only = 0
EOF
cat > /etc/systemd/system/mysqld.service << EOF
[Unit]
Description=MySQL 5.5 Database Server
After=network.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
mysqladmin -uroot password '123'

2.2、從節點安裝配置MySQL 5.5

cd /usr/local
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.5.62-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
cd /usr/local/mysql
chown -R mysql:mysql .
mkdir -p /usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql/data
./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
cat > /etc/my.cnf << EOF
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
user = mysql
port = 3306
# 主從複製配置
server-id = 2
relay-log = mysql-relay-bin
log-bin = mysql-bin
read-only = 1
EOF
cat > /etc/systemd/system/mysqld.service << EOF
[Unit]
Description=MySQL 5.5 Database Server
After=network.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
mysqladmin -uroot password '123'

select version();
mysql -V

image-20241110165912931

3、檢查主從庫 server_id 和 log_bin 配置

show variables like 'server_id';
show variables like 'log_bin';
主庫
image-20241109224202904
從庫
image-20241109224058507

4、建立主從複製使用者

主庫

#登入資料庫
mysql -uroot -p123

#建立slave使用者
grant replication slave on *.* to slave@'192.168.112.%' identified by '123';

flush privileges;
  • grant replication slave on *.*: 授予 replication slave 許可權，允許使用者從主庫複製資料。*.* 表示所有資料庫和所有表。
  • to slave@'192.168.112.%': 指定使用者名稱為 slave，允許從 192.168.112.% 子網內的任何IP地址連線到主庫。
  • identified by '123': 設定使用者的密碼為 123。

image-20241109223547699

5、獲取主庫的二進位制日誌檔案和位置

# 記錄下 File 和 Position 的值
show master status;

image-20241109224745123

6、配置從庫連線主庫引數並啟動從庫複製程序

change master to
master_host='192.168.112.10',
master_user='slave',
master_password='123',
master_log_file='mysql-bin.000004',
master_log_pos=332;

start slave;
show slave status\G;

image-20241109225649992

7、驗證主從複製

7.1、檢視主庫從庫資料

目前資料一致

show databases;

use test ; show tables;

image-20241109230533470

7.2、主庫建立表並插入資料

create table test_table (id int auto_increment primary key,name varchar(255),created_at timestamp default current_timestamp);

insert into test_table (name , created_at) values ('Data1', now());
insert into test_table (name , created_at) values ('Data2', now());
insert into test_table (name , created_at) values ('Data3', now());

7.3、從庫執行查詢操作

show tables;

select * from test_table;

image-20241109234222354

8、MySQL 5.5 單執行緒體現

# 從庫
show processlist;

image-20241109234703206

這裡有兩個系統使用者執行緒：

  • Id 1 是IO執行緒，負責從主庫讀取二進位制日誌並寫入從庫的中繼日誌。
  • Id 2 是SQL執行緒，負責從中繼日誌中讀取事件並應用到從庫。

三、MySQL 5.6 基於 schema（庫級別） 的並行複製

如果在MySQL 5.6 版本開啟並行複製功能（slave_parallel_workers > 0），那麼SQL 執行緒就變為了coordinator 執行緒。但是其並行只是基於schema的，也就是基於庫的。如果使用者的MySQL資料庫例項中存在多個schema且schema下表數量較少，對於從伺服器複製的速度的確可以有比較大的幫助。

1、原理

透過配置引數 slave_parallel_workers = n 開啟並行複製，原來的單個SQL執行緒的功能被拆分成了兩個部分：一個 Coordinator 執行緒和多個 Worker 執行緒。

coordinator執行緒主要負責兩部分內容：

  • 若判斷可以並行執行，那麼選擇worker執行緒執行事務的二進位制日誌
  • 若判斷不可以並行執行，如該操作是DDL，或者是事務跨schema操作，則等待所有的worker執行緒執行完成之後再執行當前的日誌。

這意味著coordinator 執行緒並不是僅將日誌傳送給worker 執行緒，自己也可以回放日誌，但是所有可以並行的操作交付由worker 執行緒完成。

Worker執行緒作用：

  • WorkThread 執行緒負責實際應用中繼日誌中的事務。每個 WorkThread 執行緒負責處理特定資料庫（Schema）的事務。
  • 可以透過配置引數 slave_parallel_workers 來設定 WorkThread 執行緒的數量。預設值為0，表示不啟用並行複製。

MySQL5.6並行複製

2、存在的問題

基於schema級別的並行複製存在一個問題，schema級別的並行複製效果並不高，如果使用者例項有很少的庫和較多的表，那麼並行回放效果會很差，甚至效能會比原來的單執行緒更差，但是日常維護中其實單個例項的的事務處理相對集中在一個 DB 上。因此單庫多表是比多庫多表更為常見的一種情形。

這種並行複製的模式,只有在例項中有多個 DB，且 DB 的事務都相對繁忙的情況下才會有較高的並行度。

3、部署主從複製

3.1、主節點安裝配置 MySQL 5.6

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
tar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
mkdir /application
mv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40
ln -s /application/mysql-5.6.40/ /application/mysql
cd /application/mysql/support-files/
\cp my-default.cnf /etc/my.cnf
cp mysql.server /etc/init.d/mysqld
cd /application/mysql/scripts
useradd mysql -s /sbin/nologin -M
yum -y install autoconf
./mysql_install_db --user=mysql --basedir=/application/mysql  --data=/application/mysql/data
echo 'export PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld /application/mysql/bin/mysqld_safe
sed -i '/^# basedir = /a\basedir = /application/mysql/' /etc/my.cnf
sed -i '/^# datadir = /a\datadir = /application/mysql/data/' /etc/my.cnf
cat >> /usr/lib/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
cat >> /etc/my.cnf << EOF
# 主從複製配置
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
expire_logs_days = 10

# 其他配置
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
EOF
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
mysqladmin -uroot password '123'

3.2、從節點安裝配置 MySQL 5.6

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
tar xzvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
mkdir /application
mv mysql-5.6.40-linux-glibc2.12-x86_64 /application/mysql-5.6.40
ln -s /application/mysql-5.6.40/ /application/mysql
cd /application/mysql/support-files/
\cp my-default.cnf /etc/my.cnf
cp mysql.server /etc/init.d/mysqld
cd /application/mysql/scripts
useradd mysql -s /sbin/nologin -M
yum -y install autoconf
./mysql_install_db --user=mysql --basedir=/application/mysql  --data=/application/mysql/data
echo 'export PATH="/application/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
sed -i 's#/usr/local#/application#g' /etc/init.d/mysqld /application/mysql/bin/mysqld_safe
sed -i '/^# basedir = /a\basedir = /application/mysql/' /etc/my.cnf
sed -i '/^# datadir = /a\datadir = /application/mysql/data/' /etc/my.cnf
cat >> /usr/lib/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
cat >> /etc/my.cnf << EOF
# 主從複製配置
server-id = 2
relay-log = mysql-relay-bin
log-bin = mysql-bin
read-only = 1

# 並行複製配置
slave_parallel_workers = 4
EOF
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
mysqladmin -uroot password '123'

mysql -V
select version();

image-20241110204425022

4、檢查主從庫server_id、log_bin、 slave_parallel_workers

show variables like 'server_id';

show variables like 'log_bin';

show variables like 'slave_parallel_workers';

show processlist;

image-20241110205805851

image-20241113215205180

5、建立主從複製使用者

主庫

#建立slave使用者
grant replication slave on *.* to slave@'192.168.112.%' identified by '123';

flush privileges;

image-20241110210530025

6、獲取主庫的二進位制日誌檔案和位置

# 記錄下 File 和 Position 的值
show master status;

image-20241110210601965

7、配置從庫連線主庫引數並啟動從庫複製程序

change master to
master_host='192.168.112.10',
master_user='slave',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=552;

start slave;
show slave status\G;

image-20241110211743240

8、驗證主從複製

8.1、檢視主庫從庫資料

目前資料一致

show databases;

use test ; show tables;

image-20241110212636041

8.2、主庫建立表並插入資料

create table test_table (id int auto_increment primary key,name varchar(255),created_at timestamp default current_timestamp);

insert into test_table (name , created_at) values ('Data1', now());
insert into test_table (name , created_at) values ('Data2', now());
insert into test_table (name , created_at) values ('Data3', now());

8.3、從庫執行查詢操作

show tables;

select * from test_table;

image-20241110213355697

9、主節點單庫多表 sysbench 寫壓測 從節點測試延遲

9.1、安裝 sysbench 壓測工具

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

yum -y install sysbench

sysbench --version

9.2、建立測試資料庫

# 主節點
create database tssysbench;

9.3、使用 sysbench 準備測試資料

sysbench /usr/share/sysbench/oltp_write_only.lua \
--mysql-host=192.168.112.10 --mysql-port=3306 \
--mysql-user=root --mysql-password='123' \
--mysql-db=tssysbench --db-driver=mysql \
--tables=20 --table-size=10000 --report-interval=10 \
--threads=64 --time=200 \
prepare
  • /usr/share/sysbench/ 目錄下有OLTP 基準測試的指令碼檔案，我們使用的是寫測試模擬併發寫入情況
  • --mysql-host=192.168.112.10 --mysql-port=3306 \ --mysql-user=root --mysql-password='123' \ 資料庫的使用者和密碼等資訊
  • --mysql-db=tssysbench --tables=20 --table_size=10000：這一串的意思，就是說在tssysbench這個庫裡，構造20個測試表，每個測試表裡構造1萬條測試資料，測試表的名字會是類似於sbtest1，sbtest2這個樣子的
  • --db-driver=mysql：代表資料庫驅動
  • --time=200：這個就是說連續訪問200秒
  • --threads=64：這個就是說用64個執行緒模擬併發訪問
  • --report-interval=10：這個就是說每隔10秒輸出一下壓測情況
  • 最後有一個prepare，意思是參照這個命令的設定去構造出來我們需要的資料庫裡的資料，他會自動建立20個測試表，每個表裡建立1萬條測試資料。一共20w條資料

image-20241111100048351

9.4、執行寫操作測試

sysbench /usr/share/sysbench/oltp_write_only.lua \
--mysql-host=192.168.112.10 --mysql-port=3306 \
--mysql-user=root --mysql-password='123' \
--mysql-db=tssysbench --db-driver=mysql \
--tables=20 --table-size=10000 --report-interval=10 \
--threads=64 --time=200 \
run

image-20241121143817612

每10s壓測情況報告（取第一個10s做引數詳解）：

  • thds: 64，這個意思就是有64個執行緒在壓測
  • tps: 4971.66，這個意思就是每秒執行了4971.66個事務
  • qps: 29851.48，意思是每秒執行了29851.48個請求
  • (r/w/o: 0.00/17826.55/8911.43) ，意思是每秒29851.48個請求中有19902.85個寫請求、9948.63個其他請求，是對QPS進行了rwo拆解
  • lat (ms,95%)：21.50，意思是95%的請求延遲都在21.50ms以下
  • err/s: 0.00 reconn/s: 0.00 ，意思是每秒有0.1個請求是失敗的，發生了0次網路重連

結果引數詳解：

  • SQL statistics: SQL統計資訊
    • read:0， 0次讀操作
    • write:4154598， 4154598次寫操作
    • other:2077304, 2077304次其他操作
    • total:6231902，6231902次總運算元
    • transactions:1038646 (5192.32 per sec)，在整個測試過程中，完成了 1,038,646 個事務，平均每秒完成 5192.32 個事務（tps）。
    • queries:6231902 (31154.05 per sec.)，在整個測試過程中，執行了 6,231,902 次查詢，平均每秒執行 31,154.05 次查詢（qps)。
    • ignored errors:12 (0.06 per sec），在整個測試過程中，忽略了 12 個錯誤，平均每秒忽略 0.06 個錯誤
    • reconnects:0(0.00 per sec.) ,在整個測試過程中，沒有發生任何重連，平均每秒重連次數為 0。
  • General statistics: 總體統計資訊
    • total time:200.0347s，測試總用時200.0347 秒
    • total number of events:1038646，在測試期間發生的總事件數1038646，即完成的事務數。
  • Latency (ms):延遲統計資訊
    • min: 1.29 最小延遲1.29 毫秒
    • avg: 12.32 平均延遲12.32 毫秒
    • max: 186.34 最大延遲 186.34 毫秒
    • 95th percentile: 20.37 95% 的事件的響應時間不超過 20.37 毫秒
    • sum: 12798566.05 所有事件的總延遲事件為 12798566.05 毫秒
  • Threads fairness: 執行緒公平性
    • events (avg/stddev): 16228.8438/91.38 每個執行緒平均完成的事件數及其標準偏差。平均每個執行緒完成了 16,228.8438 個事件，標準偏差為 91.38
    • execution time (avg/stddev): 199.9776/0.01 每個執行緒平均執行的時間及其標準偏差。平均每個執行緒執行了 199.9776 秒，標準偏差為 0.01

9.5、從庫採集延遲時間

collect_delay.sh

#!/bin/bash

# 從庫連線資訊
SLAVE_MYSQL_USER="root"
SLAVE_MYSQL_PASSWORD="123"

# 採集次數
COLLECT_COUNT=10

# 記錄延遲時間的陣列
DELAY_TIMES=()

for ((i=1; i<=COLLECT_COUNT; i++)); do
    echo "Collecting delay $i..."
    
    # 獲取從庫的延遲時間
    DELAY=$(mysql -u $SLAVE_MYSQL_USER -p$SLAVE_MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G;" | grep Seconds_Behind_Master | awk '{print $2}')
    DELAY_TIMES+=($DELAY)
    
    # 等待一段時間
    sleep 20
done

# 計算平均延遲時間
AVERAGE_DELAY=$(echo "${DELAY_TIMES[@]}" | tr ' ' '+' | bc -l)
AVERAGE_DELAY=$(echo "$AVERAGE_DELAY / $COLLECT_COUNT" | bc -l)

echo "Average delay for replication: $AVERAGE_DELAY seconds"

image-20241121144149716

這裡對從庫主從複製延遲的引數是採集 Seconds_Behind_Master 的值，壓測200s內同時對從庫進行延遲資料的採集，10s一次最後取值為10次平均值

最終延遲為48.0s

9.6、事務處理速度（TPS，Transactions Per Second）和查詢處理速度（QPS，Queries Per Second）

場景 單庫多表
資料量 20w
執行緒數 64
壓測時間 200s
事務數 1,038,646
TPS 5,192.32 事務/秒
QPS 3,1154.05 查詢/秒
主從複製延遲 48.0s

9.7、清除測試資料

run 改為 cleanup 即可

sysbench /usr/share/sysbench/oltp_write_only.lua \
--mysql-host=192.168.112.10 --mysql-port=3306 \
--mysql-user=root --mysql-password='123' \
--mysql-db=tssysbench --db-driver=mysql \
--tables=20 --table-size=10000 --report-interval=10 \
--threads=64 --time=200 \
cleanup

image-20241111104527432

10、主節點多庫多表 sysbench 寫壓測 從節點測試延遲

因為我目前不知道 sysbench 是否有多庫多表壓測的特性，所以採用 parallel 並行執行 sysbench 寫壓測。觀測從節點延遲。還是20w條資料

10.1、建立測試資料庫

10個資料庫，每個資料庫10張表，每張表2000條資料，64執行緒模擬併發，持續時間200s

create-db.sh

#!/bin/bash

# 主庫連線資訊
MYSQL_HOST="192.168.112.10"
MYSQL_USER="root"
MYSQL_PASSWORD="123"

# 建立10個資料庫
for i in $(seq 1 10); do
    DB_NAME="test_db$i"
    mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "CREATE DATABASE $DB_NAME;"
done

image-20241111105713798

10.2、使用 sysbench 準備測試資料

sysbench-pre.sh

#!/bin/bash

# 主庫連線資訊
MYSQL_HOST="192.168.112.10"
MYSQL_USER="root"
MYSQL_PASSWORD="123"

for i in $(seq 1 10); do
    DB_NAME="test_db$i"
    
    # 準備測試資料
    sysbench /usr/share/sysbench/oltp_write_only.lua \
    --mysql-host=$MYSQL_HOST --mysql-port=3306 \
    --mysql-user=$MYSQL_USER --mysql-password=$MYSQL_PASSWORD \
    --mysql-db=$DB_NAME --db-driver=mysql \
    --tables=10 --table-size=2000 --report-interval=10 \
    --threads=64 --time=200 \
    prepare
done

10.3、執行寫操作測試

sysbench-run.sh

#!/bin/bash

# 主庫連線資訊
MYSQL_HOST="192.168.112.10"
MYSQL_USER="root"
MYSQL_PASSWORD="123"

# 使用 parallel 執行 sysbench 測試
seq 1 10 | parallel --no-notice -j 8 '
    DB_NAME=test_db{}
    sysbench /usr/share/sysbench/oltp_write_only.lua \
    --mysql-host='$MYSQL_HOST' --mysql-port=3306 \
    --mysql-user='$MYSQL_USER' --mysql-password='$MYSQL_PASSWORD' \
    --mysql-db=$DB_NAME --db-driver=mysql \
    --tables=10 --table-size=2000 --report-interval=10 \
    --threads=8 --time=200 \
    run
'

10.4、從庫採集延遲時間

collect_delay.sh

#!/bin/bash

# 從庫連線資訊
SLAVE_MYSQL_USER="root"
SLAVE_MYSQL_PASSWORD="123"

# 採集次數
COLLECT_COUNT=10

# 記錄延遲時間的陣列
DELAY_TIMES=()

for ((i=1; i<=COLLECT_COUNT; i++)); do
    echo "Collecting delay $i..."
    
    # 獲取從庫的延遲時間
    DELAY=$(mysql -u $SLAVE_MYSQL_USER -p$SLAVE_MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G;" | grep Seconds_Behind_Master | awk '{print $2}')
    DELAY_TIMES+=($DELAY)
    
    # 等待一段時間
    sleep 20
done

# 計算平均延遲時間
AVERAGE_DELAY=$(echo "${DELAY_TIMES[@]}" | tr ' ' '+' | bc -l)
AVERAGE_DELAY=$(echo "$AVERAGE_DELAY / $COLLECT_COUNT" | bc -l)

echo "Average delay for replication: $AVERAGE_DELAY seconds"

針對MySQL 5.6 版本 多庫多表的效能測試就先這樣吧

從執行結果看使用 parallel 執行 sysbench 多庫是序列而非並行所以控制不了變數

四、MySQL 5.7 基於組提交的並行複製

MySQL5.7中slave伺服器的回放與master是一致的，即master伺服器上是怎麼並行執行的，那麼slave上就怎樣進行並行回放。不再有庫的並行複製限制。

1、原理

透過對事務進行分組，最佳化減少了生成二進位制日誌所需的運算元。當事務同時提交時，它們將在單個操作中寫入到二進位制日誌中。如果事務能同時提交成功，那麼它們就不會共享任何鎖，這意味著它們沒有衝突，因此可以在Slave上並行執行。所以透過在二進位制日誌中新增組提交資訊，實現Slave可以並行地安全地執行事務。

Group Commit技術在MySQL5.6中是為了解決事務提交的時候需要fsync導致併發性不夠而引入的。簡單來說，就是由於事務提交時必須將Binlog寫入到磁碟上而呼叫fsync，這是一個代價比較高的操作，事務併發提交的情況下，每個事務各自獲取日誌鎖並進行fsync會導致事務實際上以序列的方式寫入Binlog檔案，這樣就大大降低了事務提交的併發程度。

Group Commit技術將事務的提交階段分成了Flush、Sync、Commit三個階段，每個階段維護一個佇列，並且由該佇列中第一個執行緒負責執行該步驟，這樣實際上就達到了一次可以將一批事務的Binlog fsync到磁碟的目的，這樣的一批同時提交的事務稱為同一個Group的事務

組提交

Group Commit雖然是屬於並行提交的技術，但是解決了從伺服器上事務並行回放的一個難題——即如何判斷哪些事務可以並行回放。如果一批事務是同時Commit的，那麼這些事務必然不會有互斥的持有鎖，也不會有執行上的相互依賴，因此這些事務必然可以並行的回放。
為了標記事務所屬的組，MySQL5.7版本在產生Binlog日誌時會有兩個特殊的值記錄在 Binlog Event 中，last_committed 和 sequence_number，其中 last_committed指的是該事務提交時，上一個事務提交的編號，sequence_number是事務提交的序列號，在一個Binlog檔案內單調遞增。如果兩個事務的last_committed值一致，這兩個事務就是在一個組內提交的。
_
為了相容MySQL5.6基於庫的並行複製，5.7引入了新的變數slave-parallel-type，其可以配置的值有：DATABASE（預設值，基於庫的並行複製方式）、LOGICAL_CLOCK（基於組提交的並行複製方式)。

2、部署主從複製

因為變數 slave-parallel-type 的引數設定中 database 實際上就是 MySQL 5.6 版本的多執行緒複製

所以這個 MySQL 5.7 版本就採用 logical_clock

2.1、主節點安裝配置 MySQL 5.7

cd /usr/local
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.35-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
cd /usr/local/mysql
chown -R mysql:mysql .
mkdir -p /usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql/data
./bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data
cat > /etc/my.cnf << EOF
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
user = mysql
port = 3306
# 二進位制日誌配置
server-id = 1
log-bin = mysql-bin
binlog-format = row
expire_logs_days = 10
gtid-mode = ON
enforce-gtid-consistency=ON
# 主從複製配置
log-slave-updates = 1
read-only = 0
EOF
cat > /etc/systemd/system/mysqld.service << EOF
[Unit]
Description=MySQL 5.7 Database Server
After=network.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
======================================================================================
> 使用臨時密碼登入MySQL，修改 root 密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY '123';
FLUSH PRIVILEGES;
EXIT;

臨時密碼

image-20241114104333792

2.2、從節點安裝配置 MySQL 5.7

cd /usr/local
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.35-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
cd /usr/local/mysql
chown -R mysql:mysql .
mkdir -p /usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql/data
./bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data
cat > /etc/my.cnf << EOF
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
user = mysql
port = 3306
# 二進位制日誌配置
server-id = 2
log-bin = mysql-bin
binlog-format = row
expire_logs_days=10

# 主從複製配置
relay-log=mysql-relay-bin
log-slave-updates=ON
gtid-mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=on
read-only = 0
EOF
cat > /etc/systemd/system/mysqld.service << EOF
[Unit]
Description=MySQL 5.7 Database Server
After=network.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload && systemctl start mysqld && systemctl enable mysqld
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
======================================================================================
> 使用臨時密碼登入MySQL，修改 root 密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY '123';	
FLUSH PRIVILEGES;
EXIT;

臨時密碼

image-20241119225809302

3、檢查主從庫server_id、log_bin、 slave_parallel_workers、slave-parallel-type

show variables like 'version';

show variables like 'server_id';

show variables like 'log_bin';

show variables like 'slave_parallel_type';

image-20241119233556347

4、建立主從複製使用者

主庫

#登入資料庫
mysql -uroot -p123

#建立slave使用者
grant replication slave on *.* to slave@'192.168.112.%' identified by '123';

flush privileges;

select user,host,authentication_string from mysql.user;
  • grant replication slave on *.*: 授予 replication slave 許可權，允許使用者從主庫複製資料。*.* 表示所有資料庫和所有表。
  • to slave@'192.168.112.%': 指定使用者名稱為 slave，允許從 192.168.112.% 子網內的任何IP地址連線到主庫。
  • identified by '123': 設定使用者的密碼為 123。

image-20241119234637742

5、獲取主庫的二進位制日誌檔案和位置

# 記錄下 File 和 Position 的值
show master status;

image-20241119234739486

6、配置從庫連線主庫引數並啟動從庫複製程序

change master to
master_host='192.168.112.10',
master_user='slave',
master_password='123',
master_log_file='mysql-bin.000003',
master_log_pos=602;

start slave;
show slave status\G;

image-20241119235106658

7、驗證主從複製

7.1、檢視主庫從庫資料

目前資料一致

show databases;

# 主庫
create database test;use test;

image-20241120222907669

7.2、主庫建立表並插入資料

create table test_table (id int auto_increment primary key,name varchar(255),created_at timestamp default current_timestamp);

insert into test_table (name , created_at) values ('Data1', now());
insert into test_table (name , created_at) values ('Data2', now());
insert into test_table (name , created_at) values ('Data3', now());

7.3、從庫執行查詢操作

use test;show tables;

select * from test_table;

image-20241120223137461

8、主節點單庫多表 sysbench 寫壓測 從節點測試延遲

8.1、安裝 sysbench 壓測工具

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

yum -y install sysbench

sysbench --version

8.2、建立測試資料庫

# 主節點
create database tssysbench;

8.3、使用 sysbench 準備測試資料

與MySQL 5.6 版本的單庫多表寫壓測一樣的資料形成對照

sysbench /usr/share/sysbench/oltp_write_only.lua \
--mysql-host=192.168.112.10 --mysql-port=3306 \
--mysql-user=root --mysql-password='123' \
--mysql-db=tssysbench --db-driver=mysql \
--tables=20 --table-size=10000 --report-interval=10 \
--threads=64 --time=200 \
prepare
  • /usr/share/sysbench/ 目錄下有OLTP 基準測試的指令碼檔案，我們使用的是寫測試模擬併發寫入情況
  • --mysql-host=192.168.112.10 --mysql-port=3306 \ --mysql-user=root --mysql-password='123' \ 資料庫的使用者和密碼等資訊
  • --mysql-db=tssysbench --tables=20 --table_size=10000：這一串的意思，就是說在tssysbench這個庫裡，構造20個測試表，每個測試表裡構造1萬條測試資料，測試表的名字會是類似於sbtest1，sbtest2這個樣子的
  • --db-driver=mysql：代表資料庫驅動
  • --time=200：這個就是說連續訪問200秒
  • --threads=64：這個就是說用64個執行緒模擬併發訪問
  • --report-interval=10：這個就是說每隔10秒輸出一下壓測情況
  • 最後有一個prepare，意思是參照這個命令的設定去構造出來我們需要的資料庫裡的資料，他會自動建立20個測試表，每個表裡建立1萬條測試資料。一共20w條資料

image-20241121151757146

8.4、執行寫操作測試

    sysbench /usr/share/sysbench/oltp_write_only.lua \
    --mysql-host=192.168.112.10 --mysql-port=3306 \
    --mysql-user=root --mysql-password='123' \
    --mysql-db=tssysbench --db-driver=mysql \
    --tables=20 --table-size=10000 --report-interval=10 \
    --threads=64 --time=200 \
    run

image-20241121162542451

每10s壓測情況報告（取第一個10s做引數詳解）：

  • thds: 64，這個意思就是有64個執行緒在壓測
  • tps: 4502.30，這個意思就是每秒執行了4502.30個事務
  • qps: 27035.08，意思是每秒執行了27035.08個請求
  • (r/w/o: 0.00/18025.39/9009.69) ，意思是每秒27035.08個請求中有18025.39個寫請求、9009.69個其他請求，是對QPS進行了rwo拆解
  • lat (ms,95%)：23.52，意思是95%的請求延遲都在23.52ms以下
  • err/s: 0.10 reconn/s: 0.00 ，意思是每秒有0.1個請求是失敗的，發生了0次網路重連

結果引數詳解：

  • SQL statistics: SQL統計資訊
    • read:0， 0次讀操作
    • write:3352319， 3352319次寫操作
    • other:1676162，1676162次其他操作
    • total:5028481，5028481次總運算元
    • transactions:838077 (4189.48 per sec)，在整個測試過程中，完成了 838,077 個事務，平均每秒完成 4,189.48 個事務（tps）。
    • queries:5028481 (25136.95 per sec.)，在整個測試過程中，執行了 5,028,481 次查詢，平均每秒執行 25,136.95 次查詢（qps)。
    • ignored errors:8(0.04 per sec），在整個測試過程中，忽略了 8 個錯誤，平均每秒忽略 0.04 個錯誤
    • reconnects:0(0.00 per sec.) ,在整個測試過程中，沒有發生任何重連，平均每秒重連次數為 0。
  • General statistics: 總體統計資訊
    • total time:200.0429s，測試總用時200.0429 秒
    • total number of events:838077，在測試期間發生的總事件數838,077，即完成的事務數。
  • Latency (ms):延遲統計資訊
    • min: 1.25 最小延遲1.25 毫秒
    • avg: 15.27 平均延遲15.27 毫秒
    • max: 153.27 最大延遲 153.27 毫秒
    • 95th percentile: 25.28 95% 的事件的響應時間不超過 25.28 毫秒
    • sum: 12798796.95 所有事件的總延遲事件為 12798796.95 毫秒
  • Threads fairness: 執行緒公平性
    • events (avg/stddev): 13094.9531/66.16 每個執行緒平均完成的事件數及其標準偏差。平均每個執行緒完成了 13,094.9531 個事件，標準偏差為 66.16
    • execution time (avg/stddev): 199.9812/0.01 每個執行緒平均執行的時間及其標準偏差。平均每個執行緒執行了 199.9812 秒，標準偏差為 0.01

8.5、從庫採集延遲時間

collect_delay.sh

#!/bin/bash

# 從庫連線資訊
SLAVE_MYSQL_USER="root"
SLAVE_MYSQL_PASSWORD="123"

# 採集次數
COLLECT_COUNT=10

# 記錄延遲時間的陣列
DELAY_TIMES=()

for ((i=1; i<=COLLECT_COUNT; i++)); do
    echo "Collecting delay $i..."
    
    # 獲取從庫的延遲時間
    DELAY=$(mysql -u $SLAVE_MYSQL_USER -p$SLAVE_MYSQL_PASSWORD -e "SHOW SLAVE STATUS\G;" | grep Seconds_Behind_Master | awk '{print $2}')
    DELAY_TIMES+=($DELAY)
    
    # 等待一段時間
    sleep 20
done

# 計算平均延遲時間
AVERAGE_DELAY=$(echo "${DELAY_TIMES[@]}" | tr ' ' '+' | bc -l)
AVERAGE_DELAY=$(echo "$AVERAGE_DELAY / $COLLECT_COUNT" | bc -l)

echo "Average delay for replication: $AVERAGE_DELAY seconds"

image-20241121162629742

這裡對從庫主從複製延遲的引數是採集 Seconds_Behind_Master 的值，壓測200s內同時對從庫進行延遲資料的採集，10s一次最後取值為10次平均值

最終延遲為44.4s

8.6、事務處理速度（TPS，Transactions Per Second）和查詢處理速度（QPS，Queries Per Second）

場景 單庫多表
資料量 20w
執行緒數 64
壓測時間 200s
事務數 838077
TPS 4,189.48 事務/秒
QPS 25,136.95 查詢/秒
主從複製延遲 44.4s

。。。同樣的 sysbench 寫壓測引數，單庫多表，20張表、每張表10000條資料、併發執行緒數64、持續壓測時間200s

至少相對 MySQL 5.6 版本，MySQL 5.7 版本的主從複製延遲時間是最佳化了的

8.7、清除測試資料

run 改為 cleanup 即可

sysbench /usr/share/sysbench/oltp_write_only.lua \
--mysql-host=192.168.112.10 --mysql-port=3306 \
--mysql-user=root --mysql-password='123' \
--mysql-db=tssysbench --db-driver=mysql \
--tables=20 --table-size=10000 --report-interval=10 \
--threads=64 --time=200 \
cleanup

五、MySQL 8.0 基於 WriteSet 的並行複製

1、概述

基於組提交 LOGICAL_CLOCK 多執行緒複製機制在每組提交事務足夠多，即業務量足夠大時表現較好。但很多實際業務中，雖然事務沒有 Lock Interval 重疊，但這些事務操作的往往是不同的資料行，也不會有鎖衝突，是可以並行執行的，但 LOGICAL_CLOCK 的實現無法使這部分事務得到並行重放。為了解決這個問題，MySQL 在 5.7.22 版本推出了基於WriteSet的並行複製。簡單來說，WriteSet並行複製的思想是：不同事務的記錄不重疊，則都可在從庫上並行重放。可以看到並行的力度從組提交細化為記錄級。

MySQL8.0 是基於write-set的並行複製，write-set由binlog-transaction-dependency-tracking引數進行控制。MySQL 會有一個集合變數來儲存事務修改的記錄資訊（主鍵雜湊值），所有已經提交的事務所修改的主鍵值經過 hash 後都會與那個變數的集合進行對比，來判斷改行是否與其衝突，並以此來確定依賴關係，沒有衝突即可並行。這樣的粒度，就到了 row 級別了，此時並行的粒度更加精細，並行的速度會更快。

2、核心原理

Master 端

  1. WriteSet 的生成：

    • 當事務提交時，MySQL 會計算該事務修改的所有行的 WriteSet。WriteSet 是一個雜湊集合，包含了所有被修改的行的唯一識別符號（通常是主鍵或唯一鍵的雜湊值）。

      • WriteSet=hash(index_name | db_name | db_name_length | table_name | table_name_length | value | value_length)

  2. WriteSet 的儲存：

    • MySQL 維護一個雜湊表來儲存 WriteSet 和其對應的 sequence number。
    • 每個事務提交時，會檢查雜湊表中是否存在相同的 WriteSet。

  3. last_committed 的更新：

    • 無衝突：如果雜湊表中不存在相同的 WriteSet，說明當前事務與之前的事務沒有衝突。此時，WriteSet 插入雜湊表，並且當前事務的 last_committed 值保持不變，與前一個事務的 last_committed 值相同。
    • 有衝突：如果雜湊表中存在相同的 WriteSet，說明當前事務與之前的事務有衝突。此時，更新雜湊表中對應 WriteSet 的 sequence_number，並將當前事務的 last_committed 值更新為新的 sequence_number。

Slave 端

  1. 並行執行
    • 在從庫上，複製排程器會檢查事務的 last_committed 值。
    • 如果兩個事務的 last_committed 值相同，說明它們可以並行執行。
    • 如果兩個事務的 last_committed 值不同，說明它們有衝突，必須按順序執行。

3、MySQL 8.0 相關引數

在MySQL 8.0中，引入了引數binlog_transaction_dependency_tracking用於控制如何決定事務的依賴關係。

3.1、binlog_transaction_dependency_tracking

該值有三個選項：

  • COMMIT_ORDERE:表示繼續使用5.7中的基於組提交的方式決定事務的依賴關係(預設值)；
  • WRITESET：表示使用寫集合來決定事務的依賴關係；
  • WRITESET_SESSION：表示使用WriteSet來決定事務的依賴關係，但是同一個Session內的事務不會有相同的last_committed值(同一個會話中的事務不能並行執行)。

3.2、transaction_write_set_extraction

指定事務寫集合的雜湊演算法，可設定的值有：

  • OFF
  • MURMUR32
  • XXHASH64（預設值）。

對於 Group Replication，該引數必須設定為 XXHASH64。

注意，若要將 binlog_transaction_dependency_tracking 設定為 WRITESET 或 WRITESET_SESSION，則該引數不能設定為 OFF。

3.3、binlog_transaction_dependency_history_size

m_writeset_history 的上限，預設 25000。

一般來說，binlog_transaction_dependency_history_size 越大，m_writeset_history 能儲存的行的資訊就越多。在不出現行衝突的情況下，m_writeset_history_start 也會越小。相應地，新事務的 last_committed 也會越小，在從庫重放的併發度也會越高。

4、WriteSet 依賴檢測條件

WriteSet是基於主鍵的衝突檢測(binlog_transaction_depandency_tracking = COMMIT_ORDERE|WRITESET|WRITESET_SESSION,修改的row的主鍵或非空唯一鍵沒有衝突，即可並行)。

在開啟了WRITESET或WRITESET_SESSION後，MySQL按以下的方式標識並記錄事務的更新：

  • 如果事務當前更新的行有主鍵，則將HASH（DB名、TABLE名、KEY名稱、KEY_VALUE1、KEY_VALUE2……）加入到當前事務的vector write_set中。
  • 如果事務當前更新的行有非空的唯一鍵，同樣將HASH（DB名、TABLE名、KEY名、KEY_VALUE1）……加入到當前事務的write_set中。
  • 如果事務更新的行有外來鍵約束且不為空，則將該外來鍵資訊與VALUE的HASH加到當前事務的 write_set中。
  • 如果事務當前更新的表的主鍵是其它某個表的外來鍵，則設定當前事務has_related_foreign_key = true。
  • 如果事務更新了某一行且沒有任何資料被加入到write_set中，則標記當前事務 has_missing_key = true。在執行衝突檢測的時候，先會檢查has_related_foreign_key和has_missing_key ， 如果為true，則退到COMMIT_ORDER模式；否則，會依照事務的write_set中的HASH值與已提交的事務的write_set進行比對。如果沒有衝突，則當前事務與最後一個已提交的事務共享相同的last_commited，否則將從全域性已提交的write_set中刪除那個衝突的事務之前提交的所有write_set，並退化到COMMIT_ORDER計算last_committed。
  • 在每一次計算完事務的last_committed值以後，需要去檢測當前全域性已經提交的事務的write_set是否已經超過了binlog_transaction_dependency_history_size設定的值，如果超過，則清空已提交事務的全域性write_set。

從檢測條件上看，該特性依賴於主鍵和唯一索引，如果事務涉及的表中沒有主鍵且沒有唯一非空索引，那麼將無法從此特性中獲得效能的提升。除此之外，還需要將Binlog格式設定為Row格式。

5、基於 COMMIT_ORDER，WRITESET_SESSION，WRITESET 方案的壓測

MySQL 官方有對 COMMIT_ORDER，WRITESET_SESSION，WRITESET 這三種方案的壓測測試

我這裡就不測試了

主庫環境：16 核，SSD，1個資料庫，16 張表，共 800w 條資料。

壓測場景：OLTP Read/Write, Update Indexed Column 和 Write-only。

壓測方案：在關閉複製的情況下，在不同的執行緒數下，注入 100w 個事務。開啟複製，觀察不同執行緒數下，不同方案的從庫重放速度。

三個場景下的壓測結果如圖所示。

sysbnech RW

sysbench update index

sysbench write only

結論

1、對於 COMMIT_ORDER 方案，主庫併發度越高，從庫的重放速度越快。

2、對於 WRITESET 方案，主庫的併發執行緒數對其幾乎沒有影響。甚至，單執行緒下 WRITESET 的重放速度都超過了 256 執行緒下的COMMIT_ORDER。

3、與 COMMIT_ORDER 一樣，WRITESET_SESSION 也依賴於主庫併發。只不過，在主庫併發執行緒數較低（4 執行緒、8 執行緒）的情況下，WRITESET_SESSION 也能實現較高的吞吐量。

6、開啟並行複製

修改過 my.cnf 配置檔案中的引數都需要過載重啟 MySQL 才能生效

6.1、主庫

binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_history_size = 25000
binlog_format = ROW
  • binlog_transaction_dependency_tracking：
    • COMMIT_ORDER：基於提交順序的依賴關係。這意味著事務的依賴關係是根據它們在主庫上提交的順序來確定的。
    • WRITESET：基於寫集的依賴關係。這意味著事務的依賴關係是根據它們修改的資料行來確定的。
    • WRITESET_SESSION：基於會話的寫集依賴關係。這是 WRITESET 的一種更細粒度的變體，它不僅考慮事務修改的資料行，還考慮會話內的事務依賴關係。
  • transaction_write_set_extraction = XXHASH64：選擇用於計算 WriteSet 的雜湊演算法。
    • OFF：不計算 WriteSet。
    • XXHASH64：使用 XXHash64 演算法。這是一種快速且分佈均勻的雜湊演算法。
    • MURMUR32：使用 MurmurHash32 演算法。這也是一種快速的雜湊演算法，但比 XXHash64 稍慢。
  • binlog_transaction_dependency_history_size：設定 WriteSet 歷史記錄的最大大小。
    • 可以根據實際情況調整。較大的值可以提供更多的歷史記錄，但會佔用更多記憶體。較小的值可能會導致歷史記錄過早被清除，影響依賴關係的準確性。
  • binlog_format：設定二進位制日誌的格式。
    • STATEMENT：基於語句的複製。二進位制日誌記錄的是 SQL 語句本身。
    • ROW：基於行的複製。二進位制日誌記錄的是每行資料的變化。（基於 WRITESET 的並行複製方案，只在 binlog 格式為 ROW 的情況下才生效。）
    • MIXED：混合模式。MySQL 自動選擇 STATEMENT 或 ROW 模式，以確保複製的正確性。

6.2、從庫

slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = ON
  • slave_parallel_type：設定從庫並行複製的型別。該引數有以下取值：
    • DATABASE：基於庫級別的並行複製。MySQL 8.0.27 之前的預設值。
    • LOGICAL_CLOCK：基於組提交的並行複製。
  • slave_parallel_workers：設定 Worker 執行緒的數量。
    • 開啟了多執行緒複製，原來的 SQL 執行緒將演變為 1 個 Coordinator 執行緒和多個 Worker 執行緒。
  • slave_preserve_commit_order：
    • 事務在從庫上的提交順序是否與主庫保持一致，建議開啟。

相關文章