Posted inDatabase

MySQL Performance Schema: Diagnose Slow Queries with Internal Statistics Tables

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The Performance Schema is MySQL’s built-in observability layer — a set of over 80 tables that record everything happening inside your instance, from query execution times to mutex waits. After six months of using it daily in production, I stopped reaching for external profiling tools for most debugging tasks.

Here’s what actually matters.

Quick Start: Get Running in 5 Minutes

First, check if Performance Schema is enabled:

SHOW VARIABLES LIKE 'performance_schema';

If it returns OFF, enable it in my.cnf:

[mysqld]
performance_schema = ON

Restart MySQL, then verify:

USE performance_schema;
SHOW TABLES;

You’ll see 80+ tables. The ones you’ll use 90% of the time are:

  • events_statements_summary_by_digest — aggregated query stats
  • events_waits_summary_global_by_event_name — what MySQL is waiting on
  • table_io_waits_summary_by_table — per-table I/O stats
  • file_summary_by_event_name — file I/O breakdown

For a quick win, run this to find your top 5 slowest query patterns right now:

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
  ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;

That query alone has saved me hours of log-digging on multiple projects.

Deep Dive: Understanding What the Numbers Mean

The Statement Digest Table

The events_statements_summary_by_digest table normalizes queries — replacing literal values with ? — so WHERE id = 1 and WHERE id = 42 become the same digest. This is crucial for spotting patterns across thousands of executions.

Key columns to pull together:

SELECT
  SCHEMA_NAME,
  DIGEST_TEXT,
  COUNT_STAR,
  ROUND(MIN_TIMER_WAIT / 1e9, 2) AS min_ms,
  ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms,
  ROUND(MAX_TIMER_WAIT / 1e9, 2) AS max_ms,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
  SUM_NO_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'your_db'
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

SUM_NO_INDEX_USED is a dead giveaway — if that number is high for a query running thousands of times per hour, you’ve found a missing index. The ratio SUM_ROWS_EXAMINED / SUM_ROWS_SENT tells you query efficiency. A ratio of 1000:1 means MySQL scanned 1000 rows to return 1 — almost always an indexing problem.

Wait Events: Finding Hidden Bottlenecks

Having worked with MySQL, PostgreSQL, and MongoDB across different projects, each has its own observability model. MySQL’s wait event system is something I wish I’d learned earlier — it reveals bottlenecks that slow query logs never surface.

SELECT
  EVENT_NAME,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec,
  ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_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 15;

What to look for:

  • wait/io/file/innodb/innodb_data_file — disk I/O is your bottleneck; consider more RAM or faster storage
  • wait/synch/mutex/innodb/buf_pool_mutex — buffer pool contention; likely need a larger innodb_buffer_pool_size
  • wait/lock/table/sql/handler — table lock contention; check for missing transactions or lock conflicts

Table I/O Analysis

SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  COUNT_READ,
  COUNT_WRITE,
  ROUND(SUM_TIMER_READ / 1e9, 2) AS read_ms,
  ROUND(SUM_TIMER_WRITE / 1e9, 2) AS write_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'your_db'
ORDER BY SUM_TIMER_READ + SUM_TIMER_WRITE DESC
LIMIT 10;

This pinpoints which tables are the hottest. Running this on a client’s system once, I found a logging table accumulating millions of rows with no archiving strategy — it was adding 400ms to every request that joined against it.

Advanced Usage: Instrumenting Specific Queries

Enabling Statement History

By default, MySQL only keeps aggregates. To debug a specific session, enable per-execution history:

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

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

Then run your problematic query and inspect the history:

SELECT
  SQL_TEXT,
  TIMER_WAIT / 1e9 AS duration_ms,
  ROWS_EXAMINED,
  ROWS_SENT,
  CREATED_TMP_DISK_TABLES,
  CREATED_TMP_TABLES,
  SELECT_FULL_JOIN,
  NO_INDEX_USED
FROM performance_schema.events_statements_history_long
ORDER BY TIMER_WAIT DESC
LIMIT 20;

CREATED_TMP_DISK_TABLES being non-zero means MySQL spilled a temp table to disk — adjust tmp_table_size and max_heap_table_size.

Using the sys Schema (MySQL 5.7.7+)

The sys schema wraps Performance Schema into human-readable views:

-- Top queries by total execution time
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- Queries not using indexes
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY total_latency DESC LIMIT 10;

-- Table stats at a glance
SELECT * FROM sys.schema_table_statistics
WHERE table_schema = 'your_db'
ORDER BY total_latency DESC;

I set up a weekly cron job that dumps sys.statements_with_runtimes_in_95th_percentile to a file. Trend analysis over weeks catches regressions that point-in-time monitoring misses.

User-Level Breakdown

When multiple applications share a MySQL instance, this shows who’s responsible for the load:

SELECT * FROM sys.user_summary ORDER BY total_latency DESC;

Practical Tips

Reset stats between load tests. Stats accumulate since server start, which skews benchmarks. Before testing a change:

TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

Don’t leave statement history on permanently. The events_statements_history_long table has a fixed row limit (default 10,000) and adds overhead. Enable it for debugging sessions, then turn it off:

UPDATE performance_schema.setup_consumers
SET ENABLED = 'NO'
WHERE NAME = 'events_statements_history_long';

Correlate with EXPLAIN. Performance Schema tells you what’s slow, EXPLAIN tells you why. Grab a real query from the slow query log and run EXPLAIN ANALYZE on it after you’ve identified it via digest.

Build a baseline capture script. A simple Python job querying Performance Schema every few minutes and writing to a time-series store gives you trend data:

import mysql.connector

def capture_top_queries(conn, limit=20):
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT
            DIGEST,
            DIGEST_TEXT,
            COUNT_STAR,
            ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms,
            ROUND(SUM_TIMER_WAIT / 1e9, 2) AS total_ms,
            SUM_NO_INDEX_USED AS no_index_count
        FROM performance_schema.events_statements_summary_by_digest
        WHERE SCHEMA_NAME IS NOT NULL
        ORDER BY SUM_TIMER_WAIT DESC
        LIMIT %s
    """, (limit,))
    return cursor.fetchall()

Watching how avg_ms drifts after a deploy has caught regressions before users noticed on more than one occasion.

Watch for position changes in the top query list. If the query at rank #1 changes between monitoring intervals, something shifted — either load pattern changed or a new query deployed. Treat it as a signal worth investigating immediately rather than waiting for a user complaint.

Performance Schema isn’t glamorous, but it’s the most reliable signal I’ve found for MySQL internals. The data is already being collected — most teams just don’t know how to read it.

Share:
Tags: