本文开始讲组提交和二阶段提交前,先了解下BINLOG和REDO LOG;

两个日志关系

在ORACLE 对应的是REDO LOG和ARCHIVE LOG ,只是两者关系不一样。 在ORACLE数据库里 ARCHIVE LOG 是 REDO LOG的 历史日志记录。 REDO LOG 就记录当前数据库修改行为的日志。 REDO LOG 一般分成3组, 每组里面必须有1个日志文件,自然可以1个以上的日志文件。通过切换组 就可以把REDO LOG 文件写入到ARCHIVE LOG文件中。

在MYSQL 里面  INNDOB的REDO LOG和MYSQL 的BINLOG 是两个独立体,不像ORACLE是时间上的关系。因为MYSQL 里面可以包含多个存储引擎,每个引擎有自己的独立日志。BINLOG是处于MYSQL的服务层,而REDO LOG 是INNDODB存储引擎层。当一个事务涉及了多个存储引擎的时候,也就是跨了引擎。那么只有BINLOG记录的才是唯一正确的,而INNODB记录的只是事务修改了INNODB引擎的,而该事务修改别的引擎就无法记录了。所以在MYSQL里面一切以BINLOG为主。

REDO 组提交

所为组提交,是只一组事务一起提交。innodb Redo log的刷盘操作将会是最终影响MySQL TPS的瓶颈所在。虽然有innodb_flush_log_at_trx_commit 参数来提高性能,不过还是不给力.

该参数的有效值有 0、1、2:

0:事务提交时,不将重做日志缓冲写入磁盘,而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。

1:事务提交时,会将重做日志缓冲写入磁盘,并且立即刷新

2:事务提交时,会将重做日志缓冲写入磁盘,但是不会立即进行刷新操作,因此只是写到了操作系统的缓冲区。

可以看到,只有1才能真正地保证事务的持久性,但是由于刷新操作 fsync() 是阻塞的,直到完成后才返回,我们知道写磁盘的速度是很慢的,因此 MySQL 的性能会明显地下降。如果不在乎事务丢失,,0和2能获得更高的性能。

我又要这个保证数据写入磁盘,又要提高并发性 ,那么怎么办才好呢?

呵呵 就一组事务提交呗! 起码提高下下性能!

在ORACLE 也有组提交功能,那就是 批量日志写

alter system set commit_logging=batch scope=both;alter system set commit_wait=nowait scope=both;

在ORACLE写日志文件的行为由LGWR进程完成,写文件时机有以下几点

1 数据文件写

2 日志缓存满了1MB

3 日志缓存满了1/3

4 每隔3秒

5 事务的COMMIT语句。

设置了批量日志写参数后,实际上就忽略掉了第五个条件。从而达到了组提交的功能。

虽然实现了REDO LOG的组提交,却挖了坑埋掉了BINLOG的主从关系,因为组提交导致了主从数据不一致。原本BINLOG和REDO LOG 之间如何协调的呢?

当事务提交的时候,BINLOG向各个存储引擎喊话,兄弟们我要提交了,各个兄弟收到了! 然后BINLOG听到了兄弟的回应后,就再不管兄弟们了,自己把日志同步到磁盘上,然后在BINLOG打下COMMIT标记而已。

这坑就是 你兄弟玩组提交,我提交了你没提交,你大爷的中间时间主机崩溃了,启动了从库来当主库。 从库起来后发现BINLOG该事务提交了,而INNODB里面毛该数据。本来在单实例中,恢复的时候INNODB会参考BINLOG,如果BINLOG该事务提交了,那我INNODB该事务没有提交就提交下。如果BINLOG该事务没有提交,而我提交了则回滚掉。

二阶段提交:

就是解决这个BINLOG和REDO LOG 数据不一致性。其实呢分成了4个阶段,每个阶段都使用队列锁来控制。其实算法不好理解。总之是BINLOG和兄弟们之间加强了关系。也就是BINLOG除了前面说的喊话后,收到了回应,还要等兄弟们把日志同步完,自己才做同步工作,然后给两个日志都打上COMMIT标记。

1. Prepare Innodb:

a) Write prepare record to Innodb's log buffer

b) Sync log file to disk  -- redo组提交

c) Take prepare_commit_mutex

2. "Prepare" binary log:

a) Write transaction to binary log

b) Sync binary log based on sync_binlog

3. Commit Innodb:

a) Write commit record to log

b) Release prepare_commit_mutex

c) Sync log file to disk

d) Innodb locks are released

4. "Commit" binary log:

a) Nothing necessary to do here.

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.

看不懂上面的,就看下面的图吧!

BINLOG组提交

搞个二阶段提交,性能又降下来了,瓶颈在BINLOG身上,那就解决BINLOG。 我们把BINLOG 也搞成组提交。并且和REDO LOG组提交同一起来。这样一来保证了数据,也保证了性能。真有两全齐美的办法!

sync_binlog参数

sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。

sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

这个参数跟innodb_flush_log_at_trx_commit 是一样的功能,只是调整的是不同的日志,SYSNC_BINLOG是调整BINLOG同步行为,也就是刷到磁盘的行为。

Tips:当引入Group Commit后,sync_binlog的含义就变了,假定设为1000,表示的不是1000个事务后做一次fsync,

而是1000个事务组。

组提交参数:

binlog_group_commit_sync_delay=N:在等待N μs后,开始事务刷盘。

binlog_group_commit_sync_no_delay_count=N  N表示多少个事务打包成一组。

TIPS:参数binlog_group_commit_sync_delay,在MySQL 5.7.19中,如果该参数不为10的倍数,则会导致事务在Sync 阶段等待极大的时间,表现出来的现象就是执行的sql长时间无法返回。该bug已在MySQL 5.7.24和8.0.13被修复。

下面谈的是

SYS_BINLOG ,

binlog_group_commit_sync_delay,

binlog_group_commit_sync_no_delay_count

三者的关系: