How to enable the slow query log function in MySQL

The MySQL slow query log is very useful for tracking problematic queries. It can analyze whether there are SQL statements that consume a lot of resources in the current program. This is a useful log. It has little impact on performance (assuming all queries are fast) and highlights those queries that need the most attention (missing indexes or indexes not being optimally applied). So how do you turn on MySQL slow query logging?

By enabling the slow query log, MySQL can record query statements that exceed the specified time. By locating and analyzing performance bottlenecks, the performance of the database system can be better optimized.

(1) Configuration start

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. The system will give a default file host_name-slow.log
long_query_time=5 #Record the time exceeded, the default is 10s, here set to query statements with query time exceeding 5s log-queries-not-using-indexes = on #List query statements that do not use indexes #log-queries-not-using-indexes Whether to record all queries that do not use indexes, you can decide whether to turn it on 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 in [mysqld] of my.ini (the statement options and their meanings are the same as above):

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

(2) Viewing method

Linux:

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

eg:

-s is the order. The description is not detailed enough. I have used it and read the code. There are mainly c, t, l, r and ac, at, al, ar, which are sorted by query times, time, lock time and number of records returned respectively. The order is reversed when a is added in front.

-t means top n, which means how many records are returned.

-g, you can write a regular matching pattern after it, it is case insensitive

The specific commands are as follows:

mysqldumpslow -sc -t 20 host-slow.log

mysqldumpslow -sr -t 20 host-slow.log

The above command can show the 20 SQL statements with the most access times and the 20 SQL statements with the most returned records.

mysqldumpslow -t 10 -st -g “left join” host-slow.log returns the first 10 SQL statements containing left joins by time.

Windows:

When you enable MySQL slow query for the first time, a log file will be created in the directory you specified. In this article, it is mysqlslowquery.log. The contents of this file are 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 |
+---------------------+-------+

test

1. Execute a slow query SQL statement

mysql> select sleep(2);

2. Check whether slow query logs are generated

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

If the log exists, MySQL slow query setting is enabled successfully!

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links