Thursday, May 11, 2006

MyISAM vs InnoDB

I've been using MySQL for the database componenet of a number of projects over the years.

Usually, I've used the MyISAM storage engine. It's fast (that's the reason for using MySQL in the first place), and generally reliable.

MyISAM isn't robust against system failure though. System crashes, reboots, and power cuts tend not to be handled very well. (Yeah, I know, they shouldn't happen in the first place, but this is the real world.)

I don't need the transactional capabilities of InnoDB (from the functional point of view even MyISAM is overkill for most of what I do), but something more robust would help.

So I thought I would do a quick check of the impact of using InnoDB on the system. This isn't a benchmark, it's completely unscientific, and all that, but it told me what I needed to know.

Running InnoDB, one minute's worth of disk activity looks like:

r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device
0.1 7.0 0.8 34.1 0.0 0.1 1.6 12.4 1 5 d0

whereas with MyISAM it looks like:

r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device
0.1 2.3 0.8 3.0 0.0 0.0 3.5 14.5 1 1 d0

OK, so InnoDB generates 3 times as many writes, and 10 times as much data transfer, as MyISAM. And the mysqld process consumes correspondingly more cpu time (although it's still very small - much less than 1% of a processor), so the system load average is a bit lower with MyISAM too.

I don't think this rules out InnoDB, although it does indicate that there is a significant cost to changing, and to scale up by a factor 10 (which I'm going to need to do, and then some) would likely have problems if I was using InnoDB. If I go down that route, I need to do more optimisation of the system design and the database client interactions.


phil said...

"This isn't a benchmark, it's completely unscientific..."

Hence your results are completely pointless and invalid and you've just wasted your time running this test and writing this post.

To compare the two storage engines properly you would have to run a set number of queries of different types on each, but exactly the same queries on each, otherwise your comparting 1 minutes random traffic with another!

Anonymous said...

nice work .. i really appreciate it. But it you dint explain what would be the implications on the existing data if one tries migrate from MyISAM to InnoDB !!