Safety of O_DIRECT_NO_FSYNC for MySQL 8.0.Server version: 8.0.35-27?

MySQL & MariaDB Percona Distribution for MySQL
1

I have xfs on ebs attached volumes and have been testing and reading MySQL and Percona documentation for O_DIRECT_NO_FSYNC which seems to lower what I am seeing as a HUGE increase in I/O activity since 5.7… Is it safe?

According to Percona Docs : ```
O_DIRECT_NO_FSYNC: use O_DIRECT to open the data files and parallel doublewrite files, but does not use the fsync() system call to flush the data files, log files, and parallel doublewrite files. Do not use this option for the XFS file system.




Acc to MySQL:
`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.
2

Even if you are on 8.0.14+, this says fsync() won’t be called when InnoDB writes a block to data (.ibd) files, or to the doublewrite buffer (used to protect against partial page writes).

Running without the fsync is indeed faster, but you open yourself to risk of data loss/file corruption.

Try testing O_DIRECT + innodb_flush_log_at_trx_commit=2. This will skip the “fsync on every commit” but still fsync every 1s. Also, reduce fsync to the binlogs by using sync_binlog=500 (fsync every 500 txns)