binlog实现并发复制过程slave线程数

- 日理万妓 2022-09-24 09:26 180阅读 0赞

首先梳理下**传统MySQL/MariaDB**主备复制基本原理：

主从复制通过三个线程来完成，在master节点运行的binlog dump的线程，I/O线程和SQL线程运行在slave 节点

*          master节点的Binlog dump线程，当slave节点与master正常连接的时候，master把更新的binlog 内容推送到slave节点。
 *          slave节点的I/O 线程 ，该线程通过读取master节点binlog日志名称以及偏移量信息将其拷贝到本地relay log日志文件。
 *          slave节点的SQL线程，该线程读取relay log日志信息，将在master节点上提交的事务在本地回放，达到与主库数据保持一致的目的。

问题1：

Master节点的数据库实例并发跑多个线程同时提交事务，提交的事务按照逻辑的时间（数据库LSN号）顺序地写入binary log日志，,slave节点通过I/O线程写到本地的relay log日志，但是slave节点只有**SQL单线程**来执行relay log中的日志信息重放主库提交得事务，造成主备数据库存在延迟（lag）

思考1：

那么为了减少主备数据同步延迟时间，由于备库只有单线程补偿数据的原因而造成延迟，那么能否使slave节点同时运行多个如SQL线程一样的功能来重放在主库执行的事务？答案当然是：可以！但是我们需要解决以下问题：

1、slave本地的relay log记录的是master 的binary log日志信息，日志记录的信息按照事务的时间先后顺序记录，那么**为了保证主备数据一致性，slave节点必须按照同样的顺序执行，如果顺序不一致容易造成主备库数据不一致的风险**。

如：

在master节点提交T1和T2事务按照以下顺序

1.  State0: x= 1, y= 1

2.  T1: \{ x:= Read(y);

3.          x:= x+1;

4.          Write(x);

5.          Commit; \}

6.    
State1: x= 2, y= 1

7.  T2: \{ y:= Read(x);

8.            y:=y+1;

9.           Write(y);

10.          Commit; \}

11.   
State2: x= 2, y= 3

slave节点执行T1和T2相反的顺序：

1.  State0: x= 1, y= 1

2.  T2: \{ y:= Read(x);

3.            y:= y+1;

4.            Write(y);

5.            Commit; \}

6.    
State1: x= 1, y= 2

7.  T1: \{ x:= Read(y);

8.            x:=x+1;

9.            Write(x);

10.           Commit; \}

11.   
State2: x= 3, y= 2

MySQL 5.6改进：

MySQL 5.6版本引入并发复制（schema级别），基于schema级别的并发复制核心思想：“**不同schema下的表并发提交时的数据不会相互影响，即slave节点可以用对relay log中不同的schema各分配一个类似SQL功能的线程，来重放relay log中主库已经提交的事务，保持数据与主库一致**”。可见MySQL5.6版本的并发复制，**一个schema分配一个类似SQL线程**的功能。

实现1：

slave节点开启并发复制（slave\_parallel\_workers＝3）如下图，当前的slave的SQL线程为Coordinator（协调器），执行relay log日志的线程为worker(当前的SQL线程不仅起到协调器的作用，同时也可以重放relay log中主库提交的事务)

1.  +-----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+

3.  +-----+-------------+-----------+------+---------+-------+--------------------------------------------------------+------------------+

问题2:

MySQL 5.6基于schema级别的并发复制能够解决当业务数据的表放在不同的database库下，但是实际生产中往往大多数或者全部的业务数据表都放在同一个schema下，在这种场景即使slave\_parallel\_workers>0设置也无法并发执行relay log中记录的主库提交数据。 高并发的情况下，由于slave无法并发执行同个schema下的业务数据表，依然会造成主备延迟的情况。

思考2：

那么如果slave同时可以用多线程的方式，同时执行一个schema下的所有业务数据表，将能大大提高slave节点执行ralay log中记录的主库提交事务达到与主库数据同步的目的，实现该功能我们需要解决什么问题？

*  1、前面提到过为了保证主库数据一致性，master节点写入的binary log日志按照数据库逻辑时间先后的顺序并且slave节点执行relay log中**主库提交的事务必须按照一致的顺序**否则会造成主备数据不一致的情况。
 *  2、既然要实现scehma下所有的业务数据表能够并发执行，那么slave必须得知道**并发执行relay log中主库提交的事务不能相互影响**而且结果必须和主库保持一致。

实现2:

**MySQL 5.7** 引入Enhanced Muti-threaded slaves,当slave配置slave\_parallel\_workers>0并且global.slave\_parallel\_type＝‘LOGICAL\_CLOCK’,**可支持一个schema下，slave\_parallel\_workers个的worker线程并发执行relay log中主库提交的事务**。但是要实现以上功能，**需要在master机器标记binary log中的提交的事务哪些是可以并发执行**，虽然MySQL 5.6已经引入了binary log group commit，但是没有将可以并发执行的事务标记出来。

