How to Enable Slow Query Log in MySQL
Today, we will use MySQL shell on Windows and Ubuntu 20.04 to enable slow_query_log in MySQL. For this tutorial, we are using MySQL version 8.0 and Ubuntu 20.04.
the MySQL slow_query_log
The MySQL slow_query_log contains SQL statements that consume more than long_query_time seconds to run and need at least min_examined_row_limit rows (records) to be examined.
The SQL queries that come into sight in the MySQL slow_query_log are the queries that consume a substantial time to run. Therefore, these are the candidates that need optimization.
By default, the slow query log is disabled. Let’s see how we can enable it on Windows & Ubuntu 20.04.
Enable MySQL slow_query_log in Windows/Ubuntu
The queries given below can be executed on Windows and Ubuntu operating systems. We need to enter the MySQL shell first and then execute the following command to enable the MySQL slow_query_log.
Example Code:
mysql> SET GLOBAL slow_query_log = 'ON';
Now, run the following query to ensure that slow_query_log is enabled.
Example Code:
mysql> SHOW VARIABLES LIKE '%slow%';
OUTPUT:
+-----------------------------+--------------------------+
| Variable_name | Value |
+-----------------------------+--------------------------+
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_replica_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |<====================
| slow_query_log_file | DESKTOP-QF52OT4-slow.log |
+-----------------------------+--------------------------+
7 rows in set (0.01 sec)
Alternatively, we can also execute the command as follows to see whether the slow_query_log is enabled or not.
Example Code:
mysql> SHOW VARIABLES LIKE '%quer%';
OUTPUT:
+----------------------------------------+--------------------------+
| Variable_name | Value |
+----------------------------------------+--------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | NO |
| log_queries_not_using_indexes | OFF |
| log_throttle_queries_not_using_indexes | 0 |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
| slow_query_log | ON |<=========
| slow_query_log_file | DESKTOP-QF52OT4-slow.log |
+----------------------------------------+--------------------------+
10 rows in set (0.00 sec)
See the second last row in both outputs (given above). The slow_query_log is ON now.
Once we enable the slow_query_log, we can also enable other options. For instance, we can also update the time needed by a query to execute before being logged.
Example Code:
mysql> SET GLOBAL long_query_time = 20;
