Chapter 17 · Memory, I/O, Temporary Work, and Server Performance Engineering
Redo Capacity, I/O Capacity, Flush Methods, Storage Latency, and Checkpoint Pressure
Connect redo generation, checkpoint age, I/O-capacity hints, flush methods, dirty-page pressure, and storage latency before changing performance or durability settings.
Learning outcomes
ServiceHub writes are now the dominant load. Latency rises in bursts, dirty pages accumulate, and storage utilization is high. The wrong response is to disable durability or copy an innodb_io_capacity value from faster hardware. This lesson builds a causal chain from redo generation through checkpoint progress and page flushing to the storage device.
Explain redo capacity, current/checkpoint LSNs, checkpoint age, and why capacity influences flushing pressure.
Measure redo generation rate and dirty-page/write activity from status deltas.
Interpret innodb_io_capacity and innodb_io_capacity_max as background-I/O hints, not hardware benchmark results.
Distinguish restart-only flush-method semantics across Unix-like systems and Windows.
Diagnose write pressure before changing durability-related settings or redo capacity.
The mandatory lab generates writes but does not change innodb_flush_log_at_trx_commit, sync_binlog, or innodb_flush_method. Durability/flush-method experiments belong only on a disposable instance with an explicit failure/recovery objective.
Redo is a durability stream; checkpoint progress frees old redo
InnoDB records changes in the redo log so crash recovery can replay durable modifications that were not yet reflected in tablespace pages. The logical position in this stream is the log sequence number (LSN). The current LSN advances as redo is generated. The checkpoint LSN identifies a recovery point before which older redo is no longer needed for dirty pages. Their difference is a useful approximation of checkpoint age.
innodb_redo_log_capacity controls total redo-log disk capacity. If capacity is too small relative to redo generation and storage flushing ability, InnoDB must flush dirty pages more aggressively to advance checkpoints. Making capacity larger can provide more room between bursts and checkpoints, but it consumes disk and can increase crash-recovery work; it does not make slow storage fast.
| Evidence | Meaning | Caution |
|---|---|---|
| Innodb_redo_log_current_lsn | current redo position | monotonic position, not “bytes per second” until differenced |
| Innodb_redo_log_checkpoint_lsn | checkpoint position | subtract from current LSN for age/context |
| Innodb_redo_log_capacity_resized | effective current capacity | resize may take time; compare to checkpoint/logical size |
| Innodb_os_log_written | redo bytes written since start | use interval deltas for generation/write rate |
| Innodb_buffer_pool_pages_dirty | dirty pages now | gauge; correlate with flushing and storage latency |
| Innodb_log_waits | log buffer too small caused waits | not a direct “redo capacity full” counter |
Capture the write-path baseline
SELECT @@GLOBAL.innodb_redo_log_capacity AS configured_redo_capacity, @@GLOBAL.innodb_io_capacity AS io_capacity, @@GLOBAL.innodb_io_capacity_max AS io_capacity_max, @@GLOBAL.innodb_flush_method AS flush_method, @@GLOBAL.innodb_flush_log_at_trx_commit AS flush_at_commit;SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_redo_log_capacity_resized', 'Innodb_redo_log_current_lsn', 'Innodb_redo_log_checkpoint_lsn', 'Innodb_redo_log_logical_size', 'Innodb_os_log_written', 'Innodb_log_waits', 'Innodb_buffer_pool_pages_dirty', 'Innodb_buffer_pool_pages_flushed', 'Innodb_data_writes', 'Innodb_data_written');SELECT current_lsn, checkpoint_lsn, current_lsn - checkpoint_lsn AS checkpoint_age_bytesFROM ( SELECT MAX(CASE WHEN VARIABLE_NAME='Innodb_redo_log_current_lsn' THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS current_lsn, MAX(CASE WHEN VARIABLE_NAME='Innodb_redo_log_checkpoint_lsn' THEN CAST(VARIABLE_VALUE AS UNSIGNED) END) AS checkpoint_lsn FROM performance_schema.global_status WHERE VARIABLE_NAME IN ('Innodb_redo_log_current_lsn', 'Innodb_redo_log_checkpoint_lsn')) AS x;Checkpoint age is context, not a universal red/yellow/green percentage. Trend it beside redo generation, dirty pages, flush/write throughput, pending I/O, and device latency. A rapidly rising age during a sustained burst is different from a stable age that later drains.
Run a controlled write burst and measure deltas
-- Snapshot the counters above first.START TRANSACTION;UPDATE servicehub_perf_lab.work_ordersSET parts_cost = parts_cost + 0.01, labor_minutes = labor_minutes + 1WHERE work_order_id BETWEEN 10001 AND 30000;COMMIT;-- Immediately re-run the redo/checkpoint/status snapshots,-- then repeat after 10–30 seconds to see recovery/flushing progress.Record wall-clock timestamps with each snapshot. The difference in Innodb_os_log_written divided by elapsed seconds gives a local redo-write rate for that interval. Likewise, use deltas for pages flushed/data written. The transaction above is intentionally disposable; if you need repeated runs with identical logical data, reset the lab from Lesson 1 rather than pretending each iteration is the same workload.
Get-Counter '\PhysicalDisk(_Total)\Avg. Disk sec/Read','\PhysicalDisk(_Total)\Avg. Disk sec/Write','\PhysicalDisk(_Total)\Current Disk Queue Length' -SampleInterval 1 -MaxSamples 10# If sysstat is installed:iostat -xz 1 10# Also capture CPU/iowait and run queue:vmstat 1 10I/O capacity is a background-work hint
innodb_io_capacity describes the I/O operations per second InnoDB may assume are available for background work such as dirty-page flushing. In MySQL 8.4 its default is 10,000, a major change from older releases. innodb_io_capacity_max defaults to twice that value. Neither setting is a benchmark of your disk, and the appropriate value depends on the storage stack, workload, and latency target.
If background flushing is too timid relative to the device, dirty pages/checkpoint pressure can accumulate. If it is too aggressive, background writes can compete with foreground queries. Measure actual storage latency/queue behavior under representative read/write mix before changing these values.
MySQL 8.4 changed several InnoDB defaults, including innodb_io_capacity. Always inspect effective configuration and version before deciding a value is “too high” or “too low.”
Flush method is platform-specific and restart-scoped
innodb_flush_method is not dynamic. On Unix-like systems, MySQL 8.4 defaults to O_DIRECT when supported, otherwise fsync. On Windows the default is unbuffered. These choices affect how InnoDB interacts with the operating-system cache and storage. Hardware RAID cache, SAN behavior, filesystem, virtualization, and sector size can all change the result.
Do not change flush methods online or infer that one method is universally faster. Benchmark on hardware and workload that resemble production, and include durability validation. Unsupported test-only methods such as nosync are not production tuning options.
Wrong approach: trade durability for an attractive benchmark
-- Changing this can alter durability semantics after OS/power failure:-- SET GLOBAL innodb_flush_log_at_trx_commit = 2;-- Likewise, do not lower sync_binlog or disable safety features merely-- to improve a short benchmark without an explicit RPO/durability decision.If storage latency or checkpoint pressure is the cause, weakening durability changes the product guarantee rather than fixing capacity. Performance comparisons with different durability policies are not apples-to-apples.
When to consider redo-capacity or I/O changes
Consider redo-capacity changes when repeatable write bursts show checkpoint age rising rapidly, dirty-page flushing becoming aggressive, and the current capacity leaving little burst tolerance despite otherwise healthy storage. Consider I/O-capacity changes when device telemetry shows unused capacity but background flushing persistently falls behind—or when excessive background I/O competes with foreground latency.
SELECT VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCEFROM performance_schema.variables_infoWHERE VARIABLE_NAME IN ('innodb_redo_log_capacity', 'innodb_io_capacity','innodb_io_capacity_max', 'innodb_flush_method');SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_redo_log_resize_status','Innodb_redo_log_capacity_resized');innodb_redo_log_capacity is dynamic in 8.4, but resizing changes flushing behavior and consumes disk. Stage it, record the old value, verify resize status, repeat the same workload, and define rollback criteria. innodb_flush_method requires restart planning.
Production judgment
Performance engineering on the write path should preserve the durability contract unless a business owner explicitly changes the recovery objective. Diagnose in this order: workload/transaction shape, redo generation rate, checkpoint age/capacity, dirty-page trend, InnoDB write/flush counters, then host/device latency and queueing. Tune only the mechanism the evidence supports.
Lesson 5 turns all of Chapter 17 into a controlled experiment discipline: cache state, concurrency, repetitions, percentiles, and environment capture.
Knowledge check
- What is checkpoint age in this lesson?
- Why is
Innodb_log_waitsnot a direct redo-capacity pressure metric? - What does
innodb_io_capacityrepresent? - Why is changing
innodb_flush_log_at_trx_commita poor generic tuning fix? - Why must flush-method tests be platform- and hardware-specific?
Reveal answers
- The difference between current redo LSN and checkpoint LSN, used as contextual evidence of how far the checkpoint trails current redo.
- It counts waits because the redo log buffer was too small and had to flush; redo-file capacity/checkpoint pressure is a different mechanism.
- A hint for IOPS available to InnoDB background tasks, not a measured specification of the storage device.
- It can change durability guarantees rather than solving the underlying storage/checkpoint/capacity problem.
- They interact with OS caching, filesystem and storage-controller/SAN behavior; the best choice is not universal and the variable is restart-scoped.