This post will explains how to enable slow query log in MySQL. Slow query log helps in optimizing the long query and hence it improves application performance in good amount. Since it’s not recommended to keep enable slow query on server, because it slow down the performance of application. You can enable slow query log for optimization, debug and once your purpose is solve then disable slow query log.
So let’s start.
First, you needs to login to mysql shell on server. You can login using SSH. At the commnad line window, just type the below commnad.
mysql -u root -p
It will ask for password. Enter the password for root.
To enable slow query log, type the below command in MySQL shell.
SET GLOBAL slow_query_log = ‘ON’;
Now the slow query is enabled. After this you have to setup time and path of slow query log.
1. You have to set the time for query that is taking more than given time. By default, when you enabled slow query log, it sets time 10 seconds. It means, it will log queries those are taking more than 10 seconds.
SET GLOBAL long_query_time = X;
2. Now, you need to setup the slow query log path. You need to type the below command in MySQL shell.
SET GLOBAL slow_query_log_file = ‘/path/filename’;
That’s all. Slow query log is enabled now. TO check and verify the slow query log, you need to logout and login back to MySQL shell.
Now login again to MySQL.
mysql -u root -p
To check if the slow query log working fine, type the below the command in MySQL shell
X is greater than time you set in long_query_time;
Now you can perform the optimization related operations and debug. When you have done the with optimization, you must disable slow query log. You can disable slow query log by typing the below command in MySQL shell.
SET GLOBAL slow_query_log = ‘OFF’;
Note: You have to logout and login back to MySQL to take this command effect.