🔬 Investigating MySQL Performance With performance_schema and AI

I’ve watched a lot of engineers reach for the slow query log when the answer was sitting in performance_schema the whole time. The instrumentation in MySQL 8.0 is genuinely deep, but the raw tables are a wall of counters that nobody wants to read by hand. That’s where two things help: the sys schema, which wraps performance_schema in human-readable views, and an AI assistant that’s good at pattern-spotting once you paste the output in. My rule is unchanged from everything else I do: the AI interprets, I verify. A digest that looks like the culprit still has to be confirmed against the real workload on a replica before I touch an index or a query.

Make Sure It’s Actually Watching

performance_schema is enabled by default in 8.0, but the consumers and instruments you care about may not be. Before drawing conclusions, confirm what’s actually being collected, otherwise your “investigation” is based on a partial picture.

-- What's enabled right now?
SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_consumers
WHERE NAME LIKE 'events_statements%';

SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME LIKE 'wait/io/file/%'
LIMIT 10;

If statement consumers or the IO instruments are off, turn them on at runtime. These changes are not persistent across restarts; for permanent settings you use performance-schema-instrument lines in my.cnf.

-- Enable statement history and IO file waits at runtime
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN ('events_statements_history', 'events_statements_history_long');

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

One caution: instrumentation has overhead. On a busy server I enable a targeted slice, capture a representative window, then turn the noisy instruments back off. I don’t leave everything on forever.

The sys Views I Actually Use

The sys schema is the friendly face of all this. Three views do most of the heavy lifting for me.

statement_analysis ranks normalized statements by total latency, rows examined, and how often they go to disk. This is usually where I start.

SELECT query, exec_count, total_latency,
       rows_sent_avg, rows_examined_avg, tmp_disk_tables
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;

statements_with_full_table_scans is the missing-index detector. A high-frequency statement here is almost always an indexing gap.

SELECT query, exec_count, no_index_used_count, rows_examined_avg
FROM sys.statements_with_full_table_scans
ORDER BY exec_count DESC
LIMIT 10;

io_global_by_file_by_bytes shows where physical IO is going by file, which surfaces hot tables, runaway temp files, and binary-log pressure.

SELECT file, total, total_read, total_written
FROM sys.io_global_by_file_by_bytes
ORDER BY total DESC
LIMIT 15;

A practical note: the sys views read latency in picoseconds under the hood but present formatted values (like 4.20 s). For sorting and math, the raw x$-prefixed views (sys.x$statement_analysis) give you the unformatted numbers. I sort on the x$ view, then read the friendly one.

Wait Events and Statement Digests

When latency isn’t explained by row counts, it’s usually a wait, IO, locks, mutexes. events_waits_summary_global_by_event_name aggregates where time goes.

SELECT event_name, count_star, sum_timer_wait
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE 'wait/io/%'
ORDER BY sum_timer_wait DESC
LIMIT 10;

Statement digests are the backbone of all of it. MySQL normalizes each query into a digest (literals replaced by ?) and aggregates stats per digest. That’s how thousands of distinct queries collapse into a handful of patterns you can reason about.

SELECT digest_text, count_star, sum_timer_wait, sum_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

Feeding It to AI

This is where the AI earns its keep. I dump the top of statement_analysis and statements_with_full_table_scans into a prompt and let it spot the pattern across the rows faster than I’d scan them by eye.

You are a senior MySQL 8.0 DBA. Below is the top of sys.statement_analysis and sys.statements_with_full_table_scans from a production replica. Identify the single worst offender by total latency relative to execution count, explain why it’s likely slow (full scan, large rows examined vs sent, temp disk tables), and propose a specific index or query change. Then list exactly what I should run to confirm your hypothesis before I change anything.

A response I’d take seriously:

The worst offender is the digest SELECT ... FROM orders WHERE LOWER(coupon_code) = ?: it shows no_index_used_count equal to exec_count and rows_examined_avg near the table’s row count while rows_sent_avg is ~1, the classic full-scan signature. The LOWER() wrapper prevents any plain index on coupon_code from being used. Proposed fix: a functional index, CREATE INDEX idx_orders_lower_coupon ON orders ((LOWER(coupon_code))). Before changing production, confirm on the replica: run EXPLAIN on the literal query and check type: ALL, then add the index on the replica and re-run EXPLAIN to confirm key = idx_orders_lower_coupon and type = ref. Also verify the digest’s traffic justifies the index given write cost.

Notice the AI ends by telling me how to disprove it. That’s the only kind of AI output I trust: a hypothesis with a verification step attached.

Verify, Don’t Believe

Here’s the part I never delegate. The AI pointed at a digest; now I confirm it on a replica before any production change.

-- 1. Reproduce the suspected plan
EXPLAIN
SELECT id FROM orders WHERE LOWER(coupon_code) = 'summer';
-- Expect type: ALL if AI is right

-- 2. Apply the proposed fix on the REPLICA only
CREATE INDEX idx_orders_lower_coupon ON orders ((LOWER(coupon_code)));
ANALYZE TABLE orders;

-- 3. Re-check the plan
EXPLAIN
SELECT id FROM orders WHERE LOWER(coupon_code) = 'summer';
-- Expect key: idx_orders_lower_coupon, type: ref

I also sanity-check that the digest’s volume is real and current, not a one-off batch job that skewed the aggregates, by truncating the digest table, letting a representative window accumulate, and re-reading.

-- Reset digest stats to capture a clean window
CALL sys.ps_truncate_all_tables(FALSE);

# Capture a representative window from a replica, off the hot path
mysql --host=replica.internal mydb \
  -e "SELECT digest_text, count_star, sum_timer_wait
      FROM performance_schema.events_statements_summary_by_digest
      ORDER BY sum_timer_wait DESC LIMIT 10\G"

The Loop

The pattern is the same every investigation:

1. Confirm the right consumers and instruments are enabled.
2. Pull statement_analysis, statements_with_full_table_scans, io_global_by_file_by_bytes, and the top digests.
3. Hand the output to the AI to rank offenders and propose fixes with confirmation steps.
4. Reproduce the plan on a replica, apply the proposed change there, and re-check EXPLAIN.
5. Validate that the digest’s traffic is representative before shipping.
6. Promote the fix to production deliberately, watching write cost.

performance_schema tells you what is actually happening rather than what you assume. The AI compresses a wall of counters into a short list of suspects with a test attached. The replica is where suspicion becomes fact. Skip that last step and you’ll eventually add an index for a query that runs twice a day while the real fire keeps burning.

More of these are under the MySQL guides, and the diagnostic prompts, the one above included, live in the prompt library. The functional-index fix this guide keeps landing on is covered end to end in generated columns and functional indexes.