Enable slow-query-log

You can enable slow-log by un-commenting following lines in /etc/mysql/my.cnf

slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

Last line will tell slow-log to log queries not using indexes. You can keep it commented if you want to ignore queries which are not using indexes.

If your server has less RAM and you are seeing many of your queries in slow-query-log, you may increase value of long_query_time.

Its advisable to enable slow-query-log while debugging only and disable it once you are done with it. Lets move on to analysis part.

mysqldumpslow
This comes bundled with mysql-server.

mysqldumpslow /var/log/mysql/mysql-slow.log

Following will show top 5 query which returned maximum rows. It can find queries where you missed LIMIT clause. A common performance killer!

mysqldumpslow -a -s r -t 5 /var/log/mysql/mysql-slow.log

Following will sort output by count i.e. number of times query found in slow-log. Most frequency queries sometimes turned out to be unexpected queries!

mysqldumpslow -a -s c -t 5 /var/log/mysql/mysql-slow.log

pt-query-digest
This is part of percona toolkit.

Then basic usage is:

pt-query-digest /var/log/mysql/mysql-slow.log

If you have multiple databases, you can enable filtering for a particular database:

pt-query-digest /var/log/mysql/mysql-slow.log --filter '$event->{db} eq "db_wordpress"'

mysqlsla
This is another 3rd party tool. Can be downloaded from here.

Basic Usage:

 ./mysqlsla /var/log/mysql/mysql-slow.log

Filter for a database:

./mysqlsla /var/log/mysql/mysql-slow.log -mf "db=db_name"

MySQL Slow Query Monitor. This tool is used to analyze slow query logs collected from MySQL instances to identify problematic queries.
https://github.com/box/Anemometer

Don’t forget..
Always restart mysql, every time you enable/disable slow-query-log for changes to take effect.

About This Author

I'm a software engineer with many years of experience, open source enthusiast, now I'm creating and contributing to awesome PHP web projects. I love coding as much as learning, and I enjoy trying new languages and patterns. My passion revolves around (but is not limited to) back-end development.

You are not signed in. Sign in to post comments.