This is an old revision of the document!

MySQL tunning

3 Minute MySQL (tuning)

max_connections - set to the same (or a little bigger than) as MaxChildren from apache if you’re only using one mysql connection per request (this is most common)
key_buffer_size - Set to roughly half your available ram.
thread_cache_size - set it until show status like ‘threads_created’ quits increasing.
table_cache_size - set until show status like ‘opened_tables’ quits increasing. If you have thousands of tables this might not be possible

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.
set innodb_buffer_pool_size to some reasonable number.
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

    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

    echo 0 > /proc/sys/vm/swappiness

    mount /mnt/koha -o remount,nobarrier

Optimization links: