The mysql slow query log

The first step to tuning your database, is to find out what's running slow.

I recommend doing tuning your queries before changing your MySql memory allocation, sessions etc.

Its always better to treat the problem ground up (by finding rogue queries) rather than put a quick fix and forget about it (e.g. allocating more memory or setting sessions). Over time, the quick fixes will lead you to buy more hardware and cost more.

1. Setup the MySQL Slow query log:

To setup the log, first uncomment the log_slow_queries and long_query_time parameters:

# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 5
#log-queries-not-using-indexes

This will set the slow query log to: /var/log/mysql/mysql-slow.log and will log any queries that take more than 5 seconds.

To start with, you should set the long_query_time to a higher value, so you don't get overwhelmed with too many slow queries. Later you can bring this down to 2 seconds or so.

And later you should also set the 'log-queries-not-using-indexes, to identify queries that will slow significantly as data load increases.

2. Restart mysql:

In ubuntu, simply run the command:

sudo /etc/init.d/mysql restart

This will restart the mysql server and start logging your slow queries.

3. Use your database, and keep checking for slow queries!

I hope this helps you in tuning your MySql database from the ground up.