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

Concurrent replication (Parallel Replication) series I: Binary Log Group Commit

Author: Ma Pengfei, MySQL database engineer of Walk Technology.

MySQL Binary log launched the scaffolding mainly used for active and standby replication after MySQL version 5.1. Let's review how MySQL works when enabling / disabling Binary Log.

When MySQL does not enable Binary log:

The InnoDB storage engine can safe crash recovery the database through redo and undo logs. When the data is crash recovery, all transactions that have been committed within the storage engine will be recovered by redo log through redo logs. All transactions that have already prepared but do not have commit will use undo log as roll back. Then when the client connects, you can see that the committed data is stored in the database, and the uncommitted rolled back data needs to be reexecuted.

MySQL: when Binary log is enabled:

In order to ensure that the binary logs in the upper layer of the storage engine and the MySQL database are consistent (because the standby database replays the transactions committed by the primary database through the binary logs, and assuming that the primary database storage engine has been committed but the binary logs are not consistent, it will cause inconsistency between the master and backup data due to the data loss of the standby database), two-phase commit (two phase commit or 2pc) is introduced.

Figure 1 two-phase submission

MySQL two-stage submission process:

Storage Engine (InnoDB) transaction prepare phase: that is, the sql statement has been successfully executed and the memory logs for redo and undo have been generated

Binary log log submission

Write () writes binary log memory log data to the file system cache

Fsync () permanently writes binary log file system cache log data to disk

Storage Engine (InnoDB) internal submission

The commit phase commits within the storage engine (innodb_flush_log_at_trx_commit control) so that undo and redo are permanently written to disk

When the MySQL of Binary log is enabled, when crash recovery:

When the transaction is in the prepare phase crash, the database recovery, the transaction is not written to Binary log and the storage engine is not committed, the transaction is roll back.

When the transaction has fsync () permanently written to the binary log in the Binary log log, but the storage engine does not have time to commit, the MySQL database recovery will obtain the committed information from the Xid of the binary log (MySQL database internal distributed transaction XA) and redo the transaction and commit to keep the storage engine consistent with the binary log all the time.

The two-phase commit process of a single transaction mentioned above can ensure that the storage engine and the binary log log are consistent, but in the case of concurrency, how to ensure that the storage engine and Binary Log commit in the same order? When multiple transactions are committed concurrently, what is the impact if the Binary Log and the storage engine are not in the same order?

Figure 2 the order of submission by the InnoDB storage engine is different from that of the binary logs in the upper layer of MySQL

As shown in the figure above: the transaction starts in T1, T2, T3 order, writes the binary logs (in T1, T2, T3 order) to the log file system cache, and calls fsync () to do a group commit to permanently write the log files to disk, but the storage engine commits them in T2, T3, T1 order. When T2 and T3 commit a transaction and create a new backup program of On-line to do replication, transaction T1 finds that it is not committed in the storage engine when it is in the slave machine restore MySQL database, and the T1 transaction is roll back, and the master and backup data are inconsistent (when building Slave, the log offset of change master to records T3 after the transaction location).

Conclusion: the writing order of the upper binary log of MySQL database is the same as the transaction commit order of InnoDB layer of storage engine, which is used for backup and recovery, such as xtrabackup and innobackpex tools.

In order to solve the above problems, in the early version of MySQL, the transaction commit order of the upper binary log of MySQ database and the Innodb storage engine layer was ensured by prepare_commit_mutex lock.

Figure 3 ensures that the storage engine and binary logs are submitted in the same order through prepare_commit_mutex

Figure 3 shows that in prepare_commit_mutex, the next transaction can perform prepara operations only when the lock is released after the previous transaction commit, and Binary log does not have a call to fsync () in each transaction procedure. Because of the high overhead of writing memory data to disk, the performance of writing log data permanently to disk database will degrade sharply if fsync () is frequent. At this point, the MySQL database provides the sync_binlog parameter to set how many binlog logs are generated. Call fsync () once to flush the binary logs to disk to improve the overall performance. The setting effect of this parameter:

Sync_binlog=0, the operation of binary fsync () is based on the operating system.

Sync_binlog=1, every transaction commit calls fsync () once, which ensures the most secure data but has a big performance impact.

Sync_binlog=N, at least 1 transactions will be lost when the database crash.

Figure 3 shows that when MySQL turns on Binary log, it uses prepare_commit_mutex and sync_log to ensure that the binary log is in the same order as the storage engine (the refresh frequency of the log is controlled by sync_binlog). The lock machine of prepare_commit_mutex has very poor performance when it is made to commit transactions with high concurrency, and the binary log cannot be group commit.

So how to ensure that after MySQL opens the Binary Log log, the writing order of the binary log is consistent with the order submitted by the storage engine, and the Group Commit of the binary log can be performed?

MySQL 5.6introduces BLGC (Binary Log Group Commit), and the binary log submission process is divided into three stages, Flush stage, Sync stage, and Commit stage.

Then the transaction commit process is simplified as follows:

Storage engine (InnoDB) Prepare-> database upper layer (Binary Log) Flush Stage-> Sync Stage-> tune storage engine (InnoDB) Commit stage.

Each stage phase has its own queue so that transactions for each session are queued. When a thread registers an empty queue, the thread is treated as the leader of the queue, and the thread that later registers to the queue is follower,leader to control the behavior of the follower in the queue. Leader simultaneously leads all the follower of the current queue to the next stage to execute. When the next stage is not an empty queue, the leader can become follower to this queue. (note: the thread of follower cannot become leader)

Figure 4: binary log three-phase commit process

In Flush stage: all registered threads will be written to the binary log cache

The data cached in Sync stage: binary log will be sync to disk, and the binary log cache of all queue transactions will be permanently written to disk when sync_binlog=1 occurs.

In Commit stage:leader, the storage engine is called sequentially to commit the transaction.

When a set of transactions is in the Commit phase, other new transactions can go through the Flush phase so that the group commit continues to take effect. Then in order to increase the number of transactions in a set of queues in group commit, MySQL uses binlog_max_flush_queue_time to control the waiting time in Flush stage, and makes Flush queues wait a little longer at this stage to increase the number of transaction queues in this group so that the queue can fysn () more transactions at a time to the Sync phase.

MySQL 5.7Parallel replication implements active and standby multithreaded replication based on the main library Binary Log Group Commit, and identifies the last_commited=N of the same group of transactions and the commit order of all transactions within the same group of transactions in the Binary log log. In order to increase the number of transactions within a set of transactions and increase the concurrency when the standby group commits, the parameters binlog_group_commit_sync_delay=N and binlog_group_commit_sync_no_delay_count=N are introduced. MySQL waits for binlog_group_commit_sync_delay milliseconds until the number of binlog_group_commit_sync_no_delay_count transactions is reached. The parameters binlog_group_commit_sync_delay=N and binlog_group_commit_sync_no_delay_count=N (note: MySQL version 5.7.9 and later are no longer effective) will perform a group commit.

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.