Shulou(Shulou.com)06/01 Report--

What the editor wants to share with you this time is how to open a slow query on MySQL. The article is rich in content. Interested friends can learn about it. I hope you can get something after reading this article.

I. brief introduction

By enabling the slow query log, you can let MySQL record the statements that query for more than a specified time. By locating the bottleneck of analyzing performance, you can better optimize the performance of the database system.

Second, parameter description

Slow_query_log slow query enabled status

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

How many seconds does the long_query_time query take to record

3. Setting steps

1. View parameters related to slow query

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 | +-+-+

two。 Setting method

Method 1: global variable setting

Set the slow_query_log global variable to the "ON" state

Mysql > set global slow_query_log='ON'

Set the location where slow query logs are stored

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

Record the query for more than 1 second.

Mysql > set global long_query_time=1

Method 2: profile settings

Modify the configuration file my.cnf and add under [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)

two。 Check to see if slow query logs are generated

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

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

After reading this article on how MySQL starts slow queries, if you think the article is well written, you can share it with more people.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.