前言：
本文作者：沃趣科技MySQL数据库工程师 麻鹏飞
一、binlog提交的流程
1、MySQL没有开启Binary log的情况下：
l InnoDB存储引擎通过redo和undo日志可以safe crash recovery数据库，当数据crash recovery时，通过redo日志将所有已经在存储引擎内部提交的事务应用redo log恢复，所有已经prepared但是没有commit的transactions将会应用undo log做roll back。然后客户端连接时就能看到已经提交的数据存在数据库内，未提交被回滚地数据需要重新执行。
2、MySQL开启Binary log 的情况下：
l 为了保证存储引擎和MySQL数据库上层的二进制日志保持一致（因为备库通过二进制日志重放主库提交的事务，假设主库存储引擎已经提交而二进制日志没有保持一致，则会使备库数据丢失造成主备数据不一致），引入二阶段提交（two phase commit or 2pc）
3、二阶段提交流程
图一 二阶段提交
1）Storage Engine（InnoDB） transaction prepare阶段：即sql语句已经成功执行并生成redo和undo的内存日志
2）Binary log日志提提交
l write()将binary log内存日志数据写入文件系统缓存
l fsync()将binary log 文件系统缓存日志数据永久写入磁盘
3）Storage Engine(InnoDB)内部提交
commit阶段在存储引擎内提交( innodb_flush_log_at_trx_commit控制)使undo和redo永久写入磁盘
4、开启Binary log的MySQL在crash recovery时：
l 当事务在prepare阶段crash，数据库recovery的时候该事务未写入Binary log并且存储引擎未提交，将该事务roll back。
l 当事务在Binary log日志已经fsync()永久写入二进制日志时crash，但是存储引擎未来得及commit,此时MySQL数据库recovery的时候将会从二进制日志的Xid（MySQL数据库内部分布式事务XA）中获取提交的信息重新将该事务重做并commit使存储引擎和二进制日志始终保持一致。
5、以上提到单个事务的二阶段提交过程，能够保证存储引擎和binary log日志保持一致，但是在并发的情况下怎么保证存储引擎和Binary Log提交的顺序一致？当多个事务并发提交的情况，如果Binary Log和存储引擎顺序不一致会造成什么影响？
图2 InnoDB存储引擎提交的顺序与MySQL上层的二进制日志顺序不同
如上图：事务按照T1、T2、T3顺序开始执行，将二进制日志（按照T1、T2、T3顺序）写入日志文件系统缓存，调用fsync()进行一次group commit将日志文件永久写入磁盘，但是存储引擎提交的顺序为T2、T3、T1。当T2、T3提交事务之后做了一个On-line的backup程序新建一个slave来做replication，那么事务T1在slave机器restore MySQL数据库的时候发现未在存储引擎内提交，T1事务被roll back，此时主备数据不一致(搭建Slave时，change master to的日志偏移量记录T3在事务位置之后)。
结论：MySQL数据库上层二进制日志的写入顺序和存储引擎InnoDB层的事务提交顺序一致，用于备份及恢复需要，如xtrabackup和innobackpex工具。为了解决以上问题，在早期的MySQL版本，通过prepare_commit_mutex 锁保证MySQ数据库上层二进制日志和Innodb存储引擎层的事务提交顺序一致。
图3 通过prepare_commit_mutex保证存储引擎和二进制日志顺序提交顺序一致
图3可以看出在prepare_commit_mutex，只有当上一个事务commit后释放锁，下一个事务才可以进行prepara操作，并且在每个transaction过程中Binary log没有fsync()的调用。由于内存数据写入磁盘的开销很大，如果频繁fsync()把日志数据永久写入磁盘数据库的性能将会急剧下降。此时MySQL 数据库提供sync_binlog参数来设置多少个binlog日志产生的时候调用一次fsync()把二进制日志刷入磁盘来提高整体性能，该参数的设置作用：
l sync_binlog=0，二进制日志fsync()的操作基于操作系统。
l sync_binlog=1，每一个transaction commit都会调用一次fsync()，此时能保证数据最安全但是性能影响较大。
l sync_binlog=N，当数据库crash的时候至少会丢失N-1个transactions。
图3 所示MySQL开启Binary log时使用prepare_commit_mutex和sync_log保证二进制日志和存储引擎顺序保持一致（通过sync_binlog来控制日志的刷新频率），prepare_commit_mutex的锁机制造成高并发提交事务的时候性能非常差而且二进制日志也无法group commit。
6、那么如何保证MySQL开启Binary Log日志后使二进制日志写入顺序和存储引擎提交顺序保持一致并且能够进行二进制日志的Group Commit？
MySQL 5.6 引入BLGC（Binary Log Group Commit）,二进制日志的提交过程分成三个阶段，Flush stage、Sync stage、Commit stage。
那么事务提交过程简化为：
存储引擎（InnoDB) Prepare ----> 数据库上层(Binary Log) Flush Stage ----> Sync Stage ----> 调存储引擎（InnoDB）Commit stage.
每个stage阶段都有各自的队列，使每个session的事务进行排队。当一个线程注册了一个空队列，该线程就视为该队列的leader，后注册到该队列的线程为follower，leader控制队列中follower的行为。leader同时带领当前队列的所有follower到下一个stage去执行，当遇到下一个stage并非空队列，此时leader可以变成follower到此队列中（注：follower的线程不可能变成leader）
图4: 二进制日志三阶段提交过程
在 Flush stage：所有已经注册线程都将写入binary log缓存
在Sync stage ：binary log缓存的数据将会sync到磁盘，当sync_binlog=1时所有该队列事务的二进制日志缓存永久写入磁盘
在 Commit stage:leader根据顺序调用存储引擎提交事务。
当一组事务在进行Commit阶段时，其他新的事务可以进行Flush阶段，从而使group commit不断生效。那么为了提高group commit中一组队列的事务数量，MySQL用binlog_max_flush_queue_time来控制在Flush stage中的等待时间，让Flush队列在此阶段多等待一些时间来增加这一组事务队列的数量使该队列到Sync阶段可以一次fsync()更多的事务。
MySQL 5.7 Parallel replication实现主备多线程复制基于主库Binary Log Group Commit, 并在Binary log日志中标识同一组事务的last_commited=N和该组事务内所有的事务提交顺序。为了增加一组事务内的事务数量提高备库组提交时的并发量引入了binlog_group_commit_sync_delay=N 和binlog_group_commit_sync_no_delay_count=N (注：binlog_max_flush_queue_time 在MySQL的5.7.9及之后版本不再生效)参数，MySQL等待binlog_group_commit_sync_delay毫秒直到达到binlog_group_commit_sync_no_delay_count事务个数时，将进行一次组提交。
二、并发复制的多线程复制
首先梳理下传统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节点必须按照同样的顺序执行，如果顺序不一致容易造成主备库数据不一致的风险。
例1：
Master上执行顺序：t1,t2
Slave上执行顺序：t2,t1
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中主库提交的事务)
问题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
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
关于commit问题的几个思考：
1、主从同步环境里，事务是否commit和主从真的有关系？
2、如果是半同步环境里，事务是否commit和主从有什么关系？
为了方便大家交流，本人开通了微信公众号，和QQ群291519319。喜欢技术的一起来交流吧
扫一扫
1939
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
