InnoDB Flushing is simple – explained

As a junior once I asked a seasoned MySQL DBA (Abuelo) “How do you stay so calm in critical situations?”
Abuelo DBA then uttered golden words: “Son, I keep my dirty pages low, my checkpoint age lower, and I flush a little every day so the DBA life never has to sync flush me.”

I was super impressed with the way he told be those words but heck I didn’t understand them very well (then) but today If you’re like what I was like, let’s correct it. This post breaks down InnoDB flushing from first principles, with everyday analogies, so the next time someone mentions checkpoint age you’ll know exactly how old is he (importantly, what he’s talking about).

What is “flushing”?

Think of InnoDB like a student taking notes in class:

• Buffer pool = the student’s brain (RAM). Fast, but limited, and everything is forgotten on a restart.
• Data files on disk = the permanent notebook. Slow to write, but permanent.
• Redo log = a small rough diary where the student quickly notes “what changed” so nothing is lost if they faint mid-class.

Let’s see a simple statement:

UPDATE users SET name = 'Abuelo' WHERE id = 8582;

InnoDB does not immediately write your data file on disk. Instead it:

1. Changes the page in the buffer pool (memory). That page is now “dirty” => the copy in memory no longer matches the copy on disk.
2. Writes a small record of the change to the redo log (fast, sequential I/O). This makes the change crash-safe.
3. Later, writes the dirty page to the actual table data file. This is flushing

You may ask, Why delay the write?
Because the same page might be modified 100 times in a minute. Flushing it once instead of 100 times saves a lot of disk IO.

But dirty pages cannot pile up forever. The redo log is a fixed-size circular file, and memory is limited. Flushing is like the cleanup crew that keeps the system healthy. Let’s understand the four types of flushing below.

Background Flushing

This is the baseline behavior. A background thread called the page cleaner wakes up roughly every second and flushes a batch of dirty pages, keeping the dirty-page percentage below innodb_max_dirty_pages_pct (default 90).
It works at a calm pace bounded by innodb_io_capacity. This is where you tell InnoDB about how much IO the storage can comfortably sustain.

Adaptive Flushing

There is a problem with a fixed cleaning pace: if writes suddenly spike, a fixed-rate flush cannot keep up. Dirty pages accumulate, the redo log fills, and eventually everything slams into a wall.

Adaptive flushing (innodb_adaptive_flushing = ON, the default) solves this by watching how fast redo log records are being generated and how full the redo log is. It then dynamically speeds up or slows down flushing so the pace of cleaning matches the pace of dirtying.

For example, say your nightly batch job starts against the database with 10000 updates per second.
Adaptive flushing notices the redo log filling quickly and increases the flushing rate, say, 200 pages/sec to 2000 pages/sec spreading the work out smoothly.

LRU Flushing

The buffer pool is managed as an LRU (Least Recently Used) list. When a query needs to read a page from disk and there is no free page in memory, InnoDB must evict the least-recently-used page to make room.

• If the victim page is clean, eviction is free — just drop it.
• If the victim page is dirty, it must be flushed to disk first. That is LRU flushing.

To avoid making queries wait for this, the page cleaner proactively scans the tail of the LRU list every second (innodb_lru_scan_depth, default 1024 pages per buffer pool instance) to keep a supply of clean or free pages ready.

Let’s see an example.
Your buffer pool is 8 GB and is completely full with active dataset. A new reporting query start scaning a 20 GB table.
Every new page read from disk needs a room in so old dirty pages at the LRU tail must be flushed and evicted to make space.
If LRU flushing cannot keep up, your read queries stall waiting for free pages.

Note that background and adaptive flushing exist to keep the redo log and dirty percentage under control. LRU flushing exists to keep free pages available for new reads.

Sync Flushing

The redo log is circular and fixed in size. InnoDB cannot write to the old section of the redo log until the dirty pages in that section have been flushed to the respective data files. The boundary between “safe to reuse pages” and “still needed pages” is called the checkpoint, and the amount of un-flushed redo log pages is the checkpoint age.

