Write-heavy steps on the Insert Benchmark are much slower without O_DIRECT_NO_FSYNC

The performance loss from not having O_DIRECT_NO_FSYNC depends on fsync latency, so this is very much dependent on storage devices.

Also, two bugs for this were closed without a fix and without explanation. One of the bugs was from me. Hopefully this one gets fixed. The closed bugs are MDEV-34178 and MDEV-31227.

I only have results from my small server, I will update this as I get results from more servers.

On the small server, the throughput for the write-heavy steps of the insert benchmark drops by 30%. The steps are named l.i1 and l.i2 and do random inserts and deletes. The insert benchmark is explained here, here and here.

From the small server I run the Insert Benchmark with one client and an IO-bound workload using two my.cnf files that only differ in their value for innodb_flush_method

• z12a - this uses innodb_flush_method=O_DIRECT_NO_FSYNC
• z12b - this uses innodb_flush_method=O_DIRECT

After startup, both show:
innodb_linux_aio auto
innodb_use_native_aio ON

The insert rates sustained during the l.i1 and l.i2 steps are (see here) and the insert rates drop by about 1/3 for the z12b config that uses O_DIRECT
z12a - 2478/s and 3067/s
z12b - 1729/s and 2070/s

From the iostat metrics (see here) I see:

• w_await is 0.130 for z12a vs 0.307 for z12b, writes are ~3x slower with z12b
• f/s is 17.17 for z12a vs 127.4 for z12b, many more fsyncs with z12b
• most other IO metrics are larger for z12a (r/s, wKB/s) because it is able to run faster than z12b because z12b suffers from the extra fsync calls

I used results from 10.11.15 here because when I compare results for the latest point releases of supported versions (see here) there is a large drop from 10.11.15, which used the z12a config with O_DIRECT_NO_FSYNC, to 11.4.9, which used the new options - innodb_log_file_buffering, innodb_data_file_buffering, etc and O_DIRECT_NO_FSYNC isn't available there. And the perf drop here from 10.11.15+z12a to 11.4.9 is the same as above for 10.11.15+z12a to 10.11.5+z12b

I am working on a shell script that lets you run the Insert Benchmark without using all of my helper scripts, as they are messy and not documented.