Tim He, can you please state the MariaDB Server version that you are using? Is it older than 10.6, where MDEV-24854 changed innodb_flush_method=O_DIRECT to be the default? If it is not older than 10.6, was it linked with liburing (MDEV-24883) or with the older libaio?

What are your other InnoDB configuration parameters?

Note: innodb_doublewrite=off is only safe if writes of innodb_page_size are known to be atomic. I have not seen any Linux documentation on this, but I might assume that on a SSD with a physical block size of 4096 bytes, innodb_page_size=4k could be safe to be used with innodb_doublewrite=off.

Also note: With 4096-byte physical block size, MariaDB Server 10.8 should yield better write performance than earlier versions. See also MDEV-28766.

Version 10.5.13. Other parameters are unchanged.
Nice to see io_uring has already been applied. While in version 10.5, using fsync(2) + libaio can cause performance problem in my SSDs (see "fio-benchmark.png". I have confirmed this issue in two NVMe SSDs), So since O_SYNC + libaio can also guarantee safety, shall MariaDB could consider using adding it to one of the option to innodb_flush_method ?

Tim He, were you able to test the performance of MariaDB Server 10.6 or 10.8? Also, did you test a setup where the ib_logfile0 and the InnoDB data files reside on separate devices? Which writes and fsync() are we talking about?

I had written some notes about O_DIRECT and fdatasync() or fsync() in MDEV-24854.

In 10.8, you may also want to check MDEV-28766. In some cases, enabling O_DIRECT on the log file would reduce performance.

I think that it is worth running some performance tests, beyond what already was done in MDEV-24854. I think that we must cover various parameters (different working set sizes, innodb_buffer_pool_size and innodb_log_file_size) so that there will be different scenarios covering all the reasons of page writes.

The allowed innodb_flush_method values are as follows:

• fsync (0, SRV_FSYNC): the default before MDEV-24854: use file system cache and explicit fdatasync() or fsync()
• O_DSYNC (1, SRV_O_DSYNC): O_DIRECT on data files, and enable O_DSYNC on the redo log
• littlesync (2, SRV_LITTLESYNC): like O_DIRECT_NO_FSYNC, but using the file system cache (unsafe!)
• nosync (3, SRV_NOSYNC): like littlesync, but do not invoke fsync() or fdatasync() on the log file
• O_DIRECT (4, SRV_O_DIRECT): like fsync but bypassing the file system cache for data files (default)
• O_DIRECT_NO_FSYNC (5, SRV_O_DIRECT_NO_FSYNC): like O_DIRECT, but do not call fsync() or fdatasync() on the data files (unsafe in some cases; see this comment in MDEV-24854)

Starting with MDEV-28766 in 10.8, O_DIRECT can be enabled on the redo log:

SET GLOBAL innodb_log_file_buffering=OFF;

I think that we should deprecate the confusing parameter innodb_flush_method and map the existing values to some combinations of new parameters, which could be changed with SET GLOBAL while the server is running:

• innodb_data_file_buffering (OFF (default), ON): whether to use the file system cache for data files
• innodb_write_sync (OFF (default), ON): whether to open the log and the persistent files in O_DSYNC mode to avoid the need for explicit fdatasync() after writes

I do not think that we need to continue supporting innodb_flush_method=nosync on the redo log. A similar effect can already be achieved with non-default values of innodb_flush_log_at_trx_commit.

Running performance tests should be easier by using the 4 settable Boolean parameters that were introduced in MDEV-30136, along with deprecating innodb_flush_method. That ticket describes how the 6 values of innodb_flush_method are mapped to or related to the 4 new Boolean parameters and 1 pre-existing Boolean parameter.