High Performance MySQL [Ch.3: Profiling Server Performance]

The three questions

Almost every performance complaint reduces to one of three questions: is the server doing its work efficiently, why is this particular query slow, and what causes the occasional stall nobody can reproduce? Profiling is how you answer them with evidence instead of intuition.

The book defines performance as response time, so the goal of profiling is to explain where that time goes. Sometimes the answer is work you can remove: a missing index, a needless sort. Sometimes it is that every step is necessary, which is equally valuable because it stops you from polishing a query that is already as fast as it can be.

What a profile looks like

Timing a task end to end gives you one number. A profile splits it into child tasks, merges repeated ones, and sorts them by their share of total time. The result reads like a ranked bill:

Two flavours exist. Execution-time profiling shows where CPU work goes and suits compute-bound queries. Wait analysis shows time spent blocked on I/O or locks, which is where most slow database queries actually live.

Start with the application

Before profiling MySQL, profile the code that calls it. Application performance monitoring that times every request and breaks it into spans (database calls, external APIs, rendering) tells you whether the database is even the problem, and which queries matter most to users. Database work with no visible user impact can usually wait.

Profiling the server's workload

When you do not yet know which queries hurt, look at all of them. The slow query log records queries above long_query_time; setting that threshold to 0 captures everything. Logging costs some I/O and disk, so rotate the files and do not leave full logging on blindly. Tools such as pt-query-digest then normalize queries into fingerprints and rank them by total time.

In MySQL 8 the Performance Schema provides the same information without log files: events_statements_summary_by_digest already aggregates statements by fingerprint, with counts, latency and rows examined per digest.

Profiling a single query

Once you have a suspect, break it down. The book uses SHOW PROFILE, which lists the stages a query went through (opening tables, sending data, creating a temporary table, sorting) with durations. It is deprecated in MySQL 8; the Performance Schema stage tables (events_stages_history_long) carry the same data and can be grouped with ordinary SQL:

SELECT event_name, COUNT(*) AS calls, SUM(timer_wait) / 1e9 AS total_ms
FROM performance_schema.events_stages_history_long
WHERE nesting_event_id = <statement event id>
GROUP BY event_name
ORDER BY total_ms DESC;

Grouping matters: a stage that appears five times in the raw output is one line in the profile. A query that spends half its time copying into a temporary table tells you exactly where to look; a sort that takes 5% tells you not to bother with it.

Status counters complement stage timing. Comparing SHOW SESSION STATUS before and after a query shows how many rows it read through each access path (Handler_read_*), whether it created temporary tables on disk, and whether it sorted.

Intermittent problems

Sporadic stalls are the hardest case because they are gone by the time you look. The book's case study is a server that refused connections for seconds to minutes every day or two. The method generalizes:

• Collect context first: versions, engine, hardware, recent changes. Ideally nobody has "tried things" yet.
• Trigger on a symptom. Watch a counter that spikes during the incident (there, Threads_connected jumping from tens to hundreds) and capture everything when it fires.
• Distrust the familiar answer. Heavy disk writes looked like a known flushing bug; the flush counters said otherwise.
• Follow the evidence outside the database. The real cause was an application cache expiring and every app server rebuilding it at once, a cache stampede. Application-level logging would have shown it immediately.

How this shows up in QueryCrack

The grader is a tiny profiler: it snapshots the session's Handler_read_* counters, runs your query, and reports the difference. Rows read through index lookups, index walks and full scans are separated, so the verdict says not just "too slow" but which access path did the damage.

Common questions