MySQL Slow query configuration

1. What's the use of slow queries?

The ability to record all SQL statements that execute more than Long_query_time time will help you find SQL that executes slowly, so we can optimize these SQL.

2. How do I turn on slow queries?

First we check if the MySQL server's slow query status is turned on.

We can see that the current log_slow_queries status is off, indicating that no slow query is currently turned on.

3. Turn on Slow query

Locate the MySQL configuration file my.cnf (/ETC/MY.CNF) and add the configuration statement for the slow query below MYSQLD

Log-slow-queries: On behalf of the MySQL slow query log storage directory, this directory file must have write permissions.

Long_query_time: Maximum execution time. (, MSYQL will record all the execution time more than 2 SQL statements, here is the test time, the time should not be too small preferably within 5-10 seconds, of course, according to their own standards);

Configure it to restart a mysql later:

Service mysqld Restart

After the configuration is complete, let's look at the current status again, and you'll find the following:

Indicates that the slow query service has been turned on and the time limit is 2 seconds.

Let's take a look at whether the Mysql-slow.log file is automatically created under the/var/lib/mysql/directory.

Description already created Mysql-slow.log file

4. Testing

After executing a 3-second SQL statement, the SQL statement should be logged in the Mysql-slow.log file without exception. Ok! We cat mysql-slow.log a bit.

MySQL Slow query configuration