Mysql并行复制实践总结

mysql并行复制总结

实战篇

Mysql5.6 并行复制

一般Mysql主从复制有三个线程参与，都是单线程：Binlog Dump(主) -> IO Thread (从) -> SQL Thread(从)。

复制出现延迟一般出在两个地方:

• SQL线程忙不过来 (可能需要应用数据量较大，可能和从库本身的一些操作有锁和资源的冲突；主库可以并发写，SQL线程不可以；主要原因)
• 网络抖动导致IO线程复制延迟(次要原因)。

MySQL主从复制延迟的解决办法：MySQL从5.6开始有了SQL Thread多个的概念，可以并发还原数据，即并行复制技术。并行复制的机制，是MySQL的一个非常重要的特性，可以很好的解决MySQL主从延迟问题！

在MySQL 5.6中，设置参数slave_parallel_workers = 4(>1)，即可有4个SQL Thread（coordinator线程）来进行并行复制，其状态为：Waiting for an evant from Coordinator。但是其并行只是基于Schema的，也就是基于库的。如果数据库实例中存在多个Schema，这样设置对于Slave复制的速度可以有比较大的提升。通常情况下单库多表是更常见的一种情形，那基于库的并发就没有卵用。

其核心思想是：

不同schema下的表并发提交时的数据不会相互影响，即slave节点可以用对relay log中不同的schema各分配一个类似SQL功能的线程，来重放relay log中主库已经提交的事务，保持数据与主库一致。

MySQL 5.6版本支持所谓的并行复制，但是其并行只是基于schema的，也就是基于库的

如果用户的MySQL数据库实例中存在多个schema，对于从机复制的速度的确可以有比较大的帮助。但是基于schema的并行复制存在两个问题：

• crash safe功能不好做，因为可能之后执行的事务由于并行复制的关系先完成执行，那么当发生crash的时候，这部分的处理逻辑是比较复杂的。
• 最为关键的问题是这样设计的并行复制效果并不高，如果用户实例仅有一个库，那么就无法实现并行回放，甚至性能会比原来的单线程更差。而 单库多表是比多库多表更为常见的一种情形 。

注意：mysql 5.6的MTS是基于库级别的并行，当有多个数据库时，可以将slave_parallel_workers设置为数据库的数量，为了避免新建库后来回修改，也可以将该参数设置的大一些。设置为库级别的事务时，不允许这样做，会报错。

备库执行：

stop slave;
set global slave_parallel_workers = 4;
start slave;

主库上sysbench压一个库

5.6的binlog内容

Mysql5.7 并行复制

测试环境搭建，70主<----双向同步---->71备库

grant replication slave, replication client on *.* to repl@'127.0.0.%' identified by '333';

change master to 
master_host='127.0.0.1', 
master_user='repl', 
master_password='333', 
master_port=5470, 
MASTER_AUTO_POSITION=1;

/home/mingjie.gmj/bin/sysbench-1.0.16/bin/sysbench oltp_common --threads=64 --events=0 --mysql-socket=/home/mingjie.gmj/databases/data/mydata5470/mysql5470.sock --mysql-user=root --tables=10 --mysql-db=sbtest --table_size=1000 prepare
/home/mingjie.gmj/bin/sysbench-1.0.16/bin/sysbench oltp_read_write --threads=64 --events=0 --mysql-socket=/home/mingjie.gmj/databases/data/mydata5470/mysql5470.sock --mysql-user=root  --mysql-db=sbtest --tables=10 --table_size=1000 --time=600 --report-interval=1 run

一个组提交的事务都是可以并行回放，因为这些事务都已进入到事务的 Prepare 阶段，则说明事务之间没有任何冲突（否则就不可能提交）。

为了兼容 MySQL 5.6 基于库的并行复制，5.7 引入了新的变量 slave-parallel-type，其可以配置的值有：

• DATABASE：默认值，基于库的并行复制方式。
• LOGICAL_CLOCK：基于组提交的并行复制方式。

**其核心思想：**一个组提交的事务都是可以并行回放（配合binary log group commit）；slave机器的relay log中 last_committed相同的事务（sequence_num不同）可以并发执行。其中，变量slave-parallel-type可以有两个值：１）DATABASE 默认值，基于库的并行复制方式；２）LOGICAL_CLOCK，基于组提交的并行复制方式；

MySQL 5.7开启Enhanced Multi-Threaded Slave很简单，只需要在Slave从数据库的my.cnf文件中如下配置即可:

# slave

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=8     #一般建议设置4-8，太多的线程会增加线程之间的同步开销

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

主库也需要配置，MySQL5.7的并行复制，期望最大化还原主库的并行度，实现方式是在binlog event中增加必要的信息，以便slave节点根据这些信息实现并行复制。

MySQL5.7的并行复制建立在group commit的基础上，所有在主库上能够完成prepared的语句表示没有数据冲突，就可以在slave节点并行复制。所以在并行复制环境中，除了在Slace从数据库中配置之外，还需要在Master主数据库上的my.cnf文件中添加binlog_group_commit配置，否则从库无法做到基于事物的并行复制:

# master

binlog_group_commit_sync_delay = 100        

binlog_group_commit_sync_no_delay_count = 10

