A MySQL outage rarely starts with the server going down. It starts with one query crossing its normal latency after a release, an index change, or a traffic spike, while CPU, disk, and uptime dashboards still look acceptable. This runbook shows you how to identify that query, preserve the evidence, and turn it into an alert before customers feel the slowdown.

The failure pattern this monitoring must catch

A query that normally completes in 40 ms does not announce when it starts taking 4 seconds. Application latency rises first. Connection pools fill next. By the time the database host looks busy, the query pattern has already been damaging the service for minutes.

In 2026, query performance monitoring for MySQL servers needs to answer four questions quickly:

• Which query shape is consuming the most total database time?
• Did the regression begin after a deploy, schema change, or traffic shift?
• Is the problem execution time, lock waits, disk I/O, or replica lag?
• Which threshold should page an engineer instead of creating noise?

Mydbops applies this sequence through MySQL managed services because it creates an evidence trail for both an incident review and a compliance review. Do not start with a dashboard. Start with the query evidence.

Before you change production settings

You need the following before enabling query-level monitoring:

• Privilege to read global variables and Performance Schema tables
• MySQL 8.0 or 8.4, plus a maintenance window for persistent configuration changes
• A writable log path and a log-rotation policy
• A host that can run Percona Toolkit if you plan to use pt-query-digest
• An alert destination such as PagerDuty, email, or a Slack channel
• One measured baseline from a normal traffic period in 2026

Enable only signals you will review. Start with a usable ranking of slow query fingerprints.

Stage 1: Establish the current state

1. Check whether the server is already collecting evidence

Start by confirming the slow query log destination, threshold, and Performance Schema state:

MySQL documents a default long_query_time of 10 seconds. That is a starting default, not a production SLO. A query can create serious pool contention long before it reaches 10 seconds.

Expected outcome: you know whether slow statements are being written to a file and whether Performance Schema is available.

Common mistake: switching on the slow query log without confirming log_output includes FILE. The server can have the log enabled while writing no query entries to the expected file.

2. Set an initial slow-query threshold

Use a 1-second threshold as the first operational baseline for a typical OLTP workload, then tune it against real log volume and user-facing latency. MySQL supports microsecond resolution, but a threshold of 0.1 seconds on a busy production system usually creates more noise than action.

These runtime changes do not replace persistent configuration. Add the approved values to your MySQL configuration during the next controlled change window so they survive a restart.

Expected outcome: statements exceeding 1 second are recorded after they complete and release their locks.

Common mistake: treating long_query_time as a permanent performance standard. In 2026, the correct threshold is the one that catches material regressions without hiding in a log that nobody can review.

3. Control unindexed-query noise before it controls you

log_queries_not_using_indexes helps expose query patterns that do not use indexes for row lookups. It can also generate a high volume of entries. Enable it only with a throttle during your first baseline window.

The throttle limits these entries per minute and gives you a bounded sample to inspect. Keep the slow-query threshold and the unindexed-query signal separate in your review; a missing index and an expensive indexed range scan are different problems.

Expected outcome: you see index-avoidance patterns without allowing them to dominate the log.

Common mistake: adding an index from this log alone. Confirm row counts, selectivity, write cost, and the query-optimizer evidence first.

Stage 2: Find the query that is costing the service time

4. Rank slow-log fingerprints by total time

A raw slow query log answers “what happened.” A digest report answers “what cost the most.” Run pt-query-digest against a bounded log file after a representative traffic period:

Prioritize the queries with the highest total response time before the ones with the highest count. A statement executed 50 times at 3 seconds consumes more database time than a statement executed 10,000 times at 2 ms.

For each top fingerprint, record:

• Total response time
• Calls and average execution time
• Rows examined versus rows sent
• Lock time
• First and last observed timestamps

Expected outcome: one ranked list that turns a vague “MySQL is slow” report into a finite investigation queue.

Common mistake: analyzing a full week of logs first. Begin with a 30- to 60-minute period that includes the user-visible slowdown, then widen the window only if the pattern is unclear.

5. Compare the live statement picture in Performance Schema

The slow query log is historical; Performance Schema provides the live incident view. Start with the sys schema summary:

Use this query during an active event to identify the statement digest consuming the most cumulative time. If performance_schema is disabled, plan a controlled restart with it enabled; do not attempt to diagnose an active outage by adding broad instrumentation mid-incident.

Expected outcome: a current top-10 list that can be compared with the slow-log digest.

Common mistake: assuming the highest average latency is automatically the first priority. A moderately slow statement that runs thousands of times often creates more customer impact than a rare, slower administrative task.

6. Separate execution problems from wait problems

When the same query fingerprint appears in both the slow log and sys.statement_analysis, the next decision is what type of delay you are seeing.

• High execution time and high rows examined: inspect indexing, join order, and data volume.
• High lock time: inspect transaction length, conflicting writes, and lock-wait history.
• High disk reads: inspect working-set pressure and the access pattern.
• Slow replica reads: inspect replication lag before routing more read traffic.

Do not treat every slow query as an indexing problem. An index does not resolve a blocking transaction.

Stage 3: Prove the fix before changing schema

7. Run EXPLAIN ANALYZE on the real query shape

For a repeat offender, capture the actual plan with representative parameter values:

Look for full scans on large tables, a large gap between estimated and actual rows, unexpected nested-loop work, and expensive sorts or temporary tables. Record the plan before changing an index so you can prove whether the change fixed the intended bottleneck.

Expected outcome: a specific reason for the latency, not a guess based on a query string.

