Inspect InnoDB Metrics and Tune Storage Behavior for OLTP Workloads

Learning outcomes

A production incident channel asks, “Which InnoDB setting should we tune?” before anyone has described the workload or measured the symptom. That question reverses the engineering sequence. InnoDB exposes evidence about buffer residency, physical reads, dirty pages, redo generation, checkpoints, row operations, lock waits, deadlocks, history/purge and I/O. Tuning begins by turning a user-visible symptom into a measurable hypothesis, not by copying a configuration file from a server with different RAM, storage, concurrency and durability requirements.

This lesson uses SHOW ENGINE INNODB STATUS, global status variables and INFORMATION_SCHEMA.INNODB_METRICS to build a small OLTP baseline. You then change one low-risk workload property—an index/access path—rather than immediately altering a durability or memory variable. The same experimental discipline applies when a configuration change is justified: one variable, documented scope/dynamic behavior, bounded observation window, rollback value and acceptance criteria.

1. Reset a representative small OLTP dataset

DROP DATABASE IF EXISTS servicehub_innodb_lab;CREATE DATABASE servicehub_innodb_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub_innodb_lab;CREATE TABLE digits (n TINYINT NOT NULL PRIMARY KEY) ENGINE=InnoDB;INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);CREATE TABLE work_orders (  work_order_id BIGINT NOT NULL AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status VARCHAR(20) NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  summary VARCHAR(180) NOT NULL,  notes LONGTEXT NULL,  PRIMARY KEY (work_order_id),  KEY ix_work_orders_status (status, priority, work_order_id),  KEY ix_work_orders_customer (customer_id, opened_at)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;INSERT INTO work_orders(customer_id,status,priority,opened_at,summary,notes)SELECT 1 + (x.n % 250),       ELT(1 + (x.n % 4),'queued','open','closed','cancelled'),       1 + (x.n % 5),       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL x.n SECOND,       CONCAT('ServiceHub order ', x.n),       RPAD(CONCAT('diagnostic-note-',x.n,' '), 1200, 'x')FROM (  SELECT a.n + 10*b.n + 100*c.n + 1000*d.n AS n  FROM digits a CROSS JOIN digits b CROSS JOIN digits c CROSS JOIN digits d) AS xWHERE x.n BETWEEN 1 AND 8000;CREATE TABLE work_order_events (  event_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,  work_order_id BIGINT NOT NULL,  event_type VARCHAR(24) NOT NULL,  occurred_at DATETIME(6) NOT NULL,  payload VARCHAR(500) NOT NULL,  KEY ix_events_wo_time (work_order_id, occurred_at)) ENGINE=InnoDB;INSERT INTO work_order_events(work_order_id,event_type,occurred_at,payload)SELECT 1 + MOD(x.n,8000),       ELT(1+MOD(x.n,3),'created','assigned','note'),       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL x.n SECOND,       RPAD(CONCAT('event-',x.n),200,'e')FROM ( SELECT a.n + 10*b.n + 100*c.n + 1000*d.n AS n FROM digits a CROSS JOIN digits b CROSS JOIN digits c CROSS JOIN digits d) xWHERE x.n BETWEEN 1 AND 9000;

The dataset is intentionally modest. Its purpose is to teach the measurement loop, not claim that 8,000 rows reproduce production. A real tuning test should scale data so indexes exceed trivial cache sizes and should reproduce realistic concurrency, think time, transaction length and read/write mix.

2. Capture a baseline before changing anything

SELECT VERSION() AS server_version;SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','innodb_buffer_pool_size_max','innodb_page_size',  'innodb_log_file_size','innodb_flush_log_at_trx_commit','innodb_doublewrite',  'innodb_io_capacity','innodb_io_capacity_max','innodb_purge_threads',  'transaction_isolation');

SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',  'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_pages_free',  'Innodb_data_reads','Innodb_data_writes','Innodb_data_read','Innodb_data_written',  'Innodb_os_log_written','Innodb_rows_read','Innodb_rows_inserted',  'Innodb_rows_updated','Innodb_rows_deleted','Innodb_row_lock_waits',  'Innodb_row_lock_time','Innodb_deadlocks','Innodb_history_list_length');SHOW ENGINE INNODB STATUS\G

Record the timestamp and workload state. SHOW ENGINE INNODB STATUS includes sections for transactions, deadlocks, file I/O, insert buffer/adaptive structures where relevant, log, buffer pool and row operations. Its “per second averages” depend on the interval since the monitor snapshot, so do not compare two screenshots without understanding the interval.

3. Ask one concrete performance question

Suppose ServiceHub frequently loads recent events for one work order but a developer accidentally removed the supporting composite index. The hypothesis is specific: without (work_order_id, occurred_at), the query examines far more rows/pages and creates unnecessary buffer/I/O work. This is safer and more causally precise than changing server memory first.

EXPLAINSELECT event_id,event_type,occurred_atFROM work_order_eventsWHERE work_order_id=4242ORDER BY occurred_at DESCLIMIT 20;ALTER TABLE work_order_events DROP INDEX ix_events_wo_time;EXPLAINSELECT event_id,event_type,occurred_atFROM work_order_eventsWHERE work_order_id=4242ORDER BY occurred_at DESCLIMIT 20;

The degraded plan should lose the targeted composite access path and may scan/sort more data. Exact EXPLAIN wording is optimizer/version/data dependent, so teach the invariant: compare chosen key/access type/estimated rows and then verify runtime/workload counters rather than memorizing one plan screenshot.

4. Measure the bad workload as deltas

SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',  'Innodb_rows_read','Innodb_data_reads');SELECT event_id,event_type,occurred_atFROM work_order_eventsWHERE work_order_id IN (101,777,2048,4242,7001)ORDER BY work_order_id,occurred_at DESC;SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',  'Innodb_rows_read','Innodb_data_reads');

