Skip to content

PATTERN Cited by 1 source

Stage-level query profiling

Problem. A single query takes ~700 ms. You need to know what it's slow at — optimising? opening tables? executing the plan? waiting on a lock? waiting on handler commit? — before deciding what to fix. EXPLAIN ANALYZE answers which iterator was expensive; it does not answer which server-lifecycle stage consumed the wall-clock.

Solution. Use MySQL's stage-timing instrumentation in performance_schema:

  1. Enable the three toggles (setup_instruments, setup_consumers, setup_actors) — either globally, or scoped to a specific test user to limit overhead.
  2. Capture the connection's thread_id from performance_schema.threads.
  3. Execute the query.
  4. Query events_stages_history_long between the statement's event_id and end_event_id to get a per-stage millisecond breakdown.

(Source: sources/2026-04-21-planetscale-identifying-and-profiling-problematic-mysql-queries.)

Canonical stages to look at

  • stage/sql/executing — running the plan. Dominance here → EXPLAIN ANALYZE is the next step.
  • stage/sql/optimizing + stage/sql/statistics + stage/sql/preparing — query planning. Dominance here suggests stale stats, missing histograms, or planner bugs.
  • stage/sql/Opening tables + stage/sql/System lock — table-level locking / metadata acquisition. Dominance suggests DDL contention or too many tables.
  • stage/sql/waiting for handler commit — InnoDB commit path. Dominance suggests durability-setting issues or log flush contention.
  • stage/sql/checking permissions — auth path. Dominance suggests grant-table scans (rare but real).

Worked example

From the PlanetScale post:

stage/sql/executing                     |   735.3020 ms
stage/sql/statistics                    |     0.1040 ms
stage/sql/Opening tables                |     0.0950 ms
stage/sql/preparing                     |     0.0590 ms
stage/sql/optimizing                    |     0.0270 ms
(all others < 0.01 ms)

Total ~736 ms, >99% in executing. Reading this profile tells you "planning is fine, locks are fine, the plan itself is slow — go look at EXPLAIN ANALYZE or the rows-examined."

Enablement cost

Global enablement has "(small) adverse effect on the overall performance of your system" per PlanetScale — all sessions pay the instrumentation tax.

Selective enablement via setup_actors:

UPDATE performance_schema.setup_actors
  SET ENABLED = 'NO', HISTORY = 'NO'
  WHERE HOST = '%' AND USER = '%';

INSERT INTO performance_schema.setup_actors
  (HOST, USER, ROLE, ENABLED, HISTORY)
  VALUES ('your_host', 'your_user', '%', 'YES', 'YES');

Scopes profiling to a single principal — the one running the test queries.

Caveats

  • In-memory bounded history — events_stages_history_long is a ring buffer; old statements age out. Run the profile workflow immediately after the query.
  • Overhead — enabling every instrument has measurable cost in high-QPS workloads. Scope narrowly.
  • Timer arithmetic — (timer_end - timer_start) / 1000000000 gives milliseconds (source is picoseconds).
  • Doesn't replace EXPLAIN ANALYZE — they're complementary. Stage profiling answers "where in the server lifecycle"; EXPLAIN ANALYZE answers "where in the plan."
  • Production applicability — enabling history on prod shared hosts is discouraged; use a replica or a scoped-user on a staging instance.
  • Setup complexity — four SQL blocks + session variables; not ergonomic. Tools like PlanetScale Insights package this into a UI (for the digest-level identification step; per-execution stage profiling still requires manual performance_schema queries even with Insights).

Seen in

Last updated · 378 distilled / 1,213 read