A de-facto present recovery-related requirement of two calls of fsync() at
transaction prepare and commit by Engine per transaction
can be relaxed in favor of replacing the first fsync() by a group-fsync
of Binlog. Since when Binlog is turned ON transactions
group-committed/prepared the only fsync() per group resolves
optimization requests such as MDEV-11376.
When a trx is deposited into an fsynced binlog file its image
consisting of xid and payload suffices for its recovery. Specifically the
payload part can be effectively made use of to replay the transaction should
it have missed out the Engine write to disk.
As long as Engine maintains its last committed in binlog order durable
transaction tracking all the transactions above the last if found in binlog upon a
crash could are regarded as lost and be restored by re-applying of
their payload, that is their binlogged replication events.
The existing binlog checkpoint mechanism will continue to serve to
limit binlog files for recovery.
In the light of MDEV-16589 sync_binlog = 1 performance becomes a more concern.
MDEV-24386 shows up to 3 times grown latency and halved throughput with the new default value
and remained default of innodb_flush_log_at_trx_commit = 1.
At the same time innodb_flush_log_at_trx_commit = 0 still allows for recovery (though to be
further benchmarking sysbench4.pdf of
MDEV-24386 ensures the latency and performance of
(B = 1, I = 0) may be even better compare to (B = 0, I = 1) of the current (10.5) default.
Here B stands for sync_binlog, I for innodb_flush_log_at_trx_commit.
To the refined recovery, it needs to know engines involved in a transaction in doubt.
Specifically whether all the engines maintain the last committed transaction's binlog offset
in their persistent metadata.
For instance Innodb does so. This piece of info is crucial as at recovery
the engine may have the transaction or its branch
already committed or not even prepared and which of the two is the case can be resolved only
with an "external" help such as the tracking facility: when the transaction starts in binlog
at an offset greater than that that the engine remembers of its last committed then
this transaction obviously is not yet committed.
Unlike all other cases in case of the single Innodb engine transaction
there is no need to specify the engine explicitly in the transaction's
The recovery procedure follows most of the conventional one's steps and adds up
the following rule, simplified here to a single engine:
For the multiple engine and not-Innodb cases the property of involved engines can be
specified through extended Gtid_log_event. Consider a bitmap with the bits mapped to engines
on that local server.
The mapping is local for the server so it must be mere stable through crashes.
Gtid_log_event remembers the engines involved (except there is only
one Innodb) and at recovery the engines will be found and asked for the last commit binlog offset.
When there's an engine that does not track this transaction can't be re-executed, otherwise
branches of the in-doubt multi-engine transaction are considered individually taking into account
what the engine branch remembers of its last committed and the transaction binlog offset.
For re-execution consider MDEV-21465 as a template. MIXED binlog format guarantees re-execution
to repeat/reproduce the original changes.