MySQL Performance Schema vs sys schema:2026年モニタリングガイド

MySQL has two monitoring schemas and most engineers have a vague sense of which to use. The short answer: performance_schema is the source of truth, sys is the friendly view layer on top. Use sys when you want to read; use performance_schema when you need exact numbers or you’re building a tool.

This is the 2026 guide — what’s on by default, what to enable, the eight queries to know.

Why MySQL has both

performance_schema shipped in 5.5 (2010) as a low-level instrumentation framework — thousands of mutex/lock/IO/statement counters, all exposed as tables. It’s comprehensive and unreadable.

sys shipped in 5.7 (2015) as a set of views and stored procedures that translate performance_schema into queries a human can run. It’s opinionated.

Performance Schema, briefly

The model is three layers:

• Instruments — what to measure (locks, statements, stages, IO, …)
• Consumers — what to record (events, summaries, history)
• Tables — where to read it from

You enable specific instruments and consumers; the tables are populated automatically.

-- enable statement instruments and the digest summary consumer
UPDATE performance_schema.setup_instruments
   SET enabled='YES', timed='YES'
 WHERE name LIKE 'statement/%';

UPDATE performance_schema.setup_consumers
   SET enabled='YES'
 WHERE name LIKE 'events_statements%';

On MySQL 8.0+ most of this is on by default. On 5.7, you have to be explicit about which instruments to enable.

sys schema, briefly

Everything in sys is a view, function, or procedure over performance_schema. The naming convention is consistent and worth memorizing:

• sys.statement_analysis — top queries by total time
• sys.statements_with_runtimes_in_95th_percentile — slow tail
• sys.schema_index_statistics — index usage
• sys.schema_unused_indexes — drop candidates
• sys.io_global_by_file_by_bytes — hot files
• sys.innodb_lock_waits — current lock waits with both sides
• sys.processlist — better than SHOW PROCESSLIST

8 queries every MySQL DBA should know

1. Top queries by total time

SELECT digest_text, count_star, total_latency, avg_latency, rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 20;

2. The actual normalized statement (digest) view

SELECT DIGEST, DIGEST_TEXT, COUNT_STAR,
       SUM_TIMER_WAIT/1e12 AS total_seconds,
       AVG_TIMER_WAIT/1e9  AS avg_ms,
       SUM_ROWS_SENT, SUM_ROWS_EXAMINED, SUM_ROWS_AFFECTED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

3. Currently running expensive statements

SELECT THREAD_ID, EVENT_NAME, CURRENT_SCHEMA, SQL_TEXT,
       TIMER_WAIT/1e9 AS wait_ms, ROWS_EXAMINED
FROM performance_schema.events_statements_current
WHERE TIMER_WAIT/1e9 > 1000   -- > 1 second
ORDER BY TIMER_WAIT DESC;

4. Index usage and unused indexes

SELECT table_schema, table_name, index_name,
       rows_selected, rows_inserted, rows_updated, rows_deleted
FROM sys.schema_index_statistics
WHERE rows_selected = 0
ORDER BY rows_inserted DESC;

5. InnoDB lock waits, both sides

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

6. Slow tablescans

SELECT object_schema, object_name,
       count_read, count_fetch, sum_timer_wait/1e9 AS ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE count_fetch > 1000
ORDER BY sum_timer_wait DESC
LIMIT 20;

7. Connection pressure

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN
  ('Threads_connected','Threads_running','Max_used_connections',
   'Aborted_connects','Connection_errors_max_connections');

8. InnoDB buffer-pool hit ratio

SELECT
  ROUND((1 - reads/requests) * 100, 4) AS hit_pct
FROM (
  SELECT
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') AS reads,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests') AS requests
) AS s;

Production tuning — keep overhead under 5%

• Keep statement digests on; turn off events_waits_history_long in production.
• Bump performance_schema_digests_size on busy DBs (default 200 evicts hot signatures fast).
• Use the sys.statement_truncate_digest setting if your app generates long literal-heavy queries.
• Memory: performance_schema reserves a fixed allocation. Check SHOW STATUS LIKE 'Performance_schema_%lost%' — anything > 0 means buffers are evicting silently.

5.7 vs 8.0 vs 8.4 — what’s different

FAQ