Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.
share|improve this answer
    
This information is out of date for MySQL 5.6. I've written a blog post about it here: tocker.ca/2013/06/19/… –  Morgan Tocker Nov 13 '13 at 15:29
add comment

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
share|improve this answer
    
Good point about battery-backed write caches. I should have said "disable any non-durable write back caches". –  plinehan Oct 5 '10 at 21:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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