1.18 MySQL Slow Query Log

1. What It Is Record statements whose query time exceeds a certain threshold in the log 2. Usage 2.1. How to Enable It You need to reconnect to the database before the effect becomes visible 2.2. How to View It - View the log directly - Use the mysqldumpslow command 3. References

DatabasesCreated 06/08/2021Updated 02/26/20221 min readhistorical

This is a historical learning note and may contain outdated or incomplete understanding.

1. What It Is

Record statements whose query time exceeds a certain threshold in the log.

show variables like '%slow_query_log%';

+---------------------+------------------------+
| Variable_name       | Value                  |
+---------------------+------------------------+
| slow_query_log      | OFF                    |
| slow_query_log_file | <example>-slow.log     |
+---------------------+------------------------+

show variables like '%long_query_time%';

+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

2. Usage

2.1. How to Enable It

set global slow_query_log=1;
set global long_query_time=3;

You need to reconnect to the database before the effect becomes visible.

2.2. How to View It

View the log directly

tail -f /var/lib/mysql/<example>-slow.log -n 200

Use the mysqldumpslow command

3. References

https://mariadb.com/kb/en/library/documentation/mariadb-administration/server-monitoring-logs/slow-query-log/slow-query-log-overview/ https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html