innodb_flush_method variable specifies how InnoDB opens and flushes log and data files. In Innodb optimization, setting the variable innodb_flush_method tweaks the performance most of the times but there are cases of otherwise though. If innodb_flush_method is set to O_DIRECT it’d avoid double buffering and reduce swap pressure and helps improving performance.

innodb_flush_method has been covered at a lot of places and this is a quick reference to myself.

innodb_flush_method:

innodb_flush_method On non-Windows systems:

1. fdatasync:
– default
– InnoDB uses fsync() to flush both data and log files (though fdatasync is specified).
– fdatasync() flushes only data while fsync() also flushes metadata along with file’s data ( & thus causes more IO).
– fsync() causes double buffering. i.e. operating system buffers at least some of the data in its own cache even though InnoDB is managing it in it’s own buffers.
– When innodb_file_per_table is set, writes on multiple tables causes multiple fsync() calls as they cannot be combined in single IO.

2. O_DIRECT:
– Applies only for data files and not logs.
– O_DIRECT uses fsync() method to flush files to disk and ensures no double buffering on system.
– All reads and writes goes directly to disk.
– This setting disbles OS’s double buffering & read ahead while flushing.
– Implemented by setting O_DIRECT flag of fcntl() or directio() on Solaris.
– If innodb_file_per_table is not set, performance suffers due to serialized IO caused by inode-level mutex.

3. O_DSYNC:
– Applies only to log files and not data files.
– Sets O_SYNC flag of open() and makes all writes synchronous.
– O_SYNC doesn’t disable double buffering / caching at system level.
– O_DSYNC flushes only data while O_SYNC flushes both data and metadata.

innodb_flush_method On Windows systems:

1. async_unbuffered:
– unbuffered IO => data has been written / flushed before continue next.
– InnoDB to use unbuffered I/O for most writes.
– If innodb_flush_log_at_trx_commit = 2, InnoDB uses buffered I/O to the log files.
– This causes InnoDB to use Windows native Async IO for both Reads/Writes.
– For older version of Windows, InnoDB uses it’s own Async IO.

2. unbuffered:
– Same as async_unbuffered except here native async. IO is not used.

3. Normal:
– This option tells to use neither unbuffered IO nor native async. IO.