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

This article mainly explains "the scheme of MySQL master-slave synchronous acceleration". 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 the "MySQL master-slave synchronous acceleration scheme"!

I. the origin of the problem

There has always been a delay in master-slave synchronization in MySQL. There is a lot of background information on the Internet. The reasons are briefly described as follows:

1. MySQL has an IO thread on the library responsible for fetching binlog from the main library to writing to the local. Another SQL thread is responsible for executing these local logs to achieve command playback.

2. There is no performance problem with IO threads under normal network conditions (this will be used later). The problem is that there is only one SQL thread and the update speed can not keep up. So you will often see that the CPU idle of the slave library is very high, but the synchronization performance just can not go up.

II. The embryonic form of the plan

Single-threaded SQL threads are the main cause of this problem. The more direct idea is to change it to a multi-threaded version. It is said that in the development of the official version, we also have such a patch, but writing large pieces of code directly on slave machines that provide services online will be very difficult to promote because of concerns about stability (students who write patch and students of operation and maintenance, you know).

So I intend to use a "third-party" tool to achieve multi-thread synchronization. The basic structure is as follows:

Description:

1. These transefer synchronize part of the data from the master and update the slave independently. Multi-process or multi-thread is fine.

2. Update log asynchronously between Transfer and master, and update data synchronously between transfer and slve

3. One of the disadvantages of this scheme can be seen from this: updates can be separated independently. The more intuitive idea is to score according to the table.

III. About transfer

As this key forwarding tool, transfer needs to provide the following functions:

1. You can specify which part of the data in the master is synchronized, and you can easily modify this configuration to meet the table addition requirements of master.

2. Stop slave and start slave are supported. Supports change master commands that quickly switch to the new main library.

3. Be able to record the read point. After transfer restarts itself or master restarts, it can continue to read the following binlog according to the record point.

4. Be able to record the distribution point. After transfer restarts itself or slave restarts, it can continue to synchronize to slave according to the recording point.

When you use it, you will find that there are a lot of requirements.

IV. Realization of the scheme

With so many functions of Transfer, it's tiring to build your own wheels. MySQL is used directly to play this role here. For ease of description, it is also referred to as transfer below. Transfer update slave can use federated engine functionally, but its performance can not meet the requirements due to its tangled implementation, so a little modification is made in the MySQL framework layer-after reading the synchronization log, it is sent directly to slave.

The scheme is briefly described as follows:

1. Set up several other MySQL (transfer) on the Slave machine, set them as the slave library of Master, and set replicate-do-table, each transfer bears part of the table.

2. The update target of all Transfer is set to slave, and the update method is to read the log and execute the _ real_query directly on the slave.

From this, we can see the second disadvantage of this scheme: it can only support synchronization in statement format. In fact, row can also support it, we'll talk about it later.

Still delayed?

After transfer abandoned the federated engine to send directly, the performance improved a lot, and the performance of synchronization from the library doubled, but from the data comparison of the first figure in this article, we can see that the delay is still large.

It is found that slave's machine cpu is already very busy at this time, idle 20%-- this is good news, which is better than idle with high performance.

In fact, it is because each transfer is set to synchronize only some of the tables, but in implementation, the IO thread backs up all the commands on the master locally, and then determines when the SQL thread executes, and then gives up if it does not conform to the replicate-do-table.

The problem is that there are n transfer, the disk is written n times, and what is more serious is that it causes SQL threads to idle.

We mentioned earlier that the IO thread is relatively idle in the whole process, so modify the IO thread logic and judge before writing to disk. If it does not meet the replicate-do-table setting of this transfer, do not write disk, just give up.

VI. Effect

The QPS of the slave library will jitter due to thread switching, but the total execution time is the same as that of the master library. The cpu idle of the slave library decreased and recovered to 100 almost at the same time as the main library.

VII. Summary

After describing it, let's sum up the cost of the solution:

1. It is required to configure more than n transfer on the slave machine (whether it can be on the slave library)

2. Currently, it can only support statement's binlog format. In fact, row can support it. The plan has been decided, and it is in the development plan.

3. Cross-table update statements will be distributed to the only transfer according to the first table they updated. There is no problem of repeated updates, but sometimes there are sequential problems.

Benefits of the scheme:

1. The functions are relatively complete. Using MySQL directly, the original management functions can be used basically, and the cost of restarting / replacing the master slave library is relatively small.

2. The amount of development is small. There are only two modifications on the transfer, excluding the configuration read part, which is less than 300 lines.

3. The risk is relatively small. It is easier to receive online without directly modifying the code on master and slve.

At this point, I believe you have a deeper understanding of the "MySQL master-slave synchronous acceleration scheme". You might as well do it in practice. 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.