Chapter 16 · Performance Schema, sys Schema, Logs, Metrics, and Observability
Build a Production Dashboard, Alert Thresholds, Baselines, and Incident Timelines
Design an actionable production dashboard from golden signals plus MySQL-specific health evidence, establish workload-period baselines, define symptom-versus-cause alerts, and reconstruct a controlled incident timeline from correlated evidence.
Learning outcomes
ServiceHub now has dozens of useful metrics, views, and logs. A production dashboard should not display all of them. Its job is to tell an operator whether users are healthy, whether MySQL has capacity headroom, and where to look next when the service is unhealthy. Good alerts are similarly selective: they describe a condition that requires an action, not merely a number that moved.
Choose a small golden-signal set—latency, traffic, errors, saturation—and augment it with MySQL-specific causal health indicators.
Build baselines by workload period instead of copying universal alert thresholds from another system.
Distinguish symptom alerts that protect an SLO from cause alerts that accelerate diagnosis.
Annotate deployments, schema changes, backups, failovers, and maintenance on the same timeline as metrics/logs.
Reconstruct a deterministic two-session lock incident from application markers, Performance Schema/sys, status counters, and logs.
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.
Start with the user-visible contract
The four classic golden signals are a useful dashboard skeleton: latency, traffic, errors, and saturation. For a database, they should be expressed in ServiceHub terms. A page full of InnoDB counters can be perfectly green while users are receiving timeouts.
| Signal | ServiceHub dashboard example | MySQL causal panels nearby |
|---|---|---|
| latency | API/database request p50/p95/p99; transaction commit latency | statement digest latency, lock waits, storage latency |
| traffic | requests/s, transactions/s, rows changed/s | Questions/Com_* deltas, digest exec_count rates, redo bytes/s |
| errors | request error rate, DB timeout/deadlock/auth errors | events_errors summaries, aborted connects, statement errors, error log |
| saturation | worker/connection queueing and resource headroom | Threads_running, connection headroom, CPU run queue, disk queue, log waits |
Add a compact MySQL health row
| Panel | Why it earns dashboard space | Preferred shape |
|---|---|---|
| connections / running threads | shows demand and concurrency pressure | gauge + rate + configured limit context |
| top statement digests | points from symptom toward workload owner | top-N total latency + p95/rows examined trend |
| lock waits/deadlocks | explains latency spikes and retry load | current waiters + rate over time |
| buffer physical reads / dirty pages | working-set and flushing clues | rates/gauges, not one lifetime ratio |
| temp disk tables | query work spilling beyond memory path | rate + responsible digest |
| redo bytes/log waits/checkpoint | write pressure/durability path | bytes/s, waits/s, capacity/current LSN context |
| replication/cluster state when used | data freshness/HA readiness | member state, queue/lag, quorum/routing state |
Baseline first, threshold second
There is no universal safe value for Threads_running, p99 latency, redo bytes/s, or disk queue depth because hardware, workload, SLO, dataset, and concurrency differ. Capture normal weekdays, peak windows, backups, reporting periods, and maintenance. Then define thresholds around service impact and capacity headroom.
SELECT NOW(6) AS sample_time, (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_connected') AS threads_connected, (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_running') AS threads_running, (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Created_tmp_disk_tables') AS tmp_disk_total, (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_os_log_written') AS redo_bytes_total, (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_log_waits') AS log_waits_total, (SELECT COUNT(*) FROM sys.innodb_lock_waits) AS current_lock_waits;A production collector would store timestamped samples externally and compute rates; the query above simply shows the inputs. A baseline should record server version, important configuration, dataset/workload period, and deployment state so comparisons remain meaningful.
Symptom alert versus cause alert
| Alert type | Example | Operator action |
|---|---|---|
| symptom / SLO | DB-backed request p99 exceeds service objective for sustained window | page service/database owner; start incident; inspect cause panels |
| symptom / correctness | deadlock/timeout error rate breaches application error budget | inspect lock order/retry behavior and current blockers |
| cause / capacity | connection headroom shrinking while queue/running threads rise | find source of concurrency; protect admission/pool; avoid blind max_connections increase |
| cause / storage | I/O latency and redo/dirty-page pressure rise together | inspect storage + write burst + checkpoint/flush evidence |
| cause / query | one digest share of total latency jumps after deployment | compare plan/statistics/schema and deploy marker |
Cause alerts should be actionable and usually less urgent than direct user-impact alerts unless they predict imminent failure. Avoid paging on every transient counter spike; use sustained windows, burn-rate/SLO thinking, or baseline deviations appropriate to the service.
Annotate change events
Dashboards without deployment/maintenance markers force operators to remember what changed. Record schema migrations, application releases, server configuration changes, backups, failovers, index changes, and incident actions. The ServiceHub lab uses a tiny marker table so the concept is reproducible without an external observability vendor.
INSERT INTO servicehub_observe_lab.incident_markers(marker_type,note)VALUES ('LAB_START','Chapter 16 controlled lock incident begins');SELECT * FROM servicehub_observe_lab.incident_markersORDER BY marker_time DESC LIMIT 10;Controlled incident: build the timeline from multiple evidence streams
We will reproduce a lock wait rather than a destructive outage. Open three clients: Session A owns a row lock, Session B attempts to update the same row, and Observer C records evidence. Use a short lock timeout so cleanup is bounded.
USE servicehub_observe_lab;INSERT INTO incident_markers(marker_type,note)VALUES ('BLOCKER_START','Session A acquiring row 777');START TRANSACTION;SELECT work_order_id,status,labor_minutesFROM work_ordersWHERE work_order_id=777FOR UPDATE;-- Hold open while Observer C captures evidence.USE servicehub_observe_lab;SET SESSION innodb_lock_wait_timeout=8;INSERT INTO incident_markers(marker_type,note)VALUES ('WAITER_START','Session B attempts row 777 update');UPDATE work_ordersSET labor_minutes=labor_minutes+10WHERE work_order_id=777;-- It should wait, then proceed after Session A ends or time out.SELECT NOW(6) AS observed_at;SELECT wait_started,wait_age,locked_table, waiting_pid,waiting_query, blocking_pid,blocking_queryFROM sys.innodb_lock_waits\GSELECT conn_id,user,db,state,time,current_statementFROM sys.sessionWHERE conn_id IN ( SELECT waiting_pid FROM sys.innodb_lock_waits UNION SELECT blocking_pid FROM sys.innodb_lock_waits);SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_current_waits';SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits';SHOW GLOBAL STATUS LIKE 'Threads_running';Resolve the incident by rolling back Session A, not by killing random server threads.
ROLLBACK;INSERT INTO servicehub_observe_lab.incident_markers(marker_type,note)VALUES ('BLOCKER_END','Session A rolled back; waiter may proceed');Reconstruct after the fact
SELECT marker_time,marker_type,noteFROM servicehub_observe_lab.incident_markersORDER BY marker_time;SELECT THREAD_ID,EVENT_ID, LEFT(SQL_TEXT,140) AS sql_text, MYSQL_ERRNO,RETURNED_SQLSTATE, ROUND(TIMER_WAIT/1000000000000,6) AS secondsFROM performance_schema.events_statements_history_longWHERE CURRENT_SCHEMA='servicehub_observe_lab'ORDER BY EVENT_ID DESCLIMIT 30;SELECT LOGGED,PRIO,ERROR_CODE,SUBSYSTEM,DATAFROM performance_schema.error_logORDER BY LOGGED DESCLIMIT 20;Depending on timing and enabled history consumers, individual statement events may have rolled out of bounded history. That is a feature of the evidence model, not a reason to invent missing data. In production, external time-series/log retention should preserve the dashboard and application side of the timeline even when Performance Schema history is gone.
Wrong dashboard: everything red, nobody knows what to do
“Buffer hit < 99%,” “Threads_running > 10,” or “disk queue > 1” copied from a blog can page continuously on a healthy workload or miss a real incident. Every alert needs an owner, user/service consequence, observation window, baseline/capacity context, runbook link, and a clear condition for resolution.
| Alert design question | Required answer |
|---|---|
| What does it protect? | SLO, correctness, durability, recovery, or imminent capacity headroom |
| What evidence triggers it? | specific metric/rate/error over a defined window |
| Who owns it? | service/database/platform team with permission to act |
| What is the first action? | diagnostic query/dashboard/runbook, not “tune MySQL” |
| What suppresses/noises it? | maintenance windows, expected batch jobs, failover/backup annotations |
| How is it tested? | safe synthetic drill with known expected signal |
Production judgment and bridge to Chapter 17
A useful observability system correlates four layers: user/application symptoms, MySQL statements/transactions/locks, server resource counters/logs, and operating-system capacity. Dashboards retain trends; logs retain event narratives; Performance Schema/sys explain mechanism; incident markers explain change. None alone is enough.
Chapter 17 uses this instrumentation to make performance changes responsibly. Instead of “increase memory” or “buy faster disks,” you will size the buffer pool from working-set evidence, budget per-connection memory, diagnose temporary-table spills, understand redo/checkpoint/storage pressure, and run reproducible benchmarks with percentiles and controlled cache/concurrency conditions.
Knowledge check
- Why should a production dashboard begin with user-visible golden signals?
- What is the difference between a symptom alert and a cause alert?
- Why annotate deployments and maintenance on metric timelines?
- Why might Performance Schema history be incomplete after an incident?
- What information should every actionable alert contain?
Reveal answers
- Because the system exists to meet service objectives; internal MySQL metrics are explanatory signals and can look normal while users are unhealthy.
- A symptom alert indicates service/SLO/correctness impact; a cause alert points to a mechanism or approaching capacity problem that helps diagnosis/prevention.
- They establish temporal correlation between change and symptom, dramatically reducing the search space during regression diagnosis.
- History tables are bounded in-memory buffers, consumers may be disabled, and resets/restarts can remove context. Durable external monitoring/logging fills that retention role.
- Protected objective, exact trigger/window, owner, first diagnostic/runbook action, maintenance/noise handling, and a tested resolution condition.