Now if the dirty pages start accumulating so fast that the redo log is about to run out of the reusable space, InnoDB dials 911: sync flushing. Sync flushing aggressively flushes dirty pages immediately, and the user transactions will stall until enough redo log space has been cleared.

Let’s see this in an example:
Your redo log capacity is only 1 GB (too small for your workload). Then you start a bulk import for say, 50G. Adaptive flushing is already progressing at the higher pace, but the disk simply cannot finish flushes fast enough. Checkpoint age approaches the limit, InnoDB freezes new writes and force-flushes. This is where your application sees unexplained freezes.

Let’s see how to avoid these problems.

• Increase redo log capacity: innodb_redo_log_capacity in MySQL 8.0.30+, or innodb_log_file_size in older versions.
• Use faster storage.
• Set innodb_io_capacity and innodb_io_capacity_max realistically for your disks so background/adaptive flushing can actually keep up.

Flushing types at a glance

Flushing type	Trigger	Purpose	User impact
Background	Timer (~1s), dirty percentage threshold	Routine cleanup	None
Adaptive	Redo log generation rate / fullness	Match flushing speed to write speed, avoid spikes	Smooths performance
LRU	No free pages for new reads	Make room in the buffer pool	Read stalls if it lags
Sync	Redo log nearly out of space	Emergency checkpoint	Write stalls. Avoid at all costs.

Flushing Analogy

• Background = a cleaning agent on a fixed schedule.
• Adaptive = a clearning agent who works faster when the party gets messier.
• LRU = a cleaning agent that’s clearing a table so a new guest can sit down.
• Sync = the fire alarm, everyone stops until the mess is cleared.

If you have Percona Monitoring and Management, the “MySQL InnoDB Details” dashboard has “InnoDB Checkpoint Age” and “InnoDB Flushing by Type” along with other detailed charts to help you analyse this easily.
Let’s do some hands on activity to check your current status. You do not need to be a DBA to check whether flushing is a problem. Three simple checks cover all four flushing types.
{That said, don’t quote me for changes you make in production without testing and proper approval cycle that you must follow.}

Check 1: Risk of sync flush

Run:

SHOW ENGINE INNODB STATUS\G

Find the LOG section:

Log sequence number 584799234701
Last checkpoint at 584210077990

Subtract the two numbers — that is your checkpoint age (how much “un-cleaned mess” the redo log is holding). Then check the size of the redo log:

SELECT @@innodb_redo_log_capacity; -- MySQL 8.0.30+

Simple rule: if checkpoint age is regularly more than ~75% of the redo log capacity, you are close to the sync-flushing cliff.
Fix here is to increase the redo log.

Check 2: LRU Flushing impact

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';

Documentation describes this variable as “Number of times InnoDB waited for a free page before reading or creating a page.” On a healthy server it stays near 0. If it keeps growing, the buffer pool is too small for your active data.
Fix here would be to increase innodb_buffer_pool_size

Check 3: Is the background/adaptive flushing working OK?

Watch the dirty pages and flush activity live, refreshed every 10 seconds:

mysqladmin -uroot -p ext -ri 10 | grep -E 'pages_dirty|pages_flushed|wait_free'

When you see that the pages_dirty goes up during write bursts and comes back down afterwards -> this is good.
But when pages_dirty only climbs and never recovers -> that means flushing is not able to keep up with your writes.
Fix here is to adjust innodb_io_capacity & innodb_io_capacity_max to matches what your disks can actually do.

Conclusion

Years later, I finally understand what Abuelo was really saying. Flushing is one simple action of writing dirty pages to disk. A healthy server spends its whole life in background and adaptive flushing, quietly cleaning as it goes.
If LRU flushing shows up often, your buffer pool is too small for your working set.
And if sync flushing shows up even once, your redo log or storage cannot keep up with your writes, fix that first.

So keep your dirty pages low, your checkpoint age lower, and flush a little every day — whether it’s redo logs or life’s endless worries, never let either pile up until the fire alarm rings on you.
Somewhere, Abuelo is watching his checkpoint age graph, smiling. It’s flat. It has always been flat.