Common mistake: adding overlapping indexes. Each extra index adds write work and storage cost; the correct index supports the predicate and join pattern you measured.

8. Recheck the query after the change

After an index, query, or deployment fix, use the same evidence sources and the same traffic window:

• Re-run EXPLAIN ANALYZE.
• Compare the digest fingerprint’s average and total time.
• Confirm rows_examined fell where that was the stated problem.
• Check the application latency that users actually experience.

Do not declare success because an execution plan looks cleaner. The production outcome is lower query cost without a new write, lock, or replication penalty.

Stage 4: Turn evidence into alert rules

Dashboards help in daylight hours. Alert rules protect the service when no one is watching the graph. For teams that need coverage beyond an internal rotation, Remote DBA services provide 24x7 monitoring, observability, and support. Start with three rules tied to baseline data collected in 2026:

Alert rule 1: Slow-query rate breach

Page when slow-query entries per minute exceed the normal peak rate for a sustained 5-minute window. Use a rate, not a raw total, because traffic volume changes the total.

Alert rule 2: Top fingerprint latency breach

Alert when a single statement fingerprint exceeds its agreed latency threshold and its call volume is high enough to affect users. Pair latency with calls so one rare batch statement does not create a false production page.

Alert rule 3: Replica-lag breach

Alert when replica lag exceeds the recovery window your application can tolerate. This matters when reporting or read traffic is routed to replicas; lag turns apparently healthy reads into stale or delayed responses.

Mydbops recommends documenting the owner, escalation path, and first diagnostic query beside each alert. A page without a first action creates a slower incident, not a safer service.

Troubleshooting the monitoring setup

The slow query log stays empty

Confirm that slow_query_log is on, log_output includes FILE, and the configured path is writable by the MySQL process. Then confirm that the observed queries actually exceed long_query_time after execution.

The log fills the disk overnight

Increase the threshold temporarily, rotate the log, and inspect whether unindexed-query logging is responsible. Use log_throttle_queries_not_using_indexes before disabling the signal entirely.

sys.statement_analysis does not show the expected query

Confirm performance_schema is enabled and check whether the query is being normalized into a digest you did not recognize. Compare its timestamps and pattern against the slow-log fingerprint rather than matching literal parameter values.

The digest report is dominated by batch work

Run a narrower time window that isolates customer-facing traffic, or separate known batch jobs before ranking the results. Do not hide a batch job permanently if it competes for the same database resources during peak traffic.

Every alert fires and the team ignores it

Your baseline is too loose, too tight, or based on an abnormal period. Recalculate thresholds from normal peak traffic and require a sustained breach before paging.

A query looks fast but users still report timeouts

Check connection-pool saturation, transaction locks, network path, and replica lag. Query execution time is one part of the request path, not the whole request path.

Operating cadence after the first week

Review daily for the first 7 days, then weekly once the baseline is stable. This validates thresholds, retention, and alert ownership.

Every review in 2026 should answer the same three questions:

• Did a new fingerprint enter the top 10 by total time?
• Did a known fingerprint regress after a release or schema change?
• Did the alert rules page on real user impact or create noise?

A quarterly re-baseline is mandatory after a major traffic increase, migration, MySQL major-version upgrade, or application rewrite. Query monitoring is a control loop, not a one-time installation.

FAQ

How do I monitor MySQL query performance without buying a monitoring platform?

Enable the slow query log, use Performance Schema, and query sys.statement_analysis for a live ranking of expensive statement digests. Add a digest report so you can prioritize total database time instead of reading raw logs.

What is a good long_query_time setting for MySQL in 2026?

Start with 1 second for a typical OLTP workload, then tune it from observed log volume and your application latency target. MySQL defaults long_query_time to 10 seconds, which is usually too high for early regression detection.

Should I enable log_queries_not_using_indexes?

Enable it during a controlled baseline window with log_throttle_queries_not_using_indexes set to a limit. The signal is useful, but it can fill a slow log quickly and does not prove that every unindexed query needs a new index.

What should I inspect first in a pt-query-digest report?

Inspect total response time first, then calls, average execution time, rows examined, and lock time. Total response time identifies the query fingerprint consuming the largest share of database capacity.

Can EXPLAIN ANALYZE replace continuous monitoring?

No. EXPLAIN ANALYZE explains one query after you have identified it, while the slow query log and Performance Schema reveal which query patterns are causing recurring production cost.

Why is MySQL slow when CPU usage is normal?

Normal CPU does not rule out slow queries, lock waits, connection-pool pressure, or replica lag. Query-level evidence is required to locate the bottleneck before you change an index or add capacity.

How often should MySQL slow-query logs be reviewed?

Review them daily for the first 7 days after setup and weekly after the baseline stabilizes. Re-baseline after major traffic growth, a migration, an engine upgrade, or a significant application change.

The 10-minute readiness test

The monitoring stack is complete only when, after a deploy turns a 40 ms query into a 4-second query, the on-call engineer can name the fingerprint, evidence, owner, and first action in under 10 minutes.

Conclusion

Query performance monitoring for MySQL works when every layer answers a specific question. The slow query log and pt-query-digest show which fingerprints cost the most total time, Performance Schema confirms what is expensive right now, EXPLAIN ANALYZE proves whether a fix addressed the measured bottleneck, and three baseline-driven alert rules turn that evidence into pages an engineer can act on.

Treat the setup as a control loop rather than a project. Review it on a fixed cadence, re-baseline after major changes, and keep an owner and a first diagnostic query beside every alert. When the 10-minute readiness test passes, regressions are caught at the query level instead of in customer complaints.