MySQL Performance Schema: Query Profiling and Wait Event Analysis

Home » MySQL » MySQL Performance Schema: Query Profiling and Wait Event Analysis
April 25, 2026 MySQL
MySQL Performance Schema: Query Profiling and Wait Event Analysis

The MySQL Performance Schema (P_S) is an instrumentation framework built into the server that records query execution times, lock waits, I/O waits, and memory usage at microsecond granularity — without the overhead of general query logging. For WordPress performance tuning, P_S provides the ground truth for questions like “which query spends the most time waiting on locks?” and “which table has the most row-level lock contention?”

Problem: A WordPress production database has intermittent slow queries but the slow log and EXPLAIN output do not reveal the root cause — the bottleneck is in wait events (lock waits, I/O waits, memory allocation) rather than query execution logic.

Solution: Enable the MySQL Performance Schema and query the events_statements_summary_by_digest table to see aggregated query statistics. Use events_waits_summary_global_by_event_name to identify the most time-consuming wait events across the server. Correlate specific slow queries with their wait events using the Statement Digest table and the events_stages_history_long table.


The queries below enable the most useful P_S consumers, find the top queries by total wait time, identify the tables with the most lock contention, profile a single query execution, and show how to reset P_S counters between test runs.


-- ── 1. Enable the key consumers (add to my.cnf for persistence) ──────────
UPDATE performance_schema.setup_consumers
   SET enabled = 'YES'
 WHERE name IN (
    'events_statements_history_long',
    'events_waits_history_long',
    'events_stages_history_long'
);

UPDATE performance_schema.setup_instruments
   SET enabled = 'YES', timed = 'YES'
 WHERE name LIKE 'statement/%'
    OR name LIKE 'wait/io/table/%'
    OR name LIKE 'wait/lock/%';

-- ── 2. Top 20 queries by total execution time (slowest overall) ───────────
SELECT
    DIGEST_TEXT                                   AS query_pattern,
    COUNT_STAR                                    AS executions,
    ROUND( SUM_TIMER_WAIT / 1e12, 3 )            AS total_sec,
    ROUND( AVG_TIMER_WAIT / 1e9,  3 )            AS avg_ms,
    ROUND( MAX_TIMER_WAIT / 1e9,  3 )            AS max_ms,
    SUM_ROWS_EXAMINED                             AS rows_examined,
    SUM_NO_INDEX_USED                             AS no_index_used
FROM   performance_schema.events_statements_summary_by_digest
ORDER  BY SUM_TIMER_WAIT DESC
LIMIT  20;

-- ── 3. Tables with the most lock wait time (lock contention hotspots) ─────
SELECT
    OBJECT_SCHEMA                             AS db,
    OBJECT_NAME                               AS tbl,
    COUNT_READ_WITH_SHARED_LOCKS              AS shared_locks,
    COUNT_WRITE                               AS writes,
    ROUND( SUM_TIMER_WAIT / 1e12, 3 )        AS total_lock_sec
FROM   performance_schema.table_lock_waits_summary_by_table
WHERE  OBJECT_SCHEMA = 'helloadmin'
ORDER  BY SUM_TIMER_WAIT DESC
LIMIT  10;

-- ── 4. I/O wait by table (which tables are causing disk reads) ────────────
SELECT
    OBJECT_NAME                               AS tbl,
    COUNT_READ                                AS reads,
    COUNT_WRITE                               AS writes,
    ROUND( SUM_TIMER_READ  / 1e12, 3 )       AS read_sec,
    ROUND( SUM_TIMER_WRITE / 1e12, 3 )       AS write_sec
FROM   performance_schema.table_io_waits_summary_by_table
WHERE  OBJECT_SCHEMA = 'helloadmin'
ORDER  BY ( SUM_TIMER_READ + SUM_TIMER_WRITE ) DESC
LIMIT  10;

-- ── 5. Profile a single session: see per-stage breakdown ─────────────────
-- Step 1: In your session, run your query
-- Step 2: Find the EVENT_ID of the last statement
SELECT EVENT_ID, SQL_TEXT, TIMER_WAIT/1e9 AS ms
FROM   performance_schema.events_statements_history
ORDER  BY EVENT_ID DESC LIMIT 5;

-- Step 3: Show per-stage timing for that EVENT_ID
SELECT EVENT_NAME, TIMER_WAIT/1e9 AS stage_ms
FROM   performance_schema.events_stages_history_long
WHERE  NESTING_EVENT_ID = 
ORDER  BY EVENT_ID;

-- ── 6. Reset counters between test runs ──────────────────────────────────
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;


NOTE: Performance Schema instruments add 5–10% CPU overhead when all consumers are enabled — on production servers, enable only the consumers you actively need (events_statements_summary_by_digest is low-overhead and always useful) and disable events_waits_history_long and events_stages_history_long unless you are actively profiling a specific problem.

Related posts