MySQL Performance Schema: A Practical Guide

Performance Schema has been part of MySQL since 5.5, yet most DBAs still reach for SHOW PROCESSLIST and EXPLAIN as their first debugging instincts. Those tools show you what is happening right now — Performance Schema shows you what has been happening across thousands of executions, which queries consume the most cumulative time, where threads are waiting, and exactly how much memory each subsystem is holding. If you are tuning a production MySQL instance without consulting Performance Schema, you are optimizing based on intuition rather than data.

What Is MySQL Performance Schema?

Performance Schema is a storage engine built directly into the MySQL server that exposes internal execution metrics through a set of in-memory tables accessible via the performance_schema database. Unlike INFORMATION_SCHEMA, which describes the structure of your data, Performance Schema describes the behavior of your server — every instrumented wait event, every statement execution, every mutex acquisition, every byte allocated.

The design principle is low-overhead observation: instruments are tight inline probes woven into the server source code. When an instrument fires, it writes to lock-free ring buffers rather than locking shared structures, keeping instrumentation cost minimal for most workloads. On a busy OLTP server, total overhead is typically less than 5% of CPU, and on read-heavy workloads it is often undetectable.

Performance Schema data is ephemeral. It lives in memory and resets on server restart. It is not a replacement for a time-series monitoring system — it is a real-time and recent-history diagnostic tool. Pair it with Prometheus and Grafana for long-term trend data; use Performance Schema when you need to understand exactly what is happening at the statement and wait level right now.

How Performance Schema Works

Three concepts govern everything in Performance Schema: instruments, consumers, and tables.

Instruments are individual measurement points inside the server source. They follow a hierarchical naming convention: statement/sql/select, wait/io/file/innodb/innodb_data_file, memory/innodb/buf_buf_pool. Instruments are registered in setup_instruments and can be toggled on or off individually.

Consumers determine which destination tables receive data from enabled instruments. If an instrument is enabled but its consumer is disabled, no data flows to the table. Consumers are configured in setup_consumers and also follow a hierarchy — enabling a child consumer requires its parent consumer to be enabled.

Tables are the in-memory destinations: summary tables aggregate by digest or object, history tables retain the N most recent events per thread, and current tables show only the active event for each thread.

Enabling and Configuring Performance Schema

Verify that Performance Schema is active:

SHOW VARIABLES LIKE 'performance_schema';
-- Expected: Value = ON

If it is OFF, enable it in my.cnf and restart — it cannot be enabled at runtime:

[mysqld]
performance_schema = ON

Controlling Instruments

By default, MySQL 8.0 enables a useful subset of instruments. Enable all statement and wait instruments for a full picture:

-- Enable all statement instruments
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

-- Enable all wait instruments
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/%';

-- Enable memory instruments
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME LIKE 'memory/%';

Controlling Consumers

-- Enable the consumers needed for statement digest analysis
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN (
    'events_statements_current',
    'events_statements_history',
    'events_statements_history_long',
    'events_waits_current',
    'events_waits_history_long',
    'statements_digest'
);

Performance Tuning with Performance Schema

Finding Your Most Expensive Queries

The events_statements_summary_by_digest table aggregates every normalized query across all executions since the last reset. This is the single most useful table for identifying tuning opportunities:

SELECT
    SCHEMA_NAME                                    AS db,
    DIGEST_TEXT                                    AS query,
    COUNT_STAR                                     AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1e12, 3)               AS total_latency_sec,
    ROUND(AVG_TIMER_WAIT / 1e9, 3)                AS avg_latency_ms,
    SUM_ROWS_EXAMINED                              AS rows_examined,
    SUM_ROWS_SENT                                  AS rows_sent,
    ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0)       AS avg_rows_examined,
    SUM_NO_INDEX_USED                              AS no_index_used,
    SUM_NO_GOOD_INDEX_USED                         AS no_good_index
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Key columns to act on:

