Clarification on MySQL innodb_flush_method variable

Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in layman's terms on the difference in O_DSYNC and O_DIRECT, and how to know if it's a performance issue on a database server.

Some stats on my setup: Mac OSX 10.6 (32-bit kernel, since architecture is out of date) running MySQL 5.1.49-64bit (hoping it would allow me to use the memory). 8GB RAM, ~6GB of innodb data/indexes.

Here is an explanation on how fdatasync() works vs how fsync() works

fdatasync() flushes all data buffers of a file to disk (before the system call returns). It resembles fsync() but is not required to update the metadata, such as access time. Applications that access databases or log files often write a tiny data fragment (e.g., one line in a log file) and then call fsync() immediately in order to ensure that the written data is physically stored on the harddisk. Unfortunately, fsync() will always initiate two write operations

• one write operation for the newly written data

• one write operation in order to update the modification time stored in the inode

If the modification time is not a part of the transaction concept, then fdatasync() can be used to avoid unnecessary inode disk write operations.

In English, O_DSYNC is faster than O_DIRECT since O_DIRECT calls fsync() twice (one for logs and one for data) and fsync() verifies data writes via two write operations. Using O_DSYNC calls fdatasync() and fsync(). You can think of fdatasync() as doing an asynchronous fsync() (not verifying data).

Looking at the numbers, O_DSYNC does four write ops, two of which are verified, while fsync() does four write operations, all being verified afterwards.
CONCLUSION
O_DSYNC

• faster than O_DIRECT

• Data may/may not be consistent due to latency or an outright crash

O_DIRECT

• more stable

• data consistent

• naturally slower

I hope this answer helps, and I hope I didn't make things worse for you.

StackExchange Database Administrators Q#1568, answer score: 18