I. Introduction

Opening the slow query log enables MySQL to record the statements that are queried for more than the specified time. Only by locating and analyzing the bottleneck of performance, can we better optimize the performance of the database system.

2, Parameter description

Slow query log slow query on status
Slow query log file slow query log storage location (this directory needs the writable permission of MySQL's running account, which is generally set as the data storage directory of MySQL)
Long query time how many seconds does the query take to record

3, Setup steps

1. View slow query related parameters

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /mysql/data/localhost-slow.log   |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

Copy code
2. Setting method
Method 1: global variable setting
Set the slow query log global variable to the "ON" state

mysql> set global slow_query_log='ON'; 

Set the storage location of slow query log

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

Query records in more than 1 second

mysql> set global long_query_time=1;

Method 2: profile settings
Modify the configuration file my.cnf and add it under [mysqld]

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

3. Restart MySQL service

service mysqld restart

4. View the parameters after setting

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

Four, test

1. Execute a slow query SQL statement

mysql> select sleep(2);

2. Check whether to generate slow query log

ls /usr/local/mysql/data/slow.log

If the log exists, MySQL starts slow query setting successfully!