Posted on 2 Comments

Migrating MySQL MyISAM apps to the InnoDB storage engine

Today, most of us professionally involved with MySQL deployments regard InnoDB as the default engine to use, although the server “default default” is still MyISAM and many (even new) deployments still end up on MyISAM. It used to be the case that InnoDB had advantages in terms of write-concurrency, crash recovery and consistency/durability as well as in some cases speed, however it uses more disk space and to perform adequately it requires at least more RAM. For deployments that didn’t care for write-concurrency, MyISAM often remained the favourite. But most servers now have enough basic horsepower and sufficient RAM, and the disk-usage issue has also been addressed in the 5.1 InnoDB plugin with compressed pages. Disk use is often not the biggest issue though.

So anyway, if you don’t have FULLTEXT indexes (use Sphinx!), can’t you simple run ALTER TABLE ... ENGINE=InnoDB on all your tables (or use the mysql_convert_table_format script) and live on happily? Not quite…
First of all you’ll want to tune the InnoDB subsystem in terms of buffer pool, log file size, and some other aspects.
But also, your application can encounter additional error situations when using InnoDB, which it must handle. In my previous post (Error handling for MySQL applications) I already indicated that many apps just don’t handle errors properly at all. Many errors are not fatal, and many are transient. That is, if you do the same command again, it’ll generally succeed.

InnoDB being transactional and using row-level locking, it’s possible and entirely normal to encounter deadlocks. They are distinctly non-fatal, but do require appropriate handling. For a multi-statement transaction (i.e. more than one query, wrapped in a BEGIN or START TRANSACTION and COMMIT construct), when you get one of the lock timeout errors or a deadlock error, you can simply ROLLBACK and then re-run the whole transaction construct. This should not cause an endless loop, but re-running at least once is sensible.

If you’re not (yet) using InnoDB but just MyISAM, you’re not in the clear though: regular lock timeouts can also occur with MyISAM, although problems are, in my experience, less common.

I’m always intrigued about the “why” of things, so in this case… why aren’t MySQL application developers aware of these things? And I think I might be able to answer that question. Anyone with a background in using transactional database systems (Oracle, etc) has been taught to expert and deal with these things, so it’ll be an intrinsic part of how they write code. Contrarily, MySQL’s non-transactional origins and the fact that many MySQL application developers do not have a background in database engineering or other database systems, explains most of it.

But it’s also MyISAM being deadlock free by design, that has allowed MySQL app developers to be particularly lazy about these things, AND get away with it! MyISAM always locks specified tables in a particular order whereas InnoDB, with its row-level locking, can’t control the flow of locking needs, and thus cannot guarantee to be deadlock free. It’s not a flaw (or magic) on either side, it’s just consequences and possibilities due to the capabilities of the engines and their resulting internal architecture (also covered by one of the Open Query training day-modules).

So, learning this and without needing to pass blame on MySQL, any particular engine, or developers, we can now get on with things and develop (and fix up!) applications to have appropriate logic, whichever engine might be used underneath. But most importantly, please remember that just converting an app from MyISAM to InnoDB without checking up on the error handling is not safe.

Posted on 2 Comments

2 thoughts on “Migrating MySQL MyISAM apps to the InnoDB storage engine

  1. You said:

    If you’re not (yet) using InnoDB but just MyISAM, you’re not in the clear though: regular lock timeouts can also occur with MyISAM, although problems are, in my experience, less common.

    Locks almost never timeout in MyISAM. They only can in 5.0 if you are using server-side cursors to the client (which almost no one does since they are pretty bad). It requires using the mysql_stmt_attr_set API with the prepared statement API, which will then cause table_lock_wait_timeout to take effect.

    Otherwise, in normal operations MyISAM locks will wait forever.

  2. Hi Harry!
    Thanks for that clarification.

Comments are closed.