2

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?

HopelessN00b
  • 53,385
  • 32
  • 133
  • 208
adamSpline
  • 21
  • 1

1 Answers1

3

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.

Gabor Vincze
  • 554
  • 1
  • 4
  • 11
  • 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