The background,
All of a sudden, the delay of viewing primary and secondary data increases at noon and afternoon every day, and the delay of viewing primary and secondary data often reaches more than 10 minutes. The whole system uses the read/write separation mechanism implemented by mysql-Proxy. Therefore, data cannot be queried after data is inserted.
This problem persists for a long time because mysql services are frequently used and cannot be quickly located.
Second, the solution
1. At the time of the crime, at 16:30 in the afternoon, I directly logged in to check the process from the library and searched for the long-consuming data table & statements
2. Locate the query statement and view the table structure + the number of tables.
(1) MyISAM engine used
(2) When the data in the table reaches 500W +, it stores user operation records. The earliest data is from October 2018, that is, a large amount of useless data
(3) If the index is not matched, perform full table query
3. Solutions:
(1) Clear the old data, only keep the operation records of the last 3 months, and reduce the data volume to about 30W
(2) Change the engine to InnoDB, change the original table-level lock to row-level lock
(3) Optimize query statements and make full use of indexes
4. Verify
If the periodic script of the query statement is run and the script runs quickly, and the delay between the master and slave is less than 5s, the problem is solved.
Three, get to the bottom of it
The problem was resolved, mainly because the engine and query statements took a long time, but it was confusing: MyISAM lock table is the only table, why did a lot of services also have problems?
By searching for information, I finally found the result:
(1) Use the mysql-proxy read-write separation function. Write is to write to the master library, read is to query the slave library, and the master library needs to be periodically synchronized to the slave library
(2) Master-slave synchronization mechanism: the master library writes the binlog in sequence for read and write operations, the slave library reads the “binlog of write operations” in a single thread from the master library, and the binlog is fetched from the library and executed locally (random write) to ensure the logical consistency of the master and slave data
(3) Because the sequential execution is required, the lock table caused by the query statement will block the write operation, thus making the overall master-slave delay serious
(To the bottom)
Fourth, to reflect on
In fact, AFTER solving this problem, I did not think about it. Later, WHEN I talked about this matter with others and discussed it in detail, I found that the logic was not very clear, and I was asked speechless. Therefore, I asked for the bottom of the matter. Avoid this later.