mysql组提交优缺点_理解MYSQL组提交和二阶段提交

本文开始讲组提交和二阶段提交前,先了解下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

三者的关系：

Controls how many microseconds the binary log commit waits before synchronizing the binary log file to disk. By default binlog_group_commit_sync_delay

is set to 0, meaning that there is no delay. Setting binlog_group_commit_sync_delay

to a microsecond delay enables more transactions to be synchronized together to disk at once, reducing the overall time to commit a group of transactions because the larger groups require fewer time units per group.

When sync_binlog=0

or sync_binlog=1

is set, the delay specified by binlog_group_commit_sync_delay

is applied for every binary log commit group before synchronization (or in the case of sync_binlog=0

, before proceeding). When sync_binlog

is set to a value n greater than 1, the delay is applied after every n binary log commit groups.

Setting binlog_group_commit_sync_delay

can increase the number of parallel committing transactions on any server that has (or might have after a failover) a replication slave, and therefore can increase parallel execution on the replication slaves. To benefit from this effect, the slave servers must haveslave_parallel_type=LOGICAL_CLOCK

set, and the effect is more significant when binlog_transaction_dependency_tracking=COMMIT_ORDER

is also set. It is important to take into account both the master's throughput and the slaves' throughput when you are tuning the setting for binlog_group_commit_sync_delay

.

Setting binlog_group_commit_sync_delay

can also reduce the number of fsync()

calls to the binary log on any server (master or slave) that has a binary log.

Note that setting binlog_group_commit_sync_delay

increases the latency of transactions on the server, which might affect client applications. Also, on highly concurrent workloads, it is possible for the delay to increase contention and therefore reduce throughput. Typically, the benefits of setting a delay outweigh the drawbacks, but tuning should always be carried out to determine the optimal setting.

The maximum number of transactions to wait for before aborting the current delay as specified bybinlog_group_commit_sync_delay

. Ifbinlog_group_commit_sync_delay

is set to 0, then this option has no effect.