MySQL slow query: enabling slow query and mysql Query

MySQL slow query: enabling slow query and mysql Query

1. What is the use of slow queries?

It records all SQL statements that have been executed longer than long_query_time, and helps you find slow SQL statements to optimize these SQL statements.

Ii. parameter description

slow_query_logEnabled status of slow Query

slow_query_log_fileLocation where slow query logs are stored (this directory requires the write permission of the MySQL Running Account, which is generally set to the MySQL data storage directory)

long_query_timeHow many seconds does the query take to record?

3. Setup steps

1. View slow query 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 settings

Setslow_query_logSet global variables to "ON" status

mysql> set global slow_query_log='ON'; 

Set the location for storing slow query logs

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

Query records within 1 second

mysql> set global long_query_time=1;

Method 2: configure the configuration file

Modify the configuration file my. cnf and add it below [mysqld]

[mysqld]slow_query_log = ONslow_query_log_file = /usr/local/mysql/data/slow.loglong_query_time = 1

3. Restart the MySQL service.

service mysqld restart

4. view the configured 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 |+-----------------+----------+

Iv. Test

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 has enabled the slow query setting!

Summary

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, please leave a message, thank you for your support.