How do I turn on slow query logging for mysql
- 2020-05-15 02:16:39
- OfStack
The mysql slow query log is very useful for tracking problematic queries. You can figure out the current program has a very expensive sql statement. How do you turn on the slow query log of mysql?
In fact, it is very easy to open the slow query log of mysql, just add the following code below [mysqld] in the configuration file of mysql (windows is my.ini,linux is my.cnf) :
Note:
log slow - queries set write the log, there is empty, the system will be given the host name to the slow query log, and additional slow. log. / var/lib/mysql/slowquery log deposit for the log file location, 1 of the directory to be mysql running account can write permissions, 1 kind is set the directory to mysql data storage directory
The 2 in long_query_time=2 indicates that the query took more than two seconds to record.
If the parameter log-long-format is set, then all queries that do not use the index will also be logged. Add the following line to the file my.cnf or my.ini to record these queries
This is a useful log. It has little impact on performance (assuming all queries are fast) and highlights the ones that need the most attention (missing indexes or not being used optimically)
# Time: 070927 8:08:52
# User@Host: root[root] @ [192.168.0.20]
# Query_time: 372 Lock_time: 136 Rows_sent: 152 Rows_examined: 263630
select id, name from manager where id in (66,10135);
This is one of the slow query logs. It took 372 seconds, locked for 136 seconds, returned 152 rows, and 1 checked a total of 263630 rows
If there is a lot of content in the log, it will be tiring to read it one by one with your eyes. mysql has its own tool for analysis, and the usage is as follows:
From the command line, go into the mysql/bin directory and enter the mysqldumpslow, help or --help to see the parameters of the tool, mainly
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 (t, at, l, al, r, ar etc), 'at' is default
-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 scr scr pt)
-l don't subtract lock time from total time
-s, is the order of order, the description is not written in enough detail, I used it, including looking at the code, mainly have
c, t l, r and ac at, al, ar, respectively, in accordance with the query frequency, time, lock time and returns the number of records to sorting, adding the a flashbacks
-t, which means top n, is the number of previous data returned
-g, after which you can write a regular matching pattern, case insensitive
mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log
The above command shows the 20 most accessed sql statements and the 20 most returned sql recordsets.
mysqldumpslow-t 10-s t-g "left join" host-slow.log
This is a time-by-time return of the first 10 sql statements that contain the left join.
In fact, it is very easy to open the slow query log of mysql, just add the following code below [mysqld] in the configuration file of mysql (windows is my.ini,linux is my.cnf) :
log-slow-queries=/var/lib/mysql/slowquery.log
long_query_time=2
Note:
log slow - queries set write the log, there is empty, the system will be given the host name to the slow query log, and additional slow. log. / var/lib/mysql/slowquery log deposit for the log file location, 1 of the directory to be mysql running account can write permissions, 1 kind is set the directory to mysql data storage directory
The 2 in long_query_time=2 indicates that the query took more than two seconds to record.
If the parameter log-long-format is set, then all queries that do not use the index will also be logged. Add the following line to the file my.cnf or my.ini to record these queries
This is a useful log. It has little impact on performance (assuming all queries are fast) and highlights the ones that need the most attention (missing indexes or not being used optimically)
# Time: 070927 8:08:52
# User@Host: root[root] @ [192.168.0.20]
# Query_time: 372 Lock_time: 136 Rows_sent: 152 Rows_examined: 263630
select id, name from manager where id in (66,10135);
This is one of the slow query logs. It took 372 seconds, locked for 136 seconds, returned 152 rows, and 1 checked a total of 263630 rows
If there is a lot of content in the log, it will be tiring to read it one by one with your eyes. mysql has its own tool for analysis, and the usage is as follows:
From the command line, go into the mysql/bin directory and enter the mysqldumpslow, help or --help to see the parameters of the tool, mainly
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 (t, at, l, al, r, ar etc), 'at' is default
-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 scr scr pt)
-l don't subtract lock time from total time
-s, is the order of order, the description is not written in enough detail, I used it, including looking at the code, mainly have
c, t l, r and ac at, al, ar, respectively, in accordance with the query frequency, time, lock time and returns the number of records to sorting, adding the a flashbacks
-t, which means top n, is the number of previous data returned
-g, after which you can write a regular matching pattern, case insensitive
mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log
The above command shows the 20 most accessed sql statements and the 20 most returned sql recordsets.
mysqldumpslow-t 10-s t-g "left join" host-slow.log
This is a time-by-time return of the first 10 sql statements that contain the left join.