MYSQL group submission, mysql submission

MYSQL group submission, mysql submission

Group commit is an optimization method for MYSQL to process logs, mainly to solve the problem of frequent disk flushing when writing logs. Group submission is constantly optimized along with the development of MYSQL. From the initial support of only redo log group submission, to the current official version 5.6, both redo log and binlog group submission are supported. The implementation of group commit greatly improves the transaction processing performance of mysql. The following describes the implementation principles of group commit in various stages using the innodb Storage engine as an example.

Redo log group submission

WAL (Write-Ahead-Logging) is a common technique used to realize transaction persistence. The basic principle is to avoid random Write of disk pages when committing transactions, you only need to ensure that the redo log of the transaction is written to the disk, so that the random write of the page can be replaced by the sequential write of the redo log, and the transaction persistence can be ensured, improving the performance of the database system. Although WAL uses sequential write instead of random write, each transaction commit still requires a log disk flushing action. Due to disk IO restrictions, this operation is still the bottleneck of transaction concurrency.

The group commit idea is to merge the disk flushing actions of multiple transaction redo logs to reduce sequential disk write. In the log system of Innodb, each redo Log has an LSN (log Sequence Number), and The LSN increases monotonically. Each transaction performs an update operation and contains one or more redo logs. When each transaction copies the logs to log_sys_buffer (log_sys_buffer uses log_mutex

Protection), the maximum current LSN is obtained, so it can ensure that the LSN of different transactions will not be repeated. Assume that the maximum LSN of the three transaction Trx1, Trx2, and Trx3 logs is LSN1, LSN2, and LSN3 (LSN1 <LSN2 <LSN3), which are committed simultaneously, if the Trx3 log first obtains log_mutex for disk storage, it can also fl the [LSN1---LSN3] log, so Trx1 and Trx2 do not need to request disk IO again. The basic process of group submission is as follows:

Note: lsn indicates the transaction lsn, flushed_to_disk_lsn and current_flush_lsn indicate the fl disk LSN and the fl disk LSN respectively.

Two-phase submission

In the case of a single machine, the redo log group commit solves the problem of log disk storage. After binlog is enabled, can binlog enable group commit as well as redo log? After enabling binlog, we need to solve the problem of how to ensure the consistency between binlog and redo log. Because binlog is a bridge between Master and Slave, if the order is inconsistent, it means that the Master-Slave may be inconsistent. MYSQL solves this problem through two-phase commit. In the Prepare stage, innodb fl redo log and sets the rollback segment to the Prepared status. binlog does not perform any operations. In the commit stage, innodb releases the lock, releases the rollback segment, and sets the submission status, binlog fl binlog. If an exception occurs and a fault recovery is required, if the transaction is found to be in the Prepare stage and binlog exists, the transaction is committed; otherwise, the transaction is rolled back. The two-phase commit ensures the consistency of redo log and binlog under any circumstances.

Binlog group submission

Back to the problem in the previous section, after enabling binlog, how can we implement group commit Based on the consistency of redo log-binlog. Because of this problem, mysql was unable to implement group commit when binlog was enabled before mysql 5.6. Through a notorious prepare_commit_mutex, redo log and binlog were serialized, the purpose of serialization is only to ensure redo log-Binlog consistency, but this method sacrifices performance. This situation is obviously intolerable. Therefore, various mysql branches, mariadb, facebook, and perconal have successively developed patch improvements. mysql official version 5.6 has finally solved this problem. Since the solution for each branch version is similar, I mainly explain the implementation method by analyzing the implementation of 5.6.

The basic idea of binlog group commit is to introduce the queue mechanism to ensure that the order of innodb commit is consistent with the order of binlog disk placement, and assign the transaction group and the binlog flushing action in the group to a transaction, implement group submission. Binlog submission is divided into three phases: FLUSH, SYNC, and COMMIT. Each stage has a queue, and each queue has a mutex protection. It is agreed that the first thread in the queue is leader, the other thread is follower, and all tasks are handled by leader, after the leader completes all the actions, it notifies the follower that the flushing is complete. The basic process for binlog group submission is as follows:

FLUSH stage

1) Holding Lock_log mutex [leader holding, follower waiting]

2) Obtain a group of binlogs in the queue (all transactions in the queue)

3) binlog buffer to I/O cache

4) Notify dump thread dump binlog

SYNC phase

1) Release Lock_log mutex, holding Lock_sync mutex [leader holding, follower waiting]

2) Drop a group of binlogs into the disk (sync action, the most time-consuming, assuming that sync_binlog is 1)

COMMIT stage

1) Release Lock_sync mutex, holding Lock_commit mutex [leader holding, follower waiting]

2) traverse transactions in the queue and perform innodb commit one by one

3) Release Lock_commit mutex

4) Wake up the waiting thread in the queue

Note: Because there are multiple queues, each queue has mutex protection, and the queues are ordered, it is agreed that a thread entering the queue is leader, therefore, the leader in the FLUSH phase may be the follower in the SYNC phase, but the follower will always be the follower.

Through the above analysis, we know that the current MYSQL group commit method solves the consistency and performance problems. Solve the consistency through two-phase commit, and solve the disk IO performance through redo log and binlog group commit. I have sorted out the framework of the Prepare and Commit phases for your reference.

 

References

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