Redo Capacity, I/O Capacity, Flush Methods, Storage Latency, and Checkpoint Behavior

Learning outcomes

ServiceHub’s write latency is stable for minutes and then spikes in bursts. The server has plenty of buffer-pool space, yet storage utilization and fsync latency rise around checkpoints. This is the write-path counterpart of Lesson 1: InnoDB can absorb modified pages temporarily, but redo capacity and checkpoint progress bound how long dirty data can remain ahead of durable tablespace writes.

1. Mental model: redo lets commits advance before every dirty data page is rewritten

InnoDB uses a write-ahead redo log to record changes needed for crash recovery. Modified data pages can remain dirty in the buffer pool while redo is made durable according to the configured commit policy. A checkpoint records how far data files are known to be consistent relative to the redo stream. If redo generation outruns page flushing for too long, checkpoint pressure rises and foreground work can be forced to wait for flushing.

2. Inspect current MariaDB write-path configuration

DROP DATABASE IF EXISTS servicehub18;CREATE DATABASE servicehub18 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub18;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status ENUM('open','waiting','closed') NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  updated_at DATETIME(6) NOT NULL,  summary VARCHAR(240) NOT NULL,  INDEX ix_status_opened(status, opened_at),  INDEX ix_customer_updated(customer_id, updated_at)) ENGINE=InnoDB;INSERT INTO tickets(customer_id,status,priority,opened_at,updated_at,summary)WITH RECURSIVE seq AS (  SELECT 1 AS n  UNION ALL SELECT n+1 FROM seq WHERE n < 1000)SELECT MOD(n,125)+1,       ELT(MOD(n,3)+1,'open','waiting','closed'),       MOD(n,5)+1,       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL n MINUTE,       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL n MINUTE,       CONCAT('ServiceHub ticket ',n)FROM seq;SELECT VERSION() AS server_version, @@version_comment AS build_comment,       @@innodb_buffer_pool_size AS buffer_pool_bytes;SELECT COUNT(*) AS seeded_rows FROM tickets;

SHOW GLOBAL VARIABLES WHERE Variable_name IN ( 'innodb_log_file_size','innodb_log_buffer_size', 'innodb_flush_log_at_trx_commit','sync_binlog', 'innodb_io_capacity','innodb_io_capacity_max','innodb_flush_sync', 'innodb_data_file_buffering','innodb_log_file_buffering', 'innodb_data_file_write_through','innodb_log_file_write_through', 'innodb_flush_method');SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_lsn_current','Innodb_lsn_last_checkpoint', 'Innodb_buffer_pool_pages_dirty','Innodb_pages_written', 'Innodb_data_fsyncs','Innodb_data_writes');SHOW ENGINE INNODB STATUS\G

On modern MariaDB, innodb_log_file_size is dynamic from 10.9, and modern releases use one redo log file because innodb_log_files_in_group was deprecated/ignored and later removed. Do not calculate “redo capacity” using an old two-file formula copied from MySQL-era guidance.

3. MariaDB 11.0+ changed how flush behavior should be discussed

innodb_flush_method is deprecated from MariaDB 11.0. Current MariaDB exposes separate dynamic controls for whether data/log files use filesystem buffering and whether writes use write-through semantics: innodb_data_file_buffering, innodb_log_file_buffering, innodb_data_file_write_through, and innodb_log_file_write_through. The old option remains a compatibility mapping, but new tuning should reason about the actual flags and operating system.

SELECT @@GLOBAL.innodb_data_file_buffering,       @@GLOBAL.innodb_log_file_buffering,       @@GLOBAL.innodb_data_file_write_through,       @@GLOBAL.innodb_log_file_write_through,       @@GLOBAL.innodb_flush_log_at_trx_commit;

The filesystem/device guidance is platform-specific. Linux direct I/O, Windows unbuffered I/O, a cloud block device, a local NVMe drive, and a battery-backed RAID cache do not have identical semantics or latency. Measure the actual stack and keep the durability contract explicit.

4. Controlled write burst: measure before changing capacity

SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_lsn_current','Innodb_lsn_last_checkpoint', 'Innodb_buffer_pool_pages_dirty','Innodb_pages_written');START TRANSACTION;UPDATE servicehub18.ticketsSET summary=CONCAT(summary,' tuned'), updated_at=NOW(6)WHERE ticket_id BETWEEN 1 AND 1000;COMMIT;SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_lsn_current','Innodb_lsn_last_checkpoint', 'Innodb_buffer_pool_pages_dirty','Innodb_pages_written');SHOW ENGINE INNODB STATUS\G

