Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
I don't know if Mac OS X supports a proper direct IO option - I did not think it did. You're the second person I've seen today get confused by that manual page. I have an open bug on it here: bugs.mysql.com/bug.php?id=54306 –  Morgan Tocker Mar 21 '11 at 20:55

1 Answer 1

up vote 8 down vote accepted

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 fdatsync() and fsync(). You can think of fdatasync() as doing an asynchronous fsync() (not verfying data).

Looking at the numbers, O_DSYNC does four write ops, two of which are verified, while fsync() does four write operations, all being verfied 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.

share|improve this answer
2  
Worth pointing out: O_DIRECT is only used on the table-space files, not on the logs. Also - whether O_DIRECT is going to be useful or not depends on the hardware. I linked to an open documentation bug as a comment to the author's question. –  Morgan Tocker Mar 21 '11 at 21:00
    
Thank you for clarifying that, Morgan. I'll correct this. –  RolandoMySQLDBA Mar 21 '11 at 21:26

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.