Home / server / Questions / 164276
In Process
plinehan
Asked: 2010-07-27 14:13:09 +0800 CST

Guaranteeing ACID properties for InnoDB databases

  • 772

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?

mysql database innodb data-consistency

2 Answers

  1. 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.
    • 2

  2. 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_flush_method=O_DIRECT
innodb_support_xa = 1
innodb-flush-log-at-trx-commit  = 2
    • 1