Slow query Log detailed

MySQL's slow query log is a log record provided by MySQL, which is used to record statements in MySQL that have a response time exceeding the threshold, specifically the SQL that runs longer than the Long_query_time value, and is recorded in the slow query log. The default value of Long_query_time is 10, which means to run more than 10S of statements. By default, the MySQL database does not start the slow query log, we need to manually set this parameter, of course, if not the need for tuning, it is generally not recommended to start this parameter, because open slow query log will more or less bring certain performance impact. Slow query logging supports writing log records to a file and also supports writing log records to a database table.

The official document, about the slow query log introduction as follows (part of the information, specific reference to the official relevant links):

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least Min_examined_row_limit rows to be examined. The minimum and default values Oflong_query_time are 0 and respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only the integer times are written; The microseconds part is ignored.

By default, the administrative statements are not logged, nor are, queries,???? indexes for lookups. This behavior can is changed usinglog_slow_admin_statements and Log_queries_not_using_indexes, as described later.

slow query log related parameters

MySQL slow Query related parameters to explain:

Slow_query_log: Open Slow query log, 1 means open, 0 is closed.

Log-slow-queries: Old version (under 5.6) MySQL database slow query log storage path. You can not set this parameter, the system defaults to a default file Host_name-slow.log

Slow-query-log-file: New version (5.6 and above) MySQL database slow query log storage path. You can not set this parameter, the system defaults to a default file Host_name-slow.log

Long_query_time: Slow query threshold, log when query time is greater than set thresholds.

Log_queries_not_using_indexes: Queries that do not use indexes are also logged in the slow query log (optional).

Log_output: How the log is stored. log_output= ' file ' means that the log is saved to the file, and the default is ' file '. log_output= ' table ' means that the log is stored in the database so that the log information is written to the Mysql.slow_log table. MySQL database supports both types of log storage, when configured with commas, such as: log_output= ' file,table '. Logging to the system's private log table consumes more system resources than logging to a file, so it is recommended that priority be recorded to files if you need to enable slower query logging and you need to be able to achieve higher system performance.

Slow query log configuration

By default, the Slow_query_log value is off, indicating that the slow query log is disabled and can be opened by setting the value of Slow_query_log, as follows:

Mysql> Show variables like '%slow_query_log% ';

+---------------------+-----------------------------------------------+

| variable_name | Value |

+---------------------+-----------------------------------------------+

| Slow_query_log | Off |

| Slow_query_log_file | /home/wdpm/mysqldata/mysql/db-server-slow.log |

+---------------------+-----------------------------------------------+

2 rows in Set (0.00 sec)

mysql> set global slow_query_log=1;

Query OK, 0 rows affected (0.09 sec)

Mysql> Show variables like '%slow_query_log% ';

+---------------------+-----------------------------------------------+

| variable_name | Value |

+---------------------+-----------------------------------------------+

| Slow_query_log | On |

| Slow_query_log_file | /home/wdpm/mysqldata/mysql/db-server-slow.log |

+---------------------+-----------------------------------------------+

2 rows in Set (0.00 sec)

Using SET global slow_query_log=1 to open a slow query log only takes effect for the current database, and if MySQL restarts it will fail. If you want to take effect permanently, you must modify the configuration file My.cnf (as well as other system variables). For example, the following is shown:

Mysql> Show variables like ' slow_query% ';

+---------------------+-----------------------------------------------+

| variable_name | Value |

+---------------------+-----------------------------------------------+

| Slow_query_log | Off |

| Slow_query_log_file | /home/wdpm/mysqldata/mysql/db-server-slow.log |

+---------------------+-----------------------------------------------+

2 rows in Set (0.01 sec)

Modify the My.cnf file, add or modify parameters Slow_query_log and Slow_query_log_file, and then restart the MySQL server as shown below

Slow_query_log =1

Slow_query_log_file=/tmp/mysql_slow.log

Mysql> Show variables like ' slow_query% ';

+---------------------+---------------------+

| variable_name | Value |

+---------------------+---------------------+

| Slow_query_log | On |

| Slow_query_log_file | /tmp/mysql_slow.log |

+---------------------+---------------------+

2 rows in Set (0.00 sec)

For a slow query parameter slow_query_log_file, it specifies the path to the slow query log file, and the system defaults to a default file Host_name-slow.log (if no parameter slow_query_log_file is specified)

Mysql> Show variables like ' slow_query_log_file ';

+---------------------+-----------------------------------------------+

| variable_name | Value |

+---------------------+-----------------------------------------------+

| Slow_query_log_file | /home/wdpm/mysqldata/mysql/db-server-slow.log |

+---------------------+-----------------------------------------------+

1 row in Set (0.00 sec)

So when the slow query log is turned on, what kind of SQL is logged into the slow query log? This is controlled by the parameter long_query_time, by default Long_query_time value is 10 seconds, can use the command to modify, also can change inside my.cnf parameter. The case that the run time is exactly equal to Long_query_time is not recorded. In other words, in the MySQL source is judged to be greater than long_query_time, not greater than equal. Starting with MySQL 5.1, Long_query_time begins to record the SQL statement run time in microseconds, which is recorded only in seconds. If you log into a table, only the integer portion is recorded, and the microsecond portion is not recorded.

Mysql> Show variables like ' long_query_time% ';

+-----------------+-----------+

| variable_name | Value |

+-----------------+-----------+

| Long_query_time | 10.000000 |

+-----------------+-----------+

1 row in Set (0.00 sec)

mysql> set global long_query_time=4;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like ' long_query_time ';

+----------