Solution to MySQL master-slave delay problem

Today we will look at why master-slave delay occurs and how to deal with it.

Sit tight and get ready to go!

Master-slave common architecture

With the increasing number of visits, the response capacity of a single database has become insufficient. Therefore, a master-slave architecture that separates reading and writing was derived, in which the master database writes data and the slave database reads data.

In the production environment, there are many common master-slave architectures. Here are some common architectural patterns.

Master-slave replication principle

After understanding the basic architecture and related configuration of the master-slave, let's get to the point.

For the master-slave relationship, the usual operation is that the master database is used to write data and the slave database is used to read data. The advantage of this is that it disperses the read and write pressure and avoids all requests from being placed on the main database. At the same time, the system's scalability and load capacity have been greatly improved through horizontal expansion from the database.

But the problem arises. To keep the data in the slave database consistent with the master database, the data in the master database needs to be synchronized to the slave database after being written. How to maintain data consistency between the master database and the slave database, and how does the master database synchronize data to the slave database in real time?

Rationale

There are two very important log files in MySQL master-slave replication:

• binlog (binary log file)
• relay log

During the master-slave synchronization process, the master database will record all operation events in the binlog. The slave database maintains communication with the master database by opening an I/O thread and detects whether the binlog log file has changed at a certain time interval. If the binlog log changes, the master library generates a binlog dump thread to transmit the binlog to the slave library I/O thread. The I/O thread on the slave copies the binlog to its own relay log. Eventually, the SQL thread in the slave database reads the events in the relay log and replays them to the slave database.

Causes of master-slave delay

We already know the relevant process of master-slave replication in the above process, but if the master database is updated, the slave database will be synchronized. So why does the master-slave delay occur?

Random Replay

The operation of writing binlog in the MySQL main database is written sequentially. As we mentioned before, the sequential read and write speed of the disk is very fast. Likewise, the speed and efficiency of operating logs from the I/O thread in the library is also very high. But don't forget that there is also a SQL thread to replay the data, and the replay process is random writing to disk. You should understand by now that at a certain point in time, the data in the relay log cannot be replayed into the slave database, which will cause a master-slave delay.

High concurrency of the main database

Knowing the replay status of the SQL thread in the slave database, it is not difficult to understand why the high concurrency of the master database causes the master-slave delay. At a certain moment, a large number of write requests are sent to the master database, which means that binlog needs to be written continuously. At this time, the SQL thread in the slave database will be overwhelmed, and master-slave delay will naturally occur.

Lock Wait

For a single-threaded SQL statement, when a blockage occurs, the statement will wait until the execution succeeds before continuing. If at a certain moment the slave database has a lock waiting situation due to a query, the next operation will be performed only after the current operation is completed. Similarly, a master-slave delay will occur.

Master-slave delay processing

Knowing the cause of the master-slave delay, let's see how to deal with it.

Parallel replication

Since the speed of SQL single-thread replay is limited, can we use multi-threaded replay? MySQL version 5.6 and later provides a parallel replication method that converts the SQL thread into multiple worker threads for replay, thus solving the master-slave delay problem.

Reduce the concurrency of the main database

You may say, I am currently using an older version of the database and I cannot upgrade the version, so what should I do? For situations where the master database has high concurrency, you can only solve the delay by controlling concurrency in this way. Use Redis more often.

Read the main library

You must be familiar with this situation. For some data with high real-time requirements, you can't just read it from the database. If there is a delay of half a day, you will have to contribute to your year-end bonus.

Summarize

Master-slave replication principle

There are two very important log files in master-slave replication, binlog and relay log, which are located in the master library and the slave library respectively. Binlog is the basis of master-slave replication. Operation events are written into binlog and transmitted to the slave database through I/O threads for synchronization.

Causes of master-slave delay

• The replay process of the SQL thread in the slave database is written randomly to disk, and the SQL thread is single-threaded, so if the data cannot be replayed in time, it will cause master-slave delays.
• High concurrency of the master database will cause write operations to be continuously written to the binlog, which may overwhelm the SQL thread and cause master-slave delays.
• Lock waiting during replay is also one of the reasons for delay.

Master-slave delay processing

MySQL version 5.6 and later uses parallel replication to solve the master-slave delay problem caused by SQL single thread. For lower versions, this problem can be solved by reducing the concurrency of the main library. If the real-time requirements for data are strict, you can achieve this goal by reading the main database.

The above is the detailed content of solving the MySQL master-slave delay problem. For more information about MySQL master-slave delay, please pay attention to other related articles on 123WORDPRESS.COM!