Mysql – Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage

innodbMySQLperformancequery-performancesan

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

Best Answer

  • 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