• avg_rows_examined much higher than SUM_ROWS_SENT / COUNT_STAR indicates full scans or poorly selective indexes
• SUM_NO_INDEX_USED > 0 means executions that touched no index at all — immediate candidates for EXPLAIN
• SUM_TIMER_WAIT in aggregate reveals which queries consume the most cumulative server time, not just which are individually slowest

Diagnosing Wait Events

When queries are slow but EXPLAIN shows a good execution plan, the bottleneck is often a wait — on I/O, on a lock, on a mutex. Query the wait summary tables:

-- Top wait events by total time
SELECT
    EVENT_NAME,
    COUNT_STAR                            AS wait_count,
    ROUND(SUM_TIMER_WAIT / 1e12, 3)      AS total_wait_sec,
    ROUND(AVG_TIMER_WAIT / 1e9, 3)       AS avg_wait_ms,
    ROUND(MAX_TIMER_WAIT / 1e9, 3)       AS max_wait_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
  AND EVENT_NAME NOT LIKE 'idle%'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

High values for wait/io/file/innodb/innodb_data_file point to storage I/O pressure — consider increasing innodb_buffer_pool_size or moving to faster storage. High values for wait/synch/mutex/innodb/trx_mutex indicate transaction contention, often from long-running transactions or hot rows.

Memory Profiling

Before adjusting buffer sizes, verify where memory is actually going:

-- Memory consumption by subsystem
SELECT
    EVENT_NAME,
    CURRENT_COUNT_USED                              AS allocations,
    ROUND(CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024, 2) AS current_mb,
    ROUND(HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024, 2)    AS peak_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 25;

Using the sys Schema for Faster Investigation

The sys schema, bundled with MySQL 5.7.7+ and 8.0, provides pre-built views over Performance Schema tables with human-readable formatting. It dramatically accelerates ad-hoc investigation.

Top statements by total latency:

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

I/O wait by file type:

SELECT
    event_name,
    total,
    total_latency,
    avg_latency,
    max_latency,
    read_latency,
    write_latency
FROM sys.io_global_by_wait_by_latency
LIMIT 20;

Sessions with current wait information:

SELECT
    thd_id,
    conn_id,
    user,
    db,
    command,
    state,
    time,
    current_statement,
    last_wait,
    last_wait_latency
FROM sys.session
WHERE command != 'Sleep'
ORDER BY time DESC;

Identifying Lock Contention

-- Metadata lock waits (MySQL 8.0+)
SELECT
    OBJECT_TYPE,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    LOCK_TYPE,
    LOCK_STATUS,
    OWNER_THREAD_ID,
    OWNER_EVENT_ID
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING'
ORDER BY OBJECT_SCHEMA, OBJECT_NAME;

-- InnoDB row lock waits via sys schema
SELECT
    wait_started,
    wait_age,
    locked_table,
    locked_type,
    waiting_query,
    blocking_query
FROM sys.innodb_lock_waits;

Resetting Statistics for a Clean Baseline

# Reset all statement digests
mysql -u root -p -e "TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;"

# Reset wait event summaries
mysql -u root -p -e "TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;"

Key Takeaways

Working with JusDB on MySQL Performance Tuning

Navigating Performance Schema effectively requires knowing which tables to query, which instruments to trust, and how to correlate wait events with application symptoms — knowledge that only comes from tuning many production MySQL deployments. At JusDB, we work with engineering teams to instrument their MySQL instances correctly from the start, build repeatable profiling workflows around Performance Schema and the sys schema, and translate raw wait and digest data into actionable index changes, configuration adjustments, and schema improvements.

Whether you are dealing with a sudden latency spike, a memory allocation mystery, or chronic slow queries that survive EXPLAIN scrutiny, the answers are almost always in Performance Schema if you know where to look. If your team needs a structured approach to MySQL performance analysis — or a second set of eyes on data you have already collected — reach out to JusDB. We can help you move from guesswork to instrumented, evidence-based tuning.