Thanks, Kristian. I found this page, which confirms what you wrote and adds some context: https://mariadb.com/kb/en/binary-log-group-commit-and-innodb-flushing-perfor... The TL;DR seems to be that, for each group of transactions, one less commit happens but this won't decrease durability as long as the sync_binlog=1. Cheers, Federico On Fri, Sep 29, 2023 at 9:12 PM Kristian Nielsen <knielsen@knielsen-hq.org> wrote:
Federico Razzoli via discuss <discuss@lists.mariadb.org> writes:
Recently Laurynas commented this bug and he brought it up on social media: https://bugs.mysql.com/bug.php?id=75519
This seems to describe a configuration with --innodb-flush-log-at-trx-commit=1 and --sync-binlog=0.
The described behaviour is that in case of (kernel) crash, transactions may be lost because binlog events may be not written to disk prior to the crash, and then the corresponding prepared transaction in innodb is rolled back.
Did anyone test whether it is in MariaDB too? If so, which versions?
I did not test, but I assume it will be so. Like described in the MySQL bug, MariaDB flushes the innodb log at prepare, but not at commit when the binlog is enabled.
This is intended behaviour, though arguably a somewhat non-sensical configuration (paying the cost of --innodb-flush-log-at-trx-commit=1 without getting any durability guarantee).
I'm guessing the intention of the user here is to not really care about the binlog (not using replication perhaps), but still wants to have it enabled for debugging or the like? So enabled, but with minimum performance impact and not caring about losing binlog events?
And then the expectation is that this will not impact durability of --innodb-flush-log-at-trx-commit=1.
But this is not how it is designed. If a transaction is prepared in innodb but missing in the binlog, it must be rolled back in innodb, to prevent inconsistency between binlog and innodb.
If this can be documented better, then that's surely good, of course.
(The situation is worse that described. After the crash, not only can committed transactions be lost, the binlog and innodb can be out of sync with each other (transactions committed in InnoDB are missing from the binlog). This happens if the "commit" in the innodb log got written to disk prior to the crash but the binlog did not. The --sync-binlog=0 is not a crash-safe configuration).
Hope this helps,
- Kristian.
-- Federico Razzoli Vettabase Ltd director & founder, Database Consultant https://vettabase.com federico.razzoli@vettabase.com +44 7739 427279 Telegram: federico_razzoli Skype: fede.razzoli The information in this e-mail and any attachments may be confidential. If you received it by mistake, please inform the sender. Unless you are the intended recipient or his/her representative you are not authorised to, and must not, read, copy, distribute, use or retain this message or any part of it.