If you’re using MySQL, always choose InnoDB over MyISAM

A friend of mine posed the following question recently:

Do you have an opinion over using myisam or innodb for a read heavy MySQL DB?

I do indeed have an opinion on this topic. It is thus: Use InnoDB for everything, use MyISAM for nothing.

In some speed tests, it might appear that MyISAM is faster for read-heavy workloads. However, that only translates to reality when you've got a database which is doing nothing apart from read-only work (e.g. data warehouse system generating reports during the day, and importing new transaction data in the night.)

In most environments, the speed you get is measured by the amount of concurrency you can support, not by the throughput of a single transaction running without anything else running on the machine. InnoDB allows reads and writes simultaneously (row-level locking; multi-version concurrency control), whereas MyISAM just locks the whole table as soon as you do anything.

For example, a system where one user is reading their data, another user is writing their data, and a third user is running some reports over the entire database, the following will happen with MyISAM:

With InnoDB, on the other hand, writes lock only the rows involved, and only against other writes.

And then there are the long list of non-performance related advantages of InnoDB over MyISM: the fact that InnoDB allows you to use transactions, allows foreign key constraint checking, and various other things, which you'll be wanting to use.

BTW if you're going to do a migration, a company I worked for did this once:

Rather than doing a "turn off; migrate; turn on" scenario, they set up a new server with the new storage engine and turned on replication. A few weeks later, all the data was replicated to the new server. So they they just turned off the old server, made sure the two instances were in-sync, turned on the new server, so that reduced the downtime by quite a bit. That also works from MySQL 4.x -> 5.0, and 5.0 -> 5.1, indeed going from 4.x MyISM to 5.1 InnoDB involved a chain of databases all replicating from one another.

For more information on migration, see: http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/

This article is © Adrian Smith.
It was originally published on 16 Nov 2012
More on: MySQL