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:
- As soon as e.g. a minute-long report query starts, nothing else will be able to write to the database until it ends. This means that all your users who want to write to the database will simply wait that number of minutes. Further, if it's a web application, as those users will consume an Apache child process, any further read-only requests will also fail as Apache will run out of available child processes.
- If you have a number of individual users doing small write queries and small read queries, the writes will wait on the reads and vice versa. This means you will have low concurrent throughput.
With InnoDB, on the other hand, writes lock only the rows involved, and only against other writes.
- The minute-long report query doesn't lock anything. Other users can read and write anything they like.
- Multiple individual users can write to their own rows concurrently without waiting on each other.
- Even after a "commit" has been issued for a change, the old value is stored. This means that the minute-long report sees the values of the data which were valid at the point in time the statement or transaction started, even if, during the execution of the statement, another transaction altered the data and committed that change ("consistent read").
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/