Configure and use MySQL slow query logs ., Mysql query log

Configure and use MySQL slow query logs ., Mysql query log

 

MySQL slow query log provides query information that exceeds the specified time threshold, and provides a major reference for performance optimization. It is a very practical function,
The enabling and configuration of MySQL slow query logs is very simple. You can specify the recorded files (or tables) and the time threshold that has been exceeded to record the slow SQL statements,
To be honest, compared with SQL Server's trace or extension events (although the roles of the two are not the only ones), MySQL configuration always gives a very fresh feeling.

1. Enable slow query logs

Under normal circumstances, you only need to add the slow_query_log = 1 configuration in the configuration file to open the slow query log. If slow_query_log_file is not specified, a host name + 'low' will be automatically generated '. log File.

　　

2. The default record slow query time threshold is 10 s.

　　

By default, when slow_query_log = 1 is specified, MySQL is started to open the slow query. A default host name + + 'low' is automatically generated '. log files to record slow queries that have been executed for more than 10 s.

You can also explicitly specify the name of the slow query log file (automatically created if no log file exists) and the time threshold for recording slow query (not the default 10 s ).

　　

Note: When long_query_time is specified in the configuration file, you do not need a time unit. Only one value is required. For example, 1 represents 1 s. If the time unit is included, the service cannot be started.

　　

The following is an example of a slow SQL statement recorded in a log file:

　　

3. Record slow query logs to the table

Configuration: You need to add a log_output configuration to record the slow query to the table.

There is a default slow_log table under the mysql database. You can directly record slow_query_log_file = slow_log to the table.

　　

The recorded slow SQL statement is as follows. It can be found that SQL _text is a binary information, not the original SQL text.

　　

You can use the CONVERT function to CONVERT it.

　　

 

Differences between log files recorded in slow queries and tables:

1. When a slow query record is recorded in a log file and a table, the record itself is not much different. If it is recorded in a table, the execution time of the slow query cannot be accurate to the subtle,

2. If the slow query information is recorded in the table, it is easy to query, but because it is structured data, it may be recorded in the slow query log file (flat text file) it takes a little longer (I guess). If it is recorded in a file, it needs to be parsed by mysqldumpslow.

3. Slow queries do not record query failures. For example, long_query_time is set to 10 (10 seconds). A Query lasts for more than 10 seconds, but fails to be executed for other reasons, mySQL slow query cannot record this query information.