Detailed analysis of MySQL master-slave delay phenomenon and principle

1. Phenomenon

In the early morning, an index was added to an online table. The amount of data in the table was too large (more than 100 million data, more than 50G of data), causing a delay of several hours between the master and slave. The systems that depended on the slave database were unable to query the data, ultimately affecting the business.

Now let's sort out the principle of master-slave delay.

2. Principle

According to the description in the MySQL official document MySQL Replication Implementation Details, MySQL master-slave replication relies on three threads: one thread for master ( Binlog dump thread ) and two threads slave ( I/O thread and SQL thread ). The master-slave replication process is as follows:

When the master server and the slave server are connected, create Binlog dump thread to send bin log data:

• One Binlog dump thread corresponds to one slave server;
• Binlog dump thread will lock the data when getting it from bin log and release the lock immediately after getting the data.

When the slave server receives the START_SLAVE command, it creates I/O thread and SQL thread :

• I/O thread reads events from the master in a pull manner and stores them in relay log of the slave server;
• SQL thread reads events from relay log and executes them;
• slave can read and update data at its own pace and can also operate the replication process (start and stop) at will.

Note: After the START_SLAVE command successfully starts the thread, if the subsequent I/O thread or SQL thread stops for some reason, there will be no warning and the business party will not be aware of it. You can view the slave's error log or the SHOW SLAVE STATUS command to view the thread status on the slave.

You can view the thread status through SHOW PROCESSLIST:

Binlog dump thread:

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
  Id: 2
 User: root
 Host: localhost:32931
  db: NULL
Command: Binlog Dump
 Time: 94
 State: Has sent all binlog to slave; waiting for binlog to
   be updated
 Info: NULL

I/O thread and SQL thread:

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
  Id: 10
 User: system user
 Host:
  db: NULL
Command: Connect
 Time: 11
 State: Waiting for master to send event
 Info: NULL
 *************************** 2. row ***************************
  Id: 11
 User: system user
 Host:
  db: NULL
Command: Connect
 Time: 11
 State: Has read all relay logs; waiting for the slave I/O
   thread to update it
 Info: NULL

Analysis

According to the above principle, since slave reads data with a single thread ( I/O thread ) and updates data with a single thread ( SQL thread ), and master writes data with multiple threads, as long as master writes more frequently than slave reads and updates, there may be a master-slave delay, such as:

1. master write tps is higher, greater than slave update speed;
2. slave takes a long time to execute certain statements, such as holding locks;
3. When master executes certain DDL statements, the execution time is longer, and slave also executes them in the same amount of time;

An index is created here. After consulting the DBA, it is found that the generated bin log file is over 100 GB. The data volume is too large, causing the slave library I/O thread to keep reading the bin log events generated by DDL operation, which affects the update of normal business DML events, resulting in master-slave synchronization delay.

4. Solution

From the perspective of the causes of master-slave delay, the solution can be started from the following directions:

1. Business selection: For architectures that cannot tolerate slave database delays, you can choose a distributed architecture to avoid slave database delay issues.
2. Execution time: For online DDL operations on large tables, try to choose early morning hours when the business volume is low.
3. Hardware configuration, upgrade the slave hardware configuration, such as SSD
4. Reduce requests, increase cache layers, and reduce read request drops

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links