MySQL slow query log practice (graphic and text analysis)

This article brings you relevant knowledge about mysql, which mainly introduces mysql slow query log rotation_MySQL slow query log practice. MySQL's slow query log is a feature provided by MySQL. Kind of log record, let’s take a look at it, I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

1. Overview

MySQL’s slow query log is provided by MySQL A kind of log record, which is used to record SQL statements whose response time exceeds the threshold (long_query_time, unit: seconds) in MySQL. By default, MySQL does not start slow query logging. This article briefly introduces how to enable slow query logs and how to use mysqldumpslow to analyze slow queries.

2. Slow query log settings

1. Temporary settings

Temporarily enable the slow query log (failed after restarting)

set global slow_query_log = on;

Note: If you want to turn off the slow query log, you only need to execute set global slow_query_log = off;.

Temporary slow query time critical point Query times higher than this critical point will be recorded in the slow query log (restart failure)

set long_query_time = 1;

Set slow Query storage method (invalid after restart)

set global  log_output = file;

Explanation: As you can see, I set it to file here, which means that my slow query log is It is reflected by file. The default is none. We can set it to table or file. If it is table, the slow query information will be saved to the slow_log table under the mysql library.

2. Query the opening status of the slow query log and the storage location of the slow query log

show variables like '%quer%';

##Parameter description:

• slow_query_log: Whether slow query has been enabled
• slow_query_log_file: Slow query log file path
• long_query_time: Queries that exceed the number of seconds are written to the log

show variables like 'log_output';

Copy after login

[mysqld]slow_query_log= 1slow_query_log_file= /var/lib/mysql/slow-query.log # 若没有指定，默认名字为hostname_slow.loglong_query_time= 1log_queries_not_using_indexes= 1

Create a slow query and execute it. As follows:

mysql> select sleep(1);+----------+
| sleep(1) |
+----------+
| 0 |
+----------+
1 row in set (1.00 sec)

mysqldumpslow

Copy after login

Commonly used parameters are as follows:

• -s: Sort method, the value is as follows
• c: Number of queries
• t：Query time
• l：Lock time
• r：Return records
• ac：Average number of queries
• al：Average lock time
• ar：Average return record book
• at：Average query time
• -t：topN query
• -g ：Regular expression

Get the 5 most visited SQL statements:

$ mysqldumpslow -s t -t 5 /var/lib/mysql/slow-query.log

$ mysqldumpslow -s t -t 3 -g "like" /var/lib/mysql/slow-query.log

五、MySQL 清理slowlog方法

SET GLOBAL slow_query_log = 'OFF';
ALTER TABLEmysql.slow_log RENAME mysql.slow_log_drop;
CREATE TABLE mysql.slow_log LIKEmysql.slow_log_drop;
SET GLOBAL slow_query_log = 'ON';
DROP TABLE mysql.slow_log_drop;

推荐学习：mysql视频教程

The above is the detailed content of MySQL slow query log practice (graphic and text analysis). For more information, please follow other related articles on the PHP Chinese website!