Chapter 16 · Performance Schema, sys Schema, Logs, Metrics, and Observability
Performance Schema Instruments, Consumers, Events, and Measurement Overhead
Build a mechanism-first mental model of Performance Schema: instruments generate measurements, consumers retain selected event classes, event and summary tables answer specific questions, and deeper visibility has finite memory/CPU/retention cost.
Learning outcomes
ServiceHub users report intermittent latency, but CPU and disk graphs do not immediately show a catastrophe. The wrong response is to enable every possible diagnostic option and hope one screen reveals the answer. MySQL already has an internal instrumentation framework—Performance Schema—that can measure statements, waits, stages, transactions, locks, files, memory, sockets, and server threads. The engineering skill is to ask a narrow question, collect the minimum evidence that answers it, and understand how much history was actually retained.
Explain the hierarchy from Performance Schema instruments to consumers, current/history tables, and summary tables.
Inspect what is enabled before changing instrumentation and enable only the event classes required by a diagnostic question.
Correlate statement, wait, stage, and transaction evidence without assuming every table stores unlimited history.
Explain measurement overhead, memory sizing, retention limits, and why deeper instrumentation is not a free permanent trace.
Run a bounded local investigation and restore the instrumentation configuration used only for the exercise.
Mental model: sensors, routing switches, event buffers, summaries
An instrument is a measurement point inside the server—for example a SQL statement class, file I/O operation, mutex wait, stage, or transaction event. An instrument can be enabled and can optionally be timed. A consumer determines which enabled events are sent to destinations such as current-event tables, per-thread history, global history, or digest summaries. Event tables hold individual observations; summary tables aggregate many observations so you can ask questions like “which normalized statement shape consumed the most total time?” without retaining every execution forever.
| Layer | Question it answers | Typical evidence |
|---|---|---|
| instrument | Can this kind of server activity be measured? | performance_schema.setup_instruments |
| consumer | Where should enabled events be retained/aggregated? | performance_schema.setup_consumers |
| current event | What is running now? | events_statements_current, events_waits_current |
| history | What ended recently? | events_*_history / events_*_history_long |
| summary | What patterns dominate over the observation window? | events_statements_summary_by_digest, file/memory/lock summaries |
Performance Schema event-history tables are in-memory structures with configured/fixed capacities. Old rows are overwritten as buffers cycle. Summary tables aggregate counters, but they also describe only the period since startup/reset. Use external monitoring/log retention when you need durable long-term history.
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.
Inspect before changing anything
SELECT @@performance_schema AS performance_schema_enabled;SELECT NAME, ENABLEDFROM performance_schema.setup_consumersWHERE NAME IN ( 'global_instrumentation','thread_instrumentation', 'events_statements_current','events_statements_history', 'events_statements_history_long','statements_digest', 'events_waits_current','events_waits_history_long', 'events_stages_current','events_stages_history_long', 'events_transactions_current','events_transactions_history_long')ORDER BY NAME;SELECT NAME, ENABLED, TIMEDFROM performance_schema.setup_instrumentsWHERE NAME IN ('statement/sql/select','statement/sql/update') OR NAME LIKE 'wait/io/file/innodb/%'ORDER BY NAMELIMIT 20;The exact enabled set is configuration-dependent. Statement instruments and some statement consumers are commonly enabled, but the lesson never relies on that assumption. If the server says a history consumer is OFF, querying its table may return no useful history even though the table exists.
Create a repeatable workload before diagnosing it
DROP DATABASE IF EXISTS servicehub_observe_lab;CREATE DATABASE servicehub_observe_lab CHARACTER SET utf8mb4;USE servicehub_observe_lab;CREATE TABLE work_orders ( work_order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, customer_id INT NOT NULL, technician_id INT NULL, region VARCHAR(12) NOT NULL, status VARCHAR(12) NOT NULL, opened_at DATETIME NOT NULL, labor_minutes INT NOT NULL, summary VARCHAR(160) NOT NULL, PRIMARY KEY (work_order_id), KEY ix_status_opened (status, opened_at), KEY ix_technician (technician_id), KEY ix_demo_unused_region (region)) ENGINE=InnoDB;CREATE TABLE incident_markers ( marker_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, marker_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), marker_type VARCHAR(30) NOT NULL, note VARCHAR(255) NOT NULL, PRIMARY KEY (marker_id)) ENGINE=InnoDB;-- 10,000 deterministic rows without depending on recursive-CTE limits.INSERT INTO work_orders(customer_id, technician_id, region, status, opened_at, labor_minutes, summary)SELECT 1 + MOD(n, 800), CASE WHEN MOD(n, 11)=0 THEN NULL ELSE 1 + MOD(n, 40) END, ELT(1 + MOD(n,4),'north','south','east','west'), ELT(1 + MOD(n,5),'OPEN','OPEN','CLOSED','CLOSED','WAITING'), TIMESTAMP('2026-01-01 00:00:00') + INTERVAL MOD(n, 180*24*60) MINUTE, 15 + MOD(n*7, 360), CONCAT('ServiceHub work order ', n)FROM ( SELECT ones.i + tens.i*10 + hundreds.i*100 + thousands.i*1000 AS n FROM (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) ones CROSS JOIN (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) tens CROSS JOIN (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) hundreds CROSS JOIN (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) thousands) seed;SELECT COUNT(*) AS work_orders, MIN(opened_at) AS first_opened, MAX(opened_at) AS last_openedFROM work_orders;The dataset is intentionally larger than a toy five-row table but still small enough for a laptop. It is not intended to produce universal latency numbers. The only meaningful comparison is within your own machine and observation window.
USE servicehub_observe_lab;SELECT COUNT(*)FROM work_ordersWHERE status='OPEN' AND opened_at >= '2026-03-01' AND opened_at < '2026-04-01';SELECT technician_id, COUNT(*) AS jobs, SUM(labor_minutes) AS minutesFROM work_ordersWHERE technician_id IS NOT NULLGROUP BY technician_idORDER BY minutes DESCLIMIT 10;START TRANSACTION;UPDATE work_ordersSET labor_minutes = labor_minutes + 1WHERE work_order_id = 42;ROLLBACK; -- return the lab row to its original stateStart with summaries; descend only when needed
SELECT SCHEMA_NAME, LEFT(DIGEST_TEXT,120) AS 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_SENT, SUM_ERRORS, SUM_WARNINGSFROM performance_schema.events_statements_summary_by_digestWHERE SCHEMA_NAME='servicehub_observe_lab'ORDER BY SUM_TIMER_WAIT DESCLIMIT 10;Digest text replaces literal values with normalized placeholders so repeated executions of the same statement shape aggregate together. This is useful for workload ranking. It does not prove why a query is slow, and its sample text is not a stable application API. From a suspicious digest, move to EXPLAIN ANALYZE, waits, lock evidence, and host metrics.
SELECT THREAD_ID, EVENT_ID, LEFT(SQL_TEXT,120) AS sql_text, MYSQL_ERRNO, ROWS_EXAMINED, ROWS_SENT, ROUND(TIMER_WAIT/1000000000000,6) AS secondsFROM performance_schema.events_statements_history_longWHERE CURRENT_SCHEMA='servicehub_observe_lab'ORDER BY EVENT_ID DESCLIMIT 15;Enable only the missing diagnostic path
Suppose you need a short global statement history for an incident drill and events_statements_history_long is disabled. Capture the current state first; change only that consumer; perform the test; restore the prior value. Updating setup tables requires appropriate diagnostic privileges, so do this as a lab administrator—not from the ServiceHub application account.
SELECT NAME, ENABLEDFROM performance_schema.setup_consumersWHERE NAME='events_statements_history_long';-- Record the observed original value before changing it.UPDATE performance_schema.setup_consumersSET ENABLED='YES'WHERE NAME='events_statements_history_long';-- Run only the statements needed for the diagnostic question, then inspect history.SELECT COUNT(*) FROM servicehub_observe_lab.work_orders WHERE status='WAITING';SELECT EVENT_ID, LEFT(SQL_TEXT,100), MYSQL_ERRNO, ROUND(TIMER_WAIT/1000000000000,6) AS secondsFROM performance_schema.events_statements_history_longWHERE CURRENT_SCHEMA='servicehub_observe_lab'ORDER BY EVENT_ID DESC LIMIT 5;-- Restore ENABLED to the value you recorded, rather than blindly forcing OFF.The tempting wrong approach: turn everything on permanently
A learner may run a broad update such as UPDATE setup_instruments SET ENABLED='YES',TIMED='YES' and enable every history consumer. That feels thorough, but it destroys the discipline of measuring only what you need and may increase CPU/memory work on a busy server. It also creates more data than a human can interpret and still does not give infinite retention.
Write the question first: “Which ServiceHub statement shape dominated total latency between 14:00 and 14:10?” Start with digest summaries. If you need an individual execution, enable the smallest appropriate history. If you need a nested file or synchronization wait, enable that instrument class for the shortest useful window and correlate it with host evidence.
Production judgment
Performance Schema is the server's primary introspection substrate and is designed for low-overhead instrumentation, but “low overhead” is not “zero overhead.” Memory structures, event timing, consumer retention, and the number of enabled instrument classes all have costs. Keep a documented baseline configuration, grant diagnostic access deliberately, and send durable metrics/logs to an external retention system when incident reconstruction must survive restart or ring-buffer rollover.
Next, instead of reading raw instrumentation tables for every question, we use the sys schema. It presents common Performance Schema data through human-oriented views while preserving the requirement to verify every candidate before taking destructive action.
Knowledge check
- What is the difference between an instrument and a consumer?
- Why can an empty events_statements_history_long table be ambiguous?
- Why is events_statements_summary_by_digest a good first stop for workload ranking?
- Does enabling all instruments guarantee better diagnosis?
- What should you do before changing a setup_consumer value in production?
Reveal answers
- An instrument defines/measures a class of activity; a consumer decides whether enabled events are retained or aggregated in particular destinations.
- The relevant consumer may be disabled, the buffer may have cycled/reset, or no matching events may have occurred. Inspect configuration and observation window.
- It aggregates normalized statement shapes with execution counts, timing, rows, errors, and other counters, helping prioritize which workload deserves deeper analysis.
- No. It can add unnecessary overhead/noise and still provides bounded retention. Enable only evidence needed by the diagnostic question.
- Record the current configuration, required privilege, purpose, observation window, and restoration step so the change is reversible.