Hi,
On 2026/02/10 19:11, Kristian Nielsen wrote:
Gordan Bobic <[email protected]> writes:
On Tue, 10 Feb 2026 at 15:34, Kristian Nielsen <[email protected]> wrote:
It should be enough to run with innodb_flush_log_at_trx_commit=2 and
sync_binlog=0 to have consistent snapshots. In this case, the data is
Right, but what happens in the following scenario:
1) Transaction arrives
2) Transaction gets written to the redo log
3) Transaction gets written to the binlog
4) Before the flush is issued a second later on the innodb log, the
OS' disk I/O scheduler decides to write the binlog before flushing the
redo log to stable storage
5) Power outage / hard kernel crash happens
Without write ordering guarantees, this will lead to an inconsistent
state on disk at a lower layer.
Absolutely. To recover InnoDB+binlog consistently after a kernel crash
requires --sync-binlog=1 and --innodb-flush-log-at-trx-commit=1 in the
existing binlog. (The new binlog implementation that's in 12.3 doesn't have
this requirement incidentally).
My point was that in the backup scenario - assuming the kernel doesn't crash
during the backup - the snapshot should be consistent without the fsync to
disk, the write to the kernel buffers is enough.
Two layers of caching/buffers ... filesystem and block layer.
Snapshots (LVM) happens at the block layer. If filesystem re-orders
writes even an lvm snapshot could be inconsistent as we've observed
w.r.t. backups without the appropriate safety settings.
As example, ext4 has a delayed block allocator which means that writes
to filesystem could re-order before writes to block devices.
We have observed case (with nearly 250GB of buffers/caches) that a sync
could take up to 45 minutes in bad cases. udev rules would time out if
the block cache was too big, so we used sync to try and flush that prior
to running LVM ops (which had a reasonable success rate but still no
guarantees), eventually realised we just needed to optimize the block IO
dirty/writeback parameters to keep the block cache down so that can be
flushed in sub 3 min, whilst the FS could still do things like delayed
block allocation. Yes, an OS crash will still lose potentially huge
amounts of data.
Please do not make the mistake of thinking that two writes are
guaranteed to even hit the block cache in-order.
Kind regards,
Jaco
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
