A master-slave synchronization Acceleration Solution for MySQL

I. Problem Origin
The master-slave synchronization of MySQL has always encountered the problem of slave database latency. There are many background information on the Internet. The reasons are as follows:
1. There is an IO thread in the MySQL slave database to take the binlog from the master database and write it to the local database. In addition, an SQL thread is responsible for executing these local logs for command Replay;
2. In normal network conditions, I/O threads have no performance problems (this will be used later). The problem is that there is only one SQL thread and the update speed cannot keep up. Therefore, we often see that the CPU idle of the slave database is very high, but the synchronization performance is not high.
 
 
Ii. Prototype
Single-threaded SQL threads are the main cause of this problem. The direct idea is to change it to a multi-threaded version. It is said that in the official version development, we also have such a patch, however, if you directly write a large piece of code on a server Load balancer instance that provides services online, it will be difficult to promote it because of fear of stability (you can understand the patch writing and O & M personnel ).
Therefore, we plan to use a "third-party" tool for multi-thread synchronization. The basic structure is as follows:
 
Note:
1. These transefer synchronize part of data from the master and separately update the slave. Multi-process or multi-thread.
2. logs are asynchronously updated between Transfer and master, and data is synchronously updated between transfer and slve.
3. One of the disadvantages of this solution is that updates can be separated independently. The intuitive idea is to divide data by table.
 
Iii. About transfer
As a key forwarding tool, transfer must provide the following functions:
1. You can specify which part of the data in the master node to be synchronized, and modify this configuration to meet the requirements for adding tables to the master node;
2. Supports stop slave and start slave. Allows you to quickly switch to the change master command of the new master database.
3. Ability to record the reading point. After the transfer restarts itself or the master restarts, it can continue to read the subsequent binlog according to the record point;
4. Ability to record distribution points. After the transfer restarts or the slave restarts, it can continue to sync to the slave according to the record points.
In use, you will find that there are still many requirements...
 
Iv. solution implementation
Transfer is so tired of making its own wheels. MySQL is used as the role. It is also called transfer for convenience. Transfer can use the federated engine to update the slave function. However, due to its tangled implementation, the performance fails to meet the requirements, so I made some modifications at the MySQL framework layer-after reading the synchronization log, send it directly to slave.
The solution is described as follows:
1. Set several other MySQL (transfer) on the server Load balancer to the Slave database of the Master database and set replicate-do-table. Each transfer takes part of the table.
2. All Transfer update targets are set to slave. The update method is to directly execute mysql_real_query to slave after reading the log.
From this we can see that the second disadvantage of this solution: only the statement format synchronization mode is supported. In fact, row can also be supported.
 
5. Still delayed?
After transfer abandoned the federated engine and switched to direct sending, the performance improved a lot. The Database Synchronization performance doubled. However, we can see from the data comparison in the first figure in this article that the latency is still large.
It was found that the cpu of the slave machine was very busy at this time, idle 20%-this is good news, it is always better than idle but the performance is not good.
In fact, although each transfer is set to synchronize only some of the tables, the implementation is that the IO thread backs up all the commands on the master to the local machine, then, when the SQL thread executes the statement, it determines that if the statement does not comply with replicate-do-table, it will give up.
This problem is caused by n transfers, which write n times on the disk. What is more serious is that the SQL thread is idling.
As we mentioned above, the IO thread is relatively idle throughout the process. Therefore, modify the IO thread logic and determine before writing data to the disk. If the change does not conform to the replicate-do-table settings of the current transfer, give up without writing a disk.
Vi. Results

The QPS of the slave database is jittery due to thread switching, but the total execution time is the same as that of the master database. The cpu idle of the slave database is reduced, and it is almost restored to 100 at the same time as the master database.
 
VII. Summary
The description is complete. To sum up, the cost of the solution is as follows:
1. You must configure n more transfers on the server Load balancer instance (whether they are on the slave database or not)
2. Currently, only the statement binlog format is supported. In fact, row is supported. The solution is fixed and is in the development plan.
3. The statement for cross-Table update is distributed to the unique transfer according to the first updated table. There is no duplicate update problem, but there is a timing problem.
 
Benefits:
1. Complete functions. MySQL can be used directly, and the original management functions can basically be used. The cost of restarting/changing the master database from the slave database is relatively small.
2. The development volume is small. It can be modified only on transfer, excluding the configuration reading part and less than 300 lines.
3. Relatively low risks. Without directly modifying the code on the master and slve, it is easier to receive the code online.

Author: "follow-up knife Ding Qi"