What tables should or could be migrated to InnoDB, and which should remain MyISAM?

Question

As per title: Is there a quick list of candidates for tables to migrate to InnoDB? And what should remain MyISAM.

Some additional information

• The site has a fairly heavy read-load, but inserts approximately 10 nodes per hour, with tags and such.
• We make heavy use of CCK (a huge amount of normalised tables in the form of content_field%).
• We also use Views for about all our blocks and pages; but many of those are candidate for replacement with custom modules (as to reduce database-queries and the heaviness of those queries).
• Users are all anonymous; with the exception of a few logged in editors and webmasters.

Answer 1

You should convert all data to InnoDB to prevent table locking issues. However, here are somethings to think about:

FULLTEXT Indexing

At present, only MyISAM supports FULLTEXT indexing. FULLTEXT indexing for InnoDB is currently in the works for MySQL 5.6 but is not production-ready. If you have any Drupal tables that have FULLTEXT indexes, they cannot be converted to InnoDB at this time.

UPDATE on FULLTEXT Indexing

MySQL 5.6 is now GA (out for Production Use). Please try out FULLTEXT indexing in InnoDB.

To locate those tables that have a FULLTEXT index, run this query:

SELECT table_schema,table_name
FROM information_schema.statistics
WHERE index_type='FULLTEXT';

If no rows come back, convert all InnoDB tables to your heart's content. I wrote an earlier post on how to convert all MyISAM tables to InnoDB using only mysql.

MySQL Replication

If you have a read-heavy environment, reads can go faster in MyISAM if you do the following:

• Setup Master/Slave Replication
• Create one or more Read Slaves under the Master
• Add --skip-innodb in /etc/my.cnf on all Slaves (converts tables to MyISAM when loading data into the Slave)
• Change the row format of all MyISAM tables on every Slave to FIXED by this command: ALTER TABLE tblname ROW_FORMAT=FIXED;
• I posted something on this in the DBA StackExchange
• The book MySQL Database Design and Tuning recommends using ROW_FORMAT=FIXED on pages 72,73. This will internally convert all VARCHAR fields to CHAR. It will make the MyISAM table larger, but executed SELECTs against it will be much faster. I can personally attest to this. I once had a table that was 1.9GB. I changed the format with ALTER TABLE tblname ROW_FORMAT=FIXED. The table ended up 3.7GB. The speed of the SELECTs against it was 20-25% faster without improving or changing anything else.

The only headache with this is making your application aware of separate read slaves.

EPILOGUE

If you are looking at other benefits each storage engine has, check the DBA StackExchange:

• Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
• Sep 20, 2011 : Best of MyISAM and InnoDB
• May 02, 2012 : Which mysql storage engine to choose?
• May 03, 2012 : Which is faster, InnoDB or MyISAM?

Answer 2

Since your website is on the read-heavy side, I'd simply convert all tables to InnoDB. You'll then be able to optimize read performance by sizing the InnoDB buffer pool and the query cache appropriately. This way, we're achieving several thousand queries per second on the dedicated database servers in our Drupal hosting infrastructure.