Shulou(Shulou.com)06/01 Report--

Today, I would like to talk to you about how to understand MYSQL GroupCommit. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something from this article.

Group commit (group commit) is an optimized way for MYSQL to deal with logs, mainly to solve the problem of frequent disk scrubbing when writing logs. Group submission has been continuously optimized with the development of MYSQL, from initially only supporting redo log group submission to the current official version 5.6 supporting both redo log and binlog group submission. The implementation of group commit greatly improves the transaction processing performance of mysql. The following will take the innodb storage engine as an example to introduce in detail the implementation principle of group commit in each stage.

Group submission for redo log

WAL (Write-Ahead-Logging) is a common technology to achieve transaction persistence. The basic principle is that when committing a transaction, in order to avoid random writing of disk pages, it is only necessary to ensure that the redo log of the transaction is written to disk, so that the random writing of pages can be replaced by the sequential writing of redo log, and the persistence of transactions can be guaranteed, and the performance of the database system is improved. Although WAL uses sequential writes instead of random writes, each transaction commit still requires a log flush action, which is limited by disk IO, which is still the bottleneck of transaction concurrency.

The idea of group commit is to merge the flushing actions of multiple transactions redo log to reduce the disk write order. In Innodb's log system, each redo log has a LSN (Log Sequence Number), and LSN is monotonously increasing. Each transaction performs an update operation that contains one or more redo log, and when each transaction copies the log to log_sys_buffer (log_sys_buffer through log_mutex

Protection), the current maximum LSN is obtained, so it is guaranteed that the LSN of different transactions will not be duplicated. Then suppose that the maximum LSN of the log of the three transactions Trx1,Trx2 and Trx3 is LSN1,LSN2,LSN3 (LSN1=lsn), indicating that the log is being flushed. Jump 5 and enter the waiting state.

Refresh logs smaller than LSN (flush and sync)

Exit log_mutex

Note: lsn indicates that the lsn,flushed_to_disk_lsn and current_flush_lsn of the transaction represent the LSN that has been brushed and the LSN that is being flushed.

Redo log group submission optimization

We know that when binlog is enabled, the prepare phase will perform a flush operation (innodb_flush_log_at_trx_commit=1) on the redo log to ensure that the updates to the data page and undo page have been refreshed to disk; in the commit phase, the binlog operation will be performed (sync_binlog=1), and the undo log of the transaction will be set from the prepare state to the committed state (cleanable state). Through the two-phase commit mode (innodb_support_xa=1), the order of binlog and redo log of transactions can be guaranteed to be consistent. During the two-phase commit process, mysql_binlog acts as the coordinator, and each storage engine and mysql_binlog act as participants. When the fault is restored, scan the last binlog file (in the flush phase, determine whether the binlog exceeds the threshold, proceed with the rotate binlog file, the corresponding transaction in the binlog file of rotate must have been committed, and the binlog of the transaction in prepared has not been brushed in because it has not entered the ordered_commit function yet), extract the xid from it. Redo the redo log after the checkpoint, read the undo segment information of the transaction, collect the transaction linked list in the prepare phase, compare the xid of the transaction with the xid in the binlog, and commit if it exists, otherwise it will be rolled back.

As can be seen from the above description, when each transaction commits, a redo flush action is triggered, which affects the throughput of the system because the disk is slow to read and write. Taobao children's shoes have made an optimization, moving the redo action of prepare stage to the flush stage of commit (flush-sync-commit), ensuring that redo will be brushed before brushing binlog. In this way, the original recovery logic will not be violated. The advantage of moving to the commit phase is that instead of flushing every transaction, the leader thread helps to brush a batch of redo. How to implement it is very simple, because log_sys- > lsn always maintains the current largest lsn. As long as we swipe redo to the current log_sys- > lsn, we will be able to guarantee that the transaction redo log that will be brushed binlog must have been closed. By delaying writing redo, the purpose of redo log group submission is realized, and the competition of log_sys- > mutex is reduced. At present, this strategy has been introduced by the official mysql5.7.6.

Two-phase submission

In the stand-alone case, redo log group submission solves the log disk failure problem very well, so when binlog is enabled, can binlog enable group submission as well as redo log? After opening binlog first, one of the problems we need to solve is how to ensure the consistency of binlog and redo log. Because binlog is the bridge of Master-Slave, if the order is not consistent, it means that the Master-Slave may be inconsistent. MYSQL solves this problem well through two-phase commit. In Prepare stage, innodb brushes redo log, and sets rollback segment to Prepared state, binlog does not do any operation; in commit phase, innodb releases lock, releases rollback segment, sets submission status, and binlog brushes binlog log. When an exception occurs and fault recovery is needed, if it is found that the transaction is in the Prepare phase and binlog exists, it will be committed, otherwise it will be rolled back. Through two-phase commit, the consistency of redo log and binlog is ensured in any case.

Group submission for binlog

Going back to the question in the previous section, after enabling binlog, how to achieve group submission on the basis of ensuring redo log-binlog consistency. Because of this problem, before 5.6, mysql could not implement group submission with binlog enabled. Redo log and binlog were serialized through a notorious prepare_commit_mutex, and the purpose of serialization was only to ensure the consistency of redo log-Binlog, but this implementation sacrificed performance. This situation is obviously intolerable, so various mysql branches, mariadb,facebook,perconal and so on have issued patches to improve this problem, and the official version 5.6 of mysql has finally solved this problem. As the solutions of each branch version are similar, I mainly explain the implementation method by analyzing the implementation of 5.6.

The basic idea of binlog group commit is to introduce a queue mechanism to ensure that the innodb commit order is consistent with the binlog order, and to group transactions, and the binlog disk brushing action in the group is handed over to a transaction to achieve the purpose of group commit. Binlog commit divides the submission into three phases, the FLUSH phase, the SYNC phase and the COMMIT phase. There is a queue in each stage, and each queue has a mutex protection. It is agreed that the first thread to enter the queue is leader, and the other thread is follower. Everything is left to leader to do. After leader has done all the actions, inform follower that the flushing ends. The basic process of binlog group submission is as follows:

FLUSH stage

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

2) get a set of binlog in the queue (all transactions in the queue)

3) transfer binlog buffer to Ibank O cache

4) notify the dump thread dump binlog

SYNC stage

1) release Lock_log mutex and hold Lock_sync mutex [leader hold, follower wait]

2) set a set of binlog to disk (sync action is the most time-consuming, assuming that sync_binlog is 1)

COMMIT stage

1) release Lock_sync mutex and hold Lock_commit mutex [leader hold, follower wait]

2) traverse the transactions in the queue and innodb commit them 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 its own mutex protection, the queues are sequential, and a thread that is agreed to enter the queue is leader, so the leader of the FLUSH phase may be the follower of the SYNC phase, but the follower is always the follower.

From the above analysis, we know that MYSQL's current group submission approach solves consistency and performance issues. Consistency is resolved through two-phase commit, and disk IO performance is resolved through group commit of redo log and binlog. Below I have sorted out the framework diagrams of the Prepare phase and the Commit phase for your reference.

After reading the above, do you have any further understanding of how to understand MYSQL GroupCommit? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.