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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.