When some functions in a web service respond slower than usual when data grows and we are trying to find out what specific database queries are slowing down the service, we can give MySQL Slow Query Log a try.

Let’s start with enabling it.

> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.000 sec)

Note that the SET GLOBAL VARIABLES is only effective in the current running MySQL instance. It will switch back to the default value after MySQL restarts.

By default, any queries that take above 10 seconds to run is recognized as “slow query”. To change the threshold to any period besides 10, type the following command.

> SET GLOBAL long_query_time = 5;
Query OK, 0 rows affected (0.000 sec)

Next, we also want to specify where we are going to store the slow query logs. In our case let’s set it to file here.

> SET GLOBAL log_output = 'FILE';
Query OK, 0 rows affected (0.000 sec)

Take a look at the location where MySQL is storing the log file.

> SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+-----------------+
| Variable_name       | Value           |
+---------------------+-----------------+
| slow_query_log_file | ubuntu-slow.log |
+---------------------+-----------------+
1 row in set (0.001 sec)

Now, everything is settled. Open another MySQL client and try SELECT SLEEP(6); to simulate a slow query that takes 6 seconds to response.

After that, we can open the file /var/lib/mysql/ubuntu-slow.log (location from the output of the last command) and see that the query is correctly logged:)

...
# Time: 210615 23:56:55
# User@Host: root[root] @ localhost []
# Thread_id: 11694  Schema:   QC_hit: No
# Query_time: 6.000225  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 63
SET timestamp=1652003815;
SELECT SLEEP(6);

When we finish debugging, simply set the slow query log variable to OFF to turn off logging.

> SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.000 sec)