Using the MySQL Performance Schema for Deep Query Analysis

1. How the Performance Schema instruments MySQL

The performance_schema database is populated at runtime by measurement points built directly into the server code, not by an external profiler attached from outside. Each of these measurement points is called an instrument and covers a clearly scoped activity: a statement, a wait on a mutex, a file access, or a memory allocation. Instruments are named hierarchically, for example statement/sql/select or wait/synch/mutex/innodb/buf_pool_mutex, so whole groups can be enabled or disabled through wildcards.

Separate from instruments, consumers control whether the measured raw data actually gets written into a table at all. An enabled instrument without an enabled consumer still generates an event, but that event is never stored and costs almost nothing. This split lets measurement and storage be dialed independently, instead of either recording everything or nothing at all.

2. Configuring instruments, consumers and actors deliberately

The setup_instruments and setup_consumers tables control both levels through simple updates. In MySQL 8.0, statement instruments and their digest summary are active by default, while many wait and stage instruments stay deliberately disabled to keep the baseline load low. Anyone who needs a deeper diagnosis enables the relevant groups for the duration of the investigation and switches them off again afterward.

On top of that, setup_actors defines which users and hosts get instrumented at all. In a Magento setup with several database users, for example a separate application user and a reporting user, filtering deliberately reduces the volume of generated events noticeably without losing sight of the connections that actually matter.

-- Statement digest stays enabled (default), only enable wait
-- instruments for the duration of a diagnostic session
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/synch/mutex/innodb/%';

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

-- Only instrument the storefront application user
UPDATE performance_schema.setup_actors
SET ENABLED = 'YES', HISTORY = 'YES'
WHERE HOST = '%' AND USER = 'magento_app';

3. The digest model behind events_statements_summary_by_digest

The digest mechanism normalizes every executed statement by replacing literals with placeholders and unifying whitespace. Two queries that differ only in the concrete product ID or store code end up under the same DIGEST hash and the same DIGEST_TEXT. That is exactly what an ORM-heavy application like Magento needs, since it executes the same logical query in countless parameter variants without each one showing up as a separate entry.

The events_statements_summary_by_digest table aggregates, per digest, columns such as COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, SUM_NO_INDEX_USED and SUM_SELECT_FULL_JOIN. From these columns you can tell whether a query pattern is expensive because it runs often, because a single execution takes a long time, or because it scans far more rows than it ever needs to return.

4. Identifying expensive query patterns with a targeted query

Day to day diagnosis needs just one query sorted by aggregated execution time. The TIMER columns are stored in picoseconds and need converting for readable values. It matters to keep an eye on COUNT_STAR as well, not just the raw sum: a pattern with moderate per-execution cost that runs hundreds of thousands of times an hour often adds up to more total server load than a single, rare reporting query.

A second, often more revealing angle is the ratio of SUM_ROWS_EXAMINED to SUM_ROWS_SENT. A wide gap between the two, combined with SUM_NO_INDEX_USED greater than zero, almost always points to a missing or poorly designed index, which can then be confirmed with EXPLAIN against exactly that digest text.

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
  SUM_NO_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

5. Wait events: exposing contention at the mutex and I/O level

While statement digests show which query is expensive, wait events show why it is waiting. Instruments such as wait/synch/mutex/innodb/buf_pool_mutex or wait/io/file/innodb/innodb_data_file measure internal wait time on locks, mutexes and file access, aggregated in events_waits_summary_global_by_event_name. Only this level explains whether a slow query is genuinely a missed optimization or contention on a shared internal structure that many threads are stuck behind at the same time.

Wait instruments are far more numerous and fine grained than statement instruments, which is why they stay disabled by default. Enabling them for a bounded diagnostic window and disabling them again afterward delivers the necessary depth without turning the instrumentation itself into a permanent cost on a heavily loaded production system.

6. Stage events: tracing a statement's lifecycle

Stage events take over the role of the now removed SHOW PROFILE and show which phase a statement spends its time in, for example starting, checking permissions, Sending data or closing tables. The events_stages_history_long table keeps these phases per thread and makes visible whether the measured runtime actually sits in server execution or mostly in shipping a large result set to the client.

For Magento this matters most for export and reporting queries that return large data volumes. If the stage analysis shows most of the time spent in Sending data, a faster network link or a leaner result set helps more than yet another index optimization that misses the real cause.

7. Weighing the overhead full instrumentation costs in production

Plain statement digest instrumentation is enabled by default in MySQL 8.0 and, thanks to a largely lock-free design, adds single-digit percent CPU overhead, which is acceptable for practically any Magento store. Things look different once events_statements_history_long plus fine grained wait and stage instruments are all enabled at once: at very high query rates the per-event overhead adds up noticeably and can become measurable on systems that are already CPU bound.

The pragmatic recommendation is to leave digest aggregation running permanently but enable wait and stage instrumentation only for bounded diagnostic windows. Anyone who needs deep instrumentation regularly should measure the actual extra load on a staging system under realistic traffic instead of relying on generic percentage figures from the documentation, since the real effect depends heavily on hardware and query mix.

8. Sizing, persistence and handling a digest reset correctly

The digest table is capped in size via performance_schema_digest_size. Once it is full, new, previously unseen digests get bundled into a generic overflow entry, losing exactly the granularity a clean analysis relies on. On a store with many distinct query shapes, for example from dynamic layered navigation filters, a deliberately higher value pays off compared to the server's automatic default sizing.

All Performance Schema data lives purely in memory and does not survive a restart. For a clean before-and-after comparison, for example around a deployment or a load test, an explicit TRUNCATE TABLE on the relevant summary tables right before the run keeps old measurements from diluting the new evaluation.

-- Reset digest statistics right before a targeted load test
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

-- Raise the digest table size permanently via the config file
-- (only takes effect after a restart):
-- performance_schema_digest_size = 20000

9. Practical example: finding the most expensive category and product queries

In practice, the digest report is additionally filtered on table names that are typical Magento hotspots, such as catalog_product_entity, catalog_category_product or sales_order_grid. During a load spike window, for example a promotion day, this lets you pick out exactly the query patterns responsible for the largest share of aggregated database time instead of working through thousands of irrelevant individual entries.

Combining this filtering with the ratio of examined to returned rows produces a clear priority order: patterns with a high SUM_NO_INDEX_USED value and a poor row ratio are almost always genuine index gaps and can be fixed quickly, while patterns with a good ratio but high execution frequency are better candidates for caching or reducing how often the application code calls them in the first place.

SELECT DIGEST_TEXT, COUNT_STAR, SUM_NO_INDEX_USED,
       ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%catalog_product_entity%'
   OR DIGEST_TEXT LIKE '%catalog_category_product%'
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 15;

10. Summary

11. FAQ: Performance Schema for Query Analysis: The Essentials at a Glance