MYSQL5.7多线程复制原理

在使用mysql的过程中，复制延迟一直是一个DBA头疼的问题。

延迟优化方法：

  • 增大从库参数innodb_buffer_pool_size的值，可以缓存更多数据，减少由于转换导致的IO压力。
  • 增大参数innodb_log_file_size,innodb_log_file_in_group的值，减少buffer pool的磁盘IO，提升写入性能。
  • 修改参数innodb_flush_method为 O_DIRECT，提升写入性能（在ssd下，或者磁盘IO能力强的时候推荐使用）.
  • 如果可以的话，把从库binlog关掉，或者关掉参数log_slave_updates.
  • 修改参数innodb_flush_log_at_trx_commit为0 或者2.
  • 如果binlog没有关掉，修改sync_binlog参数为0或一个很大的数，减少磁盘压力。
  • 如果binlog_format为ROW模式，并且被修改表没有主键，则需要加上主键。
  • 如果binlog_format为ROW模式，则可以在从库中删掉一些不必要的索引，同步之后在加上。
  • 了解清楚写库上的操作内容，适当地在从库中预热以下数据，可以减少在复制时等待的时间。
  • 如果binlog_format为STATEMENT模式，或者存在DDL复制，则可以将tmpdir参数改到内存中，比如/dev/shm
  • 修改参数master_info_info_repository,relay_log_info_repository为table，减少直接IO导致的磁盘压力。
  • 升级硬件。
  • 将mysql升级到5.7版本，使用多线程并行复制。

 

mysql5.6的多线程复制。

使用5.6的多线程复制是有条件的：

  1. 当前实例中涉及的数据库比较多。
  2. 每个数据库写入的数据比较均匀。

在一般的mysql使用中，一库多表比较常见，而多库少表比较少见。单单是这一点，就把很多人希望通过升级5.6的多线程复制来解决主从延迟的想法给抹杀了。

mysql5.7版本的多线程复制

5.6表面已经支持并行复制了，但实际上属于雷大雨小。这种并行复制的功能出来之后，很多人都觉得不太合适，使用的人很少。
之后5.7的beta版本柴胡来了，它的并行复制以一种全新的姿态出现在了DBA面前，每个人都叫好，把解决复制延迟的希望，放在了mysql5.7版本上。5.7也被认为是原汁原味的并行复制，并行复制本来就应该做成这个样子。

5.7的并行复制是如何实现的呢？

首先5.7的并行复制基于一个前提，所有已经处于prepare阶段的事务，都是可以进行并行提交的，这些当然也可以在从库中并行提交，因为处于这个阶段的事务，都是没有冲突的，该获取的资源都已经获取了，反过来说，如果有冲突，则后来的会等已经获取资源的事务完成之后才能继续，故而不会进入prepare阶段，这时一种新的并行复制思路，完全摆脱了原来一直致力于未来防止冲突而做的分发算法，等待策略等复杂而又效率低的下的工作。

一言以蔽之：一个组提交的事务都是可以并行回放，

 

如何来定义哪些事务事务处于prepare阶段的，以及在生成的binlog内容中该如何告诉SLAVE哪些事务是可以并行复制的，5.7未来兼容5.6版本的库级复制，增加了一个参数slave_parallel_type，用来与之前5.6版本的库级别复制区分，5.6版的库级复制参数值为DATABASE,而5.7版的并行复制参数值为logical_clock。

首先在看一下5.7版本中生成的binlog内容:

增加mysql复制线程 mysql开启多线程复制_增加mysql复制线程

                                                                                               图1-1

如上图，只是将GTID事件过滤出来了，其他的和以前的版本时一样的，可以看出，GTID这个事件相比5.6版本，多了如下两个内容:

  1. last_committed
  2. sequence_number

如图1-1，

last_committed值有11个值，分别是0,1,2,3,4,5,6,7,8,9,10。这就表示当前binlog包括11个组，也就是说，last_committted中的每个值对应于一个组的编号，last_committed为10的有4个事务，这4个事务在5.7版本中，被定义为可以并行复制（提交）的，而sequence_number是顺序增长的，每一个事物对应一个序列号（sequence_number）。

 

另外，还有一个细节可能不太容易被发现，其实每一个组的last_commit值，都是上一个组中事务的sequence_number最大值，也是本组中事物sequence_number的最小值减1，同时这两个值的有效作用域都在文件内，同时这两个值的有效作用域都在文件内，只要换一个文件（flush binary logs），这两个值都会从0或1开始计数。

 

在MySQL 5.6版本之前，Slave服务器上有两个线程I/O线程和SQL线程。I/O线程负责接收二进制日志（更准确的说是二进制日志的event），SQL线程进行回放二进制日志。如果在MySQL 5.6版本开启并行复制功能，那么SQL线程就变为了coordinator线程，coordinator线程主要负责以前两部分的内容：

  • 若判断可以并行执行，那么选择worker线程执行事务的二进制日志.
  • 若判断不可以并行执行，如该操作是DDL，亦或者是事务跨schema操作，则等待所有的worker线程执行完成之后，再执行当前的日志.

这意味着coordinator线程并不是仅将日志发送给worker线程，自己也可以回放日志，但是所有可以并行的操作交付由worker线程完成。coordinator线程与worker是典型的生产者与消费者模型。

并行复制两个主要参数slave_parallel_type,slave_parallel_workers

增加mysql复制线程 mysql开启多线程复制_SQL_02

 

增加mysql复制线程 mysql开启多线程复制_增加mysql复制线程_03

 

 

 

