Uploaded:

I think what makes this critical is the fact that it breaks existing behavior of the server that might be important or some (many?) customers.

Some may have legacy applications running (written for MyISAM) and thus enable innodb_flush_log_at_trx_commit=2. Or they do that only temporary to speed up certain operations (mass inserts, mass deletes).

When you run the server as part of a HA setup (Galera, replication) then you can run it permanently with innodb_flush_log_at_trx_commit=2

Which operating system kernel version are you using, which file system, and what type of storage? MDEV-28111 enabled O_DIRECT for the redo log writes.

On my system (currently running on the Linux 5.17.11 kernel), libaio or io_uring on ext4fs on an Intel Optane 960 PCIe NVMe drive, I see a significant difference between the innodb_flush_log_at_trx_commit values 0 and 1. The difference will be smaller for larger number of concurrent users, thanks to the MDEV-21534 group commit mechanism, but it is there.

This was a 10.9 branch with MDEV-28708 and with/without MDEV-26603.

As noted in MDEV-28111, I had conducted tests on 3 types of storage: SATA 3.0 HDD, SATA SSD, and PCIe NVMe. All were with ext4fs, no RAID. None of the drives support FUA. Those tests were conducted on a 5.16 kernel.

Would it be possible to try a newer kernel? Linux 4.15 was released in 2018. I see that it was used in Ubuntu 18.04 LTS, which will reach its normal end of life in April 2023, less than a year from now. According to Wikipedia, 4.15 is not a LTS release; 4.14 was one.

Linux 5.4 (LTS) was packaged in Ubuntu 20.04 LTS, which is supported until April 2025.

I found reports that Linux I/O performance has been steadily improved in the past year. I have used kernel versions 5.4, 5.10 (LTS), 5.14, 5.15, 5.16, 5.17, but I do not remember observing any significant performance difference between them, or between libaio and io_uring. That could be because there are many CPU bottlenecks in our code. The Intel Optane 960 drive in my system can achieve 2300 MB/s writes or 2600 MB/s reads, but I never have seen even half of that being achieved under any test with MariaDB Server.

I conducted some more tests, again Sysbench oltp_update_index but this time innodb_flush_log_at_trx_commit=0 on 10.10 with (merging) and without MDEV-26603, as well as with and without (reverting) MDEV-28111. Previously I mainly tested these changes with innodb_flush_log_at_trx_commit=1.

We can see that even with the non-durable setting, O_DIRECT writes are faster on my hardware and on the 5.17.11 kernel. Any variant that reverts MDEV-28111 (that is, disables O_DIRECT on the redo log) is slightly slower.

There are some glitches at 160 concurrent connections. It could be that a log checkpoint occurred during that time.

Furthermore, MDEV-26603 seems to improve things slightly, or the difference fits within the margin of error. More extensive testing will be needed to say anything conclusive about it.

I think that apart from the Linux kernel version, the file system and the type of drive could matter. I have not encountered any drive that supports FUA; that could be yet another factor. Perhaps instead of trying to automatically guess whether to enable O_DIRECT on the redo log, we should introduce a new value for innodb_flush_method to control it? At the same time, we could allow that parameter to be changed without a server restart.

This graph shows the results for innodb_flush_log_at_trx_commit=1 (before: only innodb_flush_log_at_trx_commit=2). We can see that switching to O_DIRECT improves performance for innodb_flush_log_at_trx_commit=1 somewhat. Not much, but that is probably due to hardware/kernel/fs combo.

And finally the result for innodb_flush_log_at_trx_commit=0

It turns out that the negative effect exists only for innodb_flush_log_at_trx_commit=2. For all other settings of innodb_flush_log_at_trx_commit MDEV-28111 is beneficial.

I will change the issue description accordingly.

I extended the tests that I conducted yesterday on 10.10, running them for
each value of innodb_flush_trx_at_commit and all 3 storage devices that I have easy access to.

Like yesterday, I tested both the impact of disabling O_DIRECT for the redo log (reverting MDEV-28111) and enabling asynchronous redo log (applying MDEV-26603).

INTEL SSDPED1D960GAY (Intel Optane 960 NVMe PCIe, 1 TiB):

Samsung SSD 850 EVO 500GB, SATA 3.1, 6.0 Gb/s (SSD):

Western Digital Blue WDC WD20EZRZ-00Z5HB0, SATA 3.0, 6.0 Gb/s (2TiB HDD):

