In this lesson, you will learn how to leverage the MySQL performance_schema to identify specific query bottlenecks that are throttling your database throughput.
While general server metrics tell you that your system is slow, the performance_schema allows you to pinpoint why by instrumenting the execution of individual statements. Throughput is often stifled by "wait events"—instances where a thread is forced to pause because it is waiting for a resource like a table lock, a disk I/O operation, or metadata synchronization.
Pinpointing Execution Bottlenecks
To monitor throughput effectively, we focus on the events_statements_summary_by_digest table. This table aggregates execution statistics for normalized query patterns, allowing you to see which query types consume the most time or trigger the most wait events.
For instance, if your throughput suddenly drops, you can query the schema to identify high-latency statements:
SELECT
digest_text,
count_star,
sum_timer_wait / 1000000000000 AS total_latency_sec,
avg_timer_wait / 1000000000 AS avg_latency_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 5;
Analyzing Wait Events
Once you identify a slow query pattern, you can cross-reference it with the events_waits_summary_by_thread or events_waits_summary_global_by_event_name tables. These tables track exactly where time is being lost during execution.
If you see high values in wait/synch/sxlock/innodb/index_tree_rw_lock, it suggests that your throughput is being bottlenecked by concurrent threads fighting for access to an InnoDB B-Tree index page. By comparing the count_star (number of times an event occurred) against the sum_timer_wait (total time spent waiting), you can calculate the "cost per execution" for specific system resources.
When you observe a discrepancy between high CPU usage and low throughput, look for wait events related to io/data/file or io/table/handler. These indicate that your threads are frequently stalling on disk reads or table access locks, which are the primary enemies of high-throughput systems. By isolating these specific wait points, you can move away from guesswork and apply surgical optimizations—like adjusting indexes or reconfiguring buffer strategies—to remove the actual blocker.
Key Takeaway: The performance_schema transforms throughput monitoring from observing system-wide averages into identifying the specific statement digests and wait events that are stalling your engine's performance.