Summary of MySQL Master/Slave latency

Subtotal on MySQL Master/Slave latency

I have encountered several MySQL master-slave latency issues recently, and some of my colleagues complained about this. Sort it out a little bit.

------------------------------------ Body ------------------------------------

Two scenarios with problems:

Scenario 1: it takes about 10 minutes for the master database to perform the alter operation. It also takes about 10 minutes to reproduce the alter operation from the slave database, and the delay is increasing;

Scenario 2. A large number of addition, deletion, and modification operations are performed on a MyISAM table in the master database. The slave database's business statements often encounter table locks when operating this table, resulting in slave database latency;

Scenario Problem Analysis:

Scenario 1: in fact, this scenario is very simple. This alter statement, which takes a lot of time to reproduce in the slave database, takes a lot of time to reproduce in the master database, the synchronization will naturally be blocked;

Scenario 2:

First, check the features of MyISAM. MyISAM tables are table-level locks, and read/write mutex,

When the synchronization SQL thread adds, deletes, and modifies data, if a select statement operates on the table, it will generate a table-Level Lock to block the synchronization SQL thread, simulate The following table-Level Lock blocking slave Database SQL threads

In fact, this situation is different from scenario 1 in MyISAM tables, that is, operations on the master database are slow/blocked when the slave database is reproduced, where the problem occurs, the SQL statements are reproduced. For this type of problem, you can only operate these "problematic SQL statements" in idle time, which is a type of DBA operation specification, if this happens during a normal period of time, you can consider detaching a delayed slave database, shielding service requests, and adding the slave database after the delay is eliminated, then, replace the slave database with another latency and follow the master database in sequence;

There is also a type of master-slave latency, that is, the slave Database Synchronization is stuck in writing the binlog part. The typical performance is that the slave database system has a high IO wait. In this case, modify the value of the transaction group and log refresh policy based on the business type, or change the storage to improve hardware capability ~

This article permanently updates the link address: