Is MyIsam safer than InnoDB?

Question

Is MyIsam engine safer than InnoDB about loss of data due to FileSystem error?

It seems that InnoDB is not reparable with MySQL tool.

I had to choose my engine and I chose InnoDB because of foreign keys, but I will consider engine migration if this seems to be more safer.

Answer 1

InnoDB has a very elaborate architecture in the background

InnoDB Architecture

In the event of a crash, InnoDB has the double write buffer and the log files to support mechanisms for crash recovery.MyISAM has no such protection. I have written posts in the DBA StackExchange about this:

• Oct 08, 2012 : MYSQL MyISAM data recovery implementation
• Mar 19, 2012 : Very small MySQL table keeps crashing
• Mar 15, 2012 : Why do MySQL tables crash? How do I prevent it?
• Feb 16, 2012 : MyISAM table keeps crashing. What are my options?

MyISAM can crash too easily since data are never cached in memory. All read and writes require brute force I/O from the MyISAM tables .MYD file. This means open files handles that are at the mercy of OS.

If you want InnoDB to be repairable, you need to configure my.cnf to make InnoDB less reliant on the OS and more reliant on the Internal Architecture. See my recent post High average load due to the High mysql cpu usage. That way, InnoDB Crash Recovery can be more self-healing.

Answer 2

Filesystem errors, or even block level errors, aren't the sort of the items extensive tested or accounted for by the implementation of MyISAM or Innodb. While some detection may happen when they are written there's a large assumption that what once its written without an error returned, it is correct. Checksums exist and are checked on read, but the error recovery path on on pages/rows that fail checksum I don't think have been thought through fully.

I suspect the right risk mitigation strategy for this (and a few other forms of server failure) is to run a replication slave on a different filesystem, and as a DR, keep binary logs and a logical SQL snapshot dump. This way you don't need to rely on tools that might be able to work around some forms of failure with dataloss.

Answer 3

I would use InnoDB because of locks per record vs MyIsam locks per hole table. Also InnoDB doesn't require repair tool as already said earlier. Furthermore InnoDB engine can save tables in separate files so it looks safer from the point of filesystem crash.