Your innodb_buffer_pool_size should be set to the amount of InnoDB data and indexes you have. Run this query and it will tell you the Minimum recommended setting for mysql's current Innodb Data

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_innodb_buffer_pool_sizeFROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHEREengine='InnoDB') A,(SELECT 3 pw) B;

If your InnoDB data far exceeds the installed RAM on the DB server, I recommend 75% of the installed RAM on the box. So, if you have a 16GB server, use 12G as the innodb_buffer_pool_size.

You must also set innodb_log_file_size to 25% of innodb_buffer_pool_size or 2047M, which ever is smaller. To change the file ib_logfile0 and ib_logfile1, you must:

mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0;"service mysql stoprm ib_logfile0 ib_logfile1service mysql start

If you are using MySQL 5.5, set the following:

innodb_read_io_threads=64innodb_write_io_threads=64innodb_io_capacity=20000 (set this to your device's IOPs)

If you will retain MyISAM data run this query for the ideal setting for key_buffer_size:

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_key_buffer_sizeFROM (SELECT SUM(index_length) KBS FROM information_schema.tablesWHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A,(SELECT 3 pw) B;

UPDATE 2013-02-13 12:55 EDT

I have learned lately not to set innodb_io_capacity very high, if at all. This is especially true on commodity hardware and VMs:

• http://www.mysqlperformanceblog.com/2012/02/17/the-relationship-between-innodb-log-checkpointing-and-dirty-buffer-pool-pages/
• http://www.mysqlperformanceblog.com/2010/12/20/mysql-5-5-8-and-percona-server-being-adaptive/