The function of slow query logs

Slow query logs record THE SQL statements that take longer than the specified time to query. Using slow query logs, you can locate performance bottlenecks

Check whether the slow log query function is enabled and the directory for storing slow log files

SHOW VARIABLES LIKE 'slow_query%'
Copy the code
  • Enable slow log query
    • Slow_query_log Specifies the status of slow query logs
    • Long_query_time Specifies the query timeout duration, in seconds

In the MySQL configuration file /etc/my.cnf, set

slow_query_log=ON
long_query_time=1
Copy the code

Enable slow query logs to record SQL statements that are queried for more than one second. The logs take effect after the MySQL server is restarted.

You can test the following using the following SQL

SELECT SLEEP(2);
Copy the code

Slow query log files

Tcp port: 0 Unix socket: (null) Time Id Command Argument # Time: 210125 6:30:14 # User@Host: Reptile [reptile] @ [192.168.10.254] Id: 1 # Query_time: 2.000380 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1611556214; SELECT SLEEP(2);Copy the code
  • SET timestamp=1611556214; Execute SQL timestamp
  • Query_time SQL execution duration
  • Rows_sent returns several records