On Tue, 10 Feb 2026 at 15:34, Kristian Nielsen <[email protected]> wrote: > > Gordan Bobic via discuss <[email protected]> writes: > > > The problem is that unless you are running with: > > - innodb_flush_log_at_trx_commit=1 > > - sync_binlog = 1 (if binlog is enabled) > > - sync_master_info = 1 (if slave) > > > > writes can be out of order which causes a problem. > > 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 > written to the filesystem/OS (write(2) system call), but the fsync() is > skipped. And this gives most of the performance benefit.
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. What you are describing would hold true only in cases where the file system and disk I/O scheduler specifically don't try to optimise unflushed writes by reordering them (and some do). -- 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]
