Why do I recommend switching over from MyISAM to InnoDB?

Ovais Tariq -

MyISAM has been the default storage engine for MySQL for years now. But that is soon going to change. With MySQL 5.5, Innodb is going to be the default storage engine (and I am pretty pleased about that, no more new users complaining about MySQL not being ACID compliant).

But why do I recommend Innodb over MyISAM. Following are the reasons:

I will start of with reasons not so obviously discussed during comparisons of Innodb and MyISAM storage engines. But these reasons are so compelling that I bet you would want to switch over to Innodb after reading the reasons.

Innodb is being constantly improved and worked upon

There hasn’t been much work put forth into improving the MyISAM storage engine, and with Oracle acquiring MySQL and owning Innodb, it has put its weight on the Innodb storage engine. While there hasn’t been much work done on MyISAM side, Innodb has seen improvements and work is being done aggressively. MySQL 5.5 will ship with a new version of Innodb – version 1.1, which has major performance improvements. Even if you are using MySQL 5.1, you can replace the built-in Innodb with the newer Innodb plugin – version 1.0.8, which has introduced major performance improvements.

Innodb supports row-level locking

Innodb not only supports table-level locking, but also supports row-level locking, making it the storage engine of choice for high concurrency applications.

Innodb supports hot backup

With MyISAM, backing up a live database, is pretty much an issue for many DBAs because of the way how MyISAM supports locking. MyISAM only supports table-level locks which pretty much means that the server must go down during the backup, for sometime depending upon the amount of data in the database. With Innodb this is no issue at all, Innodb supports row level locking which allows you to do hot backups.

Innodb is ideal for high-concurrency applications

Yet another big reason for using Innodb is that with Innodb you can achieve high-user-throughput and high-database-concurrency. That is again thanks to the row-level mechanism of Innodb, which makes it a big winner (compared to MyISAM) in cases where you need highly concurrent read-write operations.

Innodb is crash proof

What I essentially mean by being crash proof is the database, recovering back into a consistent state in case of a crash (be it an OS crash or a MySQL server crash). While in the case of MyISAM, a crash would mean that you would have to deal with recovering tables on the crash or partially executed statements, in the case of Innodb there would be essentially no data inconsistency and all the more its more of an automated recovery, where by Innodb would recover itself by replaying its logs.

Innodb supports clustered indexes

Innodb stores rows ordered physically in PRIMARY KEY order. Storing the rows in primary key order corresponds to what is known in MSSQL Server as “Clustered index” and in Oracle as “index organized tables”. This makes primary key fetches extremely efficient.

Innodb implies various internal optimizations that improve the performance further

There are many different optimizations that Innodb performs internally that improve performance drastically. Following are some of the optimizations:

  • Adaptive Hash Indexing:
    To quote from the MySQL manual: “If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes rather than B-tree lookups. InnoDB monitors searches on each index defined for a table. If it notices that certain index values are being accessed frequently, it automatically builds an in-memory hash table for that index.
  • Read Ahead:
    A read-ahead request is an I/O request sent by Innodb to pre-fetch data pages into the innodb buffer pool cache. Innodb sends these read-ahead requests based on the access patterns of the data pages already in the buffer pool cache. This pre-fetching can improve read performance drastically for data that is being fetched linearly.
  • Innodb Buffer Pool:
    While MyISAM relies on the OS cache for caching reads and writes, Innodb has its own cache which it manages on its own and which is of course fine-tuned for Innodb operations. And combine this with adaptive hash indexing and read-aheads, and you can imagine the performance gains.
  • Innodb change buffering:
    When INSERT, UPDATE, and DELETE operations are performed on a table, Innodb buffers these changes into the Innodb buffer pool instead of flushing them directly to disk instantly. These changes are flushed to the disk only when needed. This avoids expensive I/O operations and thus providing great performance boost for applications that do a lot of inserts/updates/deletes. To quote the MySQL manual: “Because it can result in fewer disk reads and writes, this feature is most valuable for workloads that are I/O-bound, for example applications with a high volume of DML operations such as bulk inserts.”
  • Innodb background flushes:
    Innodb performs many of the I/O operations using asynchronous background threads, operations like read-ahead requests, dirty-pages flushing etc. This ensures that these I/O operations do not block user operations on the database.

Now come the reasons that are discussed more often, mentioned in all the comparisons of Innodb and MyISAM. Although you might not need transactions or foreign key constraints in all of your applications, but these do make Innodb a complete winner when compared with MyISAM.

Innodb supports transactions

Innodb supports ACID-compliant transactions, what that means is that you can group SQL statements in a transaction and be sure that the database will be in a consistent state, either you commit the transaction or rollback, or even more so the server crashes in the middle of a SQL statement.

Innodb supports foreign key constraints

Innodb supports foreign keys, so that you can enforce parent-child relationships. Operations such as deleting child rows when parent is deleted, or updating the child rows on updating of parent row are supported and enforced. This ensures that the database takes care of ensuring that the relationships are consistent, without the onus being on the application developer.

Conclusion:

If you are still not convinced about Innodb and have some questions in mind you may discuss them out with me.

What’s coming?

I had written a post about tuning MySQL server settings applicable to the MyISAM storage engine, I will soon be posting about tuning Innodb related MySQL server settings. Besides that, I will soon be deploying MySQL 5.5 on the server hosting this blog and I will be sharing my findings soon. Besides that I plan on posting some benchmarking results for MyISAM vs Innodb.