MyISAM quote of the day
Seen in #maatkit on Freenode:
I never realized just how terrible recovering MyISAM from a crash can be
Sad but true — it can be pretty painful. This is one of the reasons I pretty much recommend InnoDB (okay, okay, XtraDB) for most data unless it’s read-only.
To add some context – I once tested it on some desktops in a MySQL Training course. We estimated it could be over a week on a 200G table ;)
Recovery time is fairly linear – and related to the size of the table. And it’s single threaded.
Morgan Tocker
18 Jun 09 at 6:51 pm
InnoDB recovery can be quite ugly (and slow) as well, and if things go wrong with the tablespace there’s a lot of bits that need to be brought back in sync.
Either way it’s DIY brainsurgery, a key difference is that technically speaking, getting data out of a corrupted MyISAM table (particularly fixed row format) is peanuts compared to an equivalent problem in InnoDB.
Each has their place.
Arjen Lentz
18 Jun 09 at 8:33 pm
Arjen, InnoDB recovery is automatic! Have you forgotten that? There’s no DIY brain surgery unless automatic recovery fails, and that’s probably what, .01% of cases?
MyISAM data is almost guaranteed to be corrupt after a crash. InnoDB is almost guaranteed NOT to be corrupt. Let’s put this into true perspective: if you care about your data, InnoDB or another ACID engine is a no-brainer.
Xaprb
19 Jun 09 at 8:44 am
It’s automatic, but not fast. And if something really breaks, it’s not good and you know it. Plus, people do stupid things; and then again, it’s not pretty. Come on, you work with the gang that’s writing all these InnoDB recovery tools. You don’t need a life buoy if the ship can’t sink, right?
Note that this is not an advertisement for MyISAM! Open Query too recommends using InnoDB as the default.
However, re MyISAM and corruption. What it tends to mess up on a crash is the indexes, and as you know they can be fixed – yes that does mean that the checking needs to be enabled, and that handling is a nasty in the server code. But proper configuration can deal with it. Heck, I might tweak that code some time to at least check and report by default even if it doesn’t backup/repair without specific settings.
Arjen Lentz
19 Jun 09 at 8:51 am
We don’t get much InnoDB recovery business, and since we are basically THE company in the world who can/does do that work, I think it’s a pretty good advertisement for InnoDB’s robustness. And most of those cases are related to hardware failures, not a server crashing or losing power. Absent a hardware failure, it’s pretty hard to corrupt InnoDB’s data.
Xaprb
19 Jun 09 at 9:01 am
Some of us still yearn for the long fsck runs before file systems had proper journals; running myisamchk after a crash brings back that old sense of pioneering adventure. Wondering whether you’ll actually get your data back also makes the wait a keenly interesting time. Who would want to miss out on that?
Robert Hodges
21 Jun 09 at 11:31 am