Common Pitfalls in Database Performance Testing (2018) [pdf]

Appendix A is a thoughtful checklist for anyone doing benchmarks:

  • Choosing your Benchmarks.
  □ Benchmark covers whole evaluation space
  □ Justify picking benchmark subset
  □ Benchmark stresses functionality in the evaluation space
  
  • Reproducible. Available shall be:
  □ Hardware configuration
  □ DBMS parameters and version
  □ Source code or binary files
  □ Data, schema & queries

  • Optimization.
  □ Compilation flags
  □ System parameters

  • Apples vs Apples.
  □ Similar functionality
  □ Equivalent workload

  • Comparable tuning.
  □ Different data
  □ Various workloads

  • Cold/warm/hot runs.
  □ Differentiate between cold and hot runs
  □ Cold runs: Flush OS and CPU caches
  □ Hot runs: Ignore initial runs

  • Preprocessing.
  □ Ensure preprocessing is the same between systems
  □ Be aware of automatic index creation

  • Ensure correctness.
  □ Verify results
  □ Test different data sets
  □ Corner cases work

  • Collecting Results.
  □ Do several runs to reduce interference
  □ Check standard deviation for multiple runs
  □ Report robust metrics (e.g., median and confidence intervals)

Here's my little contribution / anecdote. I worked on automated testing of a product in the general category of SDS block device. We wanted to see some "realistic" benchmarks (instead of totally synthetic FIO-based ones), so we decided to do, beside other things, some benchmarks of PostgreSQL on top of our block device.

At that time we've already developed a convention for what would be the "optimal" way to configure a filesystem on top of our block device -- which is a whole other story, but I won't reproduce it here. The most tempting aspect of PostgreSQL was the existence of pgbench tool that I assumed would offer some sort of a standardized workload that would allow me to compare my results to results produced by other testers on other hardware...

Little did I know how and in how many ways PostgreSQL performance was dependent on configuration. The most important pieces are the memory buffers and fsync-related stuff. Eventually, by reading more documentation and some educational material about PostgreSQL I realized that, essentially, I can make the same exact benchmark on the same exact hardware to very hundreds if not thousands of times in performance for the same exact test.

I also realized that storage's performance had very little to do with the benchmark's scores as in order to game the benchmark one would simply add more memory.

Also I recommend to read the methodology here: https://github.com/ClickHouse/ClickBench/blob/main/README.md

Making an open-source, easily reproducible benchmark with clear rules allowed having more than 30 different DBMS in a single benchmark. Many of the results were submitted or improved by the vendors of the corresponding systems.

There is a comparison section that lists limitations and drawbacks in comparison with other benchmarks.

Looking forward to reading this. My “favorite” database benchmarking bugaboo is unrealistic random data distribution in the test set, typically by relying on the uniform results you’ll get out of the default random() function in your favorite language library.

Application data is almost never uniformly random. As a result, you’ll miss the real-world effects of a wide range of system behavior: CPU branch prediction, data compression, adaptive query optimization, …

the most common pitfall is: I ran the query, but didn't fetch all the rows. Query is so fast with library A but not library B!! (library B buffers the rows in some way, so they are pre-fetched).