mysql-Slow Query log

The slow query log feature is not turned on by default, and it records a value that executes longer than the parameter Long_query_time (default is 10) and the number of rows accessed exceeds the SQL statement that the parameter min_examined_row_limit is worth.

Mysql> Show variables like ' Long_query_time ' \g*************************** 1. Row ***************************variable_name:long_query_time        value:10.0000001 row in Set (0.00 sec) mysql>

The time to get the lock on the table is not within the SQL execution time. The slow query log is not written until the statement execution finishes.

1. Turn on the Slow query log function (different version open method is not the same)

5.1.6 Previous version:

--log-slow-queries[=file_name]
5.1.12 version:

--SLOW_QUERY_LOG[={0|1}]
5.1.29 version

--SLOW_QUERY_LOG[={0|1}]

--slow_query_log_file=file_name

Mysql> Show variables like ' slow_query% '; +---------------------+---------------------------------+| Variable_name       | Value                           |+---------------------+---------------------------------+| slow_query_log      | On                              

2. Slow query log format

The slow query log can be output in file format or in a database table style

Mysql> Show variables like ' log_output '; +---------------+-------+| variable_name | Value |+---------------+-------+| Log_output    | FILE  |+---------------+-------+1 row in Set (0.00 sec) mysql> set global log_output= ' table '; Query OK, 0 rows Affected (0.00 sec) mysql> Show variables like ' log_output '; +---------------+-------+| variable_name | Value |+---------------+-------+| Log_output    

When set to table format, the output is in the Mysql.slow_log table.

In addition, you can use the Mysqldumpslow tool to analyze slow query logs

3. The default slow query log name is

Host_name-slow.log

4. Parameters related to slow query
If you run a statement that does not use an index, when Log_queries_not_using_indexes is turned on, the database logs the SQL statement to the slow query log file.

Mysql> Show variables like ' log_queries_not_using_indexes '; +-------------------------------+-------+| Variable_name                 | Value |+-------------------------------+-------+| log_queries_not_using_indexes | OFF   

MySQL 5.6.5 adds a new parameter, log_throttle_queries_not_using_indexes, that represents the number of SQL statements that are allowed to log to slow log per minute and that do not use the index, which defaults to 0, which means there is no limit.

mysql-Slow Query log