OS-Level Write Mechanism Vs Fysnc

When an application writes data (such as a database commit) to disk, the Operating System (OS) handles the actual write process. However, the OS does not immediately write to disk — instead, it uses caching to optimize performance.

1. How Write Requests Work in OS

When a database or application sends a write request:

1. The data is first written to the OS cache (also called page cache), which resides in RAM.
2. The OS then delays the actual disk write (this is called write buffering).
3. After some time, the OS flushes the cached data to the disk.

Why cache first?

• Writing to RAM is much faster than writing to disk.
• It improves performance by avoiding frequent disk I/O operations.

2. The Problem: Data Loss Due to OS Caching

Since writes are cached in RAM before being persisted to disk, you face a data durability risk:

• If the OS crashes or the machine restarts before the cached data is flushed, the data is lost.
• Even though the DB thinks the transaction is committed, the data might still be in the cache and not on disk.

Example:

Step 1: You run the following query in PostgreSQL

INSERT INTO orders (id, amount) VALUES (1, 1000);
COMMIT;

Step 2: The DB sends the write request to the OS.

Step 3: The OS writes the data to the cache (RAM) but not immediately to the disk.

Step 4: Before the OS flushes the cache, the system crashes.

When the machine restarts:

• The database thinks the transaction was committed.
• But the data is lost because it was never written to disk.

3. What is *fsync*?

• fsync is a system call in Unix-like OS that forces the OS to flush all cached writes to the disk immediately.
• It ensures that all pending writes are physically written to the disk before the DB considers the transaction as committed.

How fsync works:

• When a database issues an fsync command, the OS:
• Flushes all dirty pages (modified but not saved) from the cache to disk.
• Ensures the data is physically persisted.

4. Why *fsync* is Expensive?

• Since fsync forces immediate disk writes, it causes:
• More I/O operations
• Slower performance
• Instead of batching writes (which is more efficient), the DB has to wait for each write to be fully persisted to disk.
• This slows down commits, especially in high-transaction environments.

Example:

1. Without fsync:

• The DB writes go to OS cache and are later flushed in batches → faster but less durable.

2. With fsync:

• Every commit waits until the data is written to disk → slower but more durable.

5. How Databases Handle This

Databases like PostgreSQL, MySQL, and Redis have settings for fsync behavior:

PostgreSQL:

synchronous_commit = on   -- Ensures fsync on every commit (safer but slower)
synchronous_commit = off  -- Uses OS cache (faster but less durable)

MySQL (InnoDB engine):

innodb_flush_log_at_trx_commit = 1  -- fsync on every commit (safer but slower)
innodb_flush_log_at_trx_commit = 2  -- Write to log but fsync later (faster but risky)

Redis (AOF with fsync settings):

appendfsync always   -- fsync after every write (safe but slow)
appendfsync everysec -- fsync every second (balanced)
appendfsync no       -- rely on OS cache (fast but risky)