MySQL slow query method and example

1. Introduction

By enabling the slow query log, MySQL can record query statements that exceed the specified time. By locating and analyzing performance bottlenecks, the performance of the database system can be better optimized.

2. Parameter Description

slow_query_log slow query enable status

slow_query_log_file 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 the 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!

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.