Basic usage tutorial of MySQL slow query log

Slow query log related parameters

MySQL slow query related parameter explanation: slow_query_log: whether to enable slow query log, 1 means enable, 0 means disable.

• slow_query_log : Whether to enable slow query log, 1 means enabled, 0 means disabled.
• log-slow-queries: The storage path of the slow query log of the old version (below 5.6) of MySQL database. You can leave this parameter unset, the system will default to a default file host_name-slow.log
• slow-query-log-file: The storage path of the slow query log of the new version (5.6 and above) of the MySQL database. You can leave this parameter unset, the system will default to a default file host_name-slow.log
• long_query_time: slow query threshold. When the query time exceeds the set threshold, a log is recorded.
• log_queries_not_using_indexes: Queries that do not use indexes are also logged to the slow query log (optional).
• log_output: log storage method. log_output='FILE' means saving logs to files. The default value is 'FILE'. log_output='TABLE' means storing the log in the database, so the log information will be written to the mysql.slow_log table. MySQL database supports two log storage methods at the same time. When configuring, just separate them with commas, such as: log_output='FILE,TABLE'. Logging to the system's dedicated log table consumes more system resources than logging to files. Therefore, if you need to enable slow query logs and achieve higher system performance, it is recommended to log to files first.

1. Setting method

Use slow query log to capture

Some settings are required before enabling

Method 1: Global variable setting

Set the log file location for the slow query log

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

Set whether to log SQL statements that do not use indexes

set global log_queries_not_using_indexes = on;

Set to record as long as the SQL execution time exceeds n seconds

set global long_query_time = 0.001 ;

The setting of 0.001 seconds here is for testing purposes. Generally, it is larger than this.

Enable MySQL slow query log

set global slow_query_log = on;

Method 2: Configuration file settings

Modify the configuration file my.cnf and add the following 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 parameters after setting

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

2. Contents of slow query 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: The date and time when the query was executed
• User@Host: The user and client IP address that executed the query
• Id: is the thread ID that executes the query
• Query_time: The time consumed by SQL execution
• Lock_time: The time it takes to execute a query to lock the record
• Rows_sent: The number of rows returned by the query
• Rows_examined: The number of rows read to return the queried data

3. How to analyze 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),
  The default is '*', ie match all
 -i NAME name of server instance (if using mysql.server startup script)
 -l don't subtract lock time from total time

Since the slow query log contains a large number of repeated SQLs, 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 NN\bin\mysqld.exe, Version: NNN-log (MySQL Community Server (GPL)). started with:
 TCP Port: N, Named Pipe: MySQL
 # Time: NN-08T04:N:N.269315Z
 # User@Host: h5_test[h5_test] @ localhost [::N] Id: N
 # Query_time: NN Lock_time: NN 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

The data recorded in the slow query log is similar to that in the slow query log, except that there is an additional line of Count, which records the number of times this SQL is executed during the period of recording the slow query log. If a SQL is executed multiple times, only one SQL log will appear when analyzing with this command. The value in Count represents the number of executions, and other numbers are replaced by N for merging.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.