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.