MENU


Performance Schema and Sys Schema

MySQL exposes two complementary instrumentation layers for finding what's actually slow: performance_schema, a low-level event-collection engine, and sys, a set of views and procedures built on top of it in human-readable form. A third, older mechanism – the slow query log – still has a place for offline, tool-driven analysis.

Enabling and Using performance_schema

performance_schema is enabled by default since MySQL 5.6.6 (performance_schema = ON in my.cnf; it's a startup-only variable, not changeable at runtime). It works by instrumenting server internals – statements, waits, stages, memory, table/index I/O – and recording aggregated and (for a rolling window) per-event data in tables under the performance_schema database. Individual instruments and their consumers (which tables actually get populated) are controlled via the setup_instruments and setup_consumers tables, most of which are enabled by default in modern MySQL:

UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements_%';

Ensures statement-level event consumers are recording – needed for the digest tables the sys schema queries below build on.


The most directly useful raw table is events_statements_summary_by_digest, which aggregates statements by their normalized "digest" (literals stripped) with count, total/average/max latency, rows examined, and whether a temporary table or filesort was used – but the sys schema wraps this into a much more approachable form.

The sys Schema

The sys schema (bundled by default since MySQL 5.7) provides friendly views and stored procedures over performance_schema and information_schema, with human units (milliseconds, formatted bytes) instead of raw picoseconds and byte counts. Two of the most useful views for performance work:

SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

sys.statement_analysis aggregates by normalized query, sorted here by total accumulated latency – the queries costing the workload the most time overall, not just the individually slowest ones.


SELECT * FROM sys.schema_unused_indexes;

Lists indexes with zero recorded read activity since the relevant statistics were last reset (typically server start, or since TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage;) – candidates for dropping, but confirm against a long enough observation window that infrequent-but-real usage (month-end reports, etc.) isn't missed.


Related views worth knowing: sys.schema_redundant_indexes (indexes made redundant by a broader composite index – see Index Strategy), sys.schema_tables_with_full_table_scans, and sys.io_global_by_file_by_bytes for I/O hotspots at the file level.

The Slow Query Log

The slow query log records individual statements that exceed a time threshold to a file, independent of performance_schema – useful when instrumentation was off during an incident, or for feeding an external analysis tool.

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log'; SET GLOBAL log_queries_not_using_indexes = 'ON';

long_query_time is in seconds (fractional values allowed, e.g. 0.5); statements taking at least that long are logged. log_queries_not_using_indexes additionally logs any statement that used no index at all, regardless of duration – useful for surfacing full scans on small tables that run "fast enough" individually but add up under load.


pt-query-digest

Percona Toolkit's pt-query-digest parses a slow query log (or general log, or tcpdump capture) offline and produces a ranked report of query patterns by total time, similar in spirit to sys.statement_analysis but usable against logs pulled from a server without direct performance_schema access, and with richer historical/comparison options:

pt-query-digest /var/log/mysql/slow.log > digest-report.txt

Produces a text report ranking normalized query patterns by total execution time, with per-pattern min/max/average latency and example queries.


Once a costly or unindexed query is identified here, confirm its plan with EXPLAIN and Execution Plans before deciding on an index or rewrite. 

-- Ensure statement-level instrumentation is recording
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_statements_%';

-- Top 10 queries by total accumulated latency
SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

-- Indexes with no recorded reads -- drop candidates
SELECT * FROM sys.schema_unused_indexes;

-- Indexes made redundant by a wider composite index
SELECT * FROM sys.schema_redundant_indexes;

-- Enable and configure the slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';