On Tue, 10 Feb 2026 at 21:59, Jaco Kroon <[email protected]> wrote: > > 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.
^^ This! -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net Follow us: LinkedIn: https://www.linkedin.com/company/shatteredsilicon X: https://x.com/ssiliconbg _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
