Slow query log (mysql), query log mysql

Slow query log (mysql), query log mysql

Reference

Mysql provides support for slow query logs for mysql optimization. Mysql slow query is a log record provided by mysql. It is used to record SQL statements whose response time exceeds the threshold value in mysql, if the running time of an SQL statement exceeds the set threshold (long_query_time), it is recorded in the slow query log. The default value is 10 s. By default, the mysql database does not start slow query logs. You need to manually set this parameter (slow_query_log ). Slow query logs can be written to files or database tables. It is generally not recommended to enable slow query logs because slow query logs have more or less performance impact.

Slow query log parameters

1 slow_query_log: whether to enable slow query logs. 1 indicates enabled, and 0 indicates disabled. 2 log-slow-queries: the log storage path for slow query of MySQL Databases in earlier versions (versions earlier than 5.6. If you do not set this parameter, the system will default to a default file host_name-slow.log3 slow-query-log-file: the new version (5.6 and later) MySQL database slow query log storage path. You can leave this parameter unspecified, and the system defaults to a default file host_name-slow.log4 long_query_time: Slow query threshold, which records logs when the query time exceeds the set threshold. 5 log_queries_not_using_indexes: queries without indexes are recorded in slow query logs (optional ). 6 log_output: log storage method. Log_output = 'file' indicates storing logs into files. The default value is 'file '. Log_output = 'table' indicates storing logs into the database, so that the log information is written into the mysql. slow_log TABLE. MySQL DATA <br> the database supports two log storage methods at the same time. You can separate them with commas (,) during configuration, for example, log_output = 'file, Table '. Logs are recorded in the system's dedicated log table, which consumes more system resources than those recorded in files. Therefore, to enable slow query logs, <br> to achieve higher system performance, we recommend that you record files first.

View Code

 

 

 

Use the set command to enable the slow query log. After mysql is restarted, it will become invalid. If the log takes effect permanently, write the configuration to the mysql configuration file my. cnf or my. ini.

After the slow query log is enabled, SQL statements whose execution time exceeds the threshold are recorded. Before mysql5.1, long_query_time stores an integer, starting from 5.1, and long_query_time records the SQL running time in a subtle way.

Change the threshold value from 10 s to 2.13 s

The log_output parameter specifies the log storage method. FILE indicates storing logs into files. The default value is FILE. TABLE stores logs into the database, so that the log information is written to the mysql. slow_log TABLE. The mysql database supports two logging methods at the same time. You can use commas to separate the configuration. It takes more system resources to record logs to a dedicated system log table than to a file. For slow query logs, we recommend that you record logs to files first.

Modify the log storage method to support both storage methods.

Slow query log information

File

System Table

The system variable log_slow_admin_statements indicates whether to include slow management statements such as analyze table and alter table into slow query logs.

To query the number of slow query records, you can use system variables.

 

Analysis of slow query logs is time-consuming and inefficient. You can use the mysqldumpslow or pt_query_digest tool. For more information, see