Experience with innodb_flush_method setting

I just wanted to ask about your experience with the innodb_flush_method setting since I usually went with

innodb_flush_method = O_DIRECT

Recently, while working with new machines, same kernel but a different Raid Controller / HD Setup, I’ve noticed a heavy performance drop while using O_DIRECT. Trying O_DSYNC was the same. Using the default flush method (not using the innodb_flush_method setting) worked well instead…

Any suggestions or experiences about this setting on different setups (linux kernel versions, raid setups, etc.)?

O_DIRECT bypasses OS cache for reads and for writes.

Typically it can slow down things in case

  1. Your buffer pool is too small (so caching in OS gives benefit)

  2. You do not have battery backed up cache on RAID - flushing data may become much slower with O_DIRECT in this case as OS will be unable to reorder page write requests.

Thanks a lot for your answer Peter!

I just don’t get the point about the BBU - I thought a BBU just helps in case of a power failure and keeps data in the cache until the machine is up again - why would O_DIRECT benefit from it during “normal” operation? Or was the point all about enabling write cache on the raid controller which might result in data loss without a BBU and which, if disabled, would dramatically slow down O_DIRECT?