4

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?

plinehan
  • 595
  • 1
  • 4
  • 6

2 Answers2

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.
Morgan Tocker
  • 208
  • 2
  • 12
  • This information is out of date for MySQL 5.6. I've written a blog post about it here: http://www.tocker.ca/2013/06/19/deciding-whether-or-not-to-make-mysql-durable.html – Morgan Tocker Nov 13 '13 at 15:29
1

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
Mark Henderson
  • 68,316
  • 31
  • 175
  • 255
Synchro
  • 2,983
  • 5
  • 25
  • 35
  • Good point about battery-backed write caches. I should have said "disable any non-durable write back caches". – plinehan Oct 05 '10 at 21:29