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?

Gaeta

Tons of them. Read the manual, and the MySQL Performance Blog. If you're going to use both storage engines you need to set all the parameters for both. For InnoDB, start with the buffer pool size and transaction commit type. – Stoltzfus

What means big tables (how many gigs?)? What OS do you use? – Pushover

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/

Pugnacious

This is awesome, thanks for the queries! Question: why did you just give a blanket recommendation for the innodb_io_capacity @ 20000? Shouldn't that number match the server's disk IOPS? – Bannerman

@ClaytonDukes you are absolutely right. I have a Web Hosting Client with that kind of hardware. Sorry for the extreme numbers. – Pugnacious