On a warm small lab the physical-read delta may be zero even though rows-read/logical-request deltas increase. That does not invalidate the experiment; it demonstrates why query shape and CPU/cache work matter even when storage misses are absent. On production-sized data, the same access-path regression can also translate into physical I/O and tail latency.

5. Change one property, verify, and compare

ALTER TABLE work_order_events  ADD KEY ix_events_wo_time (work_order_id, occurred_at);ANALYZE TABLE work_order_events;EXPLAINSELECT event_id,event_type,occurred_atFROM work_order_eventsWHERE work_order_id=4242ORDER BY occurred_at DESCLIMIT 20;

SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',  'Innodb_rows_read','Innodb_data_reads');SELECT event_id,event_type,occurred_atFROM work_order_eventsWHERE work_order_id IN (101,777,2048,4242,7001)ORDER BY work_order_id,occurred_at DESC;SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',  'Innodb_rows_read','Innodb_data_reads');

The acceptance criterion is not “the index exists.” It is that the plan and measured work move in the expected direction without unacceptable write/storage cost. A new index adds space and must be maintained on INSERT/UPDATE/DELETE, so a production decision should include write overhead and index-usage evidence, not query speed alone.

6. Use INNODB_METRICS when you need lower-level counters

SELECT NAME,SUBSYSTEM,STATUS,COUNT,COMMENTFROM information_schema.INNODB_METRICSWHERE NAME LIKE 'buffer_%'   OR NAME LIKE 'log_%'   OR NAME LIKE 'lock_%'ORDER BY SUBSYSTEM,NAMELIMIT 80;

INNODB_METRICS requires PROCESS privilege. Metrics can be enabled, disabled and reset, so a collector must know the metric’s status/reset behavior. Never build an alert from a counter whose lifecycle you do not understand. Performance Schema can add more statement/wait detail when deliberately enabled, but it is not required for this chapter’s baseline.

7. Translate symptoms into a diagnostic matrix

This matrix prevents a common anti-pattern: mapping one metric directly to one setting. Real bottlenecks are causal chains. For example, a missing index can increase rows scanned, extend transaction time, increase lock duration, create more buffer churn, and then appear as both CPU and concurrency symptoms.

8. Deliberately wrong approach: tune five global variables at once

Changing buffer-pool size, redo size, innodb_io_capacity, purge threads and commit durability together may improve a benchmark but leaves you unable to identify which change mattered, which harmed safety, or which is unnecessary. The repair is an experiment ledger: hypothesis, baseline, one change, exact old/new value, workload version, observation interval, latency percentiles, engine/OS counters, acceptance criteria, rollback result and conclusion.

9. Chapter checkpoint and cleanup

1. Capture the complete configuration/status baseline.
2. Drop the event index and record EXPLAIN plus before/after counter deltas.
3. Restore exactly one index, ANALYZE, and repeat the same workload/evidence window.
4. Inspect a sample of INNODB_METRICS and note PROCESS privilege/reset semantics.
5. Write a tuning decision record with explicit acceptance and rollback criteria.
6. Clean up with DROP DATABASE servicehub_innodb_lab; if you no longer need the lab.

Chapter 07 built the InnoDB physical/recovery model: rows live in clustered-index pages, secondary indexes carry clustered keys, the buffer pool caches and dirties pages, redo/undo/checkpoints/doublewrite protect transactions and crash recovery, purge removes versions only when snapshots no longer need them, and tuning must be evidence-driven. Chapter 08 now broadens the storage-engine boundary to Aria, MyISAM, MEMORY, CONNECT and other specialized engines—and asks what guarantees you lose when you leave InnoDB.