ITPub博客

首页 > 数据库 > MySQL > MySQL行级别并行复制能并行应用多少个binlog group?

MySQL行级别并行复制能并行应用多少个binlog group?

原创 MySQL 作者:沃趣科技 时间:2019-09-09 15:31:20 0 删除 编辑
前两天,无意间看到了林晓斌(丁奇)的一篇文章(详见文末参考链接的第一个),突发好奇心,想从MySQL暴露出来的一些信息上着手,看看能不能直观地看到MySQL 5.7的并行复制到底可以同时执行多少个binlog group(last_committed)的事务,经过一番折腾,将验证过程整理成这篇文章分享给大家。 测试验证过程中,使用了两个MySQL版本,如下:
  • MySQL 5.7.22:sysbench加压,然后在从库中查询information_schema.innodb_trx、performance_schema.events_transactions_current、performa nce_schema.replication_applier_status_by_worker 三张表中的数据,反复操作,发现并没有可靠数据能满足要求。
  • MySQL 8.0.17: sysbench加压,然后在从库中查询performance_schema.replication_applier_status_by_worker 表,发现该表相比之前的版本新增了一个字段看起来比较可靠(这里先卖个关子,具体哪个字段,请大家从下文中寻找吧)。

一、环 境

数据库版本: MySQL 5.7.22、MySQL 8.0.17

  • 复制拓扑: 一主两从

数据库关键参数:

  • 主库:
    binlog_transaction_dependency_tracking=COMMIT_ORDER

  • 主从库: 双一、binlog_order_commits=ON、slave_preserve_commit_order=ON

  • 从库: 双TABLE,slave_parallel_workers=32,slave_parallel_type=LOGICAL_CLOCK

sysbench版本: sysbench 1.0.9

  • 对主库使用32线程加oltp压力

  • 测试基础数据量: 8张500W的表

操作系统版本: CentOS Linux release 7.2.1511 (Core)
文件系统: xfs
IO调度策略: deadline
PS: 以下测试数据均为MySQL 8.0.17,由于MySQL 5.7.22的测试数据并不是那么可靠(本人观点),所以本文中并未列出。


二、预 热

MySQL行级别的并行复制,是在MySQL 5.7及其之后的版本中引入的,在MySQL 5.6中只支持库级别的并行复制,在MySQL 5.5及其更早的版本中只支持单线程复制。
关于MySQL行级别的并行复制原理,我们也许经常会听到如下说法:

  • 简单点的: 从库中的协调器线程根据Gtid_log_event和Anonymous_gtid_log_event中记录的last_committed值做并行分发,相同的last_committed值的事务就可以并行应用。
  • 复杂点的: 主库在做并行事务提交时,处于prepare状态的事务都可以在从库并行应用,因为他们不存在锁冲突。
    * 主库侧: 在提交事务时,对每个事务定义其lock interval,并记录到binlog中。使用last_committed表示事务lock interval的起始点,它是在事务进入prepare阶段之后,进入binlog flush队列之前获取的当时commit队列(这里指的是事务提交的flush、sync、commit三个队列中的commit队列)中所有事务的最大sequence_number(为什么要从commit队列里获取,而不是从已提交完成的事务中获取呢?因为提交完成的事务锁已经释放,无法判断有没有锁冲突);使用sequence_number表示事务lock interval的结束点,它是在事务进入binlog flush队列之后从当时正在使用的binlog file的事务计数器中获取值(每个binlog file都会将计数器重置)。
    * 从库侧: 协调器线程根据last_committed做分发,只要last_committed和sequence_number组成的lock interval锁范围重叠的事务,就可以并行应用(可以分发)。 具体实现上,在MySQL内部定义了一个last_lwm_timestamp变量,用来记录所有回放事务中已经提交完成事务的timestamp(sequence_number)的low-water-mark。 low-water-mark对应的事务已经提交,且该事务之前的事务(seqno小于low-water-mark的事务)都已经提交。 当协调器线程读取到一个事务的last_committed值时,判断该事务的last_committed值是否小于等于low-water-mark值,如果是,则协调器线程就可以将其分发给worker线程(具体是否分发还需要看是否有worker线程处于空闲状态,如果没有worker线程处于空闲,协调器线程需要等待空闲的worker线程),否则,协调器线程不能将该事务分发给worker线程,需要进行等待。

    * 从库侧并行分发策略的逻辑图解: 假设下图中的L代表lock interval的开始,C代表lock interval的结束。