我们用命令 mysqlbinlog -vvv mysqlbinlog.0000003 | grep -i last\_committed    **在MySQL 5.7的master机器上**可以看到last\_committed 和sequence\_number

1.  \#151223 15:11:28 server id 15102  end\_log\_pos 14623 CRC32 0x767a33fa GTID      last\_committed=18         sequence\_number=26

3.  \#151223 15:11:28 server id 15102  end\_log\_pos 15199 CRC32 0x7dd1bf05 GTID     last\_committed=26         sequence\_number=27

5.  \#151223 15:11:28 server id 15102  end\_log\_pos 15773 CRC32 0xb01dc76e GTID     last\_committed=26         sequence\_number=28

7.  \#151223 15:11:28 server id 15102  end\_log\_pos 16347 CRC32 0x7a8e0ee8 GTID     last\_committed=26         sequence\_number=29

9.  \#151223 15:11:28 server id 15102  end\_log\_pos 16921 CRC32 0x92516d17 GTID     last\_committed=26         sequence\_number=30

10.

11. \#151223 15:11:28 server id 15102  end\_log\_pos 17495 CRC32 0xeb14a51e GTID     last\_committed=26         sequence\_number=31

12.

13. \#151223 15:11:28 server id 15102  end\_log\_pos 18071 CRC32 0x750667d0 GTID     last\_committed=26         sequence\_number=32

14.

15. \#151223 15:11:28 server id 15102  end\_log\_pos 18645 CRC32 0xcaed6159 GTID     last\_committed=26         sequence\_number=33

16.

17. \#151223 15:11:28 server id 15102  end\_log\_pos 19219 CRC32 0x62408408 GTID     last\_committed=26         sequence\_number=34

18.

19. \#151223 15:11:28 server id 15102  end\_log\_pos 19793 CRC32 0x5cf46239 GTID     last\_committed=33         sequence\_number=35

**slave机器的relay log中 last\_committed相同的事务**（sequence\_num不同）**可以并发执行**。从上面截取的信息可以看出last\_committed=26的事务一共有8个：从sequence\_number=27~24。假设当slave\_parallel\_workers=7时，Coordinator线程（SQL线程）分配这一组事务到worker中排队去执行。这里可以看出增加master库binary log group commit组中事务的数量可以提高slave机器并发处理事务的数量，MySQL5.7引入 binlog\_group\_commit\_sync\_delay和 binlog\_group\_commit\_sync\_no\_delay\_count参数即提高binary log组提交并发数量。MySQL等待binlog\_group\_commit\_sync\_delay毫秒的时间直到binlog\_group\_commit\_sync\_no\_delay\_count个事务数时，将进行一次组提交。

总结:

MySQL 5.7 GA版本推出的 Enhanced Multi-threaded Slaves功能，彻底解决了之前版本主备数据复制延迟的问题，开启该功能参数如下：

1.  \# slave机器

2.  slave-parallel-type=LOGICAL\_CLOCK

3.  ＃slave-parallel-type=DATABASE \#兼容MySQL 5.6基于schema级别的并发复制

4.  slave-parallel-workers=16 ＃开启多线程复制

5.  master\_info\_repository=TABLE

6.  relay\_log\_info\_repository=TABLE

7.  relay\_log\_recovery=ON

经过上两篇关于MySQL／MariaDB 的Binary Log Group Commit的发展历程和enhanced multi-threaded slave的介绍，相信大家对MySQL 基于Binay Log 的replication的原理以及为了解决主备数据复制延迟问题而引入的enhanced multi-threaded slave 功能，支持从库多线程并发回放主库提交的事务有了更深入的了解。同时为了更好的发挥MySQL 5.7/MariaDB 10 并发复制的性能，两个版本都在主库Binary Log Group Commit的阶段做了更加深入的优化。

无论是MySQL还是MariaDB在Binary Log Group Commit优化的目的都是：**使高并发下的事务尽可能的在同一个时间点提交，然后用一次fsync()的操作将这一组的Binary log缓存的数据写入磁盘**。当并发事务可以在同一个时间提交，说明每个线程所执行的**事务之间没有锁冲突**（如果有锁冲突，并发的事务将无法在同一个时刻提交），那么意味着这一组并发提交的事务在slave机器上能并发重放主库提交的事务，所以我们只需要在master机器对二进制日志进行Group Commit的时候标记上组提交相关信息，slave机器就可以安全的并发执行主库提交的事务。

我们来看一个例子：

