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

I. phenomenon

When an index is added to an online table in the early morning, the amount of data on the table is too large (100 million + data, more than 50 gigabytes of data), resulting in a master-slave delay of several hours, and each system relying on the slave database is unable to query data, which ultimately affects the business.

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

Second, principle

According to the description in the MySQL official document MySQL Replication Implementation Details, MySQL master-slave replication depends on three threads: master one thread (Binlog dump thread) and slave two threads (Binlog dump thread O thread and SQL thread). The master-slave replication process is shown below:

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

A Binlog dump thread corresponds to a slave server; Binlog dump thread adds a lock when it acquires data from bin log, and releases the lock as soon as it gets the data.

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

I thread reads events from master and stores them in the relay log of the slave server in a pull way; SQL thread reads events from relay log and executes them; slave can read and update data at its own pace, or manipulate the replication process (start and stop) at will.

Note: after the START_SLAVE command successfully starts the thread, if the subsequent Imax O thread or SQL thread stops for some reason, there will be no warning and the business side will not be aware of it. You can view the thread status on the slave by looking at the error log of slave or through SHOW SLAVE STATUS.

You can view the thread status through SHOW PROCESSLIST:

Binlog dump thread:

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

I take O thread and SQL thread:

Mysql > SHOW PROCESSLIST\ gateway * 1. Row * * Id: 10 User: system user Host: db: NULLCommand: Connect Time: 11 State: Waiting for master to send event Info: NULL * * 2 .row * * Id: 11 User: system user Host: db: NULLCommand: Connect Time: 11 State: Has read all relay log Waiting for the slave I/O thread to update it Info: NULL

III. Analysis

According to the above principle, because slave reads data with single thread, SQL thread updates data, and master writes with multiple threads, master-slave delay may occur as long as the frequency of master writes is greater than that of slave read updates, such as:

Master writes tps higher than slave update speed; slave takes longer to execute some statements, such as holding locks; master takes longer to execute some DDL statements, and it takes the same time to execute slave.

The index is created here and DBA is consulted. The resulting bin log file has more than 100G, and the amount of data is too large, which causes the bin log event generated by the DDL operation to be read from the database I _ thread O thread, which affects the update of the normal business DML event, thus showing the delay of master-slave synchronization.

IV. Solution

From the perspective of master-slave delay, the solution can start in the following directions:

For business selection, for the architecture that cannot bear the slave delay, you can choose the distributed architecture, avoid the execution time of the slave delay problem, perform online DDL operations on the large table, choose the hardware configuration when the traffic is small in the early morning, upgrade the slave hardware configuration, such as SSD to reduce requests, increase cache layer, and reduce read requests.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.