MySQL slow_query_log

Slow_query_log is a parameter that records SQL execution for more than a certain period of time.

Slow query log related variables

1. Command line parameters:

-- Log-slow-queries

Specifies the location where the log file is stored, which can be null, and the system gives a default file host_name-slow.log

2. System variables

Log_slow_queries

Specifies the location where the log file is stored, which can be null, and the system gives a default file host_name-slow.log

Slow_query_log

The slow quere log switch. If the value is 1, the slow query is enabled.

Slow_query_log_file

Specifies the location where the log file is stored, which can be null, and the system gives a default file host_name-slow.log

Long_query_time

The time when the record expires. The default value is 10 s.

Log_queries_not_using_indexes

You can choose whether to enable the query that does not use the index after logs.

It is a very useful parameter for discovering SQL statements that take too long to effectively improve database performance.

This section describes how to configure MySQL 5.1 or later.

Modify/etc/my. cnf

Slow_query_log: valid (0 or OFF), invalid (1 or ON)
Slow_query_log_file: specify the log file
Long_query_time: the SQL statement that exceeds the specified time will be recorded in the log file (the default time is 10 seconds)
※Mysql5.1 can be set to less than 1 second.

# Vi/etc/my. cnf
[Mysqld]
...
Slow_query_log = 1
Slow_query_log_file =/data/log/mysql/slow_query.log
Long_query_time = 5

Use set global

# Mysql-u <user name>-p <database name>
Enter password: [Enter the password]

Set global slow_query_log = 1;
Set global slow_query_log_file = '/data/log/mysql/slow_query.log ';
Set global long_query_time = 5;

Configuration efficiency

After you restart MySQL or set global, the configuration will be valid.

Summary

The slow response of the database causes the slow response of the entire system. We recommend that you install MySQL to make slow_query_log effective.