Chapter 16 · Performance Schema, sys Schema, Logs, Metrics, and Observability
Key OS and MySQL Metrics: CPU, I/O, Buffer Pool, Connections, Temp Tables, and Redo
Convert cumulative MySQL status counters and host telemetry into rates and saturation trends, then connect CPU, storage, connection, buffer-pool, temporary-work, and redo signals to causal hypotheses instead of isolated red/green numbers.
Learning outcomes
A dashboard that says “CPU 82%, connections 140, buffer hit 99.8%” can still be useless. These numbers need a baseline, a time window, and a causal model. MySQL exposes many values as cumulative counters since server start or reset. Host tools expose CPU scheduling, memory pressure, storage latency/queueing, and network behavior outside the database. Observability becomes engineering when you measure deltas per interval, compare them with workload demand, and test one hypothesis at a time.
Distinguish counters, gauges, rates, ratios, latency distributions, and saturation indicators.
Build a causal map linking workload to CPU, storage, connections, buffer-pool behavior, temporary work, and redo/checkpoint pressure.
Capture before/after MySQL status snapshots and compute interval deltas instead of interpreting lifetime cumulative values as rates.
Collect parallel Windows/Linux host evidence without assuming one operating-system command exists everywhere.
Reject single-metric tuning and use correlated signals to choose the next diagnostic experiment.
Mandatory labs target MySQL Community Server 8.4.10 LTS on one disposable local instance. Performance Schema and the sys schema are expected in a normal initialized 8.4 instance, but every lesson first inspects availability/configuration instead of assuming a consumer, instrument, log sink, or privilege is enabled. Examples use a diagnostic administrator only where runtime instrumentation/log configuration requires it; application accounts remain least-privilege.
Classify the signal before interpreting it
| Type | Example | Correct interpretation |
|---|---|---|
| counter | Connections, Created_tmp_disk_tables, Innodb_os_log_written | monotonic-ish total; calculate delta/rate over a window |
| gauge | Threads_connected, Threads_running, Innodb_buffer_pool_pages_dirty | current state; graph over time and compare with capacity/baseline |
| ratio | physical buffer reads / logical requests | contextual efficiency indicator, not a universal pass/fail percentage |
| latency | statement p95/p99, storage await | distribution/time-to-complete; tail behavior matters |
| saturation | run queue, disk queue, log waits, connection exhaustion | demand is waiting for a constrained resource |
Build the causal map
| Demand / symptom | MySQL evidence | Host evidence | Do not conclude yet |
|---|---|---|---|
| more concurrent requests | Connections, Threads_connected, Threads_running, aborted connects | CPU run queue, memory pressure, socket/network | that max_connections should be raised |
| more reads | Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads; file I/O summaries | storage read latency/queue, filesystem cache | that a fixed buffer-pool hit ratio is universally required |
| large GROUP BY / ORDER BY | Created_tmp_tables, Created_tmp_disk_tables, digest rows/sort evidence | temp-filesystem I/O and free space | that tmp limits should simply be raised |
| write burst | Innodb_os_log_written, Innodb_log_writes, Innodb_log_waits, dirty pages | write latency, fsync latency, disk queue | that durability should be weakened |
| slow requests | digest/EXPLAIN/lock waits + errors | CPU/I/O/network/memory timeline | that the database is the only bottleneck |
Capture a repeatable MySQL interval
The Performance Schema global_status table makes it convenient to snapshot selected counters into temporary tables. This does not reset global counters and affects only your observer session.
DROP TEMPORARY TABLE IF EXISTS obs_before;DROP TEMPORARY TABLE IF EXISTS obs_after;SET @obs_t0 = NOW(6);CREATE TEMPORARY TABLE obs_before ASSELECT VARIABLE_NAME, CAST(VARIABLE_VALUE AS UNSIGNED) AS value_beforeFROM performance_schema.global_statusWHERE VARIABLE_NAME IN ( 'Connections','Threads_connected','Threads_running', 'Created_tmp_tables','Created_tmp_disk_tables', 'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads', 'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_wait_free', 'Innodb_os_log_written','Innodb_log_write_requests','Innodb_log_writes','Innodb_log_waits');Now generate a small, disclosed workload. The goal is not to benchmark your laptop; it is to create observable deltas.
USE servicehub_observe_lab;SELECT region,status,COUNT(*),SUM(labor_minutes)FROM work_ordersGROUP BY region,statusORDER BY region,status;START TRANSACTION;UPDATE work_ordersSET labor_minutes=labor_minutes+1WHERE work_order_id BETWEEN 200 AND 1199;ROLLBACK;SELECT COUNT(*) FROM work_orders WHERE technician_id BETWEEN 10 AND 20;Capture after state and compute deltas. Gauges such as Threads_connected and dirty pages are not additive rates, so interpret their before/after state separately.
SET @obs_t1 = NOW(6);CREATE TEMPORARY TABLE obs_after ASSELECT VARIABLE_NAME, CAST(VARIABLE_VALUE AS UNSIGNED) AS value_afterFROM performance_schema.global_statusWHERE VARIABLE_NAME IN ( 'Connections','Threads_connected','Threads_running', 'Created_tmp_tables','Created_tmp_disk_tables', 'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads', 'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_wait_free', 'Innodb_os_log_written','Innodb_log_write_requests','Innodb_log_writes','Innodb_log_waits');SET @seconds = TIMESTAMPDIFF(MICROSECOND,@obs_t0,@obs_t1)/1000000.0;SELECT b.VARIABLE_NAME, b.value_before, a.value_after, a.value_after-b.value_before AS delta, ROUND((a.value_after-b.value_before)/NULLIF(@seconds,0),3) AS per_secondFROM obs_before bJOIN obs_after a USING(VARIABLE_NAME)ORDER BY b.VARIABLE_NAME;SELECT @seconds AS observation_seconds;Buffer pool: logical demand versus physical reads
Innodb_buffer_pool_read_requests counts logical read requests; Innodb_buffer_pool_reads counts reads that required data to be brought into the buffer pool. Compare interval deltas and the workload phase. A cold restart, table scan larger than memory, or new working set can legitimately produce more physical reads. A long-term global “hit rate” can hide a ten-minute incident.
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';SHOW VARIABLES LIKE 'innodb_buffer_pool_size';-- This sys view reads INFORMATION_SCHEMA.INNODB_BUFFER_PAGE and can itself be expensive.-- Use it only on the disposable lab/test instance unless production impact is understood.SELECT * FROM sys.innodb_buffer_stats_by_schemaORDER BY allocated DESCLIMIT 10;Temporary work: a disk spill is a clue, not a tuning command
Created_tmp_tables and Created_tmp_disk_tables are cumulative counters. Their interval rates tell you whether the workload started producing more internal temporary work. Then identify the responsible statement digest and inspect its plan. Increasing memory thresholds globally may multiply per-session memory and merely postpone a query-design problem.
SHOW GLOBAL STATUS LIKE 'Created_tmp%';SELECT db, query, exec_count, tmp_tables, tmp_disk_tables, total_latency, rows_examinedFROM sys.statement_analysisWHERE db='servicehub_observe_lab'ORDER BY tmp_disk_tables DESC, total_latency DESCLIMIT 10;Redo and checkpoint pressure: preserve durability while diagnosing
Innodb_os_log_written measures redo bytes written over time, while Innodb_log_waits counts occasions where the log buffer was too small and work had to wait for flushing. Current redo LSN/checkpoint status adds mechanism context. A write burst plus elevated storage latency may explain commit pressure; changing durability settings is not the first response.
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';SHOW GLOBAL STATUS LIKE 'Innodb_log_write%';SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_%lsn';SHOW VARIABLES LIKE 'innodb_redo_log_capacity';SELECT *FROM performance_schema.innodb_redo_log_files;Correlate the host at the same timestamp
# Linux: basic CPU/run-queue/memory sampling (usually available)vmstat 1 5# Linux: detailed block-device latency/queue if sysstat/iostat is installed# iostat -xz 1 5# macOS alternatives include Activity Monitor and vm_stat/iostat.# Counter names can vary by Windows language/version.Get-Counter '\Processor(_Total)\% Processor Time' -SampleInterval 1 -MaxSamples 5Get-Counter '\Memory\Available MBytes' -SampleInterval 1 -MaxSamples 5# Resource Monitor / Task Manager can provide process CPU, memory and disk views# when locale-specific performance-counter names differ.Take host samples during the same MySQL interval. A slow statement with low CPU and high storage latency suggests a different experiment from the same statement with a saturated CPU run queue and no storage wait. Containerized MySQL also requires checking container memory/CPU limits, because host-wide free memory does not prove the container has headroom.
Wrong approach: tune one red number
A rising Threads_connected gauge does not automatically mean increase max_connections. A lower cache-hit ratio does not automatically mean allocate all RAM to InnoDB. Disk temporary tables do not automatically mean increase tmp-table limits. Redo waits do not justify weakening fsync/durability. Form a causal hypothesis, collect correlated evidence, change one reversible control, and measure the same workload again.
Production judgment
Store time-series rates/gauges externally so you can compare today with the same workload period last week and preserve data through server restarts. Label configuration, deployments, failovers, backups, and schema changes on the same timeline. Use percentiles for latency, errors for correctness, and resource saturation as explanatory signals. The next lesson turns this evidence into a dashboard and alert model that operators can actually act on.
Knowledge check
- Why is Connections=500000 not useful by itself?
- How should Innodb_buffer_pool_reads be interpreted?
- What is wrong with using Created_tmp_disk_tables as a single lifetime threshold?
- What does a delta in Innodb_log_waits tell you?
- Why collect host metrics during the same interval as MySQL counters?
Reveal answers
- It is a cumulative counter since startup/reset. You need an interval delta/rate, concurrent connection gauges, baseline, errors, and capacity context.
- As physical buffer-pool reads over a defined workload/time window relative to logical demand and working-set/cache state—not as a universal global ratio target.
- It accumulates over time and mixes many workload phases. Calculate a rate, identify responsible statements, and inspect plans before changing memory limits.
- The server experienced log-buffer flush waits during that observation history; correlate the interval delta with write rate, redo capacity/buffer, and storage behavior before tuning.
- To distinguish database mechanisms from host bottlenecks and establish causality around the same incident window.