MySQL slow query "one"-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. 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 One: Global variable settings
Set the Slow_query_log global variable to the "on" state

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 "one"-turn on Slow query