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

How to enable performance_schema in MySQL 5.5

One of the cool feature of MySQL 5.5 performance schema is not enabled by default to enable it in the my.cnf for linux add variable under mysqld

 [mysqld]
 performance_schema

to check if the performance_schema

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

Tune MySQL parameters

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

Tune Linux

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

Optimization links: