Mysql – Guaranteeing ACID properties for InnoDB databases

data-consistencydatabaseinnodbMySQL

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?

Best Answer

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.
Related Topic