Thursday, 13 October 2011

Tracking slow MySql queries

Useful tool to track slow queries, no need to do any coding, just need to change the MySql config :

gksudo gedit /etc/mysql/my.cnf

Search for "slow" and change the lines to

log_slow_queries = /var/lib/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

Restart mysql 

sudo service mysql restart

Run your applications, then any queries that take longer than 1 second will be stored in the log. This can be viewed with

sudo mysqldumpslow | more

This will display the slowest queries first. Useful to keep running while in production to optimise your system.

To see the queries with the actual data

sudo more /var/lib/mysql/mysql-slow.log

Credit and thanks to Laurent David @ TDM

No comments:

Post a comment