4 ms·

Demand the impossible: Rigorous database benchmarking

58 points by erthalion 3y ago

smegsicle 3y ago
[flagged]
- deleted 3y ago
  [deleted]
- trollied 3y ago
  Care to back your comment up and say why? Otherwise it's just boring trolling.
  - diffuse_l 3y ago
    [1] for example [1] https://danluu.com/anon-benchmark/#:~:text=Over%20the%20years%2C%20many%20major,forbids%20benchmarking%20of%20their%20database https://danluu.com/anon-benchmark/#:~:text=Over%20the%20year....
    - exabrial 3y ago
      I like IBM’s clause, seems the most reasonable.
egnehots 3y ago
interesting but not very practical? let's take the max_wal_size parameter for ex. for most systems it's much simpler to set the max_wal_size to a value high enough that its value does not put pressure for checkpoints under load. so get enough free disk space, allocate maybe 5%. look at the logs, increase the value if checkpoints are required. (the downside is that crash recovery will take longer since it will read the wal)
- samokhvalov 3y ago
  very good example for each setting, we need to remember trade-offs and in this particular case, the trade-off is increased recovery time after potential crashes it is possible to conduct another benchmark that will measure this time, showing how it increases (in "avg" case -- "normal" TPS, and in "worst"* case -- increased TPS, e.g. massive UPDATE with random IO/access), and collect another set of interesting data and then combine two sets to support decisions in result, we will choose something like: we decided to use 32 GiB for max_wal_size and 15 min checkpoint_timeout and we know that if we crash in the worst* case, DB will need up to 10 minutes to recover; but benefit is that we have much less disk IO stress during massive writes. ___ *) "worst" here has two levels that show off when writes are massive with random IO pattern of block writes: - excessive writes from frequent checkpoints - additionally, more WAL needs to be written due to (if) full_page_writes=on
gavinray 3y ago
It's worth noting that the author is also the same person who gave the talk "Modern B-Tree Techniques" at Strange Loop, which is sort of the de-facto overview of B-Tree's as they relate to databases in modern times. https://www.youtube.com/watch?v=4ELJDEjDpqk https://www.youtube.com/watch?v=4ELJDEjDpqk
ddtaylor 3y ago
> Unknown unknowns, e.g. we have no idea that we run the database on a buggy Linux Kernel version, where buffered IO in a cgroup is slow due to wrong memory pressure calculation and constant page reclaiming. ?
szarnyasg 3y ago
Rigorous database benchmarking is indeed very difficult and time-consuming. I spent the last ~7 years working on benchmarks for graph processing systems in the Linked Data Benchmark Council (LDBC) [1], originally established in 2012 as an EU research project. LDBC creates TPC-style application-level database benchmarks which can be used for system-to-system comparison. We provide detailed specifications, data generators, benchmark frameworks, and multiple reference implementations. The benchmarks are implemented by vendors for their database products, and the implementations submitted to be run by independent third-party auditors to ensure their correctness and reproducibility. We have found that there is a market for audits for graph processing systems, albeit it is quite small: over the last 4 years, we have published 34 audited results, see [2], [3], and [4]. A major problem we face is that process of implementing the benchmark for a system and getting an audited result is long (and therefore expensive). Vendors spend months implementing and tuning the benchmarks for their systems. It is also typical for the auditor to spend 50+ hours on the auditing process, which includes a lengthy code review step, setting up the system, running the experiments, testing ACID properties, writing a report, etc. The length of the process is exacerbated by the lack of widely-adopted standard graph query languages. This potentially necessitates the auditor to learn a new language for every new audit. We have tried to mitigate this problem by improving our documentation, creating more reference implementation, distributing pre-generated data sets. There are new standard graph query languages (SQL/PGQ, GQL) but their adoption is currently very limited. Overall, the auditing process is still quite long, which is mainly caused by the essential complexity of the problem: implementing an application-level benchmark and getting reliable results is very difficult. [1] https://ldbcouncil.org/introduction/ https://ldbcouncil.org/introduction/ [2] https://ldbcouncil.org/benchmarks/snb-interactive https://ldbcouncil.org/benchmarks/snb-interactive [3] https://ldbcouncil.org/benchmarks/snb-bi/ https://ldbcouncil.org/benchmarks/snb-bi/ [4] https://ldbcouncil.org/benchmarks/spb/ https://ldbcouncil.org/benchmarks/spb/
- paulryanrogers 3y ago
  Wouldn't a vendor tuning the benchmark be ... cheating?
  - 3y ago
nimbius 3y ago
demand it at your peril. players like Oracle are infamous for EULA's that expressly forbid publishing your findings.
- darksaints 3y ago
  They should be included in any and all benchmarking results anyway, but just ranked last on everything, with a little star note to explicitly state that oracle prohibits publishing measured performance.
- speedgoose 3y ago
  For such cases, we usually write "Database A" or similar and the reader can easily guess which one it is. Maybe include a benchmark test where the database performs poorly so the database company doesn’t want to prove that you were referring to their product.
Shorn 3y ago
This is a really good article to read if you're interested in any kind of systemic approach to load-testing, not just benchmarking databases. - open/closed load generation strategies - nature of the systems-under-test being almost always that of a networks of queues - behaviour of systems-under-test tending to be non-normal and modal

