There seems to be some contention about whether one should use innodb_flush_method = O_DIRECT_NO_FSYNC on XFS or EXT4, and MySQL 8 even turns this on as part of the innodb_dedicated_server=y option.
What filesystems safely support this option, and under what conditions or mount options?
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 innodb_flush_method]:1
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 Solaris.
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 fdatasync() and 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.
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 fdatasync().
O_DIRECT + innodb_use_fdatasync or O_DIRECT_NO_FSYNC + innodb_use_fdatasync?
MariaDB 10.5.1 automatically uses fdatasync: https://jira.mariadb.org/browse/MDEV-21382 and more in 10.5.3: https://jira.mariadb.org/browse/MDEV-22177 (The data comes from Percona 5.5.14; Oracle is slow in picking up on it.)
The data is on stable storage after write, in 2 cases : either if you flush, or speaking Unixish fsync/fdatasync them. Or if your device supports FUA (forced unit access, write-through capability), which only some devices do. SCSI disks do for example. Then with O_DSYNC (combined with O_DIRECT if one wants to, to bypass the filesystem cache) it is on stable storage, after a write() system call. Otherwise, it is in disk cache, and is lost when power is lost.
Whatever MySQL invented with O_DIRECT_NO_FSYNC seems to be only useable with battery-backed disk cache. They neither flush, nor O_DSYNC, no effort is made to protect data in case of power outage.
sync_binlogandinnodb_flush_log_at_trx_committo? Those involve tradeoffs between speed and safety.O_DIRECT_NO_FSYNCto give you the answer you desire. MySQL and MariaDB both implemented something like that some time ago, but made a few changes over time. This tells me that they may not have gotten it "right" at first, but hopefully have fixed all the issues.