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

How to open the slow query log in MySQL? I believe that many inexperienced people are at a loss about this, so this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

(1) configuration is enabled

Linux:

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

Log-slow-queries=/var/lib/mysql/slowquery.log # specifies the location of the log file, which can be empty. The system will give a default file, host_name-slow.loglong_query_time=5 #, to record for more than 10 seconds. Here, the query statement log-queries-not-using-indexes = on # is set to list the query statements that do not use the index # log-queries-not-using-indexes whether to record all the query that do not use the index, you can decide whether to turn on # log-long-format and whether to record all query records, including queries that do not use the index.

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.loglong_query_time = 5

(2) viewing method

Linux:

Use the mysql native command mysqldumpslow to view

Common command

-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 stmts that include this string

Eg:

-s, is the order of order, indicating that the writing is not detailed enough. I used it, including reading the code. There are mainly crecinct and ac,at,al,ar, which are sorted by the number of query, the time, the time of lock and the number of records returned. The reverse order of an is added before.

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

-g, a regular matching pattern can be written later, which is case-insensitive.

The specific commands are as follows:

Mysqldumpslow-s c-t 20 host-slow.logmysqldumpslow-s r-t 20 host-slow.log

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

Mysqldumpslow-t 10-st- g "left join" host-slow.log this returns the first 10 sql statements with left concatenation in time.

Windows:

When you first open the slow query of mysql, the record file will be created in the directory you specify. This article is mysqlslowquery.log, and the content of this file is roughly as follows (in the case of opening MYSQL slow query 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 for slow queries with 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)

two。 Check to see if slow query logs are generated

Ls / usr/local/mysql/data/slow.log after reading the above, do you know how to open the slow log in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.