6

I've seen some people suggest that O_DSYNC should be used with a SAN.

The MySQL docs have this to say about O_DSYNC in general:

In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and other similar methods is surprisingly slow. If you are dissatisfied with database write performance, you might try setting the innodb_flush_method parameter to O_DSYNC. The O_DSYNC flush method seems to perform slower on most systems, but yours might not be one of them.

And specifically with regard to use with a SAN:

On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with the same type of hardware and workload that reflects your production environment.

Although they suggest one should test, the wording is so wishy-washy (i.e. "some systems, "might be") that one could be forgiven if they didn't even bother testing and stuck with the default (fdatasync). But in my (unscientific) tests I have found O_DSYNC to provide enormous performance benefit - at least with some troublesome SELECT queries.

I was wondering if anyone could explain in greater detail why this option can provide such a benefit when using a SAN. The good book of course discusses this option but not in the context of a SAN.

I'm using:

  • MySQL 5.1 with the InnoDB plugin on RHEL 6 (x86_64)
  • Virtualized environment (vSphere); SAN with FC interconnect is provided by our IaaS provider
  • Ext4 file-system with barrier=0 mount option
  • I/O Scheduler is deadline
4
  • O_DSYNC is faster than O_DIRECT because it performs writes without verification.
  • O_DIRECT is paranoid but more data consistent

I wrote about this before : Clarification on MySQL innodb_flush_method variable

  • 1
    thanks for your reply. Isn't it true that it is only the log files that are written without verification? Can you speak to what (real) risk this presents? – HTTP500 May 8 '14 at 22:18
4

My understanding is following: Historically InnoDB didn't scale well with large buffer pool. So - with a lot or RAM - OS disk cache might impact performance a lot -> this is why O_DIRECT performed bad for those environments (because O_DIRECT gets no help from OS disk cache). MySQL 5.6 has those issues addressed and in recent versions you may set buffer pool as big as ~75% of any RAM. When most of RAM is used for buffer pool - no reason to use OS disk cache for InnoDB, so O_DIRECT should perform much better, e.g. innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition

TLDR: If you wish best performance (with or without SAN) - get lots of RAM, upgrade MySQL to 5.6 or 5.7, configure innodb_buffer_pool as big as possible, use O_DIRECT

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.