High Performance MySQL [Ch.2: Benchmarking MySQL]
High Performance MySQL
- A benchmark is a controlled experiment. Write down the question before you write the script.
- Report percentiles, not averages. One stalled request can move the mean; it barely moves p95.
- Throughput, latency, concurrency and scalability answer different questions. Measure the one you need.
- Most bad benchmarks fail on realism: too little data, the wrong distribution, or load that arrives too fast.
Why benchmark at all
A development database with a few hundred rows tells you almost nothing about production. Benchmarks let you put the system under a load you choose and observe it: how much it can take, where it bends, whether an assumption about hardware or schema holds, and how it behaves in the edge cases you would rather not meet live. They are also the honest way to compare two designs before committing to one.
Their weakness is that the load is invented. Real traffic is messy, bursty and unevenly spread over the data. A benchmark that ignores this produces numbers that are precise and wrong.
Where benchmarks lie
- Data size. A 1 GB test for a 100 GB table fits entirely in the buffer pool and measures memory, not storage.
- Distribution. Uniformly random keys spread load evenly; real users hammer a few hot rows and leave the rest cold.
- Arrival rate. Firing queries back-to-back as fast as possible models no real client and creates contention that never happens in production.
- Extrapolation. A server that handles 10× the transactions per second does not mean the business can grow 10×. Growth brings new features and heavier queries, not just more of today's.
Full stack or single component
Benchmarking the whole application, from HTTP to the database, answers the question users care about: how fast is the product? It captures network hops, application caches and serialization, but it is expensive to set up and noisy to read.
Benchmarking MySQL alone is the right tool for narrower questions: which of two queries is cheaper, whether a schema change helps, what a configuration variable does. Fewer moving parts means results you can attribute.
What to measure
| METRIC | WHAT IT ANSWERS |
|---|---|
| Throughput | Work per unit of time, usually transactions per second. Standard suites such as TPC-C measure this for OLTP workloads. |
| Latency | How long one task takes. Summarize with percentiles: p95 = 200 ms means 95% of requests finish within 200 ms. Min and max mostly measure luck; the mean is dragged by outliers. |
| Concurrency | How many tasks run at once. Count threads actually doing work, not open connections: thousands of browsing users may produce only a handful of concurrent queries. |
| Scalability | How performance changes as load or resources change. Few systems are linear; most show diminishing returns, then decline. |
These complement each other. A great single-threaded latency number says nothing about what happens at 64 concurrent clients.
Running a benchmark you can trust
- Pick standard or custom deliberately. A standard suite must match your workload: an analytics benchmark like TPC-H tells you little about an OLTP service.
- Start from real data. Snapshot production (scrubbed as needed) and keep the snapshot so every run starts from the same state.
- Replay real queries. Capture production query logs across representative periods instead of inventing a query mix.
- Warm up, then run long enough. Caches fill and throughput wobbles before it settles. As a rule of thumb, keep measuring for at least as long as the warm-up took.
- Record everything, raw. Extra data costs disk; missing data costs a rerun. Keep raw samples so you can aggregate them differently later.
- Automate. A scripted run is repeatable, and repeatability is what makes two configurations comparable.
Tools
For MySQL alone, mysqlslap ships with the server: it can create a test schema, run a query file or generated queries across many connections, and report timings. sysbench is the common choice for OLTP-style and hardware benchmarks. For full-stack tests, HTTP load generators drive the application from outside. Whatever you choose, learn a small toolset well and script around it.
How this shows up in QueryCrack
QueryCrack deliberately does not score wall-clock time. Timing depends on cache state, neighbours and luck, exactly the noise this chapter warns about. Rows examined is deterministic for a given plan and dataset, so it works as a benchmark metric with no variance: the same query always scores the same.
Common questions
Why not just report the average latency?
Averages hide the tail. A few slow requests can dominate the mean while most users see fast responses, or a fast mean can hide a painful p99. Percentiles describe what users actually experience.
How much data does a benchmark need?
Enough that the working set relates to memory the way production does. If production does not fit in the buffer pool, the benchmark must not either.
Source: Schwartz, Zaitsev and Tkachenko, High Performance MySQL, chapter 2 — Benchmarking MySQL. These are our notes, not a substitute for the book.