MySQL tunning
See also:
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: