Abstract
MySQL parallel replication has been a commonplace. I have been dealing with this problem online since 2010, and I have been happy to share it for the first two or three years. Now it is inevitable to mention this topic again. This topic has been brought up recently because some developers feel that “5.7 parallel replication has finally solved the replication concurrency problem once and for all” and feel that it is necessary to analyze it.
MySQL parallel replication has been a commonplace. I have been dealing with this problem online since 2010, and I have been happy to share it for the first two or three years. Now it is inevitable to mention this topic again.
This topic has been brought up recently because some developers feel that “5.7 parallel replication has finally solved the replication concurrency problem once and for all” and feel that it is necessary to analyze it. Everyone e says there is no silver bullet, but expect one.
Since we are talking about 5.7 parallel replication, I would like to explain the various versions of parallel replication, so as to have a comparison.
[background]
MySQL has been using single thread apply for backup library replication.
【 Basic ideas 】
Let’s change to multi-threaded replication.
The secondary library has two threads associated with replication: IO_thread is responsible for fetching the binlog from the primary library and writing it to relaylog, and SQL_thread is responsible for reading the relaylog and executing it.
The idea behind multithreading is to turn sql_threads into distribution threads, which are then executed by a set of worker_threads.
Almost all parallel replication uses this idea, but the difference is the distribution strategy for SQL_thread.
These strategies fall into two categories: using the traditional binlog format and modifying the binlog.
Classes that use the traditional binlog format, because the information in the binlog is only that, can only be divided by granularity, namely: by library, by table, by row
The other two policies modify the binlog format by adding additional information to the binlog to reflect the submission groups.
Let’s look at several parallel replication implementations.
【 5.5 】
MySQL 5.5 does not support parallel replication. But in ali’s business needs parallel replication years, there is no official version support, had to implement their own. And from the point of view of compatibility, the binlog format is not modified, so we use the traditional binlog format modification.
Ali’s version supports two distribution strategies: by table and by row.
Mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql
Distribute policies by table: binlog in row format, each DML is preceded by a Table_map event. So it’s easy to get the library name/table name. A simple idea is that updates to different tables do not need to be strictly sequential.
Therefore, according to the table name hash, the hash key is the library name + table name, and the update of the same table is placed on the same worker. This ensures that the same table is updated in the same order as in the main library.
Application scenario: The effect is particularly good for multi-table update scenarios. The disadvantage is the reverse. If the hotspot table is updated, this policy is invalid. Moreover, performance degrades due to hash table maintenance.
Distribute policies by rowIf you don’t have a primary key, the answer is “go ahead, who doesn’t have a primary key now :)”. Well, the serious answer is that you can’t support this strategy without a primary key.
Similarly, we assume that updates to different rows can be unordered and concurrent. Just make sure that the same row is updated in the same order on the standby database as on the primary database.
Therefore hash according to the primary key ID, so the hash key is longer and must be the library name + table name + primary key ID. Updates for the same row are placed on the same worker.
Note that the descriptions above appear to be operations on a single event, when in fact they areDoes not! Because the standby may accept reads, the atomicity of the transaction is guaranteed, that is, for transactions involving multiple update operations, not one hash key is used for decisions at a time, but a set of hash keys.
Application scenario: Update the hotspot table. Disadvantages: High cost for hash key calculation conflicts. Especially for large transactions, computing hash keys consumes a lot of CPU and memory. This is a judgment call by the business DBA.
【 5.6 】
Official 5.6 supports distribution by library. Given the above background, we know that this feature has not received much response in China after it was released.
But there are advantages to this strategy:
1. For scenarios that can be distributed by table, this policy can be applied by migrating tables to different libraries, which is operable
2. Faster because the hash key is a single library name
3. Do not require binlog format, as you can easily obtain the library name in either row or statement format.
So it’s not totally useless. It’s a matter of habit.
【 MariaDB 】
MariaDB’s parallel replication strategy appears to have several options, but the default is CONSERVATIVE for production.
Because maraiaDB supports multi-master replication, a domain_id field is used to identify the source of the transaction. If they come from different masters, they can be parallelized (this is a general concept, and it is up to the business DBA to decide).
For binlogs from the same primary library, commit_id is used to determine the grouping.
The idea is that simultaneous transactions committed on the main library are set to the same COMMIT_id. When applied on the standby repository, the same COMMIT_id can be executed in parallel, because this means that there are no row conflicts between these transactions (otherwise it would not be possible to commit simultaneously).
This idea is the same as the original change from single thread to multi-thread, which I personally think is epoch-making.
But it doesn’t solve all the problems. The worst thing about this strategy is that it drags things down.
Imagine a scenario where a large number of small update transactions (such as one update per transaction) are being performed in a DB that is happily parallel in the standby database.
Then all of a sudden, in the same instance, in another library, or in another table in the same library unrelated to the current update, there is a delte operation that deletes 10W rows.
Delete transactions are committed with the same COMMIT_ID as all other transactions committed at that time. Assumptions for N.
Subsequent small transaction update commit_id is SET to N+1.
When we go to standby apply, we find that group N, where all other small transactions have been executed, is idle and the thread cannot continue to execute the commit_id transaction of N+1 because there is still a large transaction in N, which we consider to be a drag.
The above three strategies, based on the traditional binlog format, do not have this problem. Large transactions have a thread running on their own, and other transactions continue in parallel, as long as they are strategically compatible.
【 5.7 】
The official 5.7 release of MySQL followed suit, introducing the aforementioned MariaDB strategy first. Of course, oracle does not allow direct port code for copyright security reasons.
In fact, the strategy of grouping by group is a little rough. Actually the transaction commit is not a point, but a phase. At least we can divide it into: ready to submit, submitting, and submitting completed.
All three phases occur after the transaction has completed its main operation logic and entered the COMMIT state.
Commit_id is the same commit_id that goes to commit at the same time. However, transactions that are “ready to commit” and transactions that are “committed” can actually run in parallel at any time. But obviously they will be split into two different commit_id.
This means that there is room for this strategy to improve concurrency.
Let’s look at the contrast between the two strategies.
Assume that the main library is like the sequence of transactions shown in the diagram below. Each transaction commit process is considered as two phases. Commit. Give different numbers. The number corresponding to commit is the natural increment, sequence_no. Prepare = X+1. The X represents sequence_no that has been committed.
Trx1 1… . 2
Trx2 1………………… 3.
Trx3 1…………………………………… 4.
Trx4 2………………………………………… . 5
Trx5 3……………………………………………………… . 6
Trx6 3……………………………………………………………………………… 7
Trx7 6…………………………………… . 8
Analysis:
In MariaDB’s strategy, the concurrent execution sequence is as follows:
trx1, trx2, trx3 —-group 1
trx4 —–group 2
trx 5, trx6 —-group 3
trx 7 —-group 4
After each group is executed, the next group can be started.
The time to complete execution is the sum of the maximum transaction time for each group, i.e. Trx3 + TRx4 + TRx6 +trx7.
Therefore, if there is a large transaction in a group, the execution time of the entire sequence will be prolonged.
Let’s look at the improvement strategy of 5.7:
Group1 starts first, but trx4 starts after trx1 completes.
Similarly, TRX7 can be executed as soon as TRx4 is complete, concurrent with TRX5 and TRX6.
Therefore, in this example, the standby apply procedure is exactly as concurrent as the primary one.
However, for large transactions, such as trX2 commits that take a long time, there is still a drag issue.
【 summary 】
The three strategies by granularity, coarse to fine, are by library, by table, and by row.
In the comparison of these three, the degree of parallelism is increasing, and so is the extra loss. Irrelevant large transactions do not affect concurrency.
Commit_id provides broader coverage and lower overhead.
The improved strategy of 5.7 has better concurrency. But big things can drag you down.
Also, it is important to note that the purpose of the 5.7 policy is to “simulate master library concurrency”, so there is no acceleration for single-threaded updates to the master library. For the first three strategies based on conflict, if the concurrency condition is satisfied, the secondary database will execute faster than the primary database. This requirement can be triggered in scenarios such as provisioning libraries or lazy replication.
In fact, the choice of policy depends on the application scenario, which is part of the architect’s job.
About Alibaichuan
Baichuan.taobao.com is alibaba Group “cloud” + “end” core strategy is Alibaba Group wireless open platform, based on world-class back-end services and mature business components, through the opening of “technology, business and big data”, Provide mobile entrepreneurs with solutions that can quickly build apps, commercialize apps and improve user experience; At the same time, it provides diversified entrepreneurial services – physical space, incubation operation, venture investment, etc., providing comprehensive guarantee for mobile entrepreneurs.