MySQL Slow SQL alarm system

Recently, some colleagues have reacted to the response time of the interface is fast and slow, after the detection of some data layer response time is too long, in order to speed up the accuracy of positioning and positioning slow SQL, decided to simply build a slow SQL alarm system

The specific process is as follows: Architecture diagram

　　

First step: Logging

Every business system will have its own query statements, all of the most red SQL statements we first open each MySQL slow SQL acquisition configuration

1. Set the Slow_query_log global variable to the "on" state
mysql> set global slow_query_log= ' on ';
2. Set the location of slow query log storage
3.mysql> set global slow_query_log_file= '/usr/local/mysql/data/slow.log ';
Set the time for slow SQL
mysql> set global long_query_time=1;

Test it.

Select Sleep (3); Go to the path set in the second step to find the log file, open the log discovery will have a record

Step Two: Collect logs

When all the log files are recorded, we need to collect, and the traditional elk that set of patterns, with Filebeat to collect (Filebeat acquisition principle is not much to say about), after the acquisition is complete, the unified transmission to Logstash

Step three: Format the data

When Logstash gets this data, it needs to format the Elasticsearch required format

Note: We cannot distinguish between SQL types in the log format, such as SELECT * from user where userid=2 and select * from user where userid=5 is the same type, but the data collected from the original log cannot be judged, we Special handling is required:

1: Use regular expressions to replace all numbers and quotes "". ，

2: Then encrypt the returned value with the encryption algorithm to get a new field

Change the field to indicate that there is some kind of SQL

Fourth step: Analyzing and presenting data

Elasticsearch get the data from Logstash, you can retrieve it (sql_for_hash field is the field used to discriminate the type of SQL)

　　

Fifth Step: Alarm function

The above four steps basically completed the MySQL slow SQL collection and analysis process, the next is the alarm function, the use of hourly (can be shorter) to scan the data in ES, according to the query time in the log and the number of times per hour to determine the alarm level, call the Message Center alarm interface to Alarm, This allows developers to receive in real time the occurrence of slow SQL in order to resolve the problem in a timely manner (the background also wrote a simple statistics page for easy Viewing)

　　

　　

　　

　　

MySQL Slow SQL alarm system