[MySQL]组提交和多线程复制

原创 Vic 2021-08-04

2613

组提交

简介

MySQL innodb为了保证事务的一致性和持久性，使用WAL机制。WAL先写日志，为了解决日志频繁从日志缓冲区到日志落盘的性能瓶颈问题，组提交(group commit)的优化机制出现。
当开启binlog时，为了保证redolog和binlog的数据一致性，MySQL采用了两阶段提交。所以到MySQL 5.6版本时已同时支持redo log组提交和binlog组提交，最大化每次日志刷盘的收益，提供性能。

参数

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

如上，5.7版本组提交主要有这两个参数。

binlog_group_commit_sync_delay这个参数表示binlog刷盘前，binlog提交需要等待的微秒数。默认为0表示提交后立即刷盘，设置为非0值表示允许多个事务的日志一起提交刷盘，即组提交。此参数为0并不代表组提交被关闭。组提交是默认开启的。此参数的最大值为1s，即1000000微秒。

binlog_group_commit_sync_no_delay_count：这个参数表示在binlog_group_commit_sync_delay等待时间内，如果事务数达到此参数指定的值，则会触发一次组提交。如果达到延迟时间但并未达到此参数设置的事务数限制的话，也会进行一次组提交。

Mark： binlog_group_commit_sync_delay需谨慎调优，尤其是在高并发且对SQL延迟很敏感的环境。

单线程复制主从环境

在一套普通单线程复制的主从环境做了测试，通过TPCC工具进行压测，压测完毕后分析MySQL的binlog。

可以看得到组提交是怎么回事。

主库压测

$ ./tpcc_start -h 127.0.0.1 -P 3307 -d tpcc -u root -p mypwd -w 1 -c 2 -r 20 -l 60

通过mysqlbinlog解析binlog

$ /db/mysql/app/5.7.21/bin/mysqlbinlog mysql-bin.000001 |grep last_commit

可以看到存在两个事物的last_committed是相同的，这意味什么呢，意味着两个事物是作为一个组提交的。

主库上binlog_group_commit_sync_delay的参数也是未调整的即为0。这说明组提交是默认开启的。

备库binlog

备库未开启多线程复制，但开启了slave_log_updates参数，来观察下备库的binlog是否存在组提交动作。

可以看到，对比主库上相同的事务，在备库是没有进行组提交的。

多线程复制(MTS)

简介

Multi-Threaded Slaves, 简称MTS。基于binlog组提交技术（MySQL5.7默认开启binlog组提交）。
一个组提交的事务都是可以并行回放，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）。
Slave端SQL线程分裂为coordinator线程和worker线程，worker线程对组提交的事务进行并行回放

MySQL 5.6和5.7的MTS对比

基于Schema

5.6也支持所谓的并行复制，但是其并行只是基于schema的，也就是基于库的。如果用户的MySQL数据库实例中存在多个schema，对于从机复制的速度的确可以有比较大的帮助。

最为关键的问题是这样设计的并行复制效果并不高，如果用户实例仅有一个库，那么就无法实现并行回放，甚至性能会比原来的单线程更差。而 单库多表是比多库多表更为常见的一种情形 。

基于组提交

5.7的并行复制基于组提交，上一章节已简单介绍过组提交。

slave服务器的回放与主机是一致的即master服务器上是怎么并行执行的slave上就怎样进行并行回放。即：一个组提交的事务都是可以并行回放

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

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

支持并行复制的GTID

如何知道事务是否在一组中，又是一个问题，因为原版的MySQL并没有提供这样的信息。在MySQL 5.7版本中，其设计方式是将组提交的信息存放在GTID中。那么如果用户没有开启GTID功能，即将参数gtid_mode设置为OFF呢？故MySQL 5.7又引入了称之为Anonymous_Gtid的二进制日志event类型，如：

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 | 219 | Query          |         1 |         313 | create database test                  |
| mysql-bin.000002 | 313 | Anonymous_Gtid |         1 |         378 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql-bin.000002 | 378 | Query          |         1 |         475 | use `test`; create table t1(c1 int)   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

这意味着在 MySQL 5.7版本中即使不开启GTID，每个事务开始前也是会存在一个Anonymous_Gtid ，而这GTID中就存在着组提交的信息。

开启MTS

通过上面章节的SHOW BINLOG EVENTS，虽然看到了Anonymous_Gtid，但我们并没有发现有关组提交的任何信息。

这里将从库设置为多线程复制模式

mysql> stop slave;
mysql> set global slave_parallel_type=LOGICAL_CLOCK;
mysql> set global slave_parallel_workers=2;           # 并行复制工作线程数

重新进行压测

$ ./tpcc_start -h 127.0.0.1 -P 3307 -d tpcc -u root -p mypwd -w 1 -c 2 -r 10 -l 20

主库binlog

可以看到同样发生的binlog组提交
备库binlog

