Synchronization of mysql slave lock tables

Problem description:

The log update updata on the slave locks the table with the select table from the web request, causing synchronization congestion.
The load of mysql slave is soaring, and the website anti-problem is very slow.
 
 

 
Problem Analysis:
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?
I checked it online and said this:
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.
Write operations on the MyISAM table (with a write lock) will block read and write operations by other processes on the same table. Only when the write lock is released will the read and write operations of other processes be performed.
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. No real solution has been found so far
Troubleshooting:
1. Optimize SQL statements with long query time
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. (Two salve instances have been added to distribute the salve pressure)
3. No real solution has been found so far. Further optimization is in progress.
If anyone has a good solution, please let me know.
 
This article is from the "wind" blog