• binlog_group_commit_sync_delay，这个参数控制着日志在刷盘前日志提交要等待的时间，默认是0也就是说提交后立即刷盘，但是并不代表是关闭了组提交，当设置为0以上的时候，就允许多个事物的日志同时间一起提交刷盘，也就是我们说的组提交。组提交是并行复制的基础，我们设置这个值的大于0就代表打开了组提交的延迟功能，而组提交是默认开启的。最大值只能设置为1000000微妙。
• binlog_group_commit_sync_no_delay_count，这个参数表示我们在binlog_group_commit_sync_delay等待时间内，如果事物数达到这个参数的设定值，就会触动一次组提交，如果这个值设为0的话就不会有任何的影响。如果到达时间但是事物数并没有达到的话，也是会进行一次组提交操作的。

MySQL 5.7并行复制的思想简单易懂，一言以蔽之： 一个组提交的事务都是可以并行回放 ，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）。为了兼容MySQL 5.6基于库的并行复制，5.7引入了新的变量slave-parallel-type，其可以配置的值有：

• DATABASE：默认值，基于库的并行复制方式
• LOGICAL_CLOCK：基于组提交的并行复制方式

组提交下BINLOG的区别

show global variables like '%group_commit%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 0     |
| binlog_group_commit_sync_no_delay_count | 0     |
+-----------------------------------------+-------+

测试发现binlog_group_commit_sync_delay参数为0也会有组提交

mysqlbinlog mysql-bin.000077 | grep last_committed

并行复制测试

主库

binlog_group_commit_sync_delay = 100        

binlog_group_commit_sync_no_delay_count = 10

# 或者不配置也可以有分组提交

备库

# slave

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=8     #一般建议设置4-8，太多的线程会增加线程之间的同步开销

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

sysbench

[ 141s ] thds: 64 tps: 744.00 qps: 15858.06

[ 142s ] thds: 64 tps: 719.00 qps: 15138.03

[ 143s ] thds: 64 tps: 722.99 qps: 15201.86

[ 144s ] thds: 64 tps: 760.00 qps: 16014.07

[ 145s ] thds: 64 tps: 622.97 qps: 13209.31

[ 146s ] thds: 64 tps: 785.04 qps: 16453.84

[ 147s ] thds: 64 tps: 795.00 qps: 16679.90

[ 148s ] thds: 64 tps: 905.01 qps: 19056.13

[ 149s ] thds: 64 tps: 739.01 qps: 15643.13

打开并行复制后，并行SQL线程并发工作，备库无延迟（关闭并行复制延迟高）

理论篇

请参考这里

MySQL 5.7并行复制引入了两个值last_committed和sequence_number。

last_committed表示事务提交的时候，上次事务提交的编号，在主库上同时提交的事务设置成相同的last_committed。

如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放。这个机制也是Commit-Parent-Based SchemeWL#6314中的实现方式。不过之后，官方对这种模式做了改进，所以最新的并行回放机制和WL#6314有了不同，详情见Lock-Based SchemeWL#7165。

下面介绍一下旧模式Commit-Parent-Based SchemeWL#6314和新模式Lock-Based SchemeWL#7165的不同之处，以及改进的地方。

Commit-Parent-Based Scheme WL#6314

Commit-Parent-Based Scheme简介

• 在master上，有一个全局计数器（global counter）。在每一次存储引擎提交之前，计数器值就会增加。
• 在master上，在事务进入prepare阶段之前，全局计数器的当前值会被储存在事务中。这个值称为此事务的commit-parent。
• 在master上，commit-parent会在事务的开头被储存在binlog中。
• 在slave上，如果两个事务有同一个commit-parent，他们就可以并行被执行。

此commit-parent就是我们在binlog中看到的last_committed。如果commit-parent相同，即last_committed相同，则被视为同一组，可以并行回放。

Commit-Parent-Based Scheme的问题

一句话：Commit-Parent-Based Scheme会降低复制的并行程度。

• 水平虚线表示事务按时间顺序往后走。
• P表示事务在进入prepare阶段之前读到的commit-parent值的那个时间点。可以简单的视为加锁时间点。
• C表示事务增加了全局计数器（global counter）的值的那个时间点。可以简单的视为释放锁的时间点
• P对应的commit-parent（last_commited）是取自所有已经执行完的事务的最大的C对应的sequence_number。举例来说：
  • Trx4的P对应的commit-parent（last_commited）取自所有已经执行完的事务的最大的C对应的sequence_number，也就是Trx1的C对应的sequence_number。因为这个时候Trx1已经执行完，但是Trx2还未执行完。
  • Trx5的P对应的commit-parent（last_commited）取自所有已经执行完的事务的最大的C对应的sequence_number，也就是Trx2的C对应的sequence_number；Trx6的P对应的commit-parent（last_commited）取自所有已经执行完的事务的最大的C对应的sequence_number，也就是Trx2的C对应的sequence_number。所以Trx5和Trx6具有相同的commit-parent（last_commited），在进行回放的时候，Trx5和Trx6可以并行回放。

由图可见，Trx5 和 Trx6可以并发执行，因为他们的commit-parent是相同的，都是由Trx2设定的。但是，Trx4和Trx5不能并发执行, Trx6和Trx7也不能并发执行。

我们可以注意到，在同一时段，Trx4和Trx5、Trx6和Trx7分别持有他们各自的锁，事务互不冲突。所以，如果在slave上并发执行，也是不会有问题的。

根据以上例子，可以得知：

• Trx4、Trx5和Trx6在同一时间持有各自的锁，但Trx4无法并发执行。
• Trx6和Trx7在同一时间持有各自的锁，但Trx7无法并发执行。

但是，实际上，Trx4是可以和Trx5、Trx6并行执行，Trx6可以和Trx7并行执行。

如果能实现这个，那么并行复制的效果会更好。所以官方对并行复制的机制做了改进，提出了一种新的并行复制的方式：Lock-Based Scheme。