Database benchmark and workload design

Why it matters

Claims like "X is ten times faster than Y" are easy to produce and hard to trust. Database performance depends on data size relative to memory, key skew, concurrency, durability settings and tuning. Research papers, vendor comparisons and your own engineering decisions all need workloads that answer a specific question and measurements others can repeat.

Core ideas

Start from the question. "Does the new index speed up checkout?" needs the checkout transaction mix at production-like data size and concurrency, not a generic suite. Fix the metric before running anything: throughput at a latency target, p99 latency, or cost per query.

Skew. Real access patterns are rarely uniform. Under a Zipfian distribution the k-th most popular key is requested with probability proportional to 1/k^s, so a few keys dominate. Skew raises cache hit rates (good for reads) and contention on hot rows (bad for writes). Test both uniform and skewed distributions; YCSB and pgbench can generate Zipfian keys.

Load generation. Sweep the number of clients to find the knee where latency rises sharply. Closed-loop clients wait for each response before sending the next request, so they slow down exactly when the system does and hide queueing delay; measuring latency from the intended send time avoids this coordinated omission. Check that the client machine is not the bottleneck.

Cache and steady state. Cold runs measure I/O paths; warm runs measure in-memory behavior. LSM engines need time for compaction to reach steady state, and MVCC systems accumulate old versions. Report warm-up, run length, repetitions and spread, and use percentiles for latency.

Correctness and fairness. Verify results (row counts, checksums, invariants such as total balance) so a misconfigured system cannot win by doing less work. Compare systems at equivalent durability (same flush policy) and isolation, with comparable tuning effort, and record versions, configuration, hardware and generator seeds.

Standard suites. TPC-C models order-entry OLTP with several transaction types and contention on warehouse and district rows. TPC-H is a decision-support suite whose generated data is mostly uniform and independent. TPC-DS adds more complex queries and skewed data. YCSB defines key-value workloads with configurable read/update mixes and request distributions. JOB uses real, correlated IMDB data to stress join ordering. Official TPC results follow audited rules; informal "TPC-H-like" runs do not.

Common misconceptions

• "Averages summarize latency." Tails matter; report percentiles and their variation across runs.
• "A standard benchmark answers my question." It answers its own question; check whether its mix, size and skew resemble yours.
• "Default configurations make a fair comparison." Defaults differ in durability and memory use; state and justify settings.
• "One run is enough." Caches, background work and noise require repetitions and a measure of spread.
• "Faster means better." Without correctness checks a fast run may be skipping work or returning wrong results.

How to approach it

Read "Fair Benchmarking Considered Difficult" for a catalog of pitfalls, then run a small study: pgbench or YCSB with uniform versus Zipfian keys, cold versus warm cache and three client counts, five repetitions each. Write a one-page report that someone else could reproduce from your scripts.