How to enable slow query in mysql

1. The configuration is enabled.
Linux: In the mysql configuration file my. add log-slow-queries =/var/lib/mysql/slowquery in cnf. log (specify the location where the log file is stored, which can be null, the system will give a default file host_name-slow.log) long_query_time = 2 (record time exceeded, default is 10 s) log-queries-not-using-indexes, all queries without indexes will also be recorded)
Windows: In my. add the following statement to [mysqld] of ini: log-slow-queries = E: \ web \ mysql \ log \ mysqlslowquery. loglong_query_time = 2 (other parameters are listed above) 2. For Linux, run the mysql built-in command mysqldumpslow to view Common commands-s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default-t NUM just show the top n queries-g PATTERN grep: only consider orders ts that include this stringeg: s, which is the order, the description is not detailed enough. I used it, including reading the code, mainly including c, t, l, r and ac, at, al, ar, which are based on the number of queries, time, lock time and The number of returned records is sorted. The time-reverse-t added to a indicates the top n, that is, the number of previous data records returned-g, you can write a regular expression matching mode, case-insensitive mysqldumpslow-s c-t 20 host-slow.logmysqldumpslow-s r-t 20 host-slow.log the preceding command shows the 20 most accessed SQL statements and 20 most returned SQL records. Mysqldumpslow-t 10-s t-g "left join" host-slow.log returns the first 10 SQL statements containing the left join according to time. Mysql slow query logs are very useful for queries with tracing problems. We can analyze the resource-consuming SQL statements in the current program. How can we open mysql slow query log records?
In fact, it is very easy to open the slow query log of mysql, just in the mysql configuration file (windows system is my. ini, linux is my. the following code is added to [mysqld] of cnf: log-slow-queries =/var/lib/mysql/slowquery. loglong_query_time = 2 Note: log-slow-queries writes logs there. If it is null, the system will assign the host name to the slow query log and append slow. log. /var/lib/mysql/slowquery. log is the location of the log storage file. Generally, this directory requires the write permission of the mysql running account. Generally, this directory is set to the mysql data storage directory www.2cto. 2 In comlong_query_time = 2 indicates that the query takes more than two seconds to record. if the log-long-format parameter is set, all queries without indexes will also be recorded. 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 that all queries are fast), and emphasizes the most important queries (the indexes are lost or the indexes are not used properly) # 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 (372); this is one of the slow query logs. It takes 136 seconds, locks 152 seconds, and returns rows, I checked a total of 263630 lines. If there is a lot of log Content, it will be exhausted to see it with one eye. mysql comes with an analysis tool. The method is as follows: Enter the mysql/bin directory under the command line, enter mysqldumpslow-help or -- help to see the parameters of this tool, mainly including Usage: mysqld Umpslow [OPTS...] [LOGS...] parse and summarize the MySQL slow query log. options are -- verbose -- 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 abstr Act numbers with at least n digits within names-g PATTERN grep: only consider tables ts 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 limit pt)-l don't subtract lock time from total time -www.2cto.com--s, which is the order, which indicates that the write is not detailed enough, including read code, mainly including c, t, l, r and ac, at, al, R is sorted by the number of queries, time, lock time, and number of returned records, respectively. When a is added, the reverse-t indicates top n, that is, the number of data records in the previous row-g is returned, and a regular expression matching mode can be written later, case Insensitive mysqldumpslow-s c-t 20 host-slow.log mysqldumpslow-s r-t 20 the above command shows the 20 most accessed SQL statements and 20 most returned Record Sets SQL. Mysqldumpslow-t 10-s t-g "left join" host-slow.log returns the first 10 SQL statements containing the left join according to time. Windows: When you enable mysql slow query for the first time, this record file will be created in the directory you specified. This article is mysqlslowquery. log, the content of this file is roughly as follows (when MYSQL slow query is enabled 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 use the following Command to view the number of slow query records: mysql> show global status like '% slow % '; + ------- + --- + | Variable_name | Value | + ------- + --- + | Slow_launch_threads | 0 | Slow_queries | 0 | + ------- + --- + [root @ mtargeting tomcatctl] # head/var /lib/mysql/slowquery. log [root @ mtargeting tomcatctl] # mysqldumpslow/var/lib/mysql/slowquery. log author cloudmail