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

Editor to share with you what is the method of MySQL to open the slow query log, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

1.1 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.

1.2 Log in to the database to view

[root@localhost lib] # mysql-uroot

Because the password is not set, those who have a password will receive the password in mysql-uroot-p

1.2.1 enter MySql to query whether a slow query has been opened

Mysql > show variables like 'slow_query%' +-+-- + | Variable_name | Value | +-+- -- + | slow_query_log | OFF | | slow_query_log_file | / application/mysql/data/localhost-slow.log | +-- -+ 2 rows in set (0.00 sec)

Parameter description:

Slow_query_log slow query enabled status OFF is not enabled ON is enabled

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)

1.2.2 View slow query timeout

Mysql > show variables like 'long%';+-+-+ | Variable_name | Value | +-+-+ | long_query_time | 10.000000 | +-+-+ 1 row in set (10.000000 sec)

How many seconds does the long_query_time query take to record? the default 10 seconds is changed to 1 second.

1.3 modify method 1: (not recommended)

Method 1: advantages of temporarily starting slow query, no need to restart database; disadvantages: MySql restart slow query is invalid

Recommendation: according to business needs, the second is recommended, and the first can be used temporarily.

By default, the value of slow_query_log is OFF, which means that slow log is disabled. You can enable it by setting the value of slow_query_log, as follows: whether to enable slow log, 1: enable, 0: disable.

1.3.1 check whether slow query is enabled

Mysql > show variables like'% slow_query_log%' +-+-- + | Variable_name | Value | +-+- -- + | slow_query_log | OFF | | slow_query_log_file | / application/mysql/data/localhost-slow.log | +-- -+ 2 rows in set (0.01 sec)

Input statement modification (invalid after restart. It is recommended that the modification take effect permanently in / etc/my.cnf)

Mysql > set global slow_query_log=1;Query OK, 0 rows affected (0.11 sec)

1.3.2 View again

Mysql > show variables like'% slow_query_log%' +-+-- + | Variable_name | Value | +-+- -- + | slow_query_log | ON | | slow_query_log_file | / application/mysql/data/localhost-slow.log | +-- -+ 2 rows in set (0.00 sec)

1.4 modify method 2: (recommended)

Modify MySql slow query. Many people don't know the my.cnf path, so you can use find to find it.

Note: my MySQL is compiled to / etc/my.cnf (usually here)

[root@localhost log] # find /-type f-name "my.cnf" / application/mysql-5.5.51/mysql-test/suite/rpl/my.cnf/application/mysql-5.5.51/mysql-test/suite/federated/my.cnf/application/mysql-5.5.51/mysql-5.5.51-linux2.6-x86_64/mysql-test/suite/rpl/my.cnf/application/mysql-5.5.51/mysql-5.5.51 -linux2.6-x86_64/mysql-test/suite/federated/my.cnf/etc/my.cnf # (usually here)

1.4.1.1 Modification

[root@localhost log] # vim / etc/my.cnf

Find [mysqld] and add below

Slow_query_log = 1slow_query_log_file=/application/mysql/data/localhost-slow.loglong_query_time = 1

Parameter description:

Slow_query_log slow query enabled status 1 is enabled

Location where slow_query_log_file slow query logs are stored

How many seconds does the long_query_time query take to record? the default 10 seconds is changed to 1 second.

Restart MySQL after modification

1.5 View, test

1.5.1.1 insert a test slow query

Mysql > select sleep (2); +-+ | sleep (2) | +-+ | 0 | +-+ 1 row in set (2.00 sec)

1.5.1.2 View slow query log

[root@localhost data] # cat / application/mysql/data/localhost-slow.log/application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). Started with:Tcp port: 3306 Unix socket: / tmp/mysql.sockTime Id Command Argument/application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). Started with:Tcp port: 3306 Unix socket: / tmp/mysql.sockTime Id Command Argument/application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). Started with:Tcp port: 3306 Unix socket: / tmp/mysql.sockTime Id Command Argument# Time: 170605 6 User@Host 37 tmp/mysql.sockTime Id Command Argument# Time: root [root] @ localhost [] # Query_time: 2.000835 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0SET timestamp=1496615820;select sleep (2)

1.5.1.3 check how many slow queries are available through the MySQL command

Mysql > show global status like'% Slow_queries%';+-+-+ | Variable_name | Value | +-+-+ | Slow_queries | 1 | +-+-+ 1 row in set (0.00 sec)

1.6 Log analysis tool mysqldumpslow

In a production environment, if you want to manually analyze logs, find and analyze SQL, it is obviously a manual task. MySQL provides a log analysis tool, mysqldumpslow.

The above are all the contents of what is the method for MySQL to open the slow query log. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.