Open Slow Query for MySQL Slow Query

1. What's the use of slow query?

It can record all SQL statements that execute longer than long_query_time, and help you find SQL that execute slowly, which is convenient for us to optimize these SQL.

2. Parameter description

slow_query_log Slow query open status

slow_query_log_file Slow query log storage location (this directory needs the writable permission of the running account of MySQL, and 1 is generally set as the data storage directory of MySQL)

long_query_time How many seconds does the query exceed before recording

3. Setup steps

1. View the parameters related to slow query

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 |
+-----------------+-----------+

2. Setting method

Method 1: Global variable setting

Will slow_query_log Global variable set to "ON" state

mysql> set global slow_query_log='ON'; 

Set the location of slow query log

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

Record if the query exceeds 1 second

mysql> set global long_query_time=1;

Method 2: Configuration file settings

Modify the configuration file my. cnf to add 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 set parameters

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 |
+-----------------+----------+

STEP 4 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 started the slow query setting successfully!

Summarize