Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use the slow query log in MySQL. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Parameters related to slow query log

Parameters related to MySQL slow query explain: slow_query_log: whether to enable slow query log. 1: enable; 0: disable.

Slow_query_log: whether to enable slow query log. 1: enable, 0: disable.

Log-slow-queries: the storage path of slow query logs in MySQL database in the old version (version 5.6 or later). If you do not set this parameter, the system will default to a default file host_name-slow.log

Slow-query-log-file: the storage path of slow query logs in the new version (version 5.6 and above) of MySQL database. If you do not set this parameter, the system will default to a default file host_name-slow.log

Long_query_time: slow query threshold. Log is recorded when the query time exceeds 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 method. Log_output='FILE' means to save the log to a file, and the default value is' FILE'. Log_output='TABLE' means to store the log in the database so that the log information is written to the mysql.slow_ log table. MySQL data

The library supports two log storage methods at the same time, which can be separated by commas when configured, such as log_output='FILE,TABLE'. Logging to the dedicated log table of the system consumes more system resources than recording files, so for slow query logs that need to be enabled, you also need to

To be able to achieve higher system performance, it is recommended that you log to a file first.

one。 Setting method

Capture in the slow query log

Some settings need to be made 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 a SQL that does not use an index

Set global log_queries_not_using_indexes = on

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

Set global long_query_time = 0.001

The 0.001 seconds set here is easy to test, and it is generally larger than this.

Enable mysql slow query log

Set global slow_query_log = on

Method 2: profile settings

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

[mysqld] slow_query_log = ONlog_queries_not_using_indexes = ON;slow_query_log_file = / usr/local/mysql/data/slow.loglong_query_time = 1

View the parameters after setting

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

two。 Contents of slow query log records

Time Id Command Argument# Time: 2019-01-08T04:12:09.269315Z # User@Host: h6 _ test [h6 _ 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.customertrainidLIMIT. Title ON a.customer_id. Content FROM (SELECT customer_id FROM product_comment WHERE product_id = 199726 AND audit_status = 1 LIMIT 0Magol 15) a JOIN product_comment t ON a.customer_id = t.comment_id

Time: date and time when the query was executed

User@Host: user and client IP that executes the query

Id: is the thread Id that executes the query

Time consumed by Query_time:SQL execution

Lock_time: the time when the query was executed to lock the record

Rows_sent: the number of rows returned by the query

Rows_examined: the number of rows read to return the data of the query

three。 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), 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 contains 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.logReading mysql slow query log from slow_log.logCount: 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: h6 _ test [h6 _ 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.customermechanidON a.customer_id t.titleMagi t.content FROM (SELECT customer_id FROM product_comment WHERE product_id = N AND audit_status = N LIMIT N Magi N) a JOIN product_comment t ON a.customer_id = t.comment_id

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

The above is the editor for you to share how to use the slow query log in MySQL, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.