mysql是如何做到将这些事务分组的呢，要搞清楚这个问题，首先要了解mysql的提交方式-------ordered_committed。

 

ordered_committed

只要事务提交，就都会先加入队列中。而提交有三个步骤：

  • FLUSH
  • SYNC
  • COMMIT

相应的也三个队列，首先要加入的是FLUSH队列，如果某个事务加入时，队列还是空的，则这个事物就担任队长，来代表其他事物执行提交操作。而其他事物继续加入时，就会发现此时队列已经不为空了，那么这些事务就会等待队长帮它们完成提交操作，如下图，事务2-6都是这种坐享其成之辈，事务1就是队长了，这里需要注意一点，不是说队长会一直等待提交的事物不停地加入，而是有一个时限，只有在这个时限之内成功加入到队列的，才能帮它提交，这个时限就是队长加入开始，到它去处理队列的时间，这个时间实际上非常小，基本上就是程序从这行到那行的一个过程，也没有刻意去等待。

只要队长将这个队列中的事物取出，其他事物就可以加入这个队列了。第一个加入的还是队长，但此时必须要等待，因为此时有事物正在做FLUSH，做完FLUSH之后，其他的队长才能袋子队员做FLUSH，而在同一时刻，只能有一个组在做FLUSH,这就是如中所示的等待事物组2和等待事物组3，此时队长会按照顺序依次做FLUSH，做FLUSH的过程中，有一些很重要的事物需要去做。

 

增加mysql复制线程 mysql开启多线程复制_SQL_04

 

1，要保证顺序必须是提交加入到队列的顺序。

2，如果有新的事物提交，此时队列为空，则可以加入到FLUSH队列中。不过因为此时FLUSH临界区正在被占用，所以新事务组必须要等待。

3，给每一个事务分配sequence_number，如果是第一个事物，则将这个组的last_committed设置为sequence_number-1.

4,将带着last_commmitted与sequence_number的GTID事件FLUSH到Binlog文件中。

5，将当前事物所产生的Binlog内容FLUSH到binlog文件中。

 

这样，一个事务的FLUSH就完成了。接下来，依次做完组内所有事务的FLUSH，然后做SYNC，如果SYNC的临界区是空的，则直接做SYNC操作，而如果有事务组在做，则必须要等待。同样地，做完FLUSH之后，FLUSH临界区也会空闲出来，那么此时在等待的这个临界区的组就可以做FLUSH操作了。总而言之，每一个步骤多会有事务组在做，就像一个流水线一样，完成一件产品需要三个工序，每个工序都可以批量来做，那么每个工序车间都不会闲着，都一致重复着相同的事情，最终每个产品都是完全相同的顺序完成。

 

到COMMIT这道工序时，实际做的是存储引擎提交，参数inlog_order_commit会应影响提交行为，如果设置为ON，那么此时提交就变为串行操作了，就以队列的顺序为提交顺序顺序。而如果设置为OFF，提交就不会在这里进行，而会在每个事务（队长，队员）做finishi_commit（FINISH）时各自做存储引擎的提交操作。组内每个事务做finish_commit是在队长完成COMMIT工序之后进行，到步骤DONE时，会唤醒每一个等待提交完成的事务。告诉他们可以继续了，那么每个事务就会去做finish_commit。而后，自己去做finish_commit.这样，一个组的事务就按部就班地提交完成了。可以这么说，与这个组中同时在做提交的最多还有另外两个事务，一个是在做FLUSH，一个是在做SYNC。

 

现在应该明白order_commit的原理了，而这也是logical_clock并行复制的基础，order_commit使得所有的事物分了组，并且有了序列号，从库拿到这些序列号，就可以根据序列号放心大胆地做分发了。

 

每一个组的事务数都没有做过特殊处理，因为时间上说，从队长入队，到取队列中所有事务出来，这之间时间是非常非常小的，其实就是几行代买的事，也不会有任何费时间的操作，所以在这段时间内其实不会有多少个事务。只有在压力很大，提交的事物非常多的时候，才会提高并发度。这个问题也解释的痛，主库压力很小的时候，从库何必要那么大的并发度呢？只有压力大的时候，从库才会出现延迟。

 

多线程复制分发原理

从库是如何分发的，从库是以事务为单位，做APPLY的，每一个事物会有一个GTID事件，从而都有一个last_committed及sequence_number值，分发原理如下。

1，从库SQL线程拿到一个新事务，取出last_committed及sequence_number值。

2，判断当前last_committed是不是大于当前已将执行的sequence_number的最小值（low_water_mark，简称lwm）。

3，如果大于，则说明上一个组的事务还没有完成。此时等待lwm变大，知道last_committed与lwm相等，才可以继续。

4，如果小于或等于，则说明上一个组的事务与正在执行的组是同一个组，不需要等待。

5，SQL线程通过统计，找到一个空闲的worker线程，如果没有空闲的，则SQL线程转入等待状态，直到找到一个为止。

6将当前事物打包，交给选定的worker，之后worker线程会去APPLY这个事务，此时的SQL显现出就会处理下一个事务。

上面的步骤是以事务为单位介绍的，其实实际处理中还是一个事件一个事件地分发。如果一个事务已经选定了worker,而新的event还在那个事务中，则直接交给那个worker处理即可。

 从上面的分发原理来看，同时执行的都是具有相同last_commit值的事务，不同只是后面的需要等前面做完了才能执行，事物都是随机分配到了worker线程中，但是执行的话，必须是一行一行地执行。一行事务数越多，并行读越高，也说明主库瞬时压力很大。