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

This article mainly explains "what is the method of dealing with MySQL master-slave delay". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the method of dealing with MySQL master-slave delay"?

Why is the master-slave delay so big?

Answer: MySQL uses a single thread to replay RelayLog.

How to optimize and shorten the playback time?

Answer: multithreaded parallel playback of RelayLog can shorten the time.

What's wrong with multithreaded parallel playback of RelayLog?

A: you need to consider how to split the RelayLog so that multiple database instances and multiple threads can replay RelayLog in parallel without inconsistency.

Why are there inconsistencies?

A: if RelayLog is randomly assigned to different playback threads, suppose there are three serial modification records in RelayLog:

Update account set money=100 where uid=58

Update account set money=150 where uid=58

Update account set money=200 where uid=58

If single-threaded serial playback: it can ensure that the execution sequence of all slave libraries is consistent with that of the master library.

Voiceover: the final money will be 200.

If multithreading randomly allocates replay: multiple replay threads execute these three statements concurrently, it is uncertain who will execute them last, and the slave data may be different from the master database in the end.

Voiceover: multiple slave libraries may have an money of 100150200 uncertainty.

How to allocate, multiple multithreaded playback from the library, can also get consistent data?

Answer: write operations on the same library can be replayed with the same thread on different RelayLog; libraries, and RelayLog can be replayed with multiple threads concurrently.

How do you do that?

A: design a hash algorithm, hash (db-name)% thread-num. If the library name hash is followed by the number of threads, it can be easily done. Writes on the same library are executed sequentially by the same replay thread.

Voiceover: playback on different libraries, which is parallel, plays an accelerated role.

What are the shortcomings of this plan?

A: many companies use MySQL as "multiple tables in a single library". If so, there is still only one library, and it still can't improve the playback speed of RelayLog.

Revelation: upgrade the DB schema of "single database and multi-table" to the DB schema of "multi-database and multi-table".

Voiceover: with a large amount of data and a large amount of Internet business scenarios, the "multi-database" model also has many other advantages, such as:

(1) very convenient instance extension: DBA can easily extend different libraries to different instances.

(2) Library isolation according to business: business decoupling, business isolation, reducing coupling and interaction.

(3) it is very convenient to split micro-services: it is convenient for each service to have its own instance.

In the scenario of "single library and multiple tables", how can multithreaded parallel playback RelayLog be optimized?

A: even if there is only one library, transactions are executed concurrently on the master library. Since they can be executed in parallel on the master library, they should also be able to execute in parallel on the slave library.

New idea: the transactions executed in parallel on the master library are divided into a group and numbered, and these transactions can be executed in parallel in the playback on the slave library (transactions on the master database all enter the prepare phase, indicating that there is no conflict between transactions, otherwise it is impossible to commit). That's what MySQL does.

Solution: parallel replication based on GTID.

Starting from MySQL5.7, the information submitted by the group is stored in GTID. Using the mysqlbinlog tool, you can see the internal information of the group submission:

20181014 23:52 server_id 58 XXX GTID last_committed=0 sequence_numer=120181014 23:52 server_id 58 XXX GTID last_committed=0 sequence_numer=220181014 23:52 server_id 58 XXX GTID last_committed=0 sequence_numer=320181014 23:52 server_id 58 XXX GTID last_committed=0 sequence_numer=4

Compared with the original log, there are more last_committed and sequence_number.

What is last_committed?

Answer: it is the number of the last transaction commit when the transaction is committed. If it has the same last_committed, it means that they are in a group and can be replayed and executed simultaneously.

Summary

MySQL parallel replication, the method of shortening the master-slave synchronization delay, embodies some of the following architectural ideas:

Multithreading is a common way to shorten execution time

Voiceover: for example, many crontab can be multithreaded, split data, and execute in parallel.

When dispatching tasks concurrently with multiple threads, idempotency must be guaranteed: MySQL provides two ways: "according to library idempotent" and "according to commit_id idempotent", which is worth using for reference.

Voiceover: for example, group messages can be group_id idempotents; user messages can be user_id idempotents.

Specific to MySQL master-slave synchronization delay:

Mysql5.5: parallel replication is not supported. Upgrade the MySQL version.

Mysql5.6: replicate in parallel according to the library. It is recommended to use the "multi-library" architecture.

Mysql5.7: replicate in parallel by GTID

At this point, I believe that everyone on the "MySQL master-slave delay processing method is what" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.