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

This article mainly introduces "how to solve the problem that the main library in MySQL runs too fast to catch up with the library". In the daily operation, I believe many people have doubts about how to solve the problem that the main library in MySQL runs too fast to catch up with the library. The editor consulted all kinds of materials and sorted out simple and useful methods of operation. I hope it will be helpful to solve the problem that the main library in MySQL runs too fast to catch up with the library. Next, please follow the editor to study!

-mind map-

Master-slave common architecture

With the increasing number of visits, the capacity of a single database has been stretched. Therefore, the master database is used to write data, and the master-slave architecture that separates reading and writing from the database is derived.

In the production environment, there are many kinds of common master-slave architecture. Here I will introduce several common architecture patterns.

Principle of master-slave replication

After understanding the basic structure and related configuration of the master and slave, let's 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 advantage is that by dispersing the read and write pressure, all requests are prevented from being placed on the main library. At the same time, through the horizontal expansion from the library, the scalability and load capacity of the system have been greatly improved.

But the problem is that if the data from the slave library is to be consistent with that of the master database, the data from the master library needs to be synchronized to the slave library after being written. How to keep the data consistency between the master database and the slave database, and in what way does the master library synchronize the data to the slave database in real time?

Basic principles

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

Binlog (binary log file)

Relay log (Relay Log File)

In the process of master-slave synchronization, the master database records all operation events in binlog, and the slave library keeps communication with the master database by opening an Ibind O thread, and detects whether the binlog log file has changed within a certain time interval. If the binlog log changes, the master library generates a binlog dump thread to pass the binlog to the slave library I _ UBO thread. Copy the binlog to its own relay log from the Ibank O thread on the library. Events in the relay log are eventually read from the SQL thread in the library and replayed on the slave library.

Master-slave delay reason

We already know the process of master-slave replication in the above process, but if there is an update in the master library, it will synchronize the slave database, so why is there a master-slave delay?

Random playback

The operation of writing binlog in the Mysql main library is written sequentially, and as we mentioned earlier, the sequential read and write speed of the disk is very fast. Similarly, the speed efficiency of manipulating the log from the Imax O thread in the library is also very efficient. But don't forget that there is also a SQL thread for data playback, and the playback process is random. At this point, you should understand that at some point, there will be a master-slave delay when the data in the relay log can not be reloaded into the slave database.

High concurrency of main library

Knowing the replay of the SQL threads in the slave library, it is certainly not difficult to understand the master-slave delay caused by high concurrency in the master library. At some point, a large number of write requests to the master library means that the binlog will be constantly written, and the SQL threads in the slave library will be overwhelmed, resulting in a master-slave delay.

Lock waiting

In the case of a SQL single thread, it waits when it encounters a block and does not proceed until the execution is successful. If at some point the slave database has a lock wait because of a query, the following operation will not be performed until the current operation is completed, which in the same way leads to master-slave delay.

Master-slave delay processing

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

Parallel replication

Since the speed of SQL single-threaded playback is limited, can it be replayed in a multithreaded way? After MySQL version 5.6, a way of parallel replication is provided, which solves the problem of master-slave delay by converting SQL threads into multiple work threads for playback.

Reduce the concurrency of main library

You may say, I am now using a lower version of the database, but also can not upgrade the version, ah, then how do I do it? For the case of high concurrency in the main library, you can only solve the delay by controlling concurrency and use Redis more often.

Read the main library

You must be no stranger to this situation. For some data with high real-time requirements, you can't read it from the database. In case of a delay of more than half a day, you are not allowed to contribute your year-end bonus.

Summarize the principle of master-slave replication

There are two important log files in master-slave replication, binlog and relay log, which are located in the master library and slave library, respectively. Binlog is the basis of master-slave replication, which is synchronized by writing operation events to binlog and transmitting them to the slave library through the Icano thread.

Master-slave delay reason

The process of replaying the SQL thread from the library is random, and the SQL thread is single-threaded, so there is a master-slave delay if the data is not replayed in time.

The high concurrency of the master library will cause write operations to continue to write to binlog, which may be overwhelmed by SQL threads and lead to master-slave delays.

Lock waiting during playback is also one of the reasons for the delay.

Master-slave delay processing

After MySQL version 5.6, the master-slave delay caused by SQL single thread is solved by parallel replication. For lower versions, it can be solved by reducing the concurrency of the main library. If the real-time requirements of the data are relatively strict, the goal can be achieved by reading the main library.

At this point, the study on "how to solve the problem that the main library in MySQL is too fast to catch up with the library" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.