Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to deal with synchronization exceptions caused by out-of-order submission of parallel replication in MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Phenomenon description

When parallel replication is enabled in Slave, transactions will be committed out of order by default, which may cause synchronization interruption.

The Slave side shows that the synchronous SQL thread throws an exception, duplicates for the primary key, and the modified data row does not exist.

The GTID message is similar to: 9a2a50aa-5504-11e7-9e59-246e965d93f4:1-1371939844purl 1371939846

Among them, 1371939845 are transactions that reported errors. Intuitively, the Slave side submitted 1371939846 transactions first.

Solution.

MySQLversion > = 5.7.5

Slave_preserve_commit_order:OFF (default)-> ON

Note: binlog_order_commits=ON (default)

Analysis of problems

Refer to the official WL#6314 and WL#7165, here is a simple summary of the original content, interested can take a look at the original High Level Architecture

WL#6314: https://dev.mysql.com/worklog/task/?id=6314

WL#7165: https://dev.mysql.com/worklog/task/?id=7165

Note: commit-parent transaction in the original English text, sequence number refers to last_commited and sequence_number; in binlog, that is, "logical timestamp" in simple translation.

WL#6314 's parallel applier on the Slave end

When transactions enter the prepare phase (a certain stage of the group commit process), they all get a logical timestamp that marks which transaction was recently committed

On the masterside, the process is as follows:

In the prepare phase, the timestamp is obtained from the commit_clock and stored to mark the newly committed transaction

During the commit phase (the transaction has been written to the binlog, but before the engine layer commits), perform a step operation on the commit_clock

On the Slave side, the process is as follows:

The coordinate thread reads the event of the relaylog. If these event have the same logical last_commited, then the event can be executed in parallel by the worker.

The parallelism optimization of parallel replication in WL#7165 refers to the description of WL#6314. Although parallel replication has been implemented, it has not reached the expected level.

For example: the following figure represents the execution sequence and timeline of each transaction, where P represents the prepare phase of a single transaction, in which the commit_clock timestamp is obtained, and C represents the binlog writing phase of the transaction, where the commit_clock is stepped.

As shown in the figure above, the P phase of Trx1, Trx2, and Trx3 all get the same last_ commite value (say 1), so the three transactions can be executed in parallel on the Slave side; similarly, Trx4 cannot and

< Trx1, Trx2, Trx3 >

Play back in parallel together, because of the P phase of Trx4, the last_ commite value obtained is the value after the Trx1 execution step (becomes 2 after the step)

According to WL#6314 's logic, the Slave side can find that the seven transactions are divided into four transaction groups, which are

< Trx1, Trx2, Trx3 >

< Trx4 >

< Trx5, Trx6 >

< Trx7 >

But it is important to note that for different transaction groups

< Trx4 >

And

< Trx5, Trx6 >

Can be executed concurrently, because from the timeline,

< Trx4 >

And

< Trx5, Trx6 >

The prepare phases overlap on the timeline, which means that there is no lock conflict between the two sets of transactions, so they can be executed in parallel in Slave

Optimization of parallelism

Improved parallel replication uses locks to determine whether concurrency can occur.

The basic logic is as follows:

L represents the beginning of the lock phase, and C represents the end of the lock phase.

Because the lock phases of Trx1 and Trx2 in A coincide and there is no conflict, it shows that Trx1 and Trx2 can be executed in parallel, but B can not, because the lock phases of Trx1 and Trx2 do not coincide, so it is impossible to confirm whether they can be executed in parallel (no additional judgment is made, it is directly regarded as non-parallel processing, saving performance overhead)

With regard to the judgment of lock phase, it is clearly stated in WL that there is no lock analysis, but some phases of transaction commit are directly used as the time points for locking and releasing locks (from the point of view of transaction commit phase, there is no problem).

Assume that all locks have been released before committing to the storage engine layer (point in time at the end of the lock phase)

Suppose that at the beginning of the prepare phase, all the required locks have been acquired (the point in time at which the lock phase begins)

In the binlog of MySQL, the tag L refers to is last_commited, and the tag C refers to is sequence_number

With regard to last_commited and sequence_number, WL#7165 describes as follows

Before the transaction enters the flush phase, the value of transaction.sequence_number-> is displayed as sequence_number

The value of global.max_committed_transaction is modified before the transaction enters the engine layer commit

= max (global.max_committed_timestamp, transaction.sequence_number)

= transaction.sequence_number (if binlog_order_commits uses the default value ON)

Therefore, when deciding whether SQL can be executed concurrently, the Slave side refers to the following principles:

-

Slave can execute a transactionifthe smallest sequence_number

Among all executing transactions is greater than transaction.last_committed.

-

Pseudocode is more intuitive:

-

Slave logic:

-before scheduler pushes the transaction for execution: wait until transaction_sequence [0] .sequence _ number > transaction.last_committed

-

So after using lock-based parallelism optimization, it is possible to make WL#6314 's

< Trx4 >

And

< Trx5, Trx6 >

Concurrent execution

Failure scenario restoration

The wrong transaction reported by Slave is 1371939845. The content of binlog is as follows, and the transaction is missing 1371939845.

The sequence of transactions on Master is as follows:

Referring to the format of WL#6314, the transaction sequence diagram is drawn according to the transaction sequence of Master. GTID, last_commited and sequence_number all use the last two digits as marks.

Because Slave is committed out of order, these transactions do not appear in the binlog of Slave strictly in the order in which GTID is incremented.

According to the description of WL#7165, it can be concluded that on Slave, when the execution of Trx41 is completed, Slave believes that Trx46 and Trx47 can already be scheduled by coordinate.

< Trx42, Trx43, Trx44, Trx45 >

It is executed in parallel, but there is a business order between Trx45 and Trx46 and Trx47 (and there is indeed a lock conflict), so the Trx46 executed first deletes the data needed by Trx45, resulting in a synchronization interruption

PS: if Trx45 and Trx46 have lock conflicts, why did Trx46 get 84 as last_commited instead of 88?

Refer to the pseudo code of WL#7165

-

When@@global.binlog_order_commitsistrue,inprinciple we could reduce the max

To an assignment:

Global.max_committed_transaction=transaction.sequence_number

-

MySQL-5.7.21 source code:

MYSQL_BIN_LOG::ordered_commit-- >

Process_commit_stage_queue-- >

Update_max_committed

-

Therefore, it is speculated that the main library is the following scenario: as a transaction group, the sequence_number will be incremented before entering the commit phase of the storage engine, rather than adding it all at once.

So when Trx46 enters the prepare phase, Trx41 completes the commit phase, so it gets 84 instead of 88. Although the official description is that it will reach the final consistent state, there will be a temporary inconsistency in the synchronization process, which is described as "GAP".

This is the end of the article on "how to deal with synchronization exceptions caused by parallel replication and out-of-order submission in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.