Optimizing Server Settings - High Performance MySQL - page 362

configuration option for the logs and another for the data files, but they're combined.
Here are the possible values:
fdatasync
The default value on non-Windows systems: InnoDB uses fsync() to flush both
data and log files.
InnoDB generally uses fsync() instead of fdatasync() , even though this value
seems to indicate the contrary. fdatasync() is like fsync() , except it flushes only
the file's data, not its metadata (last modified time, etc.). Therefore, fsync() can
cause more I/O. However, the InnoDB developers are very conservative, and they
found that fdatasync() caused corruption in some cases. InnoDB determines
which methods can be used safely; some options are set at compile time and some
are discovered at runtime. It uses the fastest safe method it can.
The disadvantage of using fsync() is that the operating system buffers at least some
of the data in its own cache. In theory, this is wasteful double buffering, because
InnoDB manages its own buffers more intelligently than the operating system can.
However, the ultimate effect is very system- and filesystem-dependent. The double
buffering might not be a bad thing if it lets the filesystem do smarter I/O scheduling
and batching. Some filesystems and operating systems can accumulate writes and
execute them together, reorder them for efficiency, or write to multiple devices in
parallel. They might also do read-ahead optimizations, such as instructing the disk
to preread the next sequential block if several have been requested in sequence.
Sometimes these optimizations help, and sometimes they don't. You can read your
system's manpage for fsync(2) if you're curious about exactly what your version
of fsync() does.
innodb_file_per_table causes each file to be fsync() ed separately, which means
writes to multiple tables can't be combined into a single I/O operation. This might
require InnoDB to perform a higher total number of fsync() operations.
O_DIRECT
InnoDB uses the O_DIRECT flag, or directio() , depending on the system, on the
data files. This option does not affect the log files and is not necessarily available
on all Unix-like operating systems. At least GNU/Linux, FreeBSD, and Solaris (late
5.0 and newer) support it. Unlike the O_DSYNC flag, it affects both reads and writes.
This setting still uses fsync() to flush the files to disk, but it instructs the operating
system not to cache the data and not to use read-ahead. This disables the operating
system's caches completely and makes all reads and writes go directly to the storage
device, avoiding double buffering.
On most systems, this is implemented with a call to fcntl() to set the O_DIRECT flag
on the file descriptor, so you can read the fcntl(2) manpage for your system's
details. On Solaris, this option uses directio() .
If your RAID card does read-ahead, this setting will not disable that. It disables
only the operating system's and/or filesystem's read-ahead capabilities.