I wrote this article because there was a database deletion operation before, which needed to delete data in batches. At that time, the deletion speed was not well controlled, resulting in master/slave delay and a minor accident.

Today we’ll take a look at why master-slave delays occur and how to deal with them.

Sit tight and get ready to go!

Note: Mind mapping

Common master/slave architectures

With the increasing number of visits, the capacity of a single database has been stretched. Hence the master-slave architecture of separating read and write data from the master library and write data from the master library was derived.

There are many common master-slave architectures in a production environment, and here are some common architectural patterns.

Principle of master-slave replication

Now that you know the basic master/slave architecture and related configurations, it’s time to get down to business.

For master and slave, the usual operation is that the master library is used to write data and the slave library is used to read data. The benefit is that by spreading out the read-write stress, you avoid having all the requests hit the main library. At the same time, the system’s scalability and load capacity are greatly improved by horizontal expansion from the library.

The problem, however, is that the data read from the slave must be the same as the data read from the master, which requires that the data from the master be synchronized to the slave after being written. How to keep data consistency between master and slave libraries, and how to synchronize data from master to slave in real time?

The basic principle of

There are two important log files for master/slave replication in Mysql:

  • Binary log file

  • Relay log file

During a master-slave synchronization, the master library records all operation events in the binlog. The slave library keeps communication with the master library by starting an I/O thread and detects whether the binlog file has changed at certain intervals. If the binlog log changes, the master library generates a binlog dump thread to send the binlog to the slave LIBRARY I/O thread. Copy the binlog from the I/O thread on the library to its own relay log. Events in the relay log are eventually read from the SQL thread in the slave library and replaced on the slave library.

Primary/secondary delay cause

We have already seen the process of master/slave replication, but when the master database has updates, it will synchronize the slave database. Why does the master/slave delay occur?

Random playback

The main Mysql library writes to binlogs sequentially. As we mentioned earlier, sequential reads and writes to disks are fast. Similarly, the speed and efficiency of operating logs from the I/O thread in the library is also high. But don’t forget that there is also an SQL thread for the data replay, and the replay is written randomly. At some point, the data in the relay log cannot be reloaded into the slave, so there is a master-slave delay.

The primary library has high concurrency

Knowing the replay status of SQL threads in the slave library, it is not difficult to understand why high concurrency in the master library causes master-slave delays. At some point, a large number of write requests hit the master library, meaning that the binlog is constantly being written, and the SQL threads in the slave library become overwhelmed, naturally causing master-slave latency.

Lock wait

For SQL single threads, when blocked, they wait until execution succeeds before proceeding. If at some point the slave database has a lock wait due to a query, the following operations will not take place until the current operation has completed, which also leads to a master slave delay.

Master/slave delay processing

Now that we know the reason for the master-slave delay, let’s look at how to handle it.

The parallel copying

Since the speed of a single thread of SQL replay is limited, can we use a multi-threaded way to replay? MySQL 5.6 provides a parallel replication approach by converting SQL threads into multiple work threads for replay, which solves the master-slave delay problem.

Reduce primary library concurrency

You may say, I am using a lower version of the database, also can not upgrade ah, so how can I adjust. In the case of high concurrency in the main library, this way you can only handle the delay by controlling the concurrency. Use Redis instead.

Read the main library

This situation is certainly not strange to you, for some real time requirements are relatively high data, you can not read from the library to take it, in case of delay a big half a day, you can not contribute their year-end bonus ah.

conclusion

Principle of master-slave replication

  • There are two important log files in master-slave replication, binlog and relay log, located in the master and slave repositories respectively. The binlog is the basis of master/slave replication. The operation events are written to the binlog and sent to the slave library through the I/O thread for synchronization.

Primary/secondary delay cause

  • The process of replaying SQL threads from the library is written randomly, and SQL threads are single-threaded, so there is a master-slave delay if the data is not replayed.

  • High concurrency in the master database can result in continuous write operations to the binlog, which can be overwhelmed by SQL threads and lead to master/slave latency.

  • The delay is also caused by lock waiting during replay.

Master/slave delay processing

  • MySQL 5.6 uses parallel replication to solve the master-slave delay caused by SQL single threads. For earlier versions, this can be done by reducing the concurrency of the main library. If the requirements for real-time data are more strict, you can read the main library to achieve this goal.

About the author

Author: Hello, I’m Lewu, a brick remover from BAT. From a small company to a big factory, I have learned a lot along the way. I want to share these experiences with people in need, so I created a public account [MIGRANT workers in IT industry]. At the same time, I have given you a Redis manual, which can be obtained by replying to [PDF] in my official account. I hope it will be helpful to you.