Setting up Slow Query log – MySql
Slow query log is a very useful tool using which you can identify slow running queries in your application and then tune them accordingly. This post in scope of MySql database server, which is an open source and free database server for community.
A growing application generally get corrupted by slow running queries over the time of development. This is generally not because of poor query writing skills but the changes done in DB over time or changes in use of table over the long time. Slow query log gives you opportunities to check all these instances where you can prevent the introduction of a slow query in your application. It is advisable that development or testing instance of MySql Server should run with slow query log configuration so that these queries can be caught in time and tuned accordingly.
How to setup slow query log for a mysql instance?
It is quite easy configuration which needs to be done to my.ini or my.cnf file. On windows systems my.ini generally resides in Program Files/MySql/Mysql server x.x folder which can be edited using ant text editor.
Add following lines in [mysqld] section:
[mysqld] ...... ...... # Time in seconds , All queries running more than # specified time will be logged in file long_query_time=1 # Where to create log of slow queries log-slow-queries=c:\\slow-query.log ..... .....
You need to create this file manually in C drive and then restart the mysql service (Start -> Run -> type ‘service.msc’ – > Select Mysql service – > Restart )
On linux machine my.cnf file generally resides at /etc/my.cnf or /etc/mysql/my.cnf and can be edited in similar way.
Note: Mysql service generally runs under some user account and if that user account is not the owner of file created for logging slow queries, logging will not be done. This can be problem on linux machines where user management is better and strong. Even on windows machine you need to check that you should have administrative rights if mysql service is running using administrator account.
use following command to change owner ship of a file on linux system (assuming mysql is the user under which mysql service is running)
#sudo chown mysql /var/log/mysql/slow-query.log
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Hi,
I follow above instructions.
File is generated but no entries found under it.
I am using WAMP.