July 29, 2010

 Importance of tuning MySQL with innodb_buffer_pool_size and key_buffer_size

Out of the box, MySQL has very limited memory allocation for cache use. If you are using Atmail with MySQL in production, it is imperative you review and optimize MySQL for your hardware and memory. You can increase performance dramatically and reduce I/O usage by tweaking MySQL to use more of your system memory for the cache and buffers.

The two easy options for increasing performance for mysql are the innodb_buffer_pool_size and key_buffer_size options. Atmail uses InnoDB tables for the user authentication & log-files, and if you have a large userbase performance can be dramatically improved by increasing the innodb_buffer_pool_size. Other tables such as the UserSettings, Abook, use the MyISAM table format which uses the key_buffer_size option.

For a production machine running all the Atmail services with 2GB of RAM we'd recommend the following option:

/etc/my.cnf:

innodb_buffer_pool_size=256M
key_buffer_size=256M

If you have 4GB RAM, double the above. Once enabled reboot MySQL, and you will see via the process table the daemon will be allocated more memory.

Tune and check for your environment, and remember not to use the mysqld defaults for a production environment!


Filed under: Data Mining/SQL Queries,Database — info @ 3:44 pm

1 Comment »

  1. If you want to optimize more then I recommend to use this script to give more hints on what could be done: https://github.com/rackerhacker/MySQLTuner-perl

    Comment by Raffael — April 7, 2011 @ 3:47 pm

RSS feed for comments on this post. TrackBack URI

Leave a comment