Compute the LSN delta and checkpoint-age movement only if your target version exposes the relevant status variables. If names differ, use SHOW GLOBAL STATUS LIKE 'Innodb_lsn%' and document what exists; do not fabricate missing metrics. Pair database evidence with device latency/throughput from the host.

5. The wrong approach: “fix write latency by disabling sync”

Unsafe tuning advice often recommends turning off fsync-like durability or setting commit flushing to a less durable mode without quantifying the data-loss window. The benchmark may look faster because commits acknowledge before the same persistence guarantee has been met.

SELECT @@GLOBAL.innodb_flush_log_at_trx_commit,       @@GLOBAL.sync_binlog;-- DO NOT use as a casual tuning recipe:-- SET GLOBAL innodb_flush_log_at_trx_commit = 0;-- These modes change crash-loss semantics.-- Safe baseline for comparison keeps the application durability contract.SET GLOBAL innodb_flush_log_at_trx_commit = 1;

Even a “safe” baseline command should only be run if you have the needed privilege and understand persistence. If binary logging is required for replication/PITR, sync_binlog participates in the durability picture too. Redo and binlog are separate logs with different purposes, as Chapter 13/14 established.

6. Evaluate redo size and I/O capacity as measured constraints

A larger redo log can reduce checkpoint frequency and absorb longer write bursts, but it changes recovery and storage characteristics. innodb_io_capacity is expressed in pages per second and, in current MariaDB documentation, its throttling scope is specifically checkpoint/background flushing and interacts with innodb_flush_sync. Do not set it equal to a vendor’s headline IOPS number and assume all InnoDB I/O will obey it.

SELECT @@GLOBAL.innodb_log_file_size AS before_bytes,       @@GLOBAL.innodb_io_capacity AS io_capacity,       @@GLOBAL.innodb_io_capacity_max AS io_capacity_max;-- MariaDB 10.9+ supports dynamic redo-log resizing.-- Choose a modest lab value only after checking free disk and target docs.SET @old_redo := @@GLOBAL.innodb_log_file_size;-- Example only; do not execute blindly on a shared server:-- SET GLOBAL innodb_log_file_size = 256*1024*1024;SELECT @@GLOBAL.innodb_log_file_size AS effective_bytes;

After any real experiment, rerun the identical write workload and compare latency distribution, checkpoint/LSN behavior, dirty pages, storage latency, and recovery/RTO implications. Revert if the acceptance criteria are not met.

7. Reproducible lab: write-path evidence sheet

Prerequisites: local MariaDB Community server, InnoDB, permission to read global variables/status, and host storage metrics. Dynamic variable changes require appropriate administrative privileges; the mandatory lab can be completed read-only except for the disposable data workload.

1. Record version, OS/filesystem/container storage, redo size, flush controls, commit durability, I/O capacity, LSN/checkpoint and dirty-page state.
2. Run the bounded write burst three times after a consistent warmup policy; record elapsed time and host write latency.
3. Determine whether the bottleneck appears to be redo/checkpoint pressure, device latency, locking, or something else.
4. Design exactly one change—redo size, I/O capacity, query/write batching, or no setting change—with predicted mechanism and rollback.
5. If safe in your disposable instance, test the one change and rerun the identical workload. Do not weaken durability merely to produce a faster number.
6. Restore changed variables and drop the lab schema.

Production judgment and bridge

Tune redo and flushing when evidence shows checkpoint or storage-path pressure, not because a generic checklist says so. Monitor redo/checkpoint progress, dirty pages, page-write rate, fsync latency, device queue/latency, disk space, crash-recovery expectations, replica/Galera consequences, and backup behavior. The final lesson turns all of Chapter 18 into an experimental discipline: benchmark design, percentiles, regression gates and capacity headroom.

Authoritative references

Use the target-version tab or release notes when a variable or default differs from the course baseline. These lessons intentionally avoid treating old tuning folklore as current MariaDB behavior.

• InnoDB system variables
• InnoDB page flushing
• InnoDB flush method and MariaDB 11.0+ controls