Performance Schema Instrumentation, Consumers, Statements, Waits, and Overhead

Learning outcomes

ServiceHub’s API latency has become erratic, but the team has only three kinds of evidence: application traces, a few global counters, and guesses about “disk waits.” MariaDB’s Performance Schema exists to expose server instrumentation in a structured way, but it is not a magic black box. You must know which code paths are instrumented, which consumers retain events, how long those events survive, and what measurement overhead you have accepted before interpreting the data.

1. Mental model: instrument → event → consumer → table

An instrument is a named measurement point inside MariaDB—for example a statement class, mutex, file I/O path, socket operation, or memory allocation. A consumer decides whether events flow into current/history tables or summary structures. The Performance Schema itself is an in-memory instrumentation subsystem; its tables do not represent permanent audit history.

This chain matters because an empty table can mean “nothing happened,” but it can also mean the instrument or consumer was disabled, the history window rolled over, or you reset it. Always establish collection state before interpreting absence.

2. Verify collection state before changing it

SELECT VERSION() AS server_version;SHOW VARIABLES LIKE 'performance_schema';SELECT COUNT(*) AS instrumentsFROM performance_schema.setup_instruments;SELECT NAME, ENABLEDFROM performance_schema.setup_consumersORDER BY NAME;

On a current server with Performance Schema enabled, setup_instruments should contain many named instruments and setup_consumers should show which destinations are enabled. Do not paste a “known good” matrix from another version: the actual table is the authority for this server.

DROP DATABASE IF EXISTS servicehub17;CREATE DATABASE servicehub17 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub17;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,  closed_at DATETIME(6) NULL,  INDEX ix_status_opened(status, opened_at),  INDEX ix_customer(customer_id)) ENGINE=InnoDB;CREATE TABLE ticket_events (  event_id BIGINT PRIMARY KEY AUTO_INCREMENT,  ticket_id BIGINT NOT NULL,  event_type VARCHAR(40) NOT NULL,  event_at DATETIME(6) NOT NULL,  payload VARCHAR(500) NULL,  INDEX ix_ticket_time(ticket_id, event_at)) ENGINE=InnoDB;INSERT INTO tickets(customer_id,status,priority,opened_at,closed_at)WITH RECURSIVE seq AS (  SELECT 1 AS n  UNION ALL SELECT n+1 FROM seq WHERE n < 500)SELECT 1000 + (n % 80),       CASE WHEN n % 7 = 0 THEN 'closed' WHEN n % 3 = 0 THEN 'waiting' ELSE 'open' END,       1 + (n % 5),       NOW(6) - INTERVAL n MINUTE,       CASE WHEN n % 7 = 0 THEN NOW(6) - INTERVAL (n-2) MINUTE ELSE NULL ENDFROM seq;INSERT INTO ticket_events(ticket_id,event_type,event_at,payload)SELECT ticket_id,       CASE WHEN ticket_id % 4=0 THEN 'comment' ELSE 'status_change' END,       opened_at + INTERVAL 30 SECOND,       RPAD('x', 120, 'x')FROM tickets;

The small ServiceHub fixture provides repeatable statements and I/O without pretending to be a production benchmark. The dataset is intentionally tiny; any latency numbers are local observations only.

3. Statement digests: group query shapes, not literal values

A digest normalizes literals so structurally similar statements can be aggregated. That lets you ask which query shapes dominate execution count or timed wait instead of grouping by every customer ID.

USE servicehub17;SELECT COUNT(*) FROM tickets WHERE status='open';SELECT COUNT(*) FROM tickets WHERE status='waiting';SELECT * FROM tickets WHERE customer_id=1017 ORDER BY opened_at DESC LIMIT 10;SELECT SCHEMA_NAME,       DIGEST_TEXT,       COUNT_STAR,       ROUND(SUM_TIMER_WAIT/1000000000000,6) AS total_seconds,       ROUND(AVG_TIMER_WAIT/1000000000000,6) AS avg_seconds,       SUM_ROWS_EXAMINED,       SUM_ROWS_SENTFROM performance_schema.events_statements_summary_by_digestWHERE SCHEMA_NAME='servicehub17'ORDER BY SUM_TIMER_WAIT DESCLIMIT 10;

