Adam Innes · Blog

MySQL 5.5 Made InnoDB the Default. Bye, MyISAM

· 4 min · mysql, innodb, databases, lamp

MySQL 5.5 has been generally available since 5.5.8 on December 3, and 5.5.9 followed on February 7. The headline for most of us is a changed default. Starting with 5.5.5, a CREATE TABLE without an ENGINE clause makes an InnoDB table instead of a MyISAM one. If you’ve been running PHP apps on MySQL for years, a lot of your tables are probably MyISAM simply because nobody ever picked anything else.

The MySQL 5.5 release notes are clear about the scope. Existing tables aren’t touched, only new ones. The system tables in the mysql database stay MyISAM, and the embedded version of MySQL still defaults to MyISAM. So upgrading won’t convert anything behind your back, but every table your app creates from now on will be InnoDB unless it says otherwise.

What InnoDB actually gives you

The MySQL 5.5 Reference Manual lists the big advantages as transactions that follow the ACID model (commit, rollback and crash recovery), row level locking, and foreign key constraints. Here’s what those mean when you’re writing a web app.

Transactions let a group of changes succeed or fail together. The classic example is moving money between two rows. With MyISAM, if PHP dies between the two updates, one account is short and nothing puts it back. With InnoDB you wrap them up:

START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

Either both updates land or neither does. Foreign keys come along too, so the database itself can stop you from inserting an order for a customer who doesn’t exist.

Row level locking is the one you feel under load. The manual says MySQL uses table level locking for MyISAM and row level locking for InnoDB. It also points out that most web apps do lots of selects, relatively few deletes, and updates mostly by key, and that MyISAM is well tuned for that. The trouble starts on a table that gets a steady stream of updates (sessions, comments, a counter somebody thought was a good idea). A MyISAM write takes a lock on the whole table, so writes queue up one at a time, and the manual warns that with many updates, SELECTs wait until the updates are done. InnoDB lets writers to different rows get on with it.

Crash recovery is the one you feel at 3 a.m. After a crash, according to the manual, InnoDB finishes applying changes that were committed and rolls back the ones that weren’t, and you don’t need to do anything special beyond restarting the server. The manual also says that recovery process is much faster in 5.5 than it used to be.

Converting your old tables

First find out what you have. This lists the MyISAM tables in one database:

SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop' AND ENGINE = 'MyISAM';

Converting a table is one statement:

ALTER TABLE orders ENGINE=InnoDB;

That’s the easy part. The manual has a few warnings that are worth taking seriously. Don’t convert the system tables in the mysql database, since that’s unsupported and they must stay MyISAM. InnoDB tables need a lot more disk space than MyISAM ones, and if an ALTER TABLE runs out of space, it starts a rollback that can take hours on a slow disk. Since ALTER TABLE builds a new copy of the table, make sure you have room for both before converting anything big. My advice is to convert a copy of production first, time your biggest table, and schedule the real thing for a quiet window.

The release notes also suggest testing your application’s install and runtime behavior against InnoDB and benchmarking your most important queries, since index needs can change.

The full text search catch

Here’s the one that stops people. In MySQL 5.5, the manual says full text indexes can be used only with MyISAM tables. If your site search uses MATCH ... AGAINST on a FULLTEXT index, that table has to stay MyISAM. Try to put a FULLTEXT index on an InnoDB table and MySQL refuses with error 1214, which says the table type doesn’t support FULLTEXT indexes.

The fix the release notes recommend is to be explicit. Add ENGINE=MyISAM to the CREATE TABLE for tables that need MyISAM features like full text search, and let everything else move to InnoDB. The engine is chosen per table, so mixing them in one database is fine. One workable setup is a small MyISAM table that holds just the searchable text, sitting next to InnoDB tables for everything that matters.

Applications are moving too. The INSTALL.mysql.txt in Drupal 7.0, for example, says Drupal uses InnoDB for all its tables when it’s available.

The takeaway

The new default is the right one for almost any web app that writes data. Let new tables be InnoDB, convert your busiest MyISAM tables on a copy first, and keep MyISAM only where you truly need full text search. If you have a my.cnf that sets default-storage-engine=MyISAM from years ago, now is a good time to ask whether it still earns its place.

← all posts