Edit your /etc/mysql/my.cnf file – the default file comes with a bug for setting up this slow query log, but find the line which starts log_slow_queries (it’ll be commented out probably). Don’t uncomment it, instead use something like these values:

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

long_query_time can be a decimal if you wish – it’s in seconds, but you can enter 0.1 or even 0 to just log everything.

You also need to create that log file, so use these commands to do that:

mkdir /var/log/mysql
touch /var/log/mysql/slow.log
chown mysql:mysql -R /var/log/mysql

Now restart MySQL/PerconaDB using:

service mysql restart

Run your pages on your site you wish to analyse – you can use a webcrawler if you like – Screaming Frog is great for this if you don’t have a specific set of pages you’re looking to test and just wish to test everything – download the free version, point it at your websites home page and it will spider your site and hit a lot of pages. Or just visit whichever pages you know to be slow, or even just leave it running to get your users usage pattern and discover which queries are slow for them. Run this command to check your log file is filling up:

tail /var/log/mysql/slow.log

You will see some text that PerconaDB is saving to the file. To analyse this file and get a nice hierarchy of results showing you which queries are having the most impact on your server, run this command:

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

(if you don’t have it installed already, you’ll be told the command to install it)

This will show you your league table of sql statements with the one having the most negative impact on your server at the top. You can copy one of the examples of this query to your favourite MySQL client (Toad, MySQL Workbench or just directly through SSH) and run the explain command against it to identify which tables are being hit the most and where an index or query modification will help.