sys Schema Views for Sessions, Statements, Waits, I/O, Memory, and Index Usage

Learning outcomes

Raw Performance Schema tables are precise, but operators repeatedly ask the same questions: Which sessions are blocking others? Which normalized statements consume the most time? Which files dominate I/O? How much instrumented memory is allocated? Which indexes have recorded no reads? The MySQL sys schema packages common joins, formatting, and summaries into DBA-friendly views. It is a convenience layer—not an independent source of truth.

sys is a lens over instrumentation, not magic telemetry

A normal MySQL 8.4 initialization installs sys. Its views frequently read Performance Schema tables and format picoseconds/bytes into human-readable values. Many views have paired x$... variants that expose unformatted values useful for calculations. If Performance Schema collection is disabled or has not observed a representative workload, a sys view cannot invent missing evidence.

SHOW DATABASES LIKE 'sys';SELECT * FROM sys.memory_global_total;SELECT conn_id, user, db, command, state, time, current_statementFROM sys.sessionORDER BY time DESCLIMIT 10;SELECT db, query, exec_count, total_latency, avg_latency,       rows_examined, rows_sent, full_scanFROM sys.statement_analysisWHERE db='servicehub_observe_lab'ORDER BY total_latency DESCLIMIT 10;

Find sessions and lock waits without killing by reflex

sys.session filters the process list toward foreground/user sessions, while sys.processlist can include broader thread information. The sys.innodb_lock_waits view summarizes InnoDB transactions waiting for row locks and identifies blocking connections/statements. A row in the view is a diagnosis lead—not automatic permission to kill the blocker. The blocker may be in the middle of a valid financial or maintenance transaction.

USE servicehub_observe_lab;START TRANSACTION;SELECT work_order_id, statusFROM work_ordersWHERE work_order_id=100FOR UPDATE;-- Keep this transaction open only for the lab.

USE servicehub_observe_lab;SET SESSION innodb_lock_wait_timeout=8;UPDATE work_ordersSET labor_minutes=labor_minutes+5WHERE work_order_id=100;-- This waits behind Session A, then either proceeds after COMMIT/ROLLBACK-- or returns a lock-wait timeout.

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);

The safe repair is to understand transaction ownership and then end the lab transaction in Session A with ROLLBACK. In production, a kill decision requires application/transaction context, rollback cost, data-integrity considerations, and an incident owner.

Statement analysis generates a tuning candidate—not a tuning answer

Run the same intentionally awkward reporting shape several times so it appears in statement summaries. The expression YEAR(opened_at) makes the date component less directly useful for a range access path; adding a single-column status index looks tempting because the query filters by status, but the table already has (status, opened_at). A duplicate prefix does not repair the expression problem.

SELECT COUNT(*), SUM(labor_minutes)FROM servicehub_observe_lab.work_ordersWHERE status='OPEN'  AND YEAR(opened_at)=2026;SELECT db, query, exec_count, total_latency,       rows_examined, rows_sent, full_scanFROM sys.statement_analysisWHERE db='servicehub_observe_lab'ORDER BY total_latency DESCLIMIT 8;

-- Do NOT accumulate indexes just because a query looks slow.-- CREATE INDEX ix_status_only ON servicehub_observe_lab.work_orders(status);SHOW INDEX FROM servicehub_observe_lab.work_orders;EXPLAIN ANALYZESELECT COUNT(*), SUM(labor_minutes)FROM servicehub_observe_lab.work_ordersWHERE status='OPEN'  AND YEAR(opened_at)=2026;

The corrected query expresses a half-open range so the existing composite index can provide both equality on status and a contiguous date range. Compare plan operators and actual iterator rows/timing on the same local dataset. Do not promise a fixed speedup.

EXPLAIN ANALYZESELECT COUNT(*), SUM(labor_minutes)FROM servicehub_observe_lab.work_ordersWHERE status='OPEN'  AND opened_at >= '2026-01-01'  AND opened_at <  '2027-01-01';

I/O and memory: readable summaries with scope limits

SELECT file, count_read, total_read,       count_write, total_written, totalFROM sys.io_global_by_file_by_bytesORDER BY total DESCLIMIT 12;SELECT * FROM sys.memory_global_total;SELECT event_name, current_allocFROM sys.memory_global_by_current_bytesORDER BY current_alloc DESCLIMIT 12;

These are server-instrumented observations. They do not replace OS metrics: filesystem cache, other processes, kernel memory, storage queueing, hypervisor throttling, and container limits can all matter outside MySQL's view.

The dangerous shortcut: “unused index” means “drop it”

SELECT object_schema, object_name, index_nameFROM sys.schema_unused_indexesWHERE object_schema='servicehub_observe_lab';SHOW INDEX FROM servicehub_observe_lab.work_orders;

schema_unused_indexes means no index-usage events were observed in the Performance Schema window. A recently restarted server, a quiet weekend, monthly job, failover path, foreign-key need, or infrequent critical lookup can all make a necessary index look unused. In this lab, ix_demo_unused_region may appear simply because we deliberately did not run region queries.

Production judgment

Use sys for fast orientation and communication: human-readable latency, file I/O, memory, process, lock, and index views are excellent incident starting points. When a decision has consequence—killing a session, dropping an index, changing memory, rewriting SQL—drop down to underlying Performance Schema counters, query plans, transaction state, and host metrics. Friendly formatting should reduce cognitive load, not lower the evidence standard.

Next we add MySQL's durable/semidurable log streams. Logs answer different questions from in-memory instrumentation, and their overhead/retention/privacy characteristics are different.

Authoritative references

• MySQL sys schema
• sys processlist/session views
• sys statement_analysis
• sys innodb_lock_waits
• sys io_global_by_file_by_bytes
• sys memory_global_total
• sys schema_unused_indexes