InnoDB performance tweaks

Question

I'm switching a large table to InnoDB from myISAM. There has been a lot of discussion regarding why switching makes sense, but not much about HOW to do it while making sure the table performs well.

Assuming I'll have InnoDB and MyISAM tables in on database, are there parameters I should change in the MySQL conf file to facilitate better performance?

Any other defaults that can be bumped up to tweak performance?

Answer 1

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_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE
engine='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 stop
rm ib_logfile0 ib_logfile1
service mysql start

If you are using MySQL 5.5, set the following:

innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_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_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables
WHERE 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/