事务T1、T2(start transaction)开始事务，落后于事务T3、T4的(start transaction)开始时间，但是这一组事务都在C（commit）时间点提交事务，所以这一组事务（T1、T2、T3、T4）将在master机器上进行Binary Log group Commit，然后该二进制日志推送到slave机器上时可以并发执行这一组被标记的事务。 ![28218939_14526636411GtO.png][]

原理：

从上面的例子可以看出，并发线程执行不同的事务只要在同一时刻能够commit（说明线程之间没有锁冲突），那么master节点就可以将这一组的事务标记并在slave机器上安全的进行并发重放主库提交的事务。所以尽可能的使所有线程能在同一时刻提交可以极大的提高slave机器并发执行事务的数量使主备数据同步。

在上一篇文章提到过：MySQL／MariaDB开启Binary Log日志后使 **二进制日志写入顺序和存储引擎提交顺序保持一致，**Binary Log Group Commit分为三个过程： ![28218939_1452663952cDeK.png][]

**图1: Binary Log Group Commit 三个阶段**

在 Flush stage：所有已经注册线程都将写入binary log缓存

在Sync stage ：binary log缓存的数据将会sync到磁盘，当sync\_binlog=1时所有该队列事务的二进制日志缓存永久写入磁盘

在 Commit stage:    leader根据顺序调用存储引擎提交事务。

那么为了使更多的并发线程事务能够视为在同一个时刻commit即在Sync阶段（调fsync()把binary log文件系统缓存日志永久刷入磁盘文件）master机器标记并发提交的事务为同一组事务的信息写入binary log日志中。我们可以在Flush Stage将注册为leader的线程带领更多的follower线程到Sync stage进行一次fsync()的操作，来增加Binary Log Group Commit的数量。

如下图：

当前MySQL/MariaDB数据库实例上运行三个线程分别提交T1、T2、T3事务，T1事务的线程率先提交进入第一阶段Flush stage队列，发现该队列是空队列故注册成leader，与此同时T2事务进入Flush stage成为该队列的follower等待leader调配，事务T1的leader带领T2事务进入Sync stage进行一次fsync()操作那么T1、T2在binary log进行一次group commit。  
       在二进制日志内标记了这一组事务。之后T3线程的事务随后进入了binary log提交的过程。

![28218939_1452664047OMu7.png][]  
**图2: 组提交过程**

MariaDB 10通过@@binlog\_commit\_wait\_count and @@binlog\_commit\_wait\_usec 两个参数设置，既事务commit阶段的时候至少等binlog\_commit\_wait\_usec毫秒直到有binlog\_commit\_wait\_count个数时进行一次组提交，来提高每组事务中的事务数量，并可以通过查询状态变量@@binlog\_commit和@@binlog\_group\_commit来查参数来查看当前binary log group commit比例。

MySQL5.7通过引入 binlog\_group\_commit\_sync\_delay和 binlog\_group\_commit\_sync\_no\_delay\_count参数即提高binary log组提交并发数量，既MySQL等待binlog\_group\_commit\_sync\_delay毫秒的时间直到binlog\_group\_commit\_sync\_no\_delay\_count个数时进行一次组提交。

实现：

Binary Log Group Commit在MySQL 5.7和MariaDB 10 中是默认开启不需要配置任何信息，且在binary log中标记的组提交信息依赖于GTID，而MySQL和MariaDB的GTID组成和实现方式不一样，这里我们简单梳理下。

在MySQL 5.7版本由于Binary Log Group Commit是默认开启的，所以即使你不开启gtid\_mode在配置文件中，binary log的内容中同样也有GTID 信息只不过标记的信息是"ANONYMOUS"

>    show binlog events in 'mysql-bin.000004';截取一段信息

1.  ...............  
| mysql-bin.000004 | 3571 | Anonymous\_Gtid |     15112 |        3636 | SET @@SESSION.GTID\_NEXT= 'ANONYMOUS'          |

2.  | mysql-bin.000004 | 3636 | Query          |     15112 |        3712 | BEGIN                   |

3.  | mysql-bin.000004 | 3712 | Rows\_query     |     15112 |        3763 | \# INSERT INTO t1 () VALUES ()                 |

4.  | mysql-bin.000004 | 3763 | Table\_map      |     15112 |        3807 | table\_id: 108 (db2.t1)                        |

5.  | mysql-bin.000004 | 3807 | Write\_rows     |     15112 |        3847 | table\_id: 108 flags: STMT\_END\_F               |

6.  | mysql-bin.000004 | 3847 | Xid            |     15112 |        3878 | COMMIT /\* xid=33 \*/                           |  
.................

>     mysqlbinlog -vvv mysql-bin.00004 | less