下面,我们一起来看看,除了撸源码总结并行复制原理之外,怎么通过MySQL暴露出来的一些信息来查看并行复制的踪迹。

三、揭 底

主库停止任何写压力,并确定从库无延迟。
root@localhost : (none):28: > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.30.162
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000029
          Read_Master_Log_Pos: 501683714
               Relay_Log_File: mysql-relay-bin.000086
                Relay_Log_Pos: 497690559
        Relay_Master_Log_File: mysql-bin.000029
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......
          Exec_Master_Log_Pos: 501683714
              Relay_Log_Space: 501684223
......
        Seconds_Behind_Master: 0
......

为减少后续的日志解析量,在主库与从库都各自执行binlog日志切换。

# 主库
mysql > flush binary logs;
Query OK, 0 rows affected (0.02 sec)
# 从库
mysql > flush binary logs;
Query OK, 0 rows affected (0.02 sec)

对主库sysbench加并发压力(这里为oltp事务模型)。

[root@physical-machine ~]# sysbench --db-driver=mysql --time=99999 --threads=32 --report-interval=1 --mysql-host=10.10.30.162 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=sbtest --tables=2 --table-size=5000000 oltp_read_write --db-ps-mode=disable --mysql-ignore-errors=1062 run

在从库中反复执行如下语句,直到抓取到较多的数据行为止(任选其中一个从库即可)。

ysql > select THREAD_ID,SERVICE_STATE,LAST_APPLIED_TRANSACTION,APPLYING_TRANSACTION,APPLYING_TRANSACTION_START_APPLY_TIMESTAMP from performance_schema.replication_applier_status_by_worker where APPLYING_TRANSACTION!='' order by APPLYING_TRANSACTION;select * from sys.innodb_lock_waits\G
+-----------+---------------+----------------------------------------------+----------------------------------------------+--------------------------------------------+
| THREAD_ID | SERVICE_STATE | LAST_APPLIED_TRANSACTION | APPLYING_TRANSACTION | APPLYING_TRANSACTION_START_APPLY_TIMESTAMP |
+-----------+---------------+----------------------------------------------+----------------------------------------------+--------------------------------------------+
| 94 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536428 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536438 | 2019-08-27 09:53:16.941561 |
| 95 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536429 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536439 | 2019-08-27 09:53:16.941590 |
| 96 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536430 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536440 | 2019-08-27 09:53:16.941654 |
| 97 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536431 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536441 | 2019-08-27 09:53:16.942482 |
| 98 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536432 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536442 | 2019-08-27 09:53:16.942137 |
| 99 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536433 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536443 | 2019-08-27 09:53:16.941958 |
| 100 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536434 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536444 | 2019-08-27 09:53:16.941935 |
| 101 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536435 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536445 | 2019-08-27 09:53:16.945607 |
| 102 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536436 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536446 | 2019-08-27 09:53:16.945996 |
| 103 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536423 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536447 | 2019-08-27 09:53:16.946397 |
| 104 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536424 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536448 | 2019-08-27 09:53:16.946338 |
| 105 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536425 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536449 | 2019-08-27 09:53:16.946429 |
| 106 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536426 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536450 | 2019-08-27 09:53:16.946892 |
| 107 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536396 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536451 | 2019-08-27 09:53:16.947253 |
| 108 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536397 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536452 | 2019-08-27 09:53:16.947248 |
| 109 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536398 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536453 | 2019-08-27 09:53:16.947896 |
| 110 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536399 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536454 | 2019-08-27 09:53:16.948349 |
| 111 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536400 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536455 | 2019-08-27 09:53:16.947968 |
| 112 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536401 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536456 | 2019-08-27 09:53:16.948032 |
| 113 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536402 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536457 | 2019-08-27 09:53:16.948247 |
| 114 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536403 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536458 | 2019-08-27 09:53:16.948836 |
| 115 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536404 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536459 | 2019-08-27 09:53:16.948152 |
| 116 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536408 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536460 | 2019-08-27 09:53:16.948203 |
| 117 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536409 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536461 | 2019-08-27 09:53:16.948407 |
| 118 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536375 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536462 | 2019-08-27 09:53:16.948316 |
| 119 | ON | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536376 | 364efb4f-c7e8-11e9-8f23-5254002a54f2:1536463 | 2019-08-27 09:53:16.948345 |
+-----------+---------------+----------------------------------------------+----------------------------------------------+--------------------------------------------+
26 rows in set (0.00 sec)
Empty set (0.00 sec)

