Inconsistent use of database restarts across systems

[lukasvogel]

I understand the motivation for restarting the database after every query. While I think considering the use of a buffer manager in a DBMS to be cheating is a bit strange, I agree that it makes a "cold" run less cold.
If you want to require a restart before a "cold" run, that's fine by us. It's your benchmark, after all.

However, it’s not clear why this approach is applied selectively to only some databases but not others. For example, if I restart ClickHouse after every run, I get the following results (left: main branch; right: ClickHouse restarted before each query):

Most other databases that use a buffer pool (such as MySQL or Postgres) aren't restarted at all. It just seems inconsistent to only do this with the fast database systems.

Could you clarify the rationale behind which systems are restarted and whether this policy could be made consistent across all databases?

In case you're interested, here is the modified run.sh for ClickHouse:

#!/bin/bash
# Restart clickhouse so no cache can be used
sudo clickhouse stop  > /dev/null 2>&1

# Determine which set of files to use depending on the type of run
if [ "$1" != "" ] && [ "$1" != "tuned" ] && [ "$1" != "tuned-memory" ]; then
    echo "Error: command line argument must be one of {'', 'tuned', 'tuned-memory'}"
    exit 1
else if [ ! -z "$1" ]; then
    SUFFIX="-$1"
fi
fi

TRIES=3
QUERY_NUM=1
cat queries"$SUFFIX".sql | while read -r query; do
    [ -z "$FQDN" ] && sync
    [ -z "$FQDN" ] && echo 3 | sudo tee /proc/sys/vm/drop_caches >/dev/null

    sudo clickhouse start > /dev/null 2>&1
    for _ in {1..300}
    do
        clickhouse-client --query "SELECT 1"  > /dev/null 2>&1 && break
        sleep 1
    done

    echo -n "["
    for i in $(seq 1 $TRIES); do
        RES=$(clickhouse-client --host "${FQDN:=localhost}" --password "${PASSWORD:=}" ${PASSWORD:+--secure} --time --format=Null --query="$query" --progress 0 2>&1 ||:)
        [[ "$?" == "0" ]] && echo -n "${RES}" || echo -n "null"
        [[ "$i" != $TRIES ]] && echo -n ", "

        echo "${QUERY_NUM},${i},${RES}" >> result.csv
    done
    echo "],"
    sudo clickhouse stop  > /dev/null 2>&1
    QUERY_NUM=$((QUERY_NUM + 1))
done

[nwoolmer]

However, it’s not clear why this approach is applied selectively to only some databases but not others.

Given the double-checking only started over the weekend, on balance, it might be a bit early to call it selective.

Avoiding 'hot-run' measurements bleeding into 'cold-run' results seems reasonable, and in line with the caching rules. Previously, in-memory databases (which were always 'hot-runs') were also included in 'cold-run' results. The underlying results remain impressive in either case.

There have been prior cases where inconsistences were corrected across the board, for example: #252

[puzpuzpuz]

When creating #656, I didn't not suggest restarting databases between query runs, but only page cache flush which was missing in some scripts. But I agree that all databases should be put in the same conditions, so all of them should be restarted between the runs along with page cache flush. This should make "Cold Run" results more strict and comparable.

[lukasvogel]

@alexey-milovidov @rschu1ze

Would also love if there was a public clarification if using a buffer pool is fine or not. Currently the README.md explicitly states: "It is okay if the system performs caching for source data (buffer pools and similar)".

[nwoolmer]

@alexey-milovidov @rschu1ze

Would also love if there was a public clarification if using a buffer pool is fine or not. Currently the README.md explicitly states: "It is okay if the system performs caching for source data (buffer pools and similar)".

Two more cents... what about the other half of the rule you quoted?

If the cache or buffer pools can be flushed, they should be flushed before the first run of every query.

The spirit of the rules (by my reading) seems to be that caching the data during execution is fine, but it should be cleared between runs. Yet if this was not possible via a simple command, vendors have been allowed to submit hot results anyway in a grey area.

Whereas for mmap users, clearing the OS page cache has been enforced consistently, arbitrarily disadvantaging those databases.

[rschu1ze]

Yes, there are problems with the benchmark, for example:

• Outdated submissions, usually because benchmark scripts can't be run automatically, or they are for a cloud database which requires creating an account.
• Modern databases calculate lots of queries in 10s of milliseconds. This lets the differences between databases blur. The solution will be to increment the dataset size and/or make the queries more complex.
• Results are not checked for correctness. There were repeated instances in the past where wrong query results were detected late.
• The benchmark measures peak performance. One could argue that for cloud databsaes, dollar-per-performance is the more important metric (it's a lot harder to quantify though).
• The majority of systems is benchmarked on c6a.4xlarge EC2 instances with gp2 disks. If I see correctly, the CPU came out in 2019 (not exactly super modern) and gp2 was superseded by gp3 disks by now.
• Benchmark results do not show the version of the benchmarked system (this can be fixed easily, "System & Machine" column should display sw version number(s) #639).

This ticket is about cold vs. hot runs. As required by the benchmark rules ("the first query should be run after dropping the page cache"), virtually all benchmark scripts (run.sh) clear the OS page cache between query runs:

echo 3 | sudo tee /proc/sys/vm/drop_caches >/dev/null

However, many databases cache other stuff as well (e.g. ClickHouse's query condition cache, metadata, etc.). There is no benchmark rule that such caches should be cleared between cold runs. Besides that, a lot of database provide no clear way to do so. The only rule is that query results must not be cached, respectively intermediate results at the end of the pipeline.

This leaves a lot of interpretation room what "cold" runs really mean.

A cleaner approach would be to measure:

1. Hot runs,
2. Lukewarm runs. This is today's cold run. In my view, it is difficult (not possible) to even frame the exact rules which allow or permit certain types of database caches for this measurement type (*) - database developers are too creative in working around the rules 😄. Above discussion if a buffer pool is allowed (Inconsistent use of database restarts across systems #667 (comment), Inconsistent use of database restarts across systems #667 (comment)) is a symptom of this problem. We should require clearing OS page cache though (it is hard to work around that).
3. Cold runs. That is what everyone thinks in the first place about "cold runs". In the absence of standardized and universally available ways to clear database-internal caches, the only option is to stop databases between runs (+ clear the page cache).

I think approach 3 was not implemented yet because it is a lot of work (all submissions must be changed).

However, it’s not clear why this approach is applied selectively to only some databases but not others.

Fair enough. In my view, the current situation is caused by the principal problems with 2) lukewarm runs (*). I think the only way out of this is to replace them by 3) really cold runs. I'll discuss internally.

[lukasvogel]

@rschu1ze I totally get your point - it's very hard to unify so many systems with different assumptions and use-cases in a single benchmark.
I also agree that having implemented a buffer manager puts us at an advantage for cold runs.
As a general-purpose HTAP database developers, it's just deeply ingrained for us that you obviously wouldn't restart your DBMS between queries for the use case we're targeting.
That use case, obviously, looks very different for, e.g., an analytical query engine running on Iceberg, so I get your struggle.

I appreciate you trying to come up with objective, fair rules!

On the same note, since you're planning to have an internal discussion anyway, we've noticed another part of the benchmark that could use clarification. I've opened a separate issue (issue #678), because I don't want to derail this discussion here too much.
Would be great if you could provide some guidelines for that as well!

[rschu1ze]

Hi 👋 everyone, I pushed a PR which addresses this issue: #692. The plan is to migrate all existing submissions from lukewarm to true cold runs soon (I'm also open to crowdsourcing this!).

Thanks again for flagging this!