MySQL slow query------turn on slow query

First, Introduction

Turn on the slow query log, you can let the MySQL record query over a specified time of the statement, through the location analysis of performance bottlenecks, to better optimize the performance of the database system.

Second, parameter description

Slow_query_log Slow Query Open status
Slow_query_log_file the location of the slow query log (this directory requires the writable permissions of the MySQL running account, generally set to the MySQL data storage directory)
Long_query_time query more than how many seconds before logging

Third, set the steps

1. Viewing 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 One: Global variable settings
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 ';

The query is logged in more than 1 seconds

mysql> set global long_query_time=1;

Method Two: Configuration file settings
Modify the configuration file my.cnf, add 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 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 |+-----------------+----------+ 

Iv. Testing

1. Execute a slow query SQL statement

Mysql> Select Sleep (2);

2. See if a slow query log is generated

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

If the log exists, MySQL turns on slow query settings successfully!

MySQL slow query------turn on slow query