上述文本中,相比MySQL 5.7.22版本,我们使用了performance_schema.replication_applier_status_by_worker表中新增的3个字段(当然新增的字段还有很多时间戳的,这里并未使用)。

  • LAST_APPLIED_TRANSACTION: 此worker线程已完成应用的最后一个事务的GTID。

  • APPLYING_TRANSACTION: 此worker线程当前正在应用的事务的GTID。
  • APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 此worker线程第一次尝试应用当前事务的时间戳。
从上述文本中,我们可以看到一共抓取到26个事务正在并行,GTID SET为364efb4f-c7e8-11e9-8f23-5254002a54f2:1536438-1536463,使用这个GTID SET,解析主库的最后一个binlog file去瞧瞧这些事务对应的last_committed值。
  • 上述文本太长可能导致阅读不便,下面给出一张截图。

停止对主库加压的sysbench进程,并登陆到主库服务器中,解析最后一个binlog。

[root@node1 ~]# cd /data/mysqldata1/binlog/
[root@node1 binlog]# ll
total 10530608
......
-rw-r----- 1 mysql mysql 536879186 Aug 27 09:52 mysql-bin.000035
-rw-r----- 1 mysql mysql 91067601 Aug 27 09:53 mysql-bin.000036
-rw-r----- 1 mysql mysql 1092 Aug 26 10:55 mysql-bin.index
[root@node1 binlog]# mysqlbinlog mysql-bin.000036 |grep -Ei 'last_committed|gtid_next' > a.sql

在主库中打开a.sql文件,并找到在从库中获取的GTID SET对应的日志记录(364efb4f-c7e8-11e9-8f23-5254002a54f2:1536438-1536463),从中找出对应事务的last_committed值。

[root@node1 binlog]# vim a.sql
......
#190827 9:53:16 server id 33061 end_log_pos 75854707 CRC32 0x1aeb8b8e GTID last_committed=35729 sequence_number=35755 rbr_only=yes original_committed_timestamp=1566870796937319 immediate_commit_timestamp=1566870796937319 transaction_length=1103
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536438'/*!*/;
# 为简化内容,以下将本案例中无用的类似"rbr_only=yes original_committed_timestamp=1566870796937319 immediate_commit_timestamp=1566870796937319 transaction_length=1103"信息删除
#190827 9:53:16 server id 33061 end_log_pos 75855810 CRC32 0xd6c147e8 GTID last_committed=35729 sequence_number=35756 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536439'/*!*/;
#190827 9:53:16 server id 33061 end_log_pos 75857586 CRC32 0xae2abece GTID last_committed=35729 sequence_number=35757 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536440'/*!*/;
#190827 9:53:16 server id 33061 end_log_pos 75859908 CRC32 0x2c865187 GTID last_committed=35729 sequence_number=35758 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536441'/*!*/;
#190827 9:53:16 server id 33061 end_log_pos 75861566 CRC32 0xc902562d GTID last_committed=35743 sequence_number=35759 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536442'/*!*/;
#190827 9:53:16 server id 33061 end_log_pos 75863342 CRC32 0xd424cb7a GTID last_committed=35743 sequence_number=35760 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536443'/*!*/;
#190827 9:53:16 server id 33061 end_log_pos 75865000 CRC32 0xd02babfd GTID last_committed=35743 sequence_number=35761 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536444'/*!*/;
#190827 9:53:16 server id 33061 end_log_pos 75867322 CRC32 0x4238d788 GTID last_committed=35743 sequence_number=35762 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536445'/*!*/;
#190827 9:53:16 server id 33061 end_log_pos 75868980 CRC32 0x251ff97f GTID last_committed=35743 sequence_number=35763 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536446'/*!*/;
#190827 9:53:16 server id 33061 end_log_pos 75871302 CRC32 0xd2d680c6 GTID last_committed=35743 sequence_number=35764 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536447'/*!*/;
#190827 9:53:16 server id 33061 end_log_pos 75873078 CRC32 0x603e6480 GTID last_committed=35743 sequence_number=35765 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536448'/*!*/;     
#190827 9:53:16 server id 33061 end_log_pos 75875400 CRC32 0x5bc4e556 GTID last_committed=35743 sequence_number=35766 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536449'/*!*/;    
#190827 9:53:16 server id 33061 end_log_pos 75877722 CRC32 0x0d21cd79 GTID last_committed=35753 sequence_number=35767 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536450'/*!*/;     
#190827 9:53:16 server id 33061 end_log_pos 75879694 CRC32 0x8c8f8092 GTID last_committed=35743 sequence_number=35768 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536451'/*!*/;    
#190827 9:53:16 server id 33061 end_log_pos 75882016 CRC32 0xb99f0971 GTID last_committed=35753 sequence_number=35769 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536452'/*!*/;    
#190827 9:53:16 server id 33061 end_log_pos 75883674 CRC32 0x695fc9da GTID last_committed=35753 sequence_number=35770 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536453'/*!*/;     
#190827 9:53:16 server id 33061 end_log_pos 75885996 CRC32 0x942a4ed5 GTID last_committed=35753 sequence_number=35771 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536454'/*!*/;    
#190827 9:53:16 server id 33061 end_log_pos 75887772 CRC32 0xc63fbf2b GTID last_committed=35753 sequence_number=35772 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536455'/*!*/;
#190827 9:53:16 server id 33061 end_log_pos 75889548 CRC32 0x685ba5c3 GTID last_committed=35753 sequence_number=35773 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536456'/*!*/;   
#190827 9:53:16 server id 33061 end_log_pos 75891870 CRC32 0xf1a9cd88 GTID last_committed=35753 sequence_number=35774 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536457'/*!*/;       
#190827 9:53:16 server id 33061 end_log_pos 75894192 CRC32 0xaf9c1dc5 GTID last_committed=35753 sequence_number=35775 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536458'/*!*/;     
#190827 9:53:16 server id 33061 end_log_pos 75896164 CRC32 0x5bd7a2b8 GTID last_committed=35753 sequence_number=35776 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536459'/*!*/;    
#190827 9:53:16 server id 33061 end_log_pos 75898486 CRC32 0x296ace5e GTID last_committed=35753 sequence_number=35777 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536460'/*!*/; 
#190827 9:53:16 server id 33061 end_log_pos 75900808 CRC32 0xe8917bed GTID last_committed=35753 sequence_number=35778 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536461'/*!*/; 
#190827 9:53:16 server id 33061 end_log_pos 75902584 CRC32 0x8b8c5080 GTID last_committed=35753 sequence_number=35779 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536462'/*!*/;
#190827 9:53:16 server id 33061 end_log_pos 75904906 CRC32 0x4e1c0187 GTID last_committed=35753 sequence_number=35780 ......
SET @@SESSION.GTID_NEXT= '364efb4f-c7e8-11e9-8f23-5254002a54f2:1536463'/*!*/;

