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.