Benchmarking Databases

Most database benchmarks measure something other than what the author intended. A useful benchmark answers a specific question about your workload on your hardware.

Rules

Use your data shape. Row sizes, value distributions and cardinality drive index behaviour and compression. Uniformly random synthetic data hides skew, which is exactly what breaks in production.

Exceed memory. A dataset that fits entirely in cache measures memory, not the database. Size the test data so the working set is larger than RAM, as it will be in production.

Warm up, then measure. The first minutes measure a cold cache. Discard them, or report them separately as a deliberate cold-start measurement.

Report percentiles. An average hides everything: p50, p95, p99 and p99.9. A system with a good average and a terrible p99 is a system with a user-visible problem.

Run long enough. Compaction, vacuum, checkpoints and merges all arrive on a delay. A five-minute run finishes before the background work starts, and reports a throughput the system cannot sustain.

Change one variable at a time.

Tools

# PostgreSQL: built-in, and supports custom scripts.
pgbench -i -s 500 shop                     # initialise, scale factor 500
pgbench -c 50 -j 8 -T 600 -P 10 shop       # 50 clients, 8 threads, 10 minutes
pgbench -c 50 -j 8 -T 600 -f custom.sql shop
 
# MySQL
sysbench oltp_read_write --tables=20 --table-size=5000000 \
  --threads=64 --time=600 --report-interval=10 run
 
# Cassandra / ScyllaDB
cassandra-stress user profile=profile.yaml ops\(insert=3,read=7\) n=10000000 -rate threads=64
 
# Redis
redis-benchmark -h redis.internal -t get,set -n 1000000 -c 50 -P 16
 
# HTTP-level, if the question is end-to-end.
wrk -t8 -c200 -d10m --latency http://api.internal/orders

The custom-script forms matter more than the built-in workloads: oltp_read_write measures sysbench's idea of a workload, not yours.

The mistakes that invalidate results

• Coordinated omission. A load generator that waits for each response before sending the next under-reports latency badly during a stall, because it stops sending requests exactly when the system is slowest. Use a tool with an open-loop model or explicit rate control.
• Measuring the client. A single-threaded generator or a saturated client machine measures the client. Check the generator's own CPU.
• Testing with an empty cache, or a perfect one. Both are unrealistic; state which you measured.
• Ignoring the write path. A read-only benchmark tells you nothing about a mixed workload, where writes compete for the same resources.
• Comparing engines on defaults. Default configurations target different assumptions; a comparison of untuned engines measures their defaults.

Load Testing

Benchmarking asks "how fast is this operation". Load testing asks "what happens to the system at this level of demand", and it is the one that finds real problems:

1. Ramp gradually and watch where latency departs from linear. That inflection is your real capacity, well before the throughput ceiling.
2. Sustain the target load for long enough that background maintenance participates.
3. Inject failures during the run. Kill a node, fill a disk, add network latency. What the client sees during a failover is a more valuable result than any throughput number.
4. Watch the whole system, not just throughput: connections, queue depths, replication lag, compaction backlog, cache hit ratio.