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

Note: This is written for mysql version 5.5. Old mysql version has slightly different syntax.

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. If you’re using EE4, the location of mysql config file is mentioned in it’s documentation.

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

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"

Don’t forget..

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