MySQL is slow! Did you tune your settings?

Can MySQL be slow? Yes it can. It depends on disk speed (even if you have fancy SSD drives) and some tuning can turn down query time dramatically, especially if you are currently using default settings and/or your accumulated several gigabytes of data. Sure, there is documentation about optimizing MySQL, and bunch of information on Internet but where to start?

Tune MySQL parameters

For a start, download mysqltuner.pl or tuning-primer.sh and run them. While they won't give you exactly same recommendations it will give you nudge into right direction.

I would especially recommend setting innodb_buffer_pool_size to some reasonable number. This will provide immediate performance improvement, especially if you are running with default Debian settings and have couple of gigs of data.

Find slow queries

But linking to scripts wouldn't be worth a blog post. Next step is to find which queries are slow on your system. To do that, you can use Maatkit or more specifically mk-query-digest. You can feed it with /var/log/mysql/mysql-slow.log or run in on live system:

$ wget maatkit.org/get/mk-query-digest
$ perl mk-query-digest --processlist localhost --interval 0.01

This will create considerable load on your system (since we are using inverval of 0.01 seconds to capture all queries running longer that that), but after you press Ctrl+C you will have good idea which queries need speedup. You can also take a look at Baron Schwartz video from OpenSQLCamp 2009 or presentation about mk-query-digest from PgEast 2010 if you want to know more. Hint: it supports MySQL (slow query log, binlog, genlog or tcpdump), HTTP, PostgreSQL and memcached.

If you are can modify application, dig into show queries. In my case, I found out that session handling in Koha takes more than 75% of all MySQL query time. I decided to leave this for future improvement. But, if you can't or don't want to modify application, simply move to next topic.

Tune Linux

Yoshinori Matsunobu had great presentation Linux Performance Tuning and Stabilization Tips at recent MySQL Conference & Expo in which he covered all various ways to optimize Linux for MySQL workloads. I will summarize here just few parts which I found useful for my workload which involves multiple LXCs, each with own logical volume using btrfs and sitting on battery backed RAID controller.

turn off readhead
```
hdparm -a 0 /dev/sdb
```
use noop or deadline scheduler for block device on which is logical volume with MySQL
```
echo deadline > /sys/block/sdb/queue/scheduler
```
reduce swappiness
```
echo 0 > /proc/sys/vm/swappiness
```
disable write barriers (only with battery backed controller!)
```
mount /mnt/koha -o remount,nobarrier
```

Results

So did all of this made sense? It's best to have clear goal when optimizing, and mine was less than 3 second web response. Without any MySQL tweaking and 3Gb database it was taking 5 seconds or more for each response. To my amazement, without any modification in application itself, I managed to produce visible improvement, even if only real comment I got was: It's not slow today :-)