Wednesday, November 6, 2013

Analyse slow-query-log using mysqldumpslow & pt-query-digest

Mysql can log slow queries which takes longer to execute. In some cases this is expected but some queries take longer because of coding mistakes. slow-query-log can definitely help you find those queries and make it easy to debug your application.

In WordPress world, many plugins are often coded my amateurs who have no idea about the scale at which big sites operate! Its better to use slow-query-log to find out such plugins.

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"
https://github.com/box/Anemometer

No comments:

Post a Comment