db-benchmarks

Benchmarks • Intro • Why this is important • Features • Testing principles • Installation • UI

Introduction

https://db-benchmarks.com aims to make database and search engines benchmarks:

⚖️ Fair and transparent - it should be clear under what conditions this or that database / search engine gives this or that performance

High quality - control over coefficient of variation allows producing results that remain the same if you run a query today, tomorrow or next week

Easily reproducible - anyone can reproduce any test on their own hardware

Easy to understand - the charts are very simple

➕ Extendable - pluggable architecture allows adding more databases to test

And keep it all 100% Open Source!

This repository provides a test framework which does the job.

Why is this important?

Many database benchmarks are not objective. Others don't do enough to ensure results accuracy and stability, which in some cases breaks the whole idea of benchmarks. A few examples:

Druid vs ClickHouse vs Rockset

https://imply.io/blog/druid-nails-cost-efficiency-challenge-against-clickhouse-and-rockset/ :

We actually wanted to do the benchmark on the same hardware, an m5.8xlarge, but the only pre-baked configuration we have for m5.8xlarge is actually the m5d.8xlarge ... Instead, we run on a c5.9xlarge instance

Bad news, guys: when you run benchmarks on different hardware, at the very least you can't then say that something is "106.76%" and "103.13%" of something else. Even when you test on the same bare-metal server, it's quite difficult to get a coefficient of variation lower than 5%. A 3% difference on different servers can most likely be ignored. Given all that, how can one make sure the final conclusion is true?

Lots of databases and engines

https://tech.marksblogg.com/benchmarks.html

Mark did a great job making the taxi rides test on so many different databases and search engines. But since the tests are made on different hardware, the numbers in the resulting table aren't really comparable. You always need to keep this in mind when evaluating the results in the table.

ClickHouse vs others

https://clickhouse.com/benchmark/dbms/

When you run each query just 3 times, you'll most likely get very high coefficients of variation for each of them. Which means that if you run the test a minute later, you may get a variation of 20%. And how does one reproduce a test on one's own hardware? Unfortunately, I can't find how one can do it.

Testing principles

Our belief is that a fair database benchmark should follow some key principles:

✅ Test different databases on exactly the same hardware

Otherwise, you should acknowledge an error margin when there are small differences.

✅ Test with full OS cache purged before each test

Otherwise you can't test cold queries.

✅ Database which is being tested should have all its internal caches disabled

Otherwise you'll measure cache performance.

✅ Best if you measure a cold run too. It's especially important for analytical queries where cold queries may happen often

Otherwise you completely hide how the database can handle I/O.

✅ Nothing else should be running during testing

Otherwise your test results may be very unstable.

✅ You need to restart the database before each query

Otherwise, previous queries can still impact current query's response time, despite clearing internal caches.

✅ You need to wait until the database warms up completely after it's started

Otherwise, you may end up competing with the database's warm-up process for I/O which can severely spoil your test results.

✅ Best if you provide a coefficient of variation, so everyone understands how stable your results are and make sure yourself it's low enough

Coefficient of variation is a very good metric which shows how stable your test results are. If it's higher than N%, you can't say one database is N% faster than another.

✅ Best if you test on a fixed CPU frequency

Otherwise, if you are using "on-demand" CPU governor (which is normally a default) it can easily turn your 500ms response time into a 1000+ ms.

✅ Best if you test on SSD/NVME rather than HDD

Otherwise, depending on where your files are located on HDD you can get up to 2x lower/higher I/O performance (we tested), which can make at least your cold queries results wrong.

Test framework

The test framework which is used on the backend of https://db-benchmarks.com is fully Open Source (AGPLv3 license) and can be found at https://github.com/db-benchmarks/db-benchmarks . Here's what it does:

