Repository navigation
Inconsistent use of database restarts across systems #667
Description
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
Activity
nwoolmer commentedon Oct 27, 2025
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 commentedon Oct 27, 2025
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 commentedon Oct 28, 2025
@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 commentedon Oct 28, 2025
Two more cents... what about the other half of the rule you quoted?
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
mmapusers, clearing the OS page cache has been enforced consistently, arbitrarily disadvantaging those databases.rschu1ze commentedon Oct 28, 2025
Yes, there are problems with the benchmark, for example:
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:
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:
I think approach 3 was not implemented yet because it is a lot of work (all submissions must be changed).
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 commentedon Oct 29, 2025
@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 commentedon Nov 13, 2025
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!
rschu1ze commentedon Feb 17, 2026
I opened #793 for that.