MySQL Slow Query: Introduction to how to enable slow query

Turning on the slow query log allows MySQL to record queries that exceed the specified time. By locating and analyzing performance bottlenecks, the performance of the database system can be better optimized. The following article mainly introduces the relevant information about starting slow query in MySQL. Friends in need can refer to it.

1. What is the use of slow query?

It can record all SQL statements that execute longer than long_query_time and help you find slow execution SQL, so that we can optimize these SQLs.

2. Parameter description

slow_query_log Slow query open status

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

long_query_time How many seconds does the query take before recording

3. Setting 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 ：GlobalVariableSetting

Set the slow_query_log global variable to the "ON" state

mysql> set global slow_query_log='ON';

Set the slow query log storage The location

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 fileSettings

Modify Configuration file my.cnf, add

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

# at the bottom of [mysqld] 3. Restart the MySQL service

service mysqld restart

4. Check 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 |
+-----------------+----------+

4. Test

1. Execute a slow Query the SQL statement

mysql> select sleep(2);

2. Check whether the slow query log is generated

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

If the log exists, the MySQL slow query setting is successful!

Summarize

The above is the detailed content of MySQL Slow Query: Introduction to how to enable slow query. For more information, please follow other related articles on the PHP Chinese website!