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

log_queries_not_using_indexes If the value is set to ON, all queries that do not use indexes will be recorded (enable this during performance optimization, do not enable it normally)

3. View the log storage format

show variables like 'log_output';

4. Permanently enable the slow log

Modify my.cnf

in Add the following parameters to the [mysqld] section in the configuration file my.cnf (usually /etc/my.cnf).

[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

Among them, slow_query_log = 1, means to turn on slow query, 0 means to turn off

slow_query_log_file, specify the slow query log path, MySQL needs to have write permission for this path

long_query_time = 1, indicating that the query time is >= 1 second before recording the log, the default is 10s

log_queries_not_using_indexes = 1, indicating that the SQL statements that do not use indexes are recorded

Restart the MySQL service, you will see after restarting MySQL To the /var/lib/mysql/slow-query.log file.

3. Slow query test

Create a slow query and execute it. As follows:

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

Slow query log

Open the slow query log file. You can see that the SQL statement of the above slow query is recorded in the log.

4. Slow query analysis tool

mysqldumpslow

mysqldumpslow is MySQL’s own tool for analyzing slow queries. The tool is a Perl script.

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:

Arrange by time The top 5 SQL statements

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

The top 5 SQL statements sorted by time and containing 'like'

$ 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!