The sys Schema: Practical Views for Everyday MySQL Diagnostics

1. What the sys schema is and what it was built for

The sys schema has shipped as its own installed database since MySQL 5.7 and consists exclusively of views, stored procedures and functions built on top of the performance_schema and, in part, the information_schema. It stores no data of its own, instead translating existing raw data into a form that can be read and interpreted directly without deep knowledge of internal table layouts.

The real value lies in the fact that recurring diagnostic questions, such as which query patterns are most expensive or which indexes go unused, no longer need to be formulated as a fresh complex JOIN query against several Performance Schema tables every time. That logic already sits in the sys schema as a ready-made view and can be pulled with a simple SELECT.

2. Formatted views vs. their raw x$ counterparts

Almost every sys view exists in two variants: the formatted default version, for example sys.statement_analysis, and a counterpart prefixed with x$, for example sys.x$statement_analysis. The formatted version uses helper functions like format_time() and format_bytes() to convert picoseconds and byte counts into readable units such as milliseconds or megabytes.

For manual review on screen, the formatted variant is almost always the right choice. Once the values need further processing, for example for a monitoring dashboard or an automated threshold check, the x$ variant is the better pick, since it exposes the raw numeric values without formatting loss and sorts and compares reliably.

-- Readable, formatted for manual review
SELECT * FROM sys.statement_analysis LIMIT 10;

-- Raw numeric values for monitoring/automation
SELECT * FROM sys.x$statement_analysis
ORDER BY total_latency DESC LIMIT 10;

3. sys.statement_analysis: the quick entry point into expensive queries

The sys.statement_analysis view builds directly on events_statements_summary_by_digest but adds readable columns such as avg_latency, a simple yes/no full_scan flag, and a shortened, readable query preview. That removes the need to manually convert picoseconds and look up whether SUM_NO_INDEX_USED was greater than zero for a given row.

In practice, a single query against this view often replaces the first pass that would otherwise require assembling several raw Performance Schema queries. For a deeper analysis, for example the exact, untruncated digest text, a trip back to the raw table is still needed, but as an entry point for the daily routine the view is usually enough.

SELECT query, exec_count, avg_latency, full_scan, rows_sent_avg
FROM sys.statement_analysis
WHERE db = 'magento'
ORDER BY avg_latency DESC
LIMIT 15;

4. sys.statements_with_full_table_scans: hunting down missing indexes

This view specifically filters out digests where a significant share of executions ran without an index, meaning they caused a full table scan. Instead of computing the ratio of SUM_NO_INDEX_USED to COUNT_STAR yourself, the view already delivers a pre-sorted list with columns like no_index_used_count, no_good_index_used_count and the estimated share of affected executions.

For a Magento database this is the most direct way to find layered navigation filters or one-off reporting queries that accidentally run without a matching index. Checking this view before every larger release that introduces new queries often uncovers such regressions before they even show up under real load.

SELECT query, exec_count, no_index_used_count,
       no_index_used_pct, last_seen
FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_pct DESC, exec_count DESC
LIMIT 15;

5. sys.schema_unused_indexes and sys.schema_redundant_indexes: index housekeeping

sys.schema_unused_indexes lists indexes that have not been used for a single read operation since the underlying table I/O statistics were last reset. The time reference matters here: this statistic runs since the last restart or an explicit TRUNCATE of the underlying data, which is why a young server or a batch job that just started can falsely flag many indexes as unused, even though they are needed later in the monthly run.

sys.schema_redundant_indexes works differently and purely structurally: it detects indexes whose column order is a pure prefix of another, broader index, regardless of actual usage. An index on (store_id) next to an existing index on (store_id, product_id) is a typical example this view reliably catches, and one that needlessly slows down every write operation.

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'magento';

SELECT table_name, redundant_index_name,
       dominant_index_name, redundant_index_columns
FROM sys.schema_redundant_indexes
WHERE table_schema = 'magento';

6. I/O and table statistics with sys.schema_table_statistics

This view aggregates I/O wait time and access counters per table, producing a ranking of which tables actually consume the most time on read and write access. Columns like io_read_latency and io_write_latency make it visible whether a table such as sales_order_grid is mainly stressed by read access from the grid frontend or by write access during indexing.

Especially for EAV-heavy Magento tables such as catalog_product_entity_varchar or catalog_product_entity_int, this view helps put the actual I/O share into perspective before reaching for partitioning or denormalization, whose effort only pays off if the affected table really carries a relevant share of overall load.

7. sys.processlist and sys.session: diagnosing running queries live

sys.processlist and the more compact sys.session deliver noticeably more context per running connection than the classic SHOW PROCESSLIST: the current statement text, elapsed runtime in readable form, the last known wait event, and, if a transaction is active, its duration so far. In practice this replaces several manual queries against information_schema.PROCESSLIST and the related wait tables.

This is especially valuable during acute load spikes: a single look at sys.session immediately shows which connection has been open the longest, what it is currently waiting on, and whether a long-running transaction might be blocking other sessions, instead of having to separately search SHOW ENGINE INNODB STATUS for lock information.

8. Helper procedures: ps_setup_enable_thread and friends control the Performance Schema

The sys schema also ships stored procedures that wrap the Performance Schema's configuration tables, for example sys.ps_setup_enable_thread() to enable instrumentation for a single running thread, or sys.ps_setup_reset_to_default() to reset all instrument and consumer settings back to their shipped defaults.

These procedures are especially handy when, during an acute diagnosis, a single suspicious connection needs deeper instrumentation without changing the global settings for the entire server and then having to painstakingly revert them afterward.

-- Enable instrumentation for a specific thread ID
CALL sys.ps_setup_enable_thread(42);

-- Reset all Performance Schema settings back to default
CALL sys.ps_setup_reset_to_default(TRUE);

9. Practical workflow: a Magento database check in five views

A sensible routine check combines several views in sequence: first sys.statement_analysis for an overview of the most expensive patterns, then sys.statements_with_full_table_scans to find acute index gaps, followed by sys.schema_unused_indexes and sys.schema_redundant_indexes to clean up the index landscape regularly, and finally sys.schema_table_statistics to verify that the identified hotspots actually match the most I/O-heavy tables.

It matters to run this check not once but regularly, for example weekly, with a deliberate starting point for the underlying statistics. Only then can statements about unused indexes correctly account for monthly batch jobs and seasonal query patterns, instead of making hasty deletion decisions based on just a few hours of uptime.

10. Summary

11. FAQ: sys Schema for Diagnostics: The Essentials at a Glance