3

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?

5
  • I suspect it is always "safe". Whether it is "optimal" is a different question. What are you setting sync_binlog and innodb_flush_log_at_trx_commit to? Those involve tradeoffs between speed and safety.
    – Rick James
    Aug 24, 2021 at 5:18
  • @RickJames, sync_binlog=0 ; innodb_flush_log_at_trx_commit=2 .
    – KJ7LNW
    Aug 25, 2021 at 23:05
  • (Neither of those is completely "safe".)
    – Rick James
    Aug 25, 2021 at 23:20
  • @RickJames, I understand the safety the tradeoffs about sync_binlog=0 and innodb_flush_log_at_trx_commit=2, but are you saying that O_DIRECT_NO_FSYNC isn't completely safe if the DBM manages it correctly? If so, why? This question is really only about O_DIRECT_NO_FSYNC.
    – KJ7LNW
    Aug 26, 2021 at 21:13
  • Sorry, I did not mean to imply that. Frankly, I don't have enough knowledge of O_DIRECT_NO_FSYNC to 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.
    – Rick James
    Aug 26, 2021 at 22:56

3 Answers 3

4

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.

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 fdatasync().

2
  • Awesome. Do you know if/what version/when MariaDB supports O_DIRECT_NO_FSYNC?
    – KJ7LNW
    Aug 25, 2021 at 23:04
  • I'm not sure if I'm getting this right, so if I'm on 8.0.26+ and have redo logs and data files on the same storage device, should I use O_DIRECT + innodb_use_fdatasync or O_DIRECT_NO_FSYNC + innodb_use_fdatasync?
    – Kukosk
    Nov 24, 2021 at 9:01
3

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.)

3
  • Got it. Looks like Oracle Linux 8 (RHEL 8/CentOS 8) comes with 10.3.x so its not in there yet.
    – KJ7LNW
    Aug 26, 2021 at 20:27
  • @KJ7LNW - Oracle Linux probably has MySQL 8.0 -- a very similar product.
    – Rick James
    Apr 19, 2023 at 0:13
  • good point. In fact we deploy Oracle Linux quite a bit. I think its a great alternative to Rocky/Alma and even RHEL.
    – KJ7LNW
    Apr 23, 2023 at 21:33
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.