Understanding MySQL Buffers, Cache, and Zero‑Copy I/O: A Deep Dive

1. Buffer vs. Cache

Although often used interchangeably, a buffer ("缓冲") and a cache ("缓存") serve different purposes. At the hardware level, a buffer is regular memory, while a cache is a CPU‑integrated high‑speed store.

Buffer provides a temporary area so that devices with different speeds can synchronize; data written to a buffer is later transferred to another device. Cache pre‑reads frequently accessed data to speed up reads; data placed in a cache is read by other components.

In software, a buffer is a block‑device buffer, whereas a cache is a file‑system cache. In Linux, the Buffer Cache buffers block‑device operations and flushes them to disk in batches, while the Page Cache stores file data for fast reads.

In short, buffer contents are intended to be written elsewhere, while cache contents are meant for reading elsewhere.

Typical Uses

Buffer: sharing byte streams between applications, threads, or processes, e.g., synchronizing data between devices of different speeds.

Cache: improving read/write speed by leveraging temporal or spatial locality, such as the OS page‑cache mechanism.

Often the terms overlap; a buffer can improve both speed and data sharing.

2. Zero‑Copy Overview

3. MySQL Buffer Design

MySQL employs different buffering techniques at various layers:

Application layer

Redo Log Buffer – caches write operations for InnoDB transactional durability.

InnoDB Buffer Pool – caches table data in memory, reducing disk I/O for reads and writes.

OS VFS layer

Page Cache – the OS caches file blocks as pages, providing fast read access.

Direct Buffer – when Direct I/O is used, the OS bypasses the page cache and writes directly.

Disk layer Disk Buffer – hardware‑level cache on the storage device (usually disabled for MySQL).

4. Write‑Through / Write‑Back and Direct I/O

Write‑Through : writes bypass the cache and go directly to disk, ensuring data is not lost on power failure.

Write‑Back : writes modify only the cache; the data is flushed to disk later, improving performance.

5. InnoDB Flush Parameters

5.1 innodb_flush_log_at_trx_commit

This variable controls how the Redo Log Buffer is flushed to disk, balancing strict transaction safety and performance.

1 (default): flush on every transaction commit – highest durability.

0: flush once per second – faster but risk of loss on crash.

2: flush on commit and also once per second – intermediate safety.

Flush frequency defaults to 1 s and can be tuned with innodb_flush_log_at_timeout .

5.2 innodb_flush_method

This setting determines how both the redo log and the InnoDB Buffer Pool are flushed.

fdatasync (0) – default; uses fsync for both log and data files.

O_DSYNC (1) – uses O_SYNC for log files and fsync for data files.

O_DIRECT (4) – opens data files with Direct I/O and calls fsync on each write.

O_DIRECT_NO_FSYNC (5) – Direct I/O without fsync on data files.

littlesync, nosync – internal testing only, not recommended.

Opening a file with O_SYNC forces every write to flush both data and metadata to disk.

Use O_DIRECT_NO_FSYNC only if you are certain that metadata will be persisted without an explicit fsync; otherwise prefer O_DIRECT to avoid corruption.

6. MySQL Binary Log Flush Strategy

The sync_binlog variable controls how the binary log is flushed:

0 – MySQL does not flush; the OS handles it.

1 – Flush on every transaction commit.

N – Flush after N transactions have been collected.

This also determines whether the binary log uses a write‑through or write‑back approach.

7. Typical MySQL Configuration for Performance

innodb_flush_log_at_trx_commit = 1

– ensures redo logs are flushed on each commit. innodb_flush_method = O_DIRECT – makes the InnoDB Buffer Pool use Direct I/O with fsync for both data and metadata.

8. Write Path Details

Writing a redo log involves:

Writing to the Redo Log Buffer.

Flushing to the Page Cache.

Calling fsync to persist dirty pages.

Committing the transaction.

Updating a table row follows:

Modified data is written to the InnoDB Buffer Pool.

Asynchronously, the dirty pages are flushed to disk via the file write operation.

The OS eventually calls fsync to ensure both data and metadata are persisted.

References

[1] Buffer vs. Cache.

[2] MySQL 8.0 Reference Manual – InnoDB Startup Options.

[3] MySQL 8.0 innodb_flush_method.

[4] MySQL 8.0 Reference Manual – Binary Logging Options.

[5] Why MySQL still uses fsync with O_DIRECT?