How to enable slow query logs in mysql

Many a times it happens that our web server gets too busy and ultimately goes unresponsive. And we dont even know why it happened. For such situations I recommend you should enable slow query logging in mysql and try to optimize your slow queries.

Slow query logging can be easily enabled by following the below mentioned steps:

Step 1:
You need to edit the file "/etc/my.cnf" and place the following code in it.

log-slow-queries=/var/lib/mysql/slow.log
long_query_time = 6

Note: Dont forget to take the backup of "my.cnf".

The first line will enable the slow query logging and specifies the path where the log is to be saved. The second line will set the criteria for slow queries. That is queries that takes more then 6 seconds will be considered as slow queries.


Step 2:
Now after updating "my.cnf" you need to restart mysql. This can be done using any of the below mentioned shell commands.

service mysqld restart

OR

/etc/init.d/mysqld restart

Step 3:
After restarting you need to flush mysql logs in order to get the file automatically created. This can be done by using the below mentioned shell command:

mysqladmin flush-logs

Well guys i guess thats it, you have successfully enabled slow query logging in mysql. For any queries that you may have, feel free to comment.