Every MySQL 8.x server ships with a ready-made diagnostic toolkit that most teams never open. The sys schema has been installed by default since 5.7, sits on top of performance_schema, and turns its picosecond counters and event-name soup into views a human can read at 3 a.m. No agent, no extension, no privilege escalation beyond read access.
The catch is that sys contains over a hundred views, and a list that long is the same as no list. After years of using it, I open perhaps five of them regularly. This guide covers those five, the questions they answer, and the caveats - especially the since-restart problem - that keep people from drawing wrong conclusions.
One structural note before the tour: nearly every sys view exists twice, as a formatted version (latencies as readable strings, ordered sensibly) and an x$ raw version with numeric values for scripting. Read the formatted ones interactively; collect the x$ ones programmatically. All of it is a presentation layer over performance_schema, so instrumentation must be on - the defaults suffice, and the low-overhead performance_schema setup covers what those defaults cost.
statement_analysis: the workload in one screen
If I could keep only one view, it is this. sys.statement_analysis aggregates normalized statement digests with execution count, total and average latency, rows examined versus sent, temp table counts (including on-disk), sort activity, full-scan flags, and error counts - pre-sorted by total latency descending, which is the correct default: total time, not average, is what identifies the statements that own your server.
SELECT query,
db,
exec_count,
total_latency,
avg_latency,
rows_examined_avg,
tmp_disk_tables,
full_scan
FROM sys.statement_analysis
LIMIT 10;The reading habit that matters: scan the ratio of rows_examined_avg to rows_sent_avg (a statement examining 500,000 rows to return 12 is an indexing problem wearing a latency costume), and treat any nonzero tmp_disk_tables on a hot statement as a lead - on-disk temp tables are one of the quietest ways a query family gets slow. The companion views statements_with_full_table_scans and statements_with_temp_tables are the same data pre-filtered to those two pathologies.
Two columns turn this view from a report into an investigation tool. first_seen and last_seen timestamp each digest, so when latency regressed on Tuesday you can filter for statements first seen on Tuesday and often find the culprit in seconds. And the digest column joins back to performance_schema.events_statements_summary_by_digest, where MySQL 8.0 keeps QUERY_SAMPLE_TEXT - an actual sampled statement with real literals, which is what you need to run EXPLAIN, since the normalized text with its question marks will not execute.
The index-hygiene pair: unused and redundant
Indexes cost write amplification, buffer pool space, and optimizer confusion, and every mature schema accumulates dead ones. Two views make the cleanup list for you.
SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('performance_schema', 'sys')
LIMIT 20;
SELECT table_schema,
table_name,
redundant_index_name,
dominant_index_name,
sql_drop_index
FROM sys.schema_redundant_indexes
LIMIT 20;schema_unused_indexes lists indexes with no recorded reads since counters began (it already excludes primary keys). schema_redundant_indexes finds indexes whose columns are a prefix of another index - an index on (a) alongside one on (a, b) - and even generates the DROP statement in sql_drop_index.
Now the caveats, which are load-bearing. "Unused" means unused since the counters last reset - an index that only serves the month-end report looks unused for 29 days. Unique indexes enforce constraints whether or not they are read. And a primary that never uses an index says nothing about the replicas serving reads. Redundant indexes are safer kills but still deserve a check: the "redundant" one may be smaller and preferred by specific queries. My rule: these views generate candidates, observation windows of at least a full business cycle confirm them, and drops ship one at a time.
io_global_by_file_by_bytes: where the disk traffic goes
This view ranks files by bytes read and written through MySQL's I/O instrumentation. It answers a question nothing else answers as directly: which tables, undo tablespaces, binlogs, or temp files are physically responsible for the disk throughput you see at the OS level. A table dominating read bytes despite a supposedly warm buffer pool is a working-set-versus-pool-size conversation. Binlog files dominating write bytes on a modest workload is row-image amplification worth investigating. The total column at the intersection of a file you did not expect - a general log, a forgotten dump - has flagged more than one surprise for me. It pairs naturally with io_global_by_wait_by_latency when you care about stall time rather than volume, since a file can dominate bytes while another dominates wait time, and those point at different fixes. Bytes are a throughput conversation; latency is a storage or contention conversation. When the OS-level disk graphs spike, this pair of views is how you translate "the disk is busy" into a table name someone owns.
host_summary: which client is doing this to you
Databases rarely misbehave alone; some client made them. sys.host_summary aggregates statements, statement latency, file I/O, connections (current and total), and unique users per client host. When connection counts climb or a statement family explodes, one SELECT tells you whether the source is the API fleet, the worker pool, or someone's laptop on the VPN. On fleets behind a proxy this view degrades - every client appears as the proxy host - so know which topology you have before trusting it. The user_summary sibling slices the same story by account, which survives proxying better.
Two honorable mentions round out the daily rotation without earning their own sections. sys.innodb_lock_waits resolves row-lock blocking chains the same way schema_table_lock_waits resolves metadata locks, complete with a ready-made KILL statement for the blocker - it is the view to open when applications report lock wait timeouts. And sys.session is the humane processlist: current sessions with formatted latency, the statement being run, and the last wait, already filtered to foreground threads. On servers where I have only a SQL prompt and five minutes, sys.session plus statement_analysis is a serviceable monitoring stack.
The since-restart caveat, stated plainly
Every number above is an accumulation since the underlying performance_schema tables were last cleared - in practice, since the server restarted or someone ran a truncate on the summary tables. This has three operational consequences. First, on a server up for 200 days, statement_analysis is dominated by history and answers "what has been expensive ever" rather than "what is expensive now"; the incident-time trick is to snapshot the x$ views twice and diff. Second, after any restart there is an amnesia window where unused-index and statement conclusions are worthless. Third, two servers with different uptimes cannot be compared by raw sys numbers at all.
There is a built-in reset lever, and it deserves respect rather than casual use: CALL sys.ps_truncate_all_tables(FALSE) clears the performance_schema summary tables, giving you a clean measurement window. That is genuinely useful before a load test or when isolating a deploy's effect. It is also destructive - every view in this article forgets everything - so on shared production servers it should be a deliberate, announced act, not something an engineer runs to tidy up a dashboard.
The honest fix is external collection: sample the x$ views on an interval, store deltas, and the since-restart problem disappears along with the restart-amnesia problem. That is not a task for a shell script and a cron entry forever - it is the job description of a monitoring platform.
Where MonPG fits
MonPG is a PostgreSQL monitoring platform - today its evidence engine works on pg_stat_statements, pg_stat_activity, and their siblings rather than anything MySQL. MySQL support is being built now, and interval-sampled sys and performance_schema collection with durable history - exactly the fix for the since-restart caveat above - is central to that work. Until it ships, the honest status lives at MySQL monitoring (coming soon). If PostgreSQL is also part of your fleet, you can start there today; the daily-driver philosophy is identical, just with a different system catalog.