The status-count queries should collapse into one normalized digest even though the literal status differs. The table’s timing columns use picosecond-based timer units when timing is collected; converting to seconds makes human inspection easier. Digest summaries are cumulative within their current collection lifetime, so record the baseline/reset time before comparing two releases.

4. Wait evidence and short history windows

SELECT NAME, ENABLEDFROM performance_schema.setup_consumersWHERE NAME LIKE 'events_waits%';SELECT EVENT_NAME, OPERATION, OBJECT_SCHEMA, OBJECT_NAME,       TIMER_WAIT, NUMBER_OF_BYTESFROM performance_schema.events_waits_currentWHERE EVENT_NAME IS NOT NULLORDER BY TIMER_WAIT DESCLIMIT 20;

events_waits_current exposes the most recent monitored wait per thread. History tables retain only bounded recent windows; they are not durable logs. If the waits consumers you need are disabled, enable the smallest useful scope for the diagnostic window, then revert it.

UPDATE performance_schema.setup_consumersSET ENABLED='YES'WHERE NAME IN ('events_waits_current','events_waits_history');-- Generate a small amount of known work in another session, then inspect:SELECT THREAD_ID, EVENT_NAME, OPERATION,       ROUND(TIMER_WAIT/1000000000000,6) AS wait_seconds,       OBJECT_SCHEMA, OBJECT_NAMEFROM performance_schema.events_waits_historyWHERE TIMER_WAIT IS NOT NULLORDER BY TIMER_WAIT DESCLIMIT 30;-- Restore your prior consumer state after the diagnostic window.

Do not assume the same consumer set is appropriate permanently. History depth is bounded and some sizing variables are startup-only; for longer retention, export/aggregate telemetry externally rather than inflating every in-server history table blindly.

5. Wrong approach: “enable everything and wait for the answer”

A common operator reaction is to turn every instrument and every history consumer on, leave them on indefinitely, and then sort the biggest counter. This increases measurement work and still lacks a hypothesis or time boundary.

UPDATE performance_schema.setup_instrumentsSET ENABLED='YES', TIMED='YES';UPDATE performance_schema.setup_consumersSET ENABLED='YES';

The SQL may succeed, but “success” is the failure mode: you have expanded instrumentation globally without measuring its overhead or documenting why each class is required. Repair the process by restoring the previous state, selecting the minimum instruments/consumers for the question, and recording before/after throughput and latency under the same workload.

-- Record the current state first:SELECT NAME, ENABLED FROM performance_schema.setup_consumers ORDER BY NAME;-- Example: leave statement digests enabled and activate only short statement historyUPDATE performance_schema.setup_consumersSET ENABLED='YES'WHERE NAME IN ('events_statements_current','events_statements_history','statements_digest');SELECT NOW(6) AS observation_window_started;

If you benchmark overhead, compare identical local workloads with warmup, concurrency, dataset, and cache state documented. A single laptop timing is not a universal Performance Schema overhead percentage.

6. Reset semantics: a counter without “since when?” is incomplete

Many Performance Schema summary tables can be reset with TRUNCATE TABLE. That is useful for a controlled experiment, but it destroys the previous in-memory summary. Treat a reset as an operational event worth recording.

TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;SELECT NOW(6) AS digest_window_start;USE servicehub17;SELECT COUNT(*) FROM tickets WHERE status='open';SELECT COUNT(*) FROM tickets WHERE status='waiting';SELECT DIGEST_TEXT, COUNT_STAR,       ROUND(SUM_TIMER_WAIT/1000000000000,6) AS total_secondsFROM performance_schema.events_statements_summary_by_digestWHERE SCHEMA_NAME='servicehub17'ORDER BY SUM_TIMER_WAIT DESC;

After the reset, the result is easier to interpret because the window is controlled. It is also no longer evidence about the hour before the reset. Production telemetry pipelines should preserve context externally before destructive resets.

7. Production judgment, lab verification, and cleanup

Performance Schema is strongest when you ask a specific question—“which normalized statements accumulated most latency since deployment X?” or “which wait class rose during the incident?”—and combine that answer with plans, logs, and OS metrics. It is weakest when treated as a self-explaining ranking table.

DROP DATABASE IF EXISTS servicehub17;