1.  \#151231 14:34:03 server id 15112  end\_log\_pos 2408 CRC32 0x5586fe71     Anonymous\_GTID ** last\_committed=6**        sequence\_number=8

2.  SET @@SESSION.GTID\_NEXT= 'ANONYMOUS'/\*!\*/;

3.  \# at 2408

4.  \#151231 14:34:03 server id 15112  end\_log\_pos 2484 CRC32 0x748efb17     Query   thread\_id=11    exec\_time=0     error\_code=0

5.  SET TIMESTAMP=1451543643/\*!\*/;

6.  BEGIN

7.  ..

MariaDB的GTID同样也是默认开启且GTID是由Domain ID、Server ID和transaction Sequence Number组成：

![28218939_14526644771o11.png][]  
  **  图3 MariaDB GTID组成**

>    show binlog events in 'mysql-bin.000003'；截取一段信息

1.  .......  
| mysql-bin.000003 |  335 | Gtid              |     15102 |         377 | BEGIN GTID 0-15102-64139                      |

2.  | mysql-bin.000003 |  377 | Table\_map         |     15102 |         434 | table\_id: 18 (test.sbtest1)                   |

3.  | mysql-bin.000003 |  434 | Write\_rows\_v1     |     15102 |         657 | table\_id: 18 flags: STMT\_END\_F                |

4.  | mysql-bin.000003 |  657 | Xid               |     15102 |         688 | COMMIT /\* xid=16 \*/                           |

5.  | mysql-bin.000003 |  688 | Gtid              |     15102 |         732 | BEGIN GTID 0-15102-64140 cid=20               |

6.  | mysql-bin.000003 |  732 | Table\_map         |     15102 |         789 | table\_id: 19 (test.sbtest6)                   |

7.  | mysql-bin.000003 |  789 | Write\_rows\_v1     |     15102 |        1012 | table\_id: 19 flags: STMT\_END\_F                |

8.  | mysql-bin.000003 | 1012 | Xid               |     15102 |        1043 | COMMIT /\* xid=20 \*/                           |

9.  | mysql-bin.000003 | 1043 | Gtid              |     15102 |        1087 | BEGIN GTID 0-15102-64141 cid=20               |

10. | mysql-bin.000003 | 1087 | Table\_map         |     15102 |        1145 | table\_id: 20 (test.sbtest12)                  |

11. | mysql-bin.000003 | 1145 | Write\_rows\_v1     |     15102 |        1368 | table\_id: 20 flags: STMT\_END\_F                |

12. | mysql-bin.000003 | 1368 | Xid               |     15102 |        1399 | COMMIT /\* xid=21 \*/                           |  
......

>    mysqlbinlog -vvv mysql-bin.00003  | less

1.     .......

2.     \# at 1754

3.  \#160104 15:16:46 server id 15102  end\_log\_pos 1798 CRC32 0x26104c0b     **GTID 0-15102-64143 cid=20** trans

4.  /\*!100001 SET @@session.gtid\_seq\_no=64143\*//\*!\*/;

5.  BEGIN

6.  /\*!\*/;

7.  \# at 1798

8.  \#160104 15:16:46 server id 15102  end\_log\_pos 1856 CRC32 0x2c994f5a     Table\_map: \`test\`.\`sbtest12\` mapped to number 20

9.  \# at 1856

10. \#160104 15:16:46 server id 15102  end\_log\_pos 2079 CRC32 0x02b5a694     Write\_rows: table id 20 flags: STMT\_END\_F

11.

12.  BINLOG '

13.  .........

结论：

MySQL 5.7 / MariaDB 10的parallel replication都是基于主库上Binary Log Group Commit。

MySQL：  主库并发提交的事务group commit写入binary log日志中，当事务被标记的 ** last\_committed=N**的值相同时（通过binlog\_group\_commit\_sync\_delay、 binlog\_group\_commit\_sync\_no\_delay\_count参数设置提高并发事务数量），可以在slave节点并发回放主库提交的事务。

MariaDB: 主库并发提交的事务group commit写入binary log日志中，当事务被标记的 **cid=N** 的值相同时（通过 binlog\_commit\_wait\_count、binlog\_commit\_wait\_usec参数设置提高并发事务数量），可以在slave节点并发回放主库提交的事务。

[28218939_14526636411GtO.png]: http://blog.itpub.net/attachment/201601/13/28218939_14526636411GtO.png
[28218939_1452663952cDeK.png]: http://blog.itpub.net/attachment/201601/13/28218939_1452663952cDeK.png
[28218939_1452664047OMu7.png]: http://blog.itpub.net/attachment/201601/13/28218939_1452664047OMu7.png
[28218939_14526644771o11.png]: http://blog.itpub.net/attachment/201601/13/28218939_14526644771o11.png