MySQL Method of Turning on Slow Query Log Function

mysql slow query log is very useful for tracking problematic queries. It can analyze whether there are sql statements that consume resources in the current program. This is a useful log. It has little impact on performance (assuming all queries are fast) and emphasizes those queries that need the most attention (the index is lost or the index is not best used). How do you turn on the slow query logging of mysql?

Open the slow query log, which can make MySQL record the statements that query more than the specified time. By locating the bottleneck of analysis performance, we can better optimize the performance of the database system.

(1) Configuration on

Linux:

Add the following statement to the mysql configuration file my. cnf:

log-slow-queries=/var/lib/mysql/slowquery.log # Specify the log file storage location, which can be empty, and the system will give 1 Default files host_name-slow.log
long_query_time=5 # Record the elapsed time, and the default is 10s Where the query time is set to exceed 5s Query statement of 
log-queries-not-using-indexes = on #  List query statements that do not use indexes 
#log-queries-not-using-indexes  Is it necessary to record all non-indexed query You can decide whether to turn it on or not according to the situation 
#log-long-format  Whether to record all query records, including queries that do not use indexes 

Windows:

Add the following statement to [mysqld] of my. ini (statement options and definitions are the same as above):

log-slow-queries = E:\mysql\log\mysqlslowquery.log
long_query_time = 5

(2) Viewing mode

Linux:

Use the command mysqldumpslow that comes with mysql to view

Common commands

-s ORDER what to sort by (t, at, l, al, r, ES50etc), 'at' is default -t NUM just show the top n queries -g PATTERN grep: only consider stmts that include this string

eg:

-s, is the sequence of order, the description is not detailed enough, I used it, including looking at the code, mainly c, t, l, r and ac, at, al, ar, respectively according to the number of query, time, lock time and the number of records returned to sort, before adding the reverse order of a

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

-g, which can be followed by a regular matching pattern, which is insensitive to case

Specific commands are used as follows:

mysqldumpslow -s c -t 20 host-slow.log

mysqldumpslow -s r -t 20 host-slow.log

The above command shows the 20 most visited sql statements and the 20 most returned sql recordsets.

mysqldumpslow -t 10 -s t -g “left join” host-slow.log This is to return the first 10 sql statements with left connection according to time.

Windows:

When you start the slow query of mysql for the first time, you will create this record file in the directory you specified. This article is mysqlslowquery. log, and the contents of this file are roughly as follows (when you start the slow query of MYSQL for the first time)

E:\ web\ mysql\ bin\ mysqld, Version: 5.4. 3-beta-community-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument

You can view the number of records of slow query by the following command:

mysql> show global status like  ' %slow%';
+---------------------+-------+
| Variable_name    | Value |
+---------------------+-------+
| Slow_launch_threads | 0   |
| Slow_queries    | 0   |
+---------------------+-------+

Test

1. Execute a slow query SQL statement

mysql> select sleep(2);

2. Check to see if a slow query log is generated

ls /usr/local/mysql/data/slow.log

If the log exists, MySQL started the slow query setting successfully!

Summarize