MySQL Slow Query Log Basic Usage Tutorial

Slow query log related parameters

Explanation of related parameters of MySQL slow query: slow_query_log: whether to open the slow query log, 1 means open and 0 means closed.

slow_query_log: Whether to turn on the slow query log, 1 for turn on and 0 for turn off. log-slow-queries: Old version (below 5.6) MySQL database slow query log storage path. This parameter can not be set, and the system will default to a default file host_name-slow. log slow-query-log-file: New version (5.6 and above) MySQL database slow query log storage path. If this parameter is not set, the system will default to a default file host_name-slow. log long_query_time: Slow query threshold, log when the query time is longer than the set threshold. log_queries_not_using_indexes: Queries that do not use indexes are also recorded in the slow query log (optional). log_output: Log storage mode. log_output= 'FILE' means to save the log to a file, and the default value is' FILE '. log_output= 'TABLE' means that the log is stored in the database so that the log information is written to the mysql.slow_log table. MySQL data < br > The library supports two log storage methods at the same time, which can be separated by commas when configuring, such as log_output= 'FILE, TABLE'. Logging to the system's dedicated log table consumes more system resources than logging to files, so if you need to enable slow query logging, you need to < br > To achieve higher system performance, it is recommended to record to files first.

1. Set up the method

Use the capture in the slow query log

A few settings are required before enabling

Method 1: Global variable setting

Set the log file location of the slow query log

set global slow_query_log_file = "D:/slow_log/slow_log.log" ;

Sets whether to record SQL with unused indexes

set global log_queries_not_using_indexes = on;

Set to record whenever SQL execution time exceeds n seconds

set global long_query_time = 0.001 ;

The 0.001 second set here is easy to test, and 1 case is larger than this one

Enable mysql slow query log

set global slow_query_log = on;

Method 2: Configuration file settings

Modify the configuration file my. cnf to add under [mysqld]

[mysqld]
slow_query_log = ON
log_queries_not_using_indexes = ON;
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

View the set parameters

show variables like 'slow_query%';
show variables like 'long_query__time';

2. Slow query the contents of log records

Time   Id Command Argument
# Time: 2019-01-08T04:12:09.269315Z 
# User@Host: h5_test[h5_test] @ localhost [::1] Id: 12 
# Query_time: 0.000831 Lock_time: 0.000198 Rows_sent: 1 Rows_examined: 3 
use mc_productdb;
SET timestamp=1546920729;
SELECT t.customer_id,t.title,t.content 
FROM (
SELECT customer_id FROM product_comment WHERE product_id =199726 AND audit_status = 1 LIMIT 0,15
)a JOIN product_comment t 
ON a.customer_id = t.comment_id;

Time: Date and time of query execution User @ Host: User and client executing the query IP Id: Is the thread that executes the query Id Query_time: Time elapsed for SQL execution Lock_time: Time to execute query lock on record Rows_sent: Number of rows returned by query Rows_examined: Number of rows read to return queried data

3. How to parse slow query logs

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

 --verbose verbose
 --debug debug
 --help write this text to standard output

 -v  verbose
 -d  debug
 -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
  al: average lock time
  ar: average rows sent
  at: average query time
   c: count
   l: lock time
   r: rows sent
   t: query time
 -r  reverse the sort order (largest last instead of first)
 -t NUM just show the top n queries
 -a  don't abstract all numbers to N and strings to 'S'
 -n NUM abstract numbers with at least n digits within names
 -g PATTERN grep: only consider stmts that include this string
 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
  default is '*', i.e. match all
 -i NAME name of server instance (if using mysql.server startup script)
 -l  don't subtract lock time from total time

Because the slow query log will contain a large number of duplicate SQL, for convenience, you can use the command line tool mysqldumpslow provided by mysql to analyze the log

$ mysqldumpslow.pl slow_log.log

Reading mysql slow query log from slow_log.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
 C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld.exe, Version: N.N.N-log (MySQL Community Server (GPL)). started with:
 TCP Port: N, Named Pipe: MySQL
 # Time: N-N-08T04:N:N.269315Z
 # User@Host: h5_test[h5_test] @ localhost [::N] Id: N
 # Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N
 use mc_productdb;
 SET timestamp=N;
 SELECT t.customer_id,t.title,t.content
 FROM (
 SELECT customer_id FROM product_comment WHERE product_id =N AND audit_status = N LIMIT N,N
 )a JOIN product_comment t
 ON a.customer_id = t.comment_id

It is similar to the data recorded in the slow query log, except that there is an extra line of Count, which records the execution times of this SQL during recording the slow query log. If an SQL is executed many times, only one SQL log will appear when analyzed with this command, and the numerical value in Count represents the execution times, and other numbers are replaced by N for merging

Summarize