See how MySQL turns on slow queries? Talking about the principle of slow query log

MySQL Slow query log is a log record provided by MySQL, which is used to record the corresponding time in MySQL over the threshold of the statement, that is, the running time exceeds the long_query_time value of SQL, will be recorded in the slow query log. The default value for Long_query_time is 10, which means that the statement above 10S is running.

Settings for the slow query log

1. Check if the slow query log command is turned on:

Show variables like '%slow_query_log% '

2, set the command to open the slow query

Set Global slow_query_log=1

Note:
Slow_query_log on is on, off is off
Slow_query_log_file the storage address of the slow query log

3. Query and modify the time of the 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. It is recommended to turn this option on if you are tuning. If this parameter is turned on, the SQL for full index scan will also be logged 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

Show global status like '%slow_queries% ';

Mysqldumpslow Slow Log Analysis tool
Command:

-s sort by that way    C: Access Count    L: Lock time    r: Return record    al: Average lock time    ar: Average access record    at: Average query time-T is the meaning of top N and how many data is returned. -G can keep up with the regular match pattern and is case insensitive.

Get up to 20 SQL returned records

Mysqldumpslow-s r-t Sqlslow.log

Get the most average 20 SQL access times

Mysqldumpslow-s ar-t Sqlslow.log

Get the most average hits and 20 SQL with TTT characters inside

Mysqldumpslow-s ar-t 20-g "TTT" Sqldlow.log

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

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

2, if the following error, Died At/usr/bin/mysqldumpslow line 161, <> Chunk 405659. Explain that the SQL log you want to analyze is too large, please split and then analyze

The split commands are:

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

Related articles:

Turn on the MySQL slow query log and use the Mysqldumpslow command to view

Using Mysqldumpslow and Mysqlsla to analyze the MySQL slow query log

Related videos:

MySQL Data management backup recovery case Resolution video Tutorial