The most expensive misunderstanding I keep meeting in MySQL shops is about what gets fsync'd, when, and how often. I once took over a fleet where every primary ran innodb_flush_log_at_trx_commit=2 because a blog post called it "basically as safe," and the same team lost their minds when a kernel panic cost them a second of committed transactions during a failover. The setting did exactly what it documented. The team's mental model was the broken part.
These two knobs, innodb_flush_method and innodb_flush_log_at_trx_commit, sit on the boundary between fast and survives-a-power-cut. This is how I think about them on MySQL 8.0 and 8.4, with real behavior instead of folklore.
What fsync actually buys you
When InnoDB writes a page to disk, the bytes usually land in the operating system's page cache first. The write call returns, the application moves on, and the data sits in kernel memory until the kernel feels like flushing it. If the machine loses power or the kernel panics in that window, those bytes are gone even though MySQL believes the write succeeded. fsync is the system call that forces the kernel to push a file's dirty pages to durable storage and wait for the device to confirm. Every durability guarantee MySQL makes is built out of that call.
The nuance is that "the device confirming" itself has layers. Enterprise SSDs and RAID controllers with battery-backed or capacitor-backed write caches can report a flush complete while data still sits in the device's cache, and that is fine only if the cache is genuinely protected. Consumer drives sometimes lie outright. I have seen a fast lab server turn out to be a consumer NVMe with an unprotected cache; it benchmarked beautifully and would have eaten transactions in a power event.
innodb_flush_log_at_trx_commit: the durability dial
This setting controls when the redo log is written and flushed at commit time, and it is the single biggest honest durability-versus-throughput trade in MySQL:
- 1 (the default, required for full ACID): the redo log buffer is written to the log file and fsync'd at every commit. A committed transaction survives a crash of mysqld, the OS, and the machine. This is the only value where committed means committed — for InnoDB's half of the story. On any server with binary logging enabled, sync_binlog is the other half, and it gets its own paragraph below.
- 0: the redo buffer is written and flushed roughly once per second by a background thread, on the innodb_flush_log_at_timeout cadence. A crash of mysqld alone can lose about a second of commits.
- 2: the redo buffer is written to the operating system at every commit but only fsync'd about once per second. A mysqld crash loses nothing, because the bytes are already in the OS page cache; an OS crash or power loss can lose about a second of commits.
The distinction between 0 and 2 matters operationally. With 2, only a machine-level failure loses data; with 0, even a clean mysqld crash can. That is why 2 is the setting people reach for on replicas, and on primaries feeding asynchronous replication where a lost second can be re-fetched or the business has explicitly accepted the risk. Group commit changes the math as well: when many transactions commit concurrently, InnoDB batches their log flushes, so the per-commit fsync cost under the setting of 1 is amortized. The workloads that suffer most under the default are the ones committing tiny transactions one at a time, serially, from a single thread.
My line in the sand: if the business cannot state, in writing, that losing a second of committed transactions is acceptable, the setting stays at 1. Everything else is negotiable. The companion knob matters everywhere binary logging is on — which is every replicated or point-in-time-recoverable server: sync_binlog=1 fsyncs the binary log at each commit, so the record that replicas and PITR replay from is as durable as the data itself. MySQL's own durability guidance pairs the two settings, and so do I.
innodb_flush_method: O_DIRECT vs fsync
Separate from the redo question, innodb_flush_method controls how InnoDB writes and flushes the data files, the tablespace pages themselves. On Linux the realistic choices are fsync and O_DIRECT. With fsync, tablespace reads and writes go through the OS page cache and InnoDB calls fsync to flush. With O_DIRECT, InnoDB opens the data files with the O_DIRECT flag so I/O bypasses the OS page cache, and it still fsyncs after writes to make sure the data is durable. Check your current posture before touching anything:
SELECT @@innodb_flush_method,
@@innodb_flush_log_at_trx_commit,
@@sync_binlog,
@@innodb_doublewrite,
@@innodb_flush_log_at_timeout,
@@innodb_io_capacity,
@@innodb_io_capacity_max;The practical consequence of the fsync method is double buffering: a hot page lives in the buffer pool and also in the OS page cache, wasting RAM, and you pay copy overhead on every I/O. InnoDB already runs its own cache — that is the entire point of the buffer pool, which I wrote about in the buffer pool field guide — so letting the kernel cache the same pages a second time buys almost nothing. O_DIRECT returns that memory to the buffer pool and makes readahead behavior more predictable. That is why most production guidance, mine included, lands on O_DIRECT for dedicated database hosts with local storage.
The honest exceptions: on network filesystems, some SAN-attached storage, or filesystems with exotic cache behavior, O_DIRECT can perform badly or behave unexpectedly, so benchmark before you flip it. O_DSYNC is another documented option that opens the redo log files with O_SYNC so log writes become synchronous at write time while data files still rely on fsync; I have rarely seen it win over the standard choices. And whatever you pick, remember the setting does not govern the redo log's flush discipline — that belongs to the commit setting above.
The doublewrite interaction
O_DIRECT does not let you skip the doublewrite buffer, and you should not want to. A torn page — a 16 KB InnoDB page half-written when power drops — is unrecoverable from the redo log alone, because redo records describe changes to a page that must be intact for the changes to apply. The doublewrite buffer is InnoDB's insurance: before writing pages to their real locations, InnoDB writes them to a dedicated doublewrite area, fsyncs that, then writes the real locations. On crash recovery, any torn page is restored from its doublewrite copy. I went deeper on the mechanics in the doublewrite and torn pages post.
Some storage stacks, ZFS being the classic example, make torn pages impossible, and people disable doublewrite there for a real throughput gain. On ext4 or xfs over ordinary SSDs, leave innodb_doublewrite on. The cost on modern hardware is modest; the failure it prevents is a corrupted tablespace. You can watch the whole flush pipeline directly:
SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
'Innodb_data_fsyncs',
'Innodb_pages_written',
'Innodb_log_writes',
'Innodb_log_write_requests',
'Innodb_os_log_fsyncs',
'Innodb_dblwr_writes',
'Innodb_dblwr_pages_written'
);Watch Innodb_data_fsyncs per second as your flush load, and Innodb_dblwr_pages_written against Innodb_pages_written — pages against pages, so the units match — to see how much extra write volume the doublewrite adds. If fsyncs per second is pinned near what your storage can service, that is your write ceiling, and no amount of buffer pool tuning will lift it. Redo capacity might, and I covered that side in the redo log capacity post.
What I run in production
On a dedicated Linux host with local SSDs: innodb_flush_log_at_trx_commit=1 on primaries that own money-adjacent data, 2 on replicas and on primaries where the business has signed off on the risk; sync_binlog=1 on anything with the binary log enabled; innodb_flush_method=O_DIRECT; doublewrite on unless the storage stack proves atomic writes; innodb_io_capacity set to what the device actually sustains rather than the conservative default. Then I measure, because the correct answer is always workload-shaped, and the counter deltas above are the measurement.
Where MonPG fits
Durability tuning lives or dies on measurement, and measurement is where tooling earns its keep. MonPG is a PostgreSQL monitoring product today — MySQL support is coming soon and in active development — and the MySQL design starts from counters like the ones above: fsyncs per second and commit latency trended across a configuration change, doublewrite volume next to total writes, so "did O_DIRECT actually help" gets answered by a before-and-after graph instead of a week of arguing. The PostgreSQL side of that workflow is live at MonPG for PostgreSQL, and more MySQL deep dives are on the blog.