Introduction to MySQL performance_schema and the sys schema: find top queries, waits and unused indexes

#What it is

• performance_schema is a built-in engine that records what the server is doing: statements, waits, I/O, locks, memory. It's on by default in MySQL 8.
• sys is a schema of friendly views on top of performance_schema, so you don't have to write complex joins.

Together they answer "what is slow, and why" without installing anything.

#Step 1: Check it's enabled

SHOW VARIABLES LIKE 'performance_schema';     -- must be ON (needs restart to change)
SELECT * FROM sys.version;                    -- sys schema present?

Statement and wait instrumentation is mostly on by default. Check consumers:

SELECT name, enabled FROM performance_schema.setup_consumers;

events_statements_history and statements_digest should be YES. On managed services (RDS, Azure), performance_schema is a server parameter; on some tiers it is OFF by default. Turn it on in the parameter group / server parameters and restart.

Tip

Statistics are cumulative since server start (or last reset). Note the uptime (SHOW GLOBAL STATUS LIKE 'Uptime') so you know what period you're looking at.

#Step 2: Top statements by total time

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

Or the full digest list:

SELECT  query, db, exec_count, total_latency, avg_latency, lock_latency,
        rows_examined, rows_sent, full_scan, last_seen
FROM    sys.statement_analysis
ORDER   BY total_latency DESC
LIMIT   20;

Queries are normalised (literals replaced by ?), so one line represents all executions of the same shape. Look for:

• High exec_count × modest avg_latency → chatty app; caching or batching.
• High rows_examined vs low rows_sent → missing or unused index.
• High lock_latency → contention.

#Step 3: Statements doing full table scans

SELECT query, db, exec_count, total_latency, no_index_used_count, rows_examined
FROM   sys.statements_with_full_table_scans
ORDER  BY no_index_used_count DESC
LIMIT  10;

Take one, run EXPLAIN on a real example, and check the type column (ALL = full scan) and key (index used):

EXPLAIN FORMAT=TREE
SELECT * FROM orders WHERE customer_email = 'a@example.com';

#Step 4: Tables with the most I/O

SELECT table_schema, table_name, rows_fetched, fetch_latency,
       rows_inserted, rows_updated, rows_deleted, io_read, io_write
FROM   sys.schema_table_statistics
ORDER  BY fetch_latency DESC
LIMIT  10;

#Step 5: Unused and redundant indexes

-- Indexes not used since startup (be careful: monthly jobs may need them)
SELECT * FROM sys.schema_unused_indexes
WHERE  object_schema NOT IN ('mysql','sys','performance_schema');

-- Indexes made redundant by another index
SELECT table_schema, table_name, redundant_index_name, dominant_index_name, sql_drop_index
FROM   sys.schema_redundant_indexes;

Warning

"Unused since startup" only covers the time since the last restart. Make an index invisible first (ALTER TABLE t ALTER INDEX idx INVISIBLE;), watch for a full business cycle, then drop it. Making it visible again is instant.

#Step 6: What is the server waiting on?

SELECT event_class, total, total_latency, avg_latency
FROM   sys.wait_classes_global_by_latency
LIMIT  10;

SELECT events, total, total_latency
FROM   sys.waits_global_by_latency
LIMIT  10;

Typical readings:

#Step 7: What's running right now

SELECT thd_id, conn_id, user, db, command, state, time, current_statement, lock_latency, trx_state
FROM   sys.session
WHERE  command <> 'Sleep'
ORDER  BY time DESC;

And who is blocking whom (InnoDB row locks):

SELECT wait_age, locked_table, waiting_pid, waiting_query, blocking_pid, blocking_query, sql_kill_blocking_connection
FROM   sys.innodb_lock_waits;

sql_kill_blocking_connection gives you the KILL statement, but confirm what the blocker is before using it.

#Step 8: Reset counters for a clean measurement

To measure a specific test window:

CALL sys.ps_truncate_all_tables(FALSE);
-- run the workload
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

Resetting on production removes history other people may be using; tell the team first.

#Memory note

performance_schema uses memory that is allocated as needed. Check its usage:

SELECT * FROM sys.memory_global_by_current_bytes
WHERE  event_name LIKE 'memory/performance_schema/%'
ORDER  BY current_alloc DESC LIMIT 5;

On very small instances (1-2 GB RAM) that can matter; on most servers it's a small fraction.

#Prevent problems later

• Keep performance_schema on everywhere, including managed services.
• Snapshot sys.statement_analysis daily into a table (or your monitoring tool) so you can compare before and after releases.
• Review full-scan and redundant index reports monthly.
• Combine with the slow query log for exact parameter values when a digest isn't enough.