MySQL 32nd article ~ concurrency-caused from library latency issues

A brief introduction: Today to talk about periodic from the library delay problem

Second background: The recent day of the specified time period, received from the library delayed alarm, and then a period of time to recover. Since the library is provided with read services, it is necessary to resolve

Three Analytical ideas:

1 periodic delay, and all delays from the library, should be caused by DML operations in the main library

2 View slow log records for the main library (our database is cut every hour), and there is no DML slow statement, excluding the problem caused by slow SQL (DML operations), the DML operations of the main library are slower to synchronize to from the library if there are slow statements, such as the Update,delete statement

3 View slow logging from library, whether DML slow statement appears, and does not appear

4 looking at the DML statement graph recorded by the day Rabbit platform, we found that a large number of concurrent insert operations occurred during this period, and the problem was fixed.

Four Problem Solving:

1 analysis in a specified time period using mysqlbing

Sqlbinlog--no-defaults--start-datetime= ' 2017-11-17 07:50:00 '--stop-datetime= ' 2017-11-17 08:20:00 '--base64-output =DECODE-ROWS-VV binlogname > Result.txt

2 Using the Awk tool to perform additions and deletions during this period

awk '/###/{if ($0~/update| Insert| delete/) count[$2 "" $NF]++}end{for (i in count) print I, "\ T", count[i]} ' file name | column-t | Sort-k3n

Statistical Library + table additions and deletions to check the number of changes and to sort

3 According to the results, we found the highest insert table, and then the operation to confirm the business IP, and research and development to communicate, learned that the business for a period of centralized processing, resulting in the above situation.

Five, the investigation ended successfully

MySQL 32nd article ~ concurrency-caused from library latency issues