• Automates data loading to the databases/search engines included in the repository.
• Can run a database/search engine in Docker with a particular CPU/RAM constraint.
• While testing:
  • Purges OS cache automatically
  • Automates purging database caches before each cold run
  • Restarts the database before each cold run
  • Looks after your CPU temperature to avoid throttling
  • Looks after the coefficient of variation while making queries and can stop as soon as:
    • The CV is low enough
    • And the number of queries made is sufficient
  • After starting a database/search engine, lets it do its warm-up stage (pre-read needed data from disk), stops waiting as soon as:
    • There's no IO for a few seconds
    • And it can connect to the database/search engine
  • After stopping a database/search engine waits until it fully stops
  • Can accept different timeouts: start, warm-up, initial connection, getting info about the database/search engine, query
  • Can emulate one physical core which allows benchmarking algorithmic capabilities of databases more objectively (--limited)
  • Can accept all the values as command line arguments as well as environment variables for easier integration with CI systems
  • --test saves test results to file
  • --save saves test results from files to a remote database (neither of those that have been tested)
  • Tracks a lot of things while testing:
    • Server info: CPU, memory, running processes, filesystem, hostname
    • Current repository info to make sure there's no local changes
    • Performance metrics: each query response time in microseconds, aggregated stats:
      • Coefficient of variation of all queries
      • Coefficient of variation of 80% fastest queries
      • Cold query's response time
      • Avg(response times)
      • Avg(80% fastest queries' response times)
      • Slowest query's response time
    • Database/search engine info:
      • select count(*) and select * limit 1 to make sure the data collections are similar in different databases
      • internal database/search engine data structures status (chunks, shards, segments, partitions, parts, etc.)
• Makes it easy to limit CPU/RAM consumption inside or outside the test (using environment variables cpuset and mem).
• Allows to start each database/search engine easily the same way it's started by the framework for manual testing and preparation of test queries.

Installation

Before you deploy the test framework, make sure you have the following:

• Linux server fully dedicated to testing
• Fresh CPU thermal paste to make sure your CPUs don't throttle down
• PHP 8 and:
  • curl module
  • mysqli module
• docker
• docker-compose
• sensors to control CPU temperature to prevent throttling
• dstat
• cgroups v2

To install:

1. git clone from the repository:
   bash复制

   git clone [email protected]:db-benchmarks/db-benchmarks.git
   cd db-benchmarks

2. Copy .env.example to .env
3. Update mem and cpuset in .env with the default value of the memory (in megabytes) and CPUs the test framework can use for secondary tasks (data loading, getting info about databases)
4. Tune JVM limits ES_JAVA_OPTS for your tests. Usually it's size of allocated memory for Docker Machine

Configured and nightly tests

The repository includes run_configured_tests.sh, a JSON-configured runner used by both nightly Manticoresearch benchmarks and the regular important test list.

Scripts and configs

• run_configured_tests.sh: Runs tests from a JSON config. Use -c to choose a config, -t to choose the Docker image tag for configs with an image template, and -s to skip initialization for configs that define init. Configs with init check Manticore ports and server load before starting; occupied ports or sustained high load exit with code 2 so wrappers can classify the run as skipped.
• configs/nightly.json: Manticoresearch nightly config. It pulls manticoresearch/manticore:, checks the image version and hash with searchd --version, prepares data before init, runs the configured Manticore init hooks, writes nightly output under results/nightly/, runs the initial benchmark phase, waits for the server to settle, runs retests, skips config entries that already have matching results for the same version/hash, saves only results/nightly/, and then sources the success hook if configured. Init hooks may reuse existing indexes and print No need to rebuild; use -s only to skip the init step entirely.
• configs/regular.json: Regular important test config. It mirrors the previous important_tests.sh command list except that HN and taxi Manticore entries intentionally use manticoresearch:columnar_tuned and manticoresearch:rowwise_tuned. It writes to the normal results// tree and does not save results by default.
• important_tests.sh: Compatibility wrapper for ./run_configured_tests.sh -c configs/regular.json.
• run_nightly.sh: Runs nightly Manticoresearch tests for both latest and dev tags and handles dated logs plus failure/skipped hooks.

Examples:

./run_configured_tests.sh -c configs/nightly.json
./run_configured_tests.sh -c configs/nightly.json -t latest
./run_configured_tests.sh -c configs/regular.json
./important_tests.sh

Nightly and regular configs use the same tests shape: test names map to arrays of engine/memory entries. Each entry supports engine, memory, optional limited, and optional query_timeout. Config settings control the image template, quiet mode, result saving, result engine filter, and success hook. Nightly configs add init for suite initialization. Nightly runs write and save only results/nightly/. Regular configured runs write to the normal results// tree; if result saving is enabled for a config without init, the runner saves everything under results/ except results/nightly/. Nightly saves use NIGHTLY_DB_HOST, NIGHTLY_DB_USER, and NIGHTLY_DB_PASSWORD; regular configured saves use RESULT_DB_HOST, RESULT_DB_USER, and RESULT_DB_PASSWORD. Both save paths use port 443.

Setup Cron Job

To run nightly tests automatically:

1. Ensure scripts are executable: chmod +x run_configured_tests.sh run_nightly.sh important_tests.sh
2. Add the cron job from nightly_cron to your crontab: crontab -e and paste the contents, or copy to /etc/cron.d/ for system-wide setup.
3. The job runs run_nightly.sh at 2 AM daily, which executes both latest and dev versions sequentially.
4. Logs are stored in /var/log/db-benchmarks/ with dated filenames. Successful runs: nightly_dev_YYYYMMDD.log, failed runs: nightly_dev_YYYYMMDD_failed.log (same for latest).

Log Rotation

Use nightly_logrotate for log management:

• Copy to /etc/logrotate.d/nightly_manticore (requires sudo)
• Rotates logs daily, keeps 30 days, compresses old logs
• To verify: logrotate -d /etc/logrotate.d/nightly_manticore (dry run) or check /var/lib/logrotate/status

Get started

Prepare test

First you need to prepare a test:

Go to a particular test's directory (all tests must be in directory ./tests), for example "hn_small":

cd tests/hn_small

Run the init script:

./init

This will:

• download the data co