58 points by erthalion 3y ago

smegsicle 3y ago

[flagged]

deleted 3y ago
[deleted]
trollied 3y ago
Care to back your comment up and say why? Otherwise it's just boring trolling.
- diffuse_l 3y ago
  [1] for example [1] https://danluu.com/anon-benchmark/#:~:text=Over%20the%20years%2C%20many%20major,forbids%20benchmarking%20of%20their%20database https://danluu.com/anon-benchmark/#:~:text=Over%20the%20year....
  - exabrial 3y ago
    I like IBM’s clause, seems the most reasonable.

egnehots 3y ago

interesting but not very practical? let's take the max_wal_size parameter for ex. for most systems it's much simpler to set the max_wal_size to a value high enough that its value does not put pressure for checkpoints under load. so get enough free disk space, allocate maybe 5%. look at the logs, increase the value if checkpoints are required. (the downside is that crash recovery will take longer since it will read the wal)

samokhvalov 3y ago
very good example for each setting, we need to remember trade-offs and in this particular case, the trade-off is increased recovery time after potential crashes it is possible to conduct another benchmark that will measure this time, showing how it increases (in "avg" case -- "normal" TPS, and in "worst"* case -- increased TPS, e.g. massive UPDATE with random IO/access), and collect another set of interesting data and then combine two sets to support decisions in result, we will choose something like: we decided to use 32 GiB for max_wal_size and 15 min checkpoint_timeout and we know that if we crash in the worst* case, DB will need up to 10 minutes to recover; but benefit is that we have much less disk IO stress during massive writes. ___ *) "worst" here has two levels that show off when writes are massive with random IO pattern of block writes: - excessive writes from frequent checkpoints - additionally, more WAL needs to be written due to (if) full_page_writes=on

gavinray 3y ago

It's worth noting that the author is also the same person who gave the talk "Modern B-Tree Techniques" at Strange Loop, which is sort of the de-facto overview of B-Tree's as they relate to databases in modern times. https://www.youtube.com/watch?v=4ELJDEjDpqk https://www.youtube.com/watch?v=4ELJDEjDpqk

ddtaylor 3y ago

> Unknown unknowns, e.g. we have no idea that we run the database on a buggy Linux Kernel version, where buffered IO in a cgroup is slow due to wrong memory pressure calculation and constant page reclaiming. ?

szarnyasg 3y ago

Rigorous database benchmarking is indeed very difficult and time-consuming. I spent the last ~7 years working on benchmarks for graph processing systems in the Linked Data Benchmark Council (LDBC) [1], originally established in 2012 as an EU research project. LDBC creates TPC-style application-level database benchmarks which can be used for system-to-system comparison. We provide detailed specifications, data generators, benchmark frameworks, and multiple reference implementations. The benchmarks are implemented by vendors for their database products, and the implementations submitted to be run by independent third-party auditors to ensure their correctness and reproducibility. We have found that there is a market for audits for graph processing systems, albeit it is quite small: over the last 4 years, we have published 34 audited results, see [2], [3], and [4]. A major problem we face is that process of implementing the benchmark for a system and getting an audited result is long (and therefore expensive). Vendors spend months implementing and tuning the benchmarks for their systems. It is also typical for the auditor to spend 50+ hours on the auditing process, which includes a lengthy code review step, setting up the system, running the experiments, testing ACID properties, writing a report, etc. The length of the process is exacerbated by the lack of widely-adopted standard graph query languages. This potentially necessitates the auditor to learn a new language for every new audit. We have tried to mitigate this problem by improving our documentation, creating more reference implementation, distributing pre-generated data sets. There are new standard graph query languages (SQL/PGQ, GQL) but their adoption is currently very limited. Overall, the auditing process is still quite long, which is mainly caused by the essential complexity of the problem: implementing an application-level benchmark and getting reliable results is very difficult. [1] https://ldbcouncil.org/introduction/ https://ldbcouncil.org/introduction/ [2] https://ldbcouncil.org/benchmarks/snb-interactive https://ldbcouncil.org/benchmarks/snb-interactive [3] https://ldbcouncil.org/benchmarks/snb-bi/ https://ldbcouncil.org/benchmarks/snb-bi/ [4] https://ldbcouncil.org/benchmarks/spb/ https://ldbcouncil.org/benchmarks/spb/

paulryanrogers 3y ago
Wouldn't a vendor tuning the benchmark be ... cheating?
- 3y ago

nimbius 3y ago

demand it at your peril. players like Oracle are infamous for EULA's that expressly forbid publishing your findings.

darksaints 3y ago
They should be included in any and all benchmarking results anyway, but just ranked last on everything, with a little star note to explicitly state that oracle prohibits publishing measured performance.
speedgoose 3y ago
For such cases, we usually write "Database A" or similar and the reader can easily guess which one it is. Maybe include a benchmark test where the database performs poorly so the database company doesn’t want to prove that you were referring to their product.

Shorn 3y ago

This is a really good article to read if you're interested in any kind of systemic approach to load-testing, not just benchmarking databases. - open/closed load generation strategies - nature of the systems-under-test being almost always that of a networks of queues - behaviour of systems-under-test tending to be non-normal and modal