MySQL Group submission

Group commit is an optimized way for MySQL to process logs, primarily to solve the problem of frequently brushing disks when writing logs. Group submissions are continuously optimized with MySQL, from initially supporting only the Redo Log group submission, to the current 5.6 official version supporting both redo log and Binlog group submissions. The implementation of group submissions greatly improves the transactional performance of MySQL, and the following will take the InnoDB storage engine as an example, detailing how group submissions are implemented at each stage.

Redo Log for Group submission

WAL (write-ahead-logging) is a common technique for implementing transactional persistence, with the basic principle that in order to avoid random writes of disk pages when committing a transaction, it is only necessary to ensure that the redo log of the transaction is written to the disk, which can be redo The sequential write of log replaces the random write of the page, and it can guarantee the persistence of the transaction and improve the performance of the database system. Although Wal uses sequential write instead of random write, however, each transaction commits, still need to have a log brush disk action, limited by disk IO, this operation is still the bottleneck of transaction concurrency.

The idea of group submission is to combine the brush disk actions of multiple transactions redo log to reduce the disk sequence. in the InnoDB log system, each redo log has an LSN (log Sequence number), and the LSN is monotonically incremented. Each transaction performs an update operation that contains one or more redo logs, when each transaction copies the log to Log_sys_buffer (Log_sys_buffer through Log_mutex

Protection), the current maximum LSN is obtained, so that the LSN of different transactions is not duplicated. So assuming that the maximum LSN of the logs for the three transactions trx1,trx2 and TRX3 is Lsn1,lsn2,lsn3 (LSN1<LSN2<LSN3) respectively, they commit at the same time, then if the TRX3 log is first fetched to Log_mutex for the drop disk, It can also brush [LSN1---LSN3] This log, so Trx1 and Trx2 do not have to request disk IO again. The basic process for group submission is as follows:

1. Get Log_mutex
2. If FLUSHED_TO_DISK_LSN>=LSN, indicates that the log has been brush disk, jump 5
3. If CURRENT_FLUSH_LSN>=LSN, indicates that the log is in the brush disk, jump 5 after entering the waiting state
4. Log brush disk smaller than LSN (flush and sync)
5. Exit Log_mutex

Note: The LSN indicates that the LSN,FLUSHED_TO_DISK_LSN and CURRENT_FLUSH_LSN of the transaction represent the LSN of the brush disk, respectively, and the LSN of the disk being brushed.

Two-phase commit

In the case of a single machine, redo log Group submitted a good solution to the log disk problem, then open Binlog, Binlog can and redo log also open Group submission? The first thing we need to solve when we start binlog is how to ensure the consistency of binlog and redo log. Because Binlog is the bridge of Master-slave, if the order is inconsistent, it means that master-slave may not be consistent. MySQL solves this problem well with two-phase submissions. Prepare stage, InnoDB brush redo log, and set the rollback segment to prepared state, binlog do nothing; commit phase, InnoDB release lock, Release rollback segment, set submission status, Binlog brush Binlog log. If an exception occurs and a failure recovery is required, if the transaction is found to be in the prepare phase and the Binlog exists, it is committed or rolled back. The two-phase commit ensures consistency of redo log and binlog under any circumstances.

Group Submissions for Binlog

back to the question of the previous section, after opening the binlog, how to ensure the redo Log-binlog consistent basis, the implementation of group submissions. Because of this problem, before 5.6, MySQL in the case of opening Binlog, unable to implement the group submission, through a notorious prepare_commit_mutex, the redo log and Binlog Brush disk serialization, serialization is only to ensure that redo Log-binlog consistent, but this approach sacrifices performance. This situation is obviously intolerable, so each MySQL branch, mariadb,facebook,perconal and so on has been a patch to improve this issue, MySQL official version 5.6 has finally solved the problem. Because the various branch version workarounds are similar, I mainly illustrate the implementation method by analyzing the implementation of 5.6.

Binlog Group submitted the basic idea is that the introduction of the queue mechanism to ensure that the InnoDB commit sequence and binlog order of the same, and the transaction group, the group of Binlog brush disk action to a transaction, to achieve group submission purposes. Binlog submission divides the submissions into 3 phases, the flush phase, the sync phase, and the commit phase. Each stage has a queue, each queue has a mutex protection, agreed to enter the queue the first thread is leader, the other thread is follower, all things to leader to do, leader after all the action, notify follower brush end. The basic process for Binlog group submission is as follows:

FLUSH Stage

1) Hold Lock_log mutex [leader hold, follower wait]

2) Gets a set of Binlog in the queue (all transactions in the queue)

3) Binlog buffer to the I/O cache

4) Notify dump thread dump Binlog

Sync phase

1) Release Lock_log mutex, hold Lock_sync Mutex[leader hold, follower wait]

2) Place a set of Binlog on the disc (Sync action, the most time-consuming, assuming Sync_binlog is 1)

Commit phase

1) Release Lock_sync mutex, hold Lock_commit Mutex[leader hold, follower wait]

2) Iterate through the transactions in the queue and make a InnoDB commit

3) Release Lock_commit Mutex

4) Wake-up thread waiting in queue

Note: Because there are multiple queues, each queue has a mutex protection, the queues are sequential, and a thread entering the queue is leader, so the flush phase of leader may be the follower of the sync phase, but follower is always follower.

From the above analysis, we know that MySQL's current group submission method solves the problem of consistency and performance. Resolves the performance of disk IO through two-phase commit resolution consistency through redo log and Binlog group submissions. Below I have compiled the prepare stage and commit stage frame diagram for your reference.

Reference documents

Http://mysqlmusings.blogspot.com/2012/06/binary-log-group-commit-in-mysql-56.html

Http://www.lupaworld.com/portal.php?mod=view&aid=250169&page=all

http://www.oschina.net/question/12_89981

Http://kristiannielsen.livejournal.com/12254.html

Http://blog.chinaunix.net/uid-26896862-id-3432594.html

MySQL Group submission