MySQL variable innodb_flush_method – summarized

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.