MySQL Slow query log

The MySQL slow query log is a log record provided by MySQL, which is used to record SQL statements that execute more than long_query_time seconds. The minimum and default values for Long_query_time are 0 and 10, respectively. It means SQL statements that run more than 10 seconds. The value can be specified as microseconds. For logging to a file, the write time includes the microsecond portion. To log to a table, write only the whole number of times. The microsecond portion is ignored. By default, the slow query log is disabled and should be turned on manually.

Related parameters

Slow_query_log [= {0 | 1}]: Open the slow query log, 0 is off, and 1 is on.

Long_query_time: The slow query threshold, which logs when the query time exceeds the threshold value set.

Slow_query_log_file: The goal of the target. You can not set this parameter, the system will default to a default file Host_name-slow.log

log-output[={file| Table}]: Specifies whether the log is stored in a file or a table.

Log_queries_not_using_indexes: Queries that do not use indexes are also logged in the slow query log (optional).

Log-slow-admin-statements: Include slow management statements (optional) in statements written to the slow query log.

Min_examined_row_limit: A query that checks for fewer than this number of entries is not logged in the slow query log.

Slow query configuration

mysql> show variables  like ‘%slow_query_log%‘;+---------------------+----------------------------------------+| Variable_name       | Value                                  |+---------------------+----------------------------------------+| slow_query_log      | ON                                     || slow_query_log_file | /usr/local/mysql/var/huosuSDK-slow.log |+---------------------+----------------------------------------+2 rows in set (0.00 sec)

The value of Slow_query_log is off to indicate slow query is disabled and on indicates slow query is turned on.

set global slow_query_log=1

A slow query is turned on globally, but if MySQL restarts, it is invalidated. If you want to take effect permanently, you have to configure it under/ECT/MY.CNF

log-output=FILE 保存文件slow_query_log=1 开启慢查询long_query_time=2 时间2秒log_queries_not_using_indexes=1 没有索引的也记录

Then restart MySQL.

If you want to query how many slow query records you have, you can use system variables.

show global status like ‘%Slow_queries%‘;

You can also use the tools provided by the tool to analyze slow queries

Mysqldumpslow

Mysqldumpslow parses the MySQL slow query log file and prints its content summary.

Typically, Mysqldumpslow group queries are similar except for the specific values of numeric and string data values. When the summary output is displayed, the values are "extracted" to N and "S". The-A and-n options can be used to modify the value abstraction behavior.

Call Mysqldumpslow like this:

shell> mysqldumpslow [options] [log_file ...]

Mysqldumpslow supports the following options.

Format Description
-A Do not abstract all numbers into N and string s
-n Specifies at least an abstract number of numbers
--debug Writing Debug Information
-G only consider statements that conform to the pattern
--HELP Display Help information and exit
-H the hostname of the server in the log file name
The name of the-I server instance
-L do not subtract lock time from total time
-R Reverse Sort order
-S how to sort the output
-T displays only the first numeric query
--verbose verbose mode

• --help
  Display Help information and exit
• -A
  Do not abstract all the numbers into N and the string s
• --debug,-D
  Writing debug Information
• -G Pattern
  Only queries that match (Grep-style) mode are considered.
• -H host_name
  The host name of the MySQL server is -slow.log file name. The value can contain wildcard characters. The default value is (match all).
• -I. name
  The name of the server instance (if the Mysql.server startup script is used).
• -L
  Do not subtract the lockout time from the total time.
• N- N
  There are at least n digits in the abstract number.
• -R
  Reverses the sort order.
• -S sort_type
  How to sort the output. The value of Sort_type should be selected from the following list:
  • T, at: Sort by query time or average query time
  • L, AL: Sort by lock time or average lock time
  • R, AR: Sort by the sent line or the average row sent
  • C: Sort by quantity
    By default, Mysqldumpslow is sorted by average query time (equivalent to-s at).
• -T N
  Displays only the top n queries in the output.
• --verbose,-V
  Verbose mode. Print more information about the program.

MySQL Slow query log