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"

https://github.com/box/Anemometer

Don’t forget..

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

Convert from INNODB to MYISAM

PREVIOUS

mysqlcheck with cron to optimize automatically

NEXT

Comments

Nabi

March 12, 2014

Thanks for good article.
But before it must do this:
mkdir /var/log/mysql/
touch /var/log/mysql/mysql-slow.log
chown -R mysql:mysql /var/log/mysql/
And restart mysql:
service mysql restart
1. Rahul Bansal
  
  March 14, 2014
  
  I will check again. I guess /var/log/mysql gets created with mysql installation.
  
  Thanks for your comment and commands though. Might help somebody. 🙂

Features

Useful Links

Product

Other Products

GoDAM

Subscribe for curated product updates, tips, and announcements.

"*" indicates required fields

Phone

This field is for validation purposes and should be left unchanged.

Email*

SIGN ME UP