Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to solve the problem of master-slave delay in MySQL. The content of the article is carefully selected and edited by the author. It has a certain pertinence and is of great significance to everyone's reference. Let's work with the author to understand how to solve the problem of master-slave delay in MySQL.

In the face of throwing out such a problem, let's first understand what are the master-slave structures in production? Online production environment generally has one master and one slave, one master and multiple slaves, multiple masters and one cluster (cascade replication, only after MySQL5.7), master master replication. The master-slave architecture exists for failover and read-write separation. Its principle is shown in the following figure:

The primary CVM has a worker thread io dump thread

There are two worker threads from the CVM, one is io thread and the other is sql thread.

The main library records the SQL requests received by the outside world in its own binlog log.

Io thread requests the binlog log of the master database and writes the obtained binlog log to its own relay log (Relay Log) file; the master library transmits the binlog log to the slave library io thread through io dump thread.

You can see that the commit of transactions on the master library is in concurrent mode, while the slave library has only one sql thread to work. This unfair treatment, you say, can not be delayed.

The important reasons for the delay are as follows:

1. First of all, the master library can be written concurrently, and the slave library can only complete the task through a single sql thread (before MySQL5.7)

2. The synchronization between MySQL master and slave is not always synchronous, but asynchronous synchronization, that is, after the master library commits the transaction, the slave library will execute it again.

3. Perform delete or update operations on columns without indexed large tables on the main library

4. The hardware configuration of slave library is not as good as that of master library, and the importance of slave library is often ignored.

5. Network problem

The solution is as follows:

1. With the MySQL5.7 version, parallel replication based on group commit was introduced in 5. 7, setting the parameters slave_parallel_workers > 0 and slave_parallel_type='LOGICAL_CLOCK'.

MySQL 5.7 can be called true parallel replication. The main reason is that the playback of slave CVM is consistent with that of the host. That is to say, how parallel execution is performed on the main cloud server and how parallel playback is carried out from the library. There is no longer the restriction on parallel replication of libraries in the MySQL5.6 version.

two。 We can use the percona-xtradb-cluster referred to as PXC architecture of percona company, which can achieve multi-node write and achieve synchronization all the time. Please refer to the PXC of Lao Zhang's MySQL High availability Architecture Trilogy.

Link address: http://sumongodb.blog.51cto.com/4979448/1956086

3. When planning at the initial stage of the business, we should choose the appropriate sub-database and sub-table strategy to avoid a single table or a single database is too large. Put extra replication pressure on it. This leads to the problem of master-slave delay.

4. Avoid some useless IO consumption, can be on high-speed disk, SSD or PCIE-SSD devices.

5. Array level to choose RAID10,raid cache strategy to use WB definitely not WT.

6. Deadline mode is selected for IO scheduling.

7. Resize buffer pool appropriately

8. Avoid doing a lot of operations in the database, keep in mind that the database is only used to store data, let the application side share more pressure, or it can be done through caching.

After reading the above about how to solve the problem of MySQL master-slave delay, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.