Please note that MySQL 8.0 will turn on
O_DIRECT_NO_FSYNC if it is available.
Here is what the [MySQL 8.0 Documentation currently says for
O_DIRECT or 4: InnoDB uses O_DIRECT (or directio() on Solaris) to open
the data files, and uses fsync() to flush both the data and log files.
This option is available on some GNU/Linux versions, FreeBSD, and
O_DIRECT_NO_FSYNC: InnoDB uses O_DIRECT during flushing I/O, but skips
the fsync() system call after each write operation.
Prior to MySQL 8.0.14, this setting is not suitable for file systems
such as XFS and EXT4, which require an fsync() system call to
synchronize file system metadata changes. If you are not sure whether
your file system requires an fsync() system call to synchronize file
system metadata changes, use O_DIRECT instead.
As of MySQL 8.0.14, fsync() is called after creating a new file, after
increasing file size, and after closing a file, to ensure that file
system metadata changes are synchronized. The fsync() system call is
still skipped after each write operation.
Data loss is possible if redo log files and data files reside on
different storage devices, and an unexpected exit occurs before data
file writes are flushed from a device cache that is not
battery-backed. If you use or intend to use different storage devices
for redo log files and data files, and your data files reside on a
device with a cache that is not battery-backed, use O_DIRECT instead.
How each setting affects performance depends on hardware configuration and workload. Benchmark your particular configuration to decide which setting to use, or whether to keep the default setting. Examine the Innodb_data_fsyncs status variable to see the overall number of fsync() calls (or fdatasync() calls if innodb_use_fdatasync is enabled) for each setting. The mix of read and write operations in your workload can affect how a setting performs. For example, on a system with a hardware RAID controller and battery-backed write cache, O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system file system cache. On some systems where InnoDB data and log files are located on a SAN, the default value or O_DSYNC might be faster for a read-heavy workload with mostly SELECT statements. Always test this parameter with hardware and workload that reflect your production environment. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.
Back on Mar 04, 2011, I wrote a post that mentioned the difference between
fsync() (Clarification on MySQL innodb_flush_method variable). You may want to use
fdatasync(). It was not really an option you can easily configure with older systems and old versions of MySQL.
IF YOU ARE USING MySQL 8.0.26
Starting in MySQL 8.0.26, you can now enable it with innodb_use_fdatasync. This option is dynamic.
Please note from the Documentation that you can lose data if redo logs (ib_logfile0 and ib_logfile1) is not on the same storage device as the data files (.ibd). If they are, just use
O_DIRECT. If they are all on the same storage device, then you can use innodb_use_fdatasync.
NOTE : If you are a version of MySQL before 8.0.26, just stick with
O_DIRECT. You don't want data being handled asynchronously. Besides,
O_DIRECT_NO_FSYNC switches to
O_DIRECT if hardware support does not exist. If you really want to know this, you will have to deep dive the InnoDB source code on how it autodetects OS support or research the documentation on FreeBSD and Solaris on where they allow for