MySQL slow query: Enable slow query

1. What is the use of slow query?

It can record all SQL statements that are executed for more than long_query_time, help you find the slow SQL statements, and make it easier for us to optimize these SQL statements.

2. Parameter Description

slow_query_log slow query enable status

slow_query_log_file is the location where the slow query log is stored (this directory requires writable permissions for the MySQL running account and is generally set to the MySQL data storage directory)

long_query_time How many seconds does it take for a query to be recorded?

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

2. Setting method

Method 1: Global variable setting

Set slow_query_log global variable to the "ON" state

mysql> set global slow_query_log='ON';

Set the location where the slow query log is stored

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

If the query exceeds 1 second, it will be recorded

mysql> set global long_query_time=1;

Method 2: Configuration file settings

Modify the configuration file my.cnf and add the following 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. Check 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 |
+-----------------+----------+

4. Testing

1. Execute a slow query SQL statement

mysql> select sleep(2);

2. Check whether slow query logs are generated

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

If the log exists, MySQL slow query setting is enabled successfully!

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.