You may need to consider converting everything to InnoDB anyway in order to prevent table locking issues. However, here are a couple of things to think about:
FULLTEXT Indexing
At present, only MyISAM supports FULLTEXT indexing. FULLTEXT indexing in InnoDB is currently in the works for MySQL 5.6 but is not production-ready. If you have any Drupal tables using FULLTEXT indexes, they cannot be converted to 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.
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.
Configuration
You will need to allocate enough RAM for InnoDB data, InnoDB indexes, and MyISAM indexes (MySQL Does Not Cache MyISAM data). You will need to computer the sum of the InnoDB Data Pages and Index Pages to size up innodb_buffer_pool_size. You will also need to compute the sum of the MyISAM index pages to size up key_buffer_size.
Once your main data is InnoDB, you should upgrade to MySQL 5.5 so that you can take advantage of the new settings to engage multiple CPUs in accessing InnoDB data. Regardless of the version of MySQL, you are totally at the mercy of whatever version of MySQL is running if you do not configure InnoDB properly. There are documented cases of older versions of MySQL running better that newer versions given the same level playing field.
The only thing left to your discretion is making your application aware of separate read slaves and performing all writes (INSERTs, UPDATEs, DELETEs) at the InnoDB-based Replication Masters.
UPDATE 2012-01-03 14:25 EDT
To Mass Convert all MyISAM tables to have ROW_FORMAT='Fixed' here are the steps
mysql -uroot -ppassword -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ROW_FORMAT=Fixed;') FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine = 'MyISAM' ORDER BY data_length" > MyISAMRowFormatConversionToFixed.sql
mysql -uroot -ppassword -A < MyISAMRowFormatConversionToFixed.sql
If you want to try this out by converting the 5 smallest tables, add LIMIT 5 to the query:
mysql -uroot -ppassword -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ROW_FORMAT=Fixed;') FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine = 'MyISAM' ORDER BY data_length LIMIT 5" > MyISAMRowFormatConversionToFixed.sql
mysql -uroot -ppassword -A < MyISAMRowFormatConversionToFixed.sql
SHOW FULL PROCESSLIST