There occasionally occur some log checkpoints (page writes) that will affect the throughput. This is particularly visible for the HDD benchmark, where the throughput drops to 0 for 20 seconds of the 30-second test at 80 concurrent connections, and never recovers from that average in the following 90 seconds (testing with 160, 320, and 640 concurrent connections). I guess that this happened because I was running the workload back-to-back, without triggering any log checkpoint or waiting for the history of committed transactions to be purged.

We can observe that on the slower storage (or on the NVMe at low numbers of concurrent connections), enabling O_DIRECT on the redo log does seem to help in this simple benchmark where all data fits in the buffer pool, and most writes are for the redo log. We can observe this even for the safe setting innodb_flush_log_at_trx_commit=1 in some cases.

However, we should also keep in mind that not using O_DIRECT will pollute the file system cache with useless data.

Because my results do not show any clear pattern, I think that we must introduce a settable Boolean configuration parameter, for enabling or disabling the file system cache for the redo log. I maintain that the caching should be disabled by default. Also mariadb-backup should benefit from enabling the file system cache when it is running.

I developed a patch that introduces a new Boolean parameter

SET GLOBAL innodb_log_file_buffering=ON;

to explicitly the disable file system cache on the InnoDB redo log. This parameter is only available on Linux and Microsoft Windows. On other platforms, we do not implement any interface for enabling or disabling any file system cache for the redo log.

On Linux, if the physical block size cannot be determined, this parameter will be hard-wired to ON. If the log file is on persistent memory (MDEV-25090), the parameter will be hard-wired to OFF.

On Microsoft Windows, we will retain the feature that if the server is started up with innodb_flush_log_at_trx_commit=2, the file system cache on the redo log file will be disabled.

It is generally a bad idea to add any configuration parameters. For this change, there seems to be a consensus that the file system cache must not be disabled whenever innodb_flush_log_at_trx_commit=2 is set, either when the server is being started up, or while it is running. I will revise the fix accordingly.

For the sake of completeness, I tested on the Intel Optane 960 the durable setting innodb_flush_log_at_trx_commit=1 at lower numbers of concurrent connections, with the file system cache for the log enabled and disabled. Direct unbuffered writes were always faster.

I got some feedback that in an environment based on a Ubuntu 20.04 kernel (Linux 5.4.something), on a 3TiB NVMe drive, innodb_flush_log_at_trx_commit=1 was significantly faster when the file system cache was not enabled (the log file was opened without O_DIRECT). Based on this, it looks like we must introduce the Boolean parameter

SET GLOBAL innodb_log_file_buffering=OFF;

SET GLOBAL innodb_log_file_buffering=ON;

so that the file system cache can be enabled or disabled independently of innodb_flush_log_at_trx_commit or innodb_flush_method. For compatibility with old releases, we could set the default value to ON when innodb_flush_log_at_trx_commit=2 has been specified at startup.

On Linux and Microsoft Windows, the Boolean parameter innodb_log_file_buffering will indicate whether the file system cache on the redo log file is enabled. Its default value is OFF. If innodb_flush_log_at_trx_commit=2 on startup, the parameter will be set ON.

The parameter innodb_flush_method no longer determines whether file system caching is enabled on the redo log; it will only affect the buffering of InnoDB data files.

Hi axel

What is the reason O_DIRECT should not be used when innodb_flush_log_at_trx_commit = 2 is set ?

I actually always had both of these enabled (O_DIRECT and "innodb_flush_log_at_trx_commit = 2"), for many years.

Never seen this combination causing an issue, but I'm wondering if the problems I'm having since I upgraded from 10.5 to 10.8.3 could be related to these.

Thank you!

Hello nunop,

this has nothing to do with innodb_flush_method which is the only way you could "use O_DIRECT". It is however only be used for writes to table spaces (checkpoints).

MDEV-28111 is about using O_DIRECT for the redo log. This is especially bad with innodb_flush_log_at_trx_commit=2. To put it in other words: it stops buffering log writes and makes the server behave as if innodb_flush_log_at_trx_commit=1.

That's why we introduced the variable innodb_log_file_buffering. It defaults to a setting that makes innodb_flush_log_at_trx_commit=2 working again. So with this MDEV-28766 in place everything should be as before.

Hi axel

Thank you very much for clarifying. I understand now, and makes sense!

So when I update to 10.8.4, I'll put back O_DIRECT and innodb_flush_log_at_trx_commit=2, as I had disabled these two due to problems I was having with mariabackup.

Thanks! Have a great day.