MySQL Exception Handling log: Analysis of the synchronization latency of the Master/Slave database for too long

Problem description:

ProgramAfter the master database is updated, the data queried from the slave database does not change. It is suspected that the synchronization delay of the Master/Slave database is caused. Check the master-slave synchronization status from the database and find that seconds_behind_master takes more than one thousand seconds. Under normal circumstances, the latency of the Master/Slave database can be tolerated for more than 10 seconds. Is there a problem in over one thousand seconds...

 

Problem Analysis:

We have created four or five databases on a MySQL instance, one of which has a large data volume and pressure, from the processlist of the database, we can see that the slave database often locks when processing logs. But the lock is only a lot of pressure. Why does the database affect the latency of other databases?

 

The original slave database processes synchronization logs in a single thread. That is to say, no matter how many databases are updated through one thread, the synchronization delay of the Master/Slave database is not for the database, is for a MySQL instance.

 

 

So why does the slave database lock occur when processing logs?

 

Generally, we separate the read and write operations from the master database. The master database is responsible for write operations, and the slave database is responsible for read operations. Generally, Data Reading operations on web applications are much larger than Data Writing. Therefore, the lock caused by data update is almost invisible on the master database. So how does the slave database lock occur?

 

Read operations on the MyISAM table (with a read lock) will not block read requests from other processes to the same table, but will block write requests to the same table. Write operations of other processes are performed only after the read lock is released. <Br/> write operations on the MyISAM table (with a write lock) will block read and write operations on the same table by other processes. Only when the write lock is released, to read and write other processes. 

As can be seen from the above, we will block write requests by default during select. When the data volume of a table reaches tens of millions, it is very likely that it will be difficult to execute a SELECT statement, A certain amount of pressure is added to the Select Operation. Although the read data is not affected, the operation of synchronizing logs from the slave database is blocked. Long term... You can imagine...

 

Troubleshooting:

1. First, do not create too many databases for a MySQL instance. Otherwise, once one of the databases is often locked due to high pressure, synchronization of all databases will be delayed. You cannot afford to hurt...

2. When the pressure is high, it is worth considering several slave databases. If multiple slave databases are used, the above lock can be properly mitigated.