MariaDB / Advanced: evidence and reliable operations / Lesson 13

Optimizer evidence and workload performance

Diagnose a slow dispatch report by separating estimation errors, query work and system contention. Use MariaDB instrumentation with explicit operational boundaries.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Compare estimated and observed work without overclaiming timings.
Choose query, statistics or workload changes from evidence.

Define a useful baseline

A dispatch dashboard can be slow because it examines too many rows, waits for a lock or competes for storage with backups. Begin with the exact SQL, parameter distribution, server version, table sizes and user-visible latency target. Record repeated latency percentiles rather than one fast run. A cache-warm query differs from a cold one, and a single-customer sample may hide skew. Preserve the query result contract while experimenting; lower latency with missing rows is not an improvement.

Read estimates before executing analysis

EXPLAIN FORMAT=JSON provides a structured estimated plan for the fixture. Inspect access paths, join order and estimated filtering, then compare the predicates with known data distribution. MariaDB JSON plan fields differ from MySQL fields, so use a MariaDB-aware reader. If one customer owns half the table, an average cardinality estimate may be misleading. The query below deliberately contains no claimed numeric plan output because none was measured in this environment.

sql Illustrative SQL; expected results are reasoned, not measured.

EXPLAIN FORMAT=JSON
SELECT customer_id, COUNT(*) AS waiting
FROM shipments
WHERE state = 'pending' AND created_at < '2026-10-20'
GROUP BY customer_id;

This estimates a read-only report. It needs a representative shipments fixture and does not claim a selected index.

Separate instrumentation from maintenance

ANALYZE FORMAT=JSON SELECT executes the report and includes runtime information. Compare estimated rows with observed r_rows and loops in the relevant nodes, accounting for how those metrics are defined. Large discrepancies suggest investigating distribution and statistics, but are not automatic proof that a statistics refresh will fix latency. ANALYZE TABLE updates statistics and can consume resources or affect plans. Treat that maintenance as a reviewed change, rather than silently running it during a read-only diagnosis.

Measure only a bounded laboratory SELECT

The next statement illustrates MariaDB executed analysis and is non-runnable here. Use it only after approving its work in an isolated representative fixture; it can scan and aggregate many rows. It returns analysis rather than the ordinary result table. A useful comparison records identical data, parameters, session settings and correctness checks before and after a candidate index or rewrite. An optimizer trace can explain rejected alternatives, but enable and inspect it only in a deliberate diagnostic session.

sql Illustrative SQL; expected results are reasoned, not measured.

ANALYZE FORMAT=JSON
SELECT customer_id, COUNT(*) AS waiting
FROM shipments
WHERE state = 'pending' AND created_at < '2026-10-20'
GROUP BY customer_id;

Unlike EXPLAIN, this SELECT is actually executed by MariaDB. No observed measurements are supplied.

Improve the constrained resource

If actual work is excessive, reduce unnecessary joins, use selective access or precompute a carefully maintained summary. If work is reasonable but elapsed time grows under concurrency, inspect lock waits, buffer-pool pressure, storage latency and connection demand. Slow-query logs can reveal frequency and rows examined, but may contain sensitive literals and need controlled retention. Avoid increasing every global buffer: per-connection allocations multiply under load. Accept changes using end-user latency, throughput, correctness and operational cost together.

Common errors and fixes

A faster query returns fewer business records

Why it happens: A rewrite changed NULL, join or date-boundary semantics.
How to fix it: Compare keys and aggregates with boundary fixtures before accepting timing.
Prevent it next time: Make semantic equivalence an explicit tuning acceptance condition.

A global memory increase causes pressure

Why it happens: Per-session or concurrent allocations were underestimated.
How to fix it: Revert the reviewed change and model peak concurrent memory use.
Prevent it next time: Tune from measured resource limits with a rollback plan.

ANALYZE is assumed to be passive inspection

Why it happens: Executed analysis was confused with estimated EXPLAIN or statistics maintenance.
How to fix it: Identify the full command and assess its actual execution impact.
Prevent it next time: Label diagnostics by whether they execute, mutate metadata or only estimate.

Check your understanding

1. Estimated rows are 100 but observed rows average 20000 for a join node. What should you investigate before forcing an index?

Show worked solution

Check skew, parameter representativeness, predicate conversions and the freshness and type of statistics. Confirm whether r_rows is per loop and how many loops ran before comparing total work. Test a justified statistics or query change in a representative lab. Forcing a key without explaining the estimation error can freeze a poor choice for another customer or release.

2. One query takes 20 ms alone and 2 seconds at peak, with similar rows examined. What does that suggest?