I am trying to determine the difference in durability between MySQL's MyISAM and InnoDB when innodb_flush_log_at_trx_commit=0.

I am aware of the some of the benefits of InnoDB with ACID. However, under the default settings inserts with InnoDB are too slow. Changing innodb_flush_log_at_trx_commit speeds things up, but we could loose about 1 or so seconds of data (which we are rather ok with).

Even with innodb_flush_log_at_trx_commit set to 0 (or 2) is InnoDB more durable than MyISAM? Is it less susceptible to data corruption? Or does it essentially reduce InnoDB's durability and corruption to that of MyISAM?

Any thoughts on the differences?

closed as not constructive by Ladadadada, HopelessN00b, Magellan, EEAA, rnxrx Oct 27 '12 at 5:53

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. If this question can be reworded to fit the rules in the help center, please edit the question.


InnoDB definitely more durable than MyISAM even if you go with trx_commit=2. it is good in performance, however is not ACID anymore but I am using this configuration for a long time on several write intensive instances and already had a few crashes without any corruption.

  • In what way is InnoDB more durable? It seems that using trx_commit=0||2 InnoDB delays sync with the disk to about one second later. Clearly this will give speed up. Does MyISAM do the same thing by default? And if it does, then shouldn't they have similar durability? Or, is the durability and corruption problem with MyISAM elsewhere and not due to an delayed sync with the disk? – adamSpline Oct 26 '12 at 20:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.