See how to enable slow query in mysql? A brief discussion on the principle of slow query log

mysql slow query log is a log record provided by mysql. It is used to record statements in mysql whose corresponding time exceeds the threshold. It means that SQL whose running time exceeds the long_query_time value will be recorded. in the slow query log. The default value of long_query_time is 10, which means running statements for more than 10 seconds.

Recommended mysql video tutorials: "mysql tutorial"

Slow query log settings

1 , check whether the slow query log is turned on Command:

show variables like '%slow_query_log%'

2. Set the command to enable slow query

set global slow_query_log=1

Note:
slow_query_log ON means to turn on, OFF means to turn off
slow_query_log_file is the storage address of the slow query log

3. Query and modify the time of slow query definition

show variables like 'long_query_time%'

set global long_query_time=4

4. Queries that do not use indexes are recorded in the slow query log. If tuning, it is recommended to enable this option. If this parameter is enabled, the SQL of full index scan will also be recorded in the slow query log.

show variables like 'log_queries_not_using_indexes'

set global log_queries_not_using_indexes=1

5. Query how many slow query records there are

show global status like '%Slow_queries%';

mysqldumpslow slow log analysis tool
Command:

-s 按照那种方式排序    c：访问计数
    l：锁定时间
    r:返回记录
    al：平均锁定时间
    ar：平均访问记录数
    at：平均查询时间
-t 是top n的意思，返回多少条数据。-g 可以跟上正则匹配模式，大小写不敏感。

Get the 20 sql with the most returned records

mysqldumpslow -s r -t 20 sqlslow.log

Get the 20 sql with the highest average number of visits

mysqldumpslow -s ar -t 20 sqlslow.log

Get the 20 sql with the highest average number of visits and containing ttt characters

mysqldumpslow -s ar -t 20 -g "ttt" sqldlow.log

Note:
1. If -bash: mysqldumpslow: command not found error occurs, please execute

ln -s /usr/local/mysql/bin/mysqldumpslow /usr/bin

2. If the following error occurs, Died at /usr/bin/mysqldumpslow line 161, <> chunk 405659. It means you want to analyze The sql log is too large, please split it and then analyze it

The splitting command is:

tail -100000 mysql-slow.log>mysql-slow.20180725.log

Related articles:

Enable mysql slow query log and Use the mysqldumpslow command to view

Use mysqldumpslow and mysqlsla to analyze the mysql slow query log

Related videos:

MySQL data management Backup and recovery case analysis video tutorial

The above is the detailed content of See how to enable slow query in mysql? A brief discussion on the principle of slow query log. For more information, please follow other related articles on the PHP Chinese website!