欢迎关注公众号：
一介IT
本站博文抢先发布在公众号。
摘自个人网站，文章原文地址 https://l080l.com/mysql/ha/chapter09.html
本文
2.5万字。
文章目录
1. 并行复制背景
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 的并发度，减少复制延迟。
2. 搭建复制性能的测试环境
我们可以将复制的时间分为两部分：一是事件从主库到从库的传输时间，二是事件在从库上的执行时间。事件在主库上记录二进制日志后到传递到从库的时间理论上非常快，因为它只取决于网络速度。MySQL 二进制日志的 dump 线程不是通过轮询方式请求事件，而是由主库来通知从库新的事件，因为前者低效且缓慢。从主库读取一个二进制日志事件是一个阻塞型网络调用，当主库记录事件后，马上就开始发送。因此可以说，只要 I/O 线程被唤醒并且能够通过网络传输数据，事件就会很快到达从库。但如果网络很慢并且二进制日志事件很大，记录二进制日志和在从库上执行的延迟可能会非常明显。如果查询需要执行很长时间而网络很快，通常可以认为重放时间占据了更多的复制时间开销。
本节主要从日志持久化、组提交与多线程复制，以及新增的 WRITESET 特性三个方面，讨论对复制性能产生的影响。我们先简要介绍每种特性的基础知识，然后针对不同情况进行测试，最后由测试结果得出结论。所有测试均基于 GTID 的标准主从异步复制。
2.1 测试规划
这里使用的思路是：记录主库加压前后的 GTID，得到从库需要执行的事务数。然后在从库上执行复制，记录执行时间，得到从库的每秒执行事务数（TPS）作为衡量复制性能的指标。测试目的在于对比不同情况下复制的性能，而不是针对测量绝对值进行优化。主库加压使用 tpcc-mysql 基准测试工具。
2.1.1 测试环境
#测试环境如下，已经配置好GTID异步复制。
主库：172.16.1.125
从库：172.16.1.126
MySQL版本：8.0.16
#测试通用参数：
主库：
server_id=1125
gtid_mode=ON
enforce-gtid-consistency=true
innodb_buffer_pool_size=4G
从库：
server_id=1126
gtid_mode=ON
enforce-gtid-consistency=true
innodb_buffer_pool_size=4G
2.1.2 tpcc-mysql测试前准备
PC-C 是专门针对联机交易处理系统（OLTP系统）的规范，tpcc-mysql 则是 percona 公司基于 TPC-C 衍生出来的产品，专用于 MySQL 基准测试，下载地址为 https://github.com/Percona-Lab/tpcc-mysql。这里使用 tpcc-mysql 只是为了给主库加压。使用 tpcc-mysql 开始测试前完成以下准备工作，所有步骤均在主库上执行：
#1. 安装
cd tpcc-mysql-master/src
make
#2. 建立测试库
mysql -uroot -p123456 -e "create database tpcc_test;"
#3. 建表和索引
cd tpcc-mysql-master
mysql -uroot -p123456 -Dtpcc_test < create_table.sql
mysql -uroot -p123456 -Dtpcc_test < add_fkey_idx.sql
#4. 生成数据
tpcc_load -h127.0.0.1 -d tpcc_test -u root -p "123456" -w 10
-w参数指定建立的仓库数。
#5. 备份测试库
为在同等环境下进行比较，每次测试前都要重新生成测试库中的表、索引和数据，因此这里做一个测试库的逻辑备份。一定要加--set-gtid-purged=off，因为将备份导入主库时，需要在从库通过复制同时生成。下面是每次测试在从库执行的自动化脚本:
# 初始化tpcc数据
mysql -uwxy -p123456 -h172.16.1.125 < tpcc_test.sql
# 读取主库的二进制坐标
read master_file master_pos < <(mysql -uwxy -p123456 -h172.16.1.125 -e "show master status;" --skip-column-names | awk '{print $1,$2}')
# 从库初始化tcpp数据结束后停止复制
mysql -uwxy -p123456 -e "select master_pos_wait('$master_file',$master_pos);stop slave;"
# 取得从库开始GTID
read start_gtid < <(mysql -uwxy -p123456 -e "show variables like 'gtid_executed';" --skip-column-names | awk '{print $2}' | sed "s/\\\n//g")
# 主库执行压测，10个仓库，32个并发线程，预热1分钟，压测5分钟
tpcc_start -h172.16.1.125 -d tpcc_test -u wxy -p "123456" -w 10 -c 32 -r 60 -l 300 > tpcc_test.log 2>&1
# 读取主库的二进制坐标
read master_file master_pos < <(mysql -uwxy -p123456 -h172.16.1.125 -e "show master status;" --skip-column-names | awk '{print $1,$2}')
# 从库复制开始时间
start_time=`date '+%s'`
# 从库执行复制
mysql -uwxy -p123456 -e "start slave;select master_pos_wait('$master_file',$master_pos);"
# 从库复制结束时间
end_time=`date '+%s'`
# 复制执行时长
elapsed=$(($end_time - $start_time))
# 取得从库结束GTID
read end_gtid < <(mysql -uwxy -p123456 -e "show variables like 'gtid_executed';" --skip-column-names | awk '{print $2}' | sed "s/\\\n//g")
# 取得从库执行的事务数
read start end < <(mysql -uwxy -p123456 -e "select gtid_subtract('$end_gtid','$start_gtid');" --skip-column-names | awk -F: '{print $2}' | awk -F- '{print $1,$2}')
trx=$(($end - $start + 1))
# 计算从库、主库的TPS
Slave_TPS=`expr $trx / $elapsed`
Master_TPS=`expr $trx / 360`
# 打印输出
echo "TRX: $trx" "Elapsed: $elapsed" "Slave TPS: $Slave_TPS" "Master TPS: $Master_TPS"
2.2 sync_binlog与innodb_flush_log_at_trx_commit
sync_binlog控制 MySQL 服务器将二进制日志同步到磁盘的频率，可取值 0、1、N，MySQL 8 的缺省值为 1。innodb_flush_log_at_trx_commit控制提交时是否将 innodb 日志同步到磁盘，可取值 0、1、2，MySQL 8 的缺省值为 1。关于这两个参数已经在【MySQL复制（一）——异步复制】中详细讨论，这里不再赘述。简单说，对于复制来讲，sync_binlog 为 0 可能造成从库丢失事务，innodb_flush_log_at_trx_commit为 0 可能造成从库比主库事务多。而从性能角度看，双1的性能最差，双0的性能最好。权衡数据安全与性能，一般建议主库都设置为双1，根据场景从库可以设置成其它组合来提升性能。
下表所示为从库上sync_binlog、innodb_flush_log_at_trx_commit四种设置的测试结果：
| sync_binlog | innodb_flush_log_at_trx_commit | 事务数 | 复制执行时间（秒） | 从库TPS | 主库TPS |
|---|---|---|---|---|---|
| 0 | 0 | 183675 | 330 | 556 | 510 |
| 0 | 1 | 184177 | 498 | 369 | 511 |
| 1 | 0 | 183579 | 603 | 304 | 509 |
| 1 | 1 | 183020 | 683 | 267 | 508 |
测试中主库执行了一共 360 秒（预热+压测），TPS 为 510。从表中可以明显看到这两个参数的不同组合对复制性能的影响。当从库仅为单线程复制时，只有双 0 的设置在执行时间和 TPS 上优于主库，其它组合会造成复制延迟。
3. MySQL5.6基于库级别的并行复制
MySQL 5.6 版本也支持并行复制，但是其并行只是基于库的。如果用户的 MySQL 数据库中是多个库，对于从库复制的速度的确可以有比较大的帮助。在实例中有多个数据库的情况下，可以开启多个线程，每个线程对应一个数据库。该模式下从节点会启动多个线程。线程分为两类 Coordinator 和 WorkThread。
- 线程分工执行逻辑
Coordinator线程负责判断事务是否可以并行执行，如果可以并行就把事务分发给WorkThread线程执行，如果判断不能执行，如DDL，跨库操作等，就等待所有的worker线程执行完成之后，再由Coordinator执行。
- 关键配置信息
#(不同库的事务，没有锁冲突)
slave-parallel-type=DATABASE
# slave-parallel-type是5.7.2才新增的参数，5.6本身默认就是database级别的复制模式
这种并行复制的模式,只有在实例中有多个 DB，且 DB 的事务都相对繁忙的情况下才会有较高的并行度，但是日常维护中其实单个实例的的事务处理相对集中在一个 DB 上。通过观察延迟可以发现基本上都是基于热点表出现延迟的情况占大多数。如果能够提供基于表的并行度是一个很好方法。
[外链图片转存中…(img-pW0WUmUT-1700367539639)]
4. MySQL5.7基于组提交的并行复制
- MySQL 5.6 支持多线程复制（multi-threaded slave，MTS），但太过局限。它只实现了基于 schema 的多线程复制，使不同数据库下的DML操作可以在从库并行重放，这样设计的复制效率并不高。如果用户实例仅有一个库，那么就无法实现并行重放，甚至性能会比原来的单线程更差，而单库多表是比多库多表更为常见的一种情形。
- MySQL 5.7 的多线程复制基于组提交实现，不再有基于 schema 的多线程复制限制。
4.1 组提交
从 MySQL 5.6 开始同时支持 Innodb redo log 和 binlog 组提交，并且默认开启，大大提高了 MySQL 的事务处理性能。和很多 RDBMS 一样，MySQL 为了保证事务处理的一致性和持久性，使用了 WAL（Write Ahead Log）机制，即对数据文件进行修改前，必须将修改先记录日志。Redo log 就是一种 WAL 的应用，每次事务提交时，不用同步刷新磁盘数据文件，只需要同步刷新 redo log 就够了。相比写数据文件时的随机 I/O，写 Redo log 时的顺序 I/O 能够提高事务提交速度。Redo log 的刷盘操作将会是最终影响 MySQL TPS 的瓶颈所在。为了缓解这一问题的影响，MySQL 使用了 redo log 组提交，将多个 redo log 刷盘操作合并成一个。
为了保证 redo log 和 binlog 的数据一致性，MySQL 使用了两阶段提交（prepare 阶段和 commit 阶段），由 binlog 作为事务的协调者。而引入两阶段提交使得 binlog 又成为了性能瓶颈，于是 MySQL 5.6 增加了 binlog 的组提交，目的同样是将 binlog 的多个刷盘操作合并成一个。结合 redo log 本身已经实现的组提交，将提交过程分成 Flush stage、Sync stage、Commit stage 三个阶段完成组提交，最大化每次刷盘的收益，弱化磁盘瓶颈。每个阶段都有各自的队列，使每个会话的事务进行排队，提高并发性能。
- Flush阶段：
- 首先获取队列中的事务组，将 redo log 中 prepare 阶段的数据刷盘。
- 将 binlog 数据写入文件系统缓冲，并不能保证数据库崩溃时 binlog 不丢失。
- Flush 阶段队列的作用是提供了 redo log 的组提交。
- 如果在这一步完成后数据库崩溃，由于协调者 binlog 中不保证有该组事务的记录，所以 MySQL 可能会在重启后回滚该组事务。
- Sync阶段：
- 将 binlog 缓存 sync 到磁盘，sync_binlog=1 时该队列中所有事务的binlog将永久写入磁盘。
- 为了增加一组事务中的事务数量，提高刷盘收益，MySQL 使用两个参数控制获取队列事务组的时机：
binlog_group_commit_sync_delay=N：在等待N微秒后，开始事务刷盘。binlog_group_commit_sync_no_delay_count=N：如果队列中的事务数达到N个，就忽视binlog_group_commit_sync_delay的设置，直接开始刷盘。
- Sync 阶段队列的作用是支持 binlog 的组提交。
- 如果在这一步完成后数据库崩溃，由于协调者 binlog 中已经有了事务记录，MySQL 会在重启后通过 Flush 阶段中 Redo log 刷盘的数据继续进行事务的提交。
- Commit阶段：
- 首先获取队列中的事务组。
- 依次将 redo log 中已经 prepare 的事务在存储引擎层提交，清除回滚信息，向 redo log 中写入 COMMIT 标记。
- Commit 阶段不用刷盘，如上所述，Flush阶段中的 redo log 刷盘已经足够保证数据库崩溃时的数据安全了。
- Commit 阶段队列的作用是承接 Sync 阶段的事务，完成最后的引擎提交，使得 Sync 可以尽早的处理下一组事务，最大化组提交的效率。
Commit 阶段会受到参数binlog_order_commits的影响，当该参数为 OFF 时，不保证 binlog 和事务提交的顺序一致，因为此时允许多个线程发出事务提交指令。也正是基于同样的原因，可以防止逐个事务提交成为吞吐量瓶颈，性能会有少许提升。多数情况下，存储引擎的提交指令与 binlog 不同序无关紧要，因为多个单独事务中执行的操作，无论提交顺序如何都应该产生一致的结果。但也不是绝对的，例如会影响 XtraBackup 工具的备份。XtraBackup 会从 innodb page 中获取最后提交事务的binlog位置信息，binlog_order_commits=0时事务提交顺序和binlog顺序可能不一致，这样此位置前可能存在部分prepare状态的事务，这些事务在备份恢复后会因回滚而丢失。binlog_order_commits的缺省值为 ON，此时存储引擎的事务提交指令将在单个线程上串行化，以致事务始终以与写入二进制日志相同的顺序提交。
4.2 多线程并行复制
MySQL 5.7 是基于组提交(group commit)的并行复制，MySQL 5.7.2 进行了优化，可称为真正的并行复制，这其中最为主要的原因就是slave服务器的回放与master服务器是一致的，即 master 服务器上是怎么并行执行的slave上就怎样进行并行回放。不再有库的并行复制限制。为了兼容 MySQL 5.6 基于库的并行复制，5.7 引入了新的变量slave-parallel-type。
# DATABASE （默认值，基于库的并行复制方式，5.6默认就是这个参数，即每个库智能有一个复制进程）
# LOGICAL_CLOCK （基于组提交的并行复制方式）
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_type=DATABASE
mysql> show variables like 'slave_parallel_type';
+---------------------+---------------+
| Variable_name | Value |
+---------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
+---------------------+---------------+
1 row in set (0.00 sec)
4.3 事务提交模式
InnoDB 事务提交采用的是两阶段提交模式。一个阶段是prepare，另一个是commit。MySQL 5.7 是通过对事务进行分组，当事务提交时，它们将在单个操作中写入到二进制日志中:
slave-parallel-type=LOGICAL_CLOCK : Commit-Parent-Based模式
同一组的事务[
last-commit相同]，没有锁冲突。同一组，肯定没有冲突，否则没办法成为同一组。slave-parallel-type=LOGICAL_CLOCK : Lock-Based模式
即便不是同一组的事务，如果多个事务能同时提交成功，只要事务之间没有锁冲突[
prepare阶段]，就可以并发。 不在同一组，只要 N 个事务 prepare 阶段可以重叠，说明没有锁冲突。当然也可以在 Slave 中并行提交，因为处理这个阶段的事务都是没有冲突的。
4.3.1 Commit-Parent-Based模式
[外链图片转存中…(img-dSFWymqL-1700367539640)]
重点解读：
1.c 代表 last commited，表示事务进入到 prepare 阶段前获取到的最大 commit 的逻辑时间
2.S 代表 sequence num，表示每个事务 commit 的结束后的逻辑时间
3.拥有同样的C的事务，说明这些事务属于同一组，同一组的事务没有冲突，可以并行
4.trx1，trx2，trx3，trx4，不属于同一个事务组，无法并行
5.trx5，trx6，trx7 属于同一个事务组，他们的 c 是 4，可以并行
6.trx8，tx9 属于同一事物组，可以并行，但是必须等到 tx7 执行完才可以
7.这里的并行粒度相比库级别，已经很细了，但是如果 master 的组事务越少，并行越低
4.3.2 Lock-Based模式
[外链图片转存中…(img-te1NwOKW-1700367539640)]
重点解读：
1.c 代表 last commited，表示事务进入到 preparel 阶段前获取到的最大 commit 的逻辑时间
2.S 代表 sequence num，表示每个事务 commit 的结束后的逻辑时间
3.可以重叠的事务，说明这些事务 prepare 可以一起执行，就意味着没有锁冲突
4.Trx1，trx2，trx3，tx4 均没有重叠，他们不可以并行执行
5.trx5，trx6，trx7 重叠，他们可以并行执行
6.重点：tx7，tx8，trx9 重叠，但是他们并不是同一组，也可以并行，怎么并行呢？
当 trx5，trx6 结束后，trx7，trx8，trx9 就可以并行了，这样的话并行粒度就更细了
4.4 查看提交的内部信息
如何判断事务在一个组内呢?
在 MySQL 5.7 版本中，其设计方式是将组提交的信息存放在 GTID 中。为了避免用户没有开启 GTID 功（gtid_mode=OFF），MySQL 5.7 又引入了称之为Anonymous_Gtid的二进制日志event类型ANONYMOUS_GTID_LOG_EVENT。通过mysqlbinlog工具分析binlog日志，就可以发现组提交的内部信息，可以发现二进制日志较之原来的二进制日志内容多了last_committed和sequence_number两个参数信息，其中last_committed存在重复的情况，表示这些事务都在一组内，可以进行并行的回放。
[root@mysql-master mysql]# mysqlbinlog mysql-bin.0000002 | grep last_committed
GTID last_committed=0 sequence_number=1
GTID last_committed=0 sequence_number=2
GTID last_committed=2 sequence_number=3
GTID last_committed=2 sequence_number=4
GTID last_committed=2 sequence_number=5
GTID last_committed=2 sequence_number=6
GTID last_committed=6 sequence_number=7
GTID last_committed=6 sequence_number=8
sequence_number这个值指的是事务提交的序号，单调递增。last_committed这个值有两层含义- 1.相同值代表这些事务是在同一个组内
- 2.该值同时又是代表上一组事务的最大编号。
last_committed表示事务提交的时候，上次事务提交的编号。事务在perpare阶段获取相同的last_committed而且相互不影响，最终会作为一组进行提交。如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行重放。例如上述last_committed为 0 的 10 个事务在从库是可以进行并行重放的。这个机制是Commit-Parent-BasedScheme 的实现方式。sequence_number是事务计数器。记录在GTID_EVENT中的sequence_number和last_committed使用的是相对当前二进制日志文件的值。即每个二进制日志文件中事务的last_commited起始值为0，sequence_number为1。由于二进制日志文件切换时，需要等待上一个文件的事务执行完，所以这里记录相对值并不会导致冲突事务并行执行。
由于在 MySQL 中写入是基于锁的并发控制，所以所有在主库同时处于 prepare 阶段且未提交的事务就不会存在锁冲突，从库就可以并行执行。Commit-Parent-Based Scheme 使用的就是这个原理，简单描述如下：
- 主库上有一个全局计数器（
global counter）。每一次存储引擎提交之前，计数器值就会增加。 - 主库上，事务进入
prepare阶段之前，全局计数器的当前值会被储存在事务中，这个值称为此事务的commit-parent。 - 主库上，
commit-parent会在事务的开头被储存在 binlog 中。 - 从库上，如果两个事务有同一个
commit-parent，它们就可以并行被执行。
此commit-parent就是在 binlog 中看到的last_committed。如果commit-parent相同，即last_committed相同，则被视为同一组，可以并行重放。Commit-Parent-Based Scheme的问题在于会降低复制的并行程度，如图所示（引自https://dev.mysql.com/worklog/task/?id=7165）。
每一个水平线代表一个事务,时间从左到右。P 表示事务在进入 prepare 阶段之前读到的commit-parent值的那个时间点，可以简单视为加锁时间点。C 表示事务增加了全局计数器值的那个时间点，可以简单视为释放锁的时间点。P 对应的commit-parent是取自所有已经执行完的事务的最大的C对应的sequence_number，举例来说：Trx4 的 P 对应的commit-parent是Trx1的C对应的sequence_number。因为这个时候Trx1已经执行完，但是Trx2还未执行完。Trx5 的 P 对应的 commit-parent 是 Trx2 的 C 对应的sequence_number。Trx6 的 P对应的commit-parent是 Trx2 的 C 对应的sequence_number。
Trx5 和 Trx6 具有相同的commit-parent，在进行重放的时候，Trx5 和 Trx6 可以并行执行。Trx4 和 Trx5 不能并行执行，Trx6 和 Trx7也不能并行执行，因为它们的commit-parent不同。但注意到，在同一时段，Trx4 和 Trx5、Trx6 和 Trx7分别持有它们各自的锁，事务互不冲突，所以在从库上并行执行是不会有问题的。针对这种情况，为了进一步增加并行度，MySQL 对并行复制的机制做了改进，提出了一种新的并行复制的方式：Lock-Based Scheme，使同时持有各自锁的事务可以在从库并行执行。
Lock-Based Scheme定义了一个称为lock interval的概念，表示一个事务持有锁的时间间隔。假设有两个事务 Trx1、Trx2，Trx1 先于 Trx2。那么，当且仅当 Trx1、Trx2 的lock interval有重叠，则可以并行执行。换言之，若 Trx1 结束自己的lock interval早于 Trx2 开始自己的 lock interval，则不能并行执行。如图所示，L表示lock interval的开始点，C 表示 lock interval 的结束。
[外链图片转存中…(img-kAJIVR6W-1700367539641)]
对于 C（lock interval的结束点），MySQL 会给每个事务分配一个逻辑时间戳（logical timestamp），命名为transaction.sequence_number。此外，MySQL 会获取全局变量global.max_committed_transaction，表示所有已经结束lock interval的事务的最大的sequence_number。对于 L（lock interval 的开始点），MySQL 会把global.max_committed_transaction分配给一个变量，并取名叫transaction.last_committed。transaction.sequence_number和transaction.last_committed这两个时间戳都会存放在 binlog 中，就是前面看到的last_committed和sequence_number。
根据以上分析得出，只要事务和当前执行事务的Lock Interval都存在重叠，就可以在从库并行执行。上图中，Trx3、Trx4、Trx5、Trx6 四个事务可以并行执行，因为 Trx3 的sequence_number大于 Trx4、Trx5、Trx6 的last_committed，即它们的Lock Interval存在重叠。当 Trx3、Trx4、Trx5 执行完成之后，Trx6 和 Trx7 可以并发执行，因为 Trx6 的sequence_number大于Trx7的last_committed，即两者的lock interval存在重叠。Trx5 和 Trx7 不能并发执行，因为 Trx5 的sequence_number小于 Trx7 的last_committed，即两者的lock interval不存在重叠。
可以通过以下命令粗略查看并发度：
[mysql@hdp2/usr/local/mysql/data]$mysqlbinlog binlog.000064 | grep -o 'last_committed.*' | sed 's/=/ /g' | awk '{print $4-$2-1}' | sort -g | uniq -c
1693 0
4795 1
8174 2
11378 3
13879 4
15407 5
15979 6
15300 7
13762 8
11471 9
9061 10
6625 11
4533 12
3006 13
1778 14
1021 15
521 16
243 17
135 18
61 19
31 20
23 21
18 22
7 23
5 24
7 25
3 26
3 27
6 28
1 29
1 30
2 31
1 32
3 33
3 34
1 37
1 39
1 40
1 42
1 44
1 46
1 49
1 50
1 56
1 120
第一列为事务数量，第二列表示这些事务能与它们之前的多少个事务并行执行。例如有 1693 个事务不能与之前的事务并发，必须等到所有前面的事务完成之后才能开始，但并不表示不能和后面的事务并行执行。当前事务无法判断能否和后面的事务并行执行，只能与前面事务的sequence_number比较，得出自己是否可以并发执行。仅仅设置为LOGICAL_CLOCK还会存在问题，因为此时在从库上应用事务是无序的，和 relay log 中记录的事务顺序可能不一样。在这种情况下，从库的 GTID 会产生间隙，事务可能在某个时刻主从是不一致的，但是最终会一致，满足最终一致性。相同记录的修改，会按照顺序执行，这由事务隔离级保证。不同记录的修改，可以产生并行，并无数据一致性风险。这大概也是slave_preserve_commit_order参数缺省为 0 的原因之一。
如果要保证事务是按照 relay log 中记录的顺序来重放，需要设置参数slave_preserve_commit_order=1，这要求从库开启 log_bin 和log_slave_updates，并且slave_parallel_type设置为LOGICAL_CLOCK。
启用slave_preserve_commit_order后，正在执行的worker线程将等待，直到所有先前的事务提交后再提交。当复制线程正在等待其它worker线程提交其事务时，它会将其状态报告为等待提交前一个事务。使用此模式，多线程复制的重放顺序与主库的提交顺序保持一致。
slave_parallel_workers 参数控制并行复制worker线程的数量。若将slave_parallel_workers设置为0，则退化为单线程复制。如果slave_parallel_workers=N（N>0），则单线程复制中的 SQL 线程将转为 1 个 coordinator 线程和N个worker线程，coordinator 线程负责选择worker线程执行事务的二进制日志。例如将slave_parallel_workers设置为1，则 SQL 线程转化为 1 个 coordinator 线程和1个worker线程，也是单线程复制。然而，与slave_parallel_workers=0相比，多了一次coordinator线程的转发，因此slave_parallel_workers=1的性能反而比 0 还要差。MySQL 8 中slave_parallel_workers参数可以动态设置，但需要重启复制才能生效。
LOGICAL_CLOCK多线程复制为了准确性和实现的需要，其lock interval实际获得的区间比理论值窄，会导致原本一些可以并发行行的事务在从库上没有并行执行。当使用级联复制时，LOGICAL_CLOCK可能会使离主库越远的从库并行度越小。
4.5 开启MTS
#参数持久化设置
[root@mysql80-01 ~]# cat /etc/my.cnf
[mysqld]
slave-parallel-type = LOGICAL_CLOCK
#重启服务查看
mysql> show variables like 'slave_parallel_type';
+---------------------+---------------+
| Variable_name | Value |
+---------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
+---------------------+---------------+
1 row in set (0.00 sec)
4.6 多线程复制测试
#从库增加以下配置参数：
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
slave_preserve_commit_order = 1
slave_parallel_type = LOGICAL_CLOCK
下表所示为从库上 slave_parallel_workers 分别设置为 2、4、8、16 的测试结果：
| slave_parallel_workers | 事务数 | 复制执行时间（秒） | 从库TPS | 主库TPS |
|---|---|---|---|---|
| 2 | 183717 | 460 | 399 | 510 |
| 4 | 183248 | 396 | 462 | 509 |
| 8 | 182580 | 334 | 546 | 507 |
| 16 | 183290 | 342 | 535 | 509 |
测试中主库执行了一共 360 秒（预热+压测），TPS 为 509。从表中可以看到，在实验负载场景下，多线程复制性能明显高于单线程复制。slave_parallel_workers=8时性能最好，当 worker 数量增加到 16 时，性能反而比 8 时差。太多线程会增加线程间同步的开销，因此slave_parallel_workers值并非越大越好，需要根据实际负载进行测试来确定其最佳值，通常建议建议 4-8 个 worker 线程。
5. MySQL8.0基于write-set的并行复制
5.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 级别了，此时并行的粒度更加精细，并行的速度会更快。
5.2 WriteSet对象
MySQL 中用 WriteSet 对象来记录每行记录，从源码来看 WriteSet 就是每条记录 hash 后的值（必须开启 ROW 格式的二进制日志），具体算法如下：
WriteSet=hash(index_name | db_name | db_name_length | table_name | table_name_length | value | value_length)
上述公式中的 index_name 只记录唯一索引，主键也是唯一索引。如果有多个唯一索引，则每条记录会产生对应多个 WriteSet 值。另外，value 这里会分别计算原始值和带有字符集排序规则（Collation）值的两种 WriteSet。所以一条记录可能有多个 WriteSet 对象。新产生的 WriteSet 对象会插入到 WriteSet 哈希表，哈希表的大小由参数binlog_transaction_dependency_history_size设置，默认25000。内存中保留的哈希行数达到此值后，将清除历史记录。
5.3 启用writeset并行复制
# master
[root@master ~]# cat /etc/my.cnf
[mysqld]
loose-binlog_transaction_dependency_tracking = WRITESET
#默认
loose-transaction_write_set_extraction = XXHASH64
binlog_transaction_dependency_history_size = 25000
#slave
[root@slave ~]# cat /etc/my.cnf
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 8
5.4 核心原理
- master
- master 端在记录 binlog 的
last_committed方式变了；- 基于
commit-order的方式中，last_committed表示同一组的事务拥有同一个parent_commit；- 基于
writeset的方式中，last_committed的含义是保证冲突事务（相同记录）不能拥有同样的last_committed值；- 当事务每次提交时，会计算修改的每个行记录的
WriteSet值，然后查找哈希表中是否已经存在有同样的WriteSet；
- 1.若无，
WriteSet插入到哈希表，写入二进制日志的last_committed值保持不变，意味着上一个事务跟当前事务的last_committed相等，那么在slave就可以并行执行；- 2.若有，更新哈希表对应的
writeset的value为sequencenumber，并且写入到二进制日志的 last_committed 值也要更新为sequnce_number。意味着，相同记录（冲突事务）回放，last_committed值必然不同，必须等待之前的一条记录回放完成后才能执行。- slave
- slave 的逻辑跟以前一样没有变化，
last_committed相同的事务可以并行执行。
基于 WriteSet 的复制优化了主库组提交的实现，主要体现主库端last_committed的定义变了。原来一组事务是指拥有同一个parent_commit的事务，在二进制日志中记录为同一个last_committed。基于WriteSet的方式中，last_committed的含义是保证冲突事务（更新相同记录的事务）不能拥有同样的last_committed值，事务执行的并行度进一步提高。当事务每次提交时，会计算修改的每个行记录的 WriteSet 值，然后查找哈希表中是否已经存在有同样的WriteSet，若无，WriteSet插入到哈希表，写入二进制日志的last_committed值不变。上一个事务跟当前事务的last_committed相等，意味着它们可以最为一组提交。若有，更新哈希表对应的WriteSet值为sequence_number，并且写入到二进制日志的last_committed值也更新为sequnce_number。上一个事务跟当前事务的last_committed必然不同，表示事务冲突，必须等待之前的事务提交后才能执行。
从库端的逻辑跟以前一样没有变化，last_committed相同的事务可以并行执行。
要使用 WriteSet 方式组提交，需要设置binlog_transaction_dependency_tracking参数为 WRITESET。binlog_transaction_dependency_tracking参数指定主库确定哪些事务可以作为一组提交的方法，有三个可选值：
COMMIT_ORDER：依赖事务提交的逻辑时间戳，是默认值。如果事务更新的表上没有主键和唯一索引，也使用该值。这是 MySQL 5.7 所使用使用的方式。WRITESET：更新不同记录的事务（不冲突）都可以并行化。WRITESET_SESSION：与WRITESET的区别是WRITESET_SESSION需要保证同一个会话内的事务的先后顺序。消除了从库中某一时刻可能看到主库从未出现过的数据库状态的问题。
从下面这个简单的实验可以直观看到COMMIT_ORDER与WRITESET的区别。
drop table if exists t1;
create table t1 (a int primary key);
insert into t1 values (1), (2);
flush logs;
set global binlog_transaction_dependency_tracking = WRITESET;
update t1 set a=10 where a=1;
update t1 set a=20 where a=2;
set global binlog_transaction_dependency_tracking = COMMIT_ORDER;
update t1 set a=1 where a=10;
update t1 set a=2 where a=20;
#查看二进制日志：
[mysql@hdp2/usr/local/mysql/data]$mysqlbinlog binlog.000002 --base64-output=decode-rows -v | grep -e 'last_committed' -A4 -e 'UPDATE' | grep -v "# original\|# immediate\|/*!" | awk '{if ($1!="###") {print $11, $12} else {print $0}}'
last_committed=0 sequence_number=1
### UPDATE `test`.`t1`
### WHERE
### @1=1
### SET
### @1=10
last_committed=0 sequence_number=2
### UPDATE `test`.`t1`
### WHERE
### @1=2
### SET
### @1=20
last_committed=2 sequence_number=3
### UPDATE `test`.`t1`
### WHERE
### @1=10
### SET
### @1=1
last_committed=3 sequence_number=4
### UPDATE `test`.`t1`
### WHERE
### @1=20
### SET
### @1=2
[mysql@hdp2/usr/local/mysql/data]$
第一和第二个事务的last_committed都是 0。虽然这两个事务的 lock_interval 没有重叠，但它们修改的是不同的数据行，不存在事务冲突，因此它们的last_committed相同，可以作为一组并行提交。
当设置 global binlog_transaction_dependency_tracking为COMMIT_ORDER时，第三和第四个事务的last_committed分别为 2 和3。这两个事务的lock_interval没有重叠，即使更新的行不冲突，它们的last_committed也不相同，不能作为同一组并行提交。
与 WriteSet 相关的另一个参数是transaction_write_set_extraction。该参数定义计算 WriteSet 使用的哈希算法。如果用于多线程复制，必须将此变量设置为XXHASH64，这也是缺省值。如果设置为 OFF，则binlog_transaction_dependency_tracking只能设置为COMMIT_ORDER。如果binlog_transaction_dependency_tracking的当前值为WRITESET或WRITESET_SESSION，则无法更改transaction_write_set_extraction的值。
5.5 参数配置与调优
5.5.1 配置writeset并行复制
#master
[root@master ~]# cat /etc/my.cnf
[mysqld]
binlog_transaction_dependency_tracking = WRITESET
#其中配置项的value值
# 使用 5.7 Group commit 的方式决定事务依赖。
COMMIT_ORDER
# 使用写集合的方式决定事务依赖。
WRITESET
# 使用写集合，但是同一个session中的事务不会有相同的last_committed。
WRITESET_SESSION
#slave
5.5.2 控制集合变量的大小
#参数持久化设置
[root@mysql80-01 ~]# cat /etc/my.cnf
[mysqld]
binlog-transaction-dependency-history-size = 25000
# 默认25000
5.5.3 控制事务的检测算法
#参数持久化设置
[root@mysql80-01 ~]# cat /etc/my.cnf
[mysqld]
transaction-write-set-extraction = XXHASH64
# 该模式支持三种算法:OFF、 XXHASH64、MURMUR32
# 默认采用XXHASH64
# 当从节点配置writeset复制的时候，该配置不能配置为OFF。
# 该参数已经在MySQL 8.0.26中被弃用，后续将会进行删除。
5.6 WriteSet多线程复制测试
#主库增加以下配置参数：
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
#从库增加以下配置参数：
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
slave_preserve_commit_order = 1
slave_parallel_type = LOGICAL_CLOCK
下表所示为从库上 slave_parallel_workers 分别设置为 2、4、8、16、32 的测试结果：
| slave_parallel_workers | 事务数 | 复制执行时间（秒） | 从库TPS | 主库TPS |
|---|---|---|---|---|
| 2 | 209237 | 515 | 406 | 581 |
| 4 | 207083 | 438 | 472 | 575 |
| 8 | 207292 | 364 | 569 | 575 |
| 16 | 205060 | 331 | 619 | 569 |
| 32 | 201488 | 340 | 592 | 559 |
测试中主库执行了一共 360 秒（预热+压测），TPS 平均为 572，同等场景下的比COMMIT_ORDER高出 12%。当 16 个复制线程时从库 TPS 达到峰值 619，比COMMIT_ORDER下性能最好的 8 复制线程高出 13%。
MySQL 的复制延迟是一直被诟病的问题之一，从以上三组测试得出了目前解决延迟最普遍的三种方法：
- 如果负载和数据一致性要求都不是太高，可以采用单线程复制 + 安全参数双 0。这种模式同样拥有不错的表现，一般压力均可应付。
- 如果主库的并发量很高，那么基于
order-commit的模式的多线程复制可以有很好的表现。 - 基于 WriteSet 的模式是目前并发度最高的多线程复制，基本可以满足大部分场景。如果并发量非常高，或是要求从库与主库的延迟降至最低，可以采取这种方式。
6. MTS其他参数
6.1 复制的线程数
多线程从服务器可以将事务分发到不同的线程中，通过slave_parallel_workers变量调整使用的线程数量。
#参数持久化设置
[root@mysql80-01 ~]# cat /etc/my.cnf
[mysqld]
slave-parallel-type = LOGICAL_CLOCK
slave_parallel_workers = 4
#重启服务查看
mysql> show variables like "slave_parallel_workers";
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_parallel_workers | 4 |
+------------------------+-------+
1 row in set (0.00 sec)
6.2 主从状态信息存储
默认情况下 master 状态信息和 slave 状态信息都是以文件形式存储起来的，如果数据更新频次非常高的话，对磁盘性能是个考验，可以通过如下的方法查看。
#参数持久化设置
[root@mysql80-01 ~]# cat /etc/my.cnf
[mysqld]
slave-parallel-type = LOGICAL_CLOCK
slave_parallel_workers = 4
master_info_repository=TABLE
relay_log_info_repository=TABLE
#重启服务查看
mysql> show variables like '%info_repository';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
2 rows in set (0.00 sec)
两个参数都各有两个值，分别是 file 和 table，该参数决定了记录的状态：
- 如果参数
master_info_repository=file，就会创建master.info文件。 - 如果参数
master_info_repository=table，就在创建mysql.slave_master_info的表。 - 如果参数
relay_log_info_repository=file，就会创建一个realy-log.info文件。 - 如果参数
relay_log_info_repository=table，就会创建mysql.slave_relay_info表来记录同步的位置信息。
使用数据表TABLE代替文件FILE可以在一定程度上提高性能。
#mysql中查看
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
......
| servers |
#==此处==
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
#==此处==
| slow_log |
......
| user |
+------------------------------------------------------+
38 rows in set (0.00 sec)
# 其中slave_master_info是本地下载的master的同步数据，包括文件、位置等等，
# 而slave_relay_log_info是本地已同步的数据，包括文件、位置等等。
#slave_master_info的示例。
mysql> select * from mysql.slave_master_info \G;
*************************** 1. row ***************************
Number_of_lines: 33
Master_log_name: mysql-bin.000002
Master_log_pos: 197
Host: 192.168.2.80
User_name: rep
User_password: rep
Port: 3306
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid: dd746660-528a-11ed-9c86-000c293b9f86
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 1
Channel_name:
Tls_version:
Public_key_path:
Get_public_key: 0
Network_namespace:
Master_compression_algorithm: uncompressed
Master_zstd_compression_level: 3
Tls_ciphersuites: NULL
Source_connection_auto_failover: 0
Gtid_only: 0
1 row in set (0.00 sec)
#slave_relay_log_info的示例
mysql> select * from mysql.slave_relay_log_info \G;
*************************** 1. row ***************************
Number_of_lines: 14
Relay_log_name: ./mysql-slave02-relay-bin.000004
Relay_log_pos: 1232
Master_log_name: mysql-bin.000002
Master_log_pos: 1016
Sql_delay: 0
Number_of_workers: 4
Id: 1
Channel_name:
Privilege_checks_username: NULL
Privilege_checks_hostname: NULL
Require_row_format: 0
Require_table_primary_key_check: STREAM
Assign_gtids_to_anonymous_transactions_type: OFF
Assign_gtids_to_anonymous_transactions_value:
1 row in set (0.00 sec)
6.3 保证提交的顺序性
在 slave 上应用事务的顺序是无序的，和relay log中记录的事务顺序不一样，这样数据一致性是无法保证的，为了保证事务是按照 relay log 中记录的顺序来回放，就需要开启参数slave_preserve_commit_order。
#参数持久化设置
[root@mysql80-01 ~]# cat /etc/my.cnf
[mysqld]
slave-parallel-type = LOGICAL_CLOCK
slave_parallel_workers = 4
master_info_repository = TABLE
relay_log_info_repository = TABLE
slave_preserve_commit_order = ON
#重启服务查看
mysql> show variables like "slave_preserve_commit_order";
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| slave_preserve_commit_order | ON |
+-----------------------------+-------+
1 row in set (0.01 sec)
6.4 事务延迟提交参数
基于LOGICAL_CLOCK的同步有个不足点，就是当主节点的事务繁忙度较低的时候，导致时间段内组提交fsync刷盘的事务量较少，于是导致从库回放的并行度并不高，甚至可能一组里面只有一个事务，这样从节点的多线程就基本用不到，可以通过设置下面两个参数，让主节点延迟提交。
- binlog_group_commit_sync_delay
- 等待延迟提交的时间，binlog 提交后等待一段时间再
fsync。让每个 group 的事务更多，人为提高并行度。
- 等待延迟提交的时间，binlog 提交后等待一段时间再
- binlog_group_commit_sync_no_delay_count
- 待提交的最大事务数，如果等待时间没到，而事务数达到了，就立即
fsync。达到期望的并行度后立即提交，尽量缩小等待延迟。
- 待提交的最大事务数，如果等待时间没到，而事务数达到了，就立即
# 默认 0
mysql> show variables like 'binlog_group_commit_sync%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
+-----------------------------------------+-------+
2 rows in set (0.00 sec)
欢迎关注公众号：
一介IT
本站博文抢先发布在公众号。
扫一扫
409
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
