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:

mysql/tunning.txt · Last modified: 2011/01/01 19:16 by a
CC Attribution-Share Alike 4.0 International
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0 ipv6 ready