可以看到备库由于开启了MTS，在进行复制事务回放时也进行了组提交动作

last_committed表示事务提交的时候，上次事务提交的编号，如果事务具有相同的last_committed，表示这些事务都在一组内，可以进行并行的回放。

LOGICAL_CLOCK

上述的last_committed和sequence_number代表的就是所谓的LOGICAL_CLOCK。

源码：

class Logical_clock
{
  private:
  int64 state;
  /*
  Offset is subtracted from the actual "absolute time" value at
  logging a replication event. That is the event holds logical
  timestamps in the "relative" format. They are meaningful only in
  the context of the current binlog.
  The member is updated (incremented) per binary log rotation.
  */
  int64 offset;
  ......

state是一个自增的值，offset在每次二进制日志发生rotate时更新，记录发生rotate时的state值。其实state和offset记录的是全局的计数值，而存在二进制日志中的仅是当前文件的相对值。

使用LOGICAL_CLOCK的场景如下：

class MYSQL_BIN_LOG: public TC_LOG
{
  ...
  public:
  /* Committed transactions timestamp */
  Logical_clock max_committed_transaction;
  /* "Prepared" transactions timestamp */
  Logical_clock transaction_counter;
  ...

max_committed_transaction

记录上次组提交时的logical_clock，代表上述mysqlbinlog中的last_committed
transaction_counter

记录当前组提交中各事务的logcial_clock，代表上述mysqlbinlog中的sequence_number

并行复制测试

测试的工具是sysbench的单表全update测试，测试结果显示在16个线程下的性能最好，从机的QPS可以达到25000以上，进一步增加并行执行的线程至32并没有带来更高的提升。而原单线程回放的QPS仅在4000左右。

MTS调优

master_info_repository

开启MTS功能后，务必将参数master_info_repostitory设置为TABLE，这样性能可以有50%~80%的提升。这是因为并行复制开启后对于元master.info这个文件的更新将会大幅提升，资源的竞争也会变大。在之前 InnoSQL 的版本中，添加了参数来控制刷新master.info这个文件的频率，甚至可以不刷新这个文件。因为刷新这个文件是没有必要的，即根据master-info.log这个文件恢复本身就是不可靠的。

slave_parallel_workers

若将slave_parallel_workers设置为0，则MySQL 5.7退化为原单线程复制，但将slave_parallel_workers设置为1，则SQL线程功能转化为coordinator线程，但是只有1个worker线程进行回放，也是单线程复制。然而，这两种性能却又有一些的区别，因为多了一次coordinator线程的转发，因此slave_parallel_workers=1的性能反而比0还要差。

这里其中引入了另一个问题，如果主机上的负载不大，那么组提交的效率就不高，很有可能发生每组提交的事务数量仅有1个，那么在从机的回放时，虽然开启了并行复制，但会出现性能反而比原先的单线程还要差的现象，即延迟反而增大了

slave_preserve_commit_order

mysql 5.7 后的MTS可以实现更小粒度的并行复制，但需要将slave_parallel_type设置为LOGICAL_CLOCK,但仅仅设置为LOGICAL_CLOCK也会存在问题，因为此时在slave上应用事务的顺序是无序的，和relay log中记录的事务顺序不一样，这样数据一致性是无法保证的。为了保证事务是按照relay log中记录的顺序来回放，就需要开启参数slave_preserve_commit_order。

Enhanced Multi-Threaded Slave配置

# slave
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
master_info_repository='TABLE'
relay_log_info_repository='TABLE'
relay_log_recovery=ON
slave_preserve_commit_order=ON

并行复制监控

复制的监控依旧可以通过SHOW SLAVE STATUS\G，但是MySQL 5.7在performance_schema架构下多了这些表，用户可以更细力度的进行监控：

mysql> use performance_schema;
Database changed
mysql> show tables like 'replication%';
+---------------------------------------------+
| Tables_in_performance_schema (replication%) |
+---------------------------------------------+
| replication_applier_configuration           |
| replication_applier_status                  |
| replication_applier_status_by_coordinator   |
| replication_applier_status_by_worker        |
| replication_connection_configuration        |
| replication_connection_status               |
| replication_group_member_stats              |
| replication_group_members                   |
+---------------------------------------------+

work线程的工作情况：

mysql> select * from replication_applier_status_by_worker;
+--------------+-----------+-----------+---------------+-----------------------+-------------------+--------------------+----------------------+
| CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE | LAST_ERROR_TIMESTAMP |
+--------------+-----------+-----------+---------------+-----------------------+-------------------+--------------------+----------------------+
|              |         1 |        36 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
|              |         2 |        37 | ON            |                       |                 0 |                    | 0000-00-00 00:00:00  |
+--------------+-----------+-----------+---------------+-----------------------+-------------------+--------------------+----------------------+

参考文章：

https://www.tuicool.com/articles/m2Unmeq

https://bugs.mysql.com/bug.php?id=89247

mysql mysql主从复制

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者