newbie Gotcha – storage engines

As we started to make the switch to MySQL, several of our managers and developers (and I) assumed we’d use clustering to improve availability and performance. (Performance, because they thought we’d be able to spread our load among clustered servers.)

I read up on storage engines and got to explain more than once that we couldn’t use clustering, much to their surprise and initial disbelief.

Clustering relies on a particular database engine (NDB) that is currently an in-memory-only engine. (Ie, all your data has to fit in memory.) Since our database is much bigger than our RAM, we couldn’t use it. (Future versions promise to allow data stored on disk.) We ended up using the InnoDB storage engine, and replication for availability and performance.

So what is a storage engine? If you don’t know, you need to find out before moving to MySQL.

MySQL implements a sort of data-handling architecture, into which you can “plug” various data-storing implementations. Ie, how you store your data on disk (among other things) depends on which storage engine you use.

The original storage engine for MySQL was ISAM (now MyISAM).

However, if you want ACID-compliant transactions (with commits, rollbacks, etc), or foreign keys in the database, you currently need to use InnoDB (or buy SolidDB). A new transactional engine from MySQL, Falcon, is in the works.

There are many storage engines, each with its own features and limitations. You can mix-and-match, and use more than one at the same time. Here are some sources where you can read more.

A nice intro by Arjen Lentz is available from MySQL. It includes a comparison of features.

Another intro, by Robin Schumacher, is also available from MySQL. It comes in two unlinked pages: part 1 and part 2.

A linux planet tutorial is also available online.

When you’re done with those, you can consult the manual (chapter 13) if you want further details on particular storage engines.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: