What steps must one take to ensure that an otherwise defaultly-configured InnoDB server is truly ACID compliant? The InnoDB configuration page mentions that the hardware itself must be configured to honor fsync calls, i.e. disable any write-back caches.

This page mentions some other concerns, but may be conflating the binary log and the InnoDB log, and may be a bit out of date regarding default settings for MySQL 5.x.

Upon reading the binary log document page it would seem that the "sync_binlog=1" setting is not required for ACID properties in general, only for ACID properties vis a vis point-in-time recovery and replication.

So, is disabling write-back disk caching sufficient, or are there other settings that must be tweaked?

2 Answers 2


You ask a hard question. Here are the settings in MySQL:

  • sync-frm
  • sync-binlog
  • innodb-flush-log-at-trx-commit = 1 (default)
  • innodb_support_xa = 1 (default)
  • innodb_doublewrite= 1 (default)
  • sync-relay-log = 1 (if a slave)
  • rpl_transaction_enabled=1 (if a slave - only in 5.5 / Percona Server)

And from the OS/Filesystem/etc:

  • Disable any non battery backed caches on disks or raid controllers.
  • Make sure the OS is not in any laptop mode, etc.

Disabling write-back isn't necessarily going to break ACID. It won't do so if you have battery-backed write cache on your RAID controller, and the payoff for having that is a huge increase in write capacity. Something to beware of though is that some hard drives and SSDs (e.g. intel X-25) have their own write back caches that are NOT battery backed even if your RAID controller is and they definitely need disabling. Setting O_DIRECT will also keep your OS and file system out of the way and let the RAID controller do its thing more effectively.

I normally use these settings for reasonable reliability:

innodb_support_xa = 1
innodb-flush-log-at-trx-commit  = 2
  • Good point about battery-backed write caches. I should have said "disable any non-durable write back caches".
    – plinehan
    Oct 5, 2010 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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