|Applicable To||InnoDB on Unix like OS|
|Server Startup Option||--innodb_flush_method=<value>|
|Possible Values||enum(O_DSYNC, O_DIRECT, <<none>> )|
This variable changes the way InnoDB open files and flush data to disk and is should be considered as very important for InnoDB performance. By default, InnoDB uses
O_DSYNC) to flush both log and data files.
Setting this variable to
O_DIRECT will result in InnoDB using
O_DIRECT while opening files and
fsync() to flush both data and log files.
O_DIRECT is useful when an application maintains it's own caching mechanism which is very well true for MySQL/InnoDB.
O_DIRECT is the option that should be used in most of the cases as it takes the overhead of double buffering and reduces swap pressure. You should be careful if you are not using a battery backed up RAID cache as it may lead to data loss in case of a crash. Also, the MySQL documentation warns against using
O_DIRECT when files are located on a SAN, I don't have any idea on that.
O_DSYNC makes InnoDB to use
O_SYNC to open and flush the log files, and uses
fsync() to flush data files.
For Windows, the flush method is always
If you are not doing anything unusual like SAN storage etc (which otherwise also you should reconsider before doing), always use
O_DIRECT for this. This leads to a significant improvement in InnoDB performance by removing double buffering.
- MySQL manual entry on innodb_flush_method
- Heikki Tuuri recommends
O_DIRECT: Check Q35 @ mysqlperformanceblog.com
man 2 fsync
man 2 open
Hope you enjoyed reading this.