上述文本太长可能导致阅读不便,下面给出两张截图(留意图中的红色方框标记处)。

从上述内容中,我们可以发现,在从库中获取到的并行应用事务的GTID SET,对应了3个last_committed值,分别为last_committed=35729、last_committed=35743、last_committed=35753。 至此,验证过程结束(注意: 这里的验证结果只能说明并行应用的binlog group可以达到3个,并不是说一定是3个)。
以后,与别人谈论MySQL 5.7及其之后版本的并行复制原理的时候,可别告诉他只有相同的last_committed值的事务才允许并行执行,只告诉他是根据last_committed进行分发的就够了。
PS: 最后,感谢重庆八怪对本文提供支持(关于多线程复制原理的简要源码解读,可参考文末第4个链接)。
参考链接:
https://zhuanlan.zhihu.com/p/49548095
https://dev.mysql.com/doc/refman/8.0/en/replication-applier-status-by-coordinator-table.html
https://dev.mysql.com/doc/refman/8.0/en/replication-applier-status-by-worker-table.html
https://www.jianshu.com/p/8706d7422d89
http://mysql.taobao.org/monthly/2017/12/03/

| 作者简介

罗小波·沃趣科技高级数据库技术专家

IT从业多年,主要负责MySQL 产品的数据库支撑与售后二线支撑。曾参与版本发布系统、轻量级监控系统、运维管理平台、数据库管理平台的设计与编写,熟悉MySQL体系结构,Innodb存储引擎,喜好专研开源技术,多次在公开场合做过线下线上数据库专题分享,发表过多篇数据库相关的研究文章。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28218939/viewspace-2656476/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
杭州沃趣科技股份有限公司创建于2012年(股票代码:839849),是一家专注为企业用户提供基于高性能、高可用、可扩展的开放数据库云平台解决方案的国产厂商。公司创始团队为原阿里巴巴数据库技术团队核心骨干,凭借丰富的研发及运维经验,为行业客户提供数据库云产品及软硬件一体化解决方案。

注册时间:2016-07-18

  • 博文量
    256
  • 访问量
    724868