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

Mysql slow query log is a kind of log record provided by mysql, which is used to record statements that exceed the threshold for the corresponding time in mysql, that is, sql whose running time exceeds the value of long_query_ time will be recorded in the slow query log. The default value for long_query_time is 10, which means to run a statement above 10s.

Settings of slow query log

1. Check whether the slow query log command is enabled:

Show variables like'% slow_query_log%'

2. Set the command to open slow query

Set global slow_query_log=on

Note:

Slow_query_log ON is on, OFF is closed

Slow_query_log_file is the address where slow log is stored.

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 turn on 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 sort in that way

C: access count

L: lock time

R: returns the record

Al: average lock time

Ar: average number of access records

At: average query time

-t means top n, how many pieces of data are returned.

-g can keep up with the regular matching pattern and is case-insensitive.

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 20 sql that have the most average visits and contain ttt characters

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

Note:

1. A-bash: mysqldumpslow: command not found error occurred

Solution:

PATH=$PATH:/usr/local/mysql/bin/mysqldumpslow

2. If the following error occurs, Died at / usr/bin/mysqldumpslow line 161, chunk 405659. It means that the sql log you want to analyze is too large.

Solution: analyze after split

The command to split is:

Tail-1000 mysql-slow.log > mysql-slow.20180725.log

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.