MySQL slow query log makes it easy to track SQL queries that take more than a specific time for execution. This allows you to find inefficient SQL queries that can be optimized to improve database performance. Here are the steps to enable MySQL slow query log. You can use them to enable slow query log in RDS, MariaDB and Aurora.
How To Enable MySQL Slow Query Log in MySQL
Here are the steps to enable slow query log in MySQL.
1. Log into MySQL
Open terminal and log into MySQL
$ mysql -u root -p
You will be prompted to enter password. Enter it to log in.
Bonus Read : Top 5 MySQL Monitoring Tools
2. Enable Slow Query Log
Type the following command to turn on the slow query log
mysql> SET GLOBAL slow_query_log = 'ON';
3. Customize Slow Query Log
After you turn on the slow query log, MySQL will log any SQL query that takes more than 10 seconds to run.
If you want, you can change this time interval by running the following command. Replace ‘x’ with the time interval seconds you want MySQL to wait before logging a query.
mysql> SET GLOBAL long_query_time = x;
How to Find Slow Query Log
By default, MySQL stores slow query log at /var/lib/mysql/hostname-slow.log. If you want, you can change this to a different file path using the following command. Replace path and filename with file path and filename respectively.
mysql> SET GLOBAL slow_query_log_file = '/path/filename';
Bonus Read : Top 5 MySQL GUI Tools
4. Verify Slow query logging
Log out of MySQL and log in again. Run the following query to log it as a slow query. Replace y with the time interval greater than the value you used in step 3.
mysql> sleep(y);
Open the slow query log
$ sudo vi /path/filename
You will find information about the above query.
Bonus Read : How to Speed Up MySQL Query
5. Disable Slow Query log
If you want to disable slow query log, then log into MySQL and run the following command.
mysql> SET GLOBAL slow_query_log = 'OFF';
Hopefully, the above article will help you enable slow query log in MySQL.
