Reported load times of some systems are impossibly low

[lukasvogel]

Some systems report load times below the theoretical minimum.

For example, gp2 provides ~250MB/s throughput.
With the smallest data size anyone reports being ~10 GB, any load time below 40 seconds should be phyiscally impossible (if we assume the input file to be cached which is fair imho for the big instances like c6a.metal).

Yet, several systems report far lower load times. For example, ClickHouse on c6a.metal reports just 7 seconds.
We had a quick look at the docs and it seems that ClickHouse doesn't perform fsyncs by default, which means the file system actually spends about two minutes syncing data after the load but before the first query is executed.

So while the benchmark reports "7 seconds", the actual import process isn't complete at that point. It seems misleading to report that ClickHouse (or any other of those systems) loads the data in 7 seconds under those conditions.

By contrast, most of the other systems include the sync to disk in the load time before acknowledging the COMMIT to ensure crash consistency and durability, putting them at a clear disadvantage in this metric.

[puzpuzpuz]

For example, ClickHouse on c6a.metal reports just 7 seconds.

I think, the files are in the page cache when the import is run. That's because the CSV/parquet files are first downloaded and then uncompressed to the local fs, leaving the uncompressed file pages sitting in the page cache.

[toschmidt]

@puzpuzpuz you are right, the CSV/parquet files will be cached in the os page cache. What’s puzzling here is that ClickHouse reports an execution time of just 7 seconds for the insert (even less on c7a.metal-48xl). However, since gp2 volumes only support about 250 MB/s throughput, it’s physically impossible to write more than ~2 GB to the EBS device in that time.

As far as we understand, ClickHouse writes the inserted data only to the OS page cache and considers it committed once it reaches memory. At that point, however, the data is not yet durable, since it hasn’t been flushed to disk (the EBS volume). If the instance crashes, all data reported as committed would be lost, which is not what you’d expect from an ACID-compliant database system.

Other database systems ensure durability by syncing modified pages or their write-ahead logs (WAL) to disk, allowing recovery after a failure. The cost of this syncing step is even visible in ClickHouse benchmarks: load time on c6a.metal is about 30× faster than on c6a.4xlarge (https://benchmark.clickhouse.com/#system=+lik&type=-&machine=-a2l|g4e|6ax|ae-l|6ale|g-l|3al&cluster_size=-&opensource=-&tuned=+n&metric=load&queries=-)
The smaller instance has less memory, so some pages are evicted from the OS cache and actually written to disk at the 250 MB/s limit.
For comparison, loading the same data in DuckDB is only about 1.16× slower on c6a.4xlarge, a much smaller performance gap.

[puzpuzpuz]

@toschmidt I see the concern now. Current benchmark rules don't require powerloss durability from data load, i.e. as long as the data is queryable and it's written to disk, even without fsync, it's fine.

which is not what you’d expect from an ACID-compliant database system.

I doubt all of the measured databases are ACID-compliant. In fact, most OLAP (and even some OLTP ones) databases have very limited transactions support, no speaking of powerloss durability.

Anyhow, let's hear from the CH team.

[Mytherin]

For more context, the actual write to disk happens right before executing the first query as part of the sync which is used for flushing the buffers. This is not included in the load timing.

For fairness, I think it would make the most sense to add sync calls to the load scripts of all systems to ensure all data is written to disk and the load is fully finished, and including the timing of that sync call in the load time. For example, below is a diff that does this for ClickHouse.

The alternative of allowing partial loads while processing the actual load in an untimed part prior to execution opens up a whole other can of worms. For example, systems could spawn a background process to do more optimization / compaction while the sync call is finishing, which would be completely untimed.

If ClickHouse wants to show off its impressive in-memory load times, maybe an explicit "ClickHouse (Memory)" run can be added similar to what DuckDB has. Or alternatively, the runs could be updated to use faster disks so that the disk itself is less of a bottleneck and the system specific ingestion timing is more important.

diff --git a/clickhouse/benchmark.sh b/clickhouse/benchmark.sh
index 34c19b4f..a88eaf5c 100755
--- a/clickhouse/benchmark.sh
+++ b/clickhouse/benchmark.sh
@@ -41,9 +41,21 @@ seq 0 99 | xargs -P100 -I{} bash -c 'wget --continue --progress=dot:giga https:/
 sudo mv hits_*.parquet /var/lib/clickhouse/user_files/
 sudo chown clickhouse:clickhouse /var/lib/clickhouse/user_files/hits_*.parquet
 
+# sync any outstanding buffers to ensure they do not affect load times of the database
+sync
+echo 'Start Data Load'
+echo `date --utc +%FT%T.%3NZ`
+
 echo -n "Load time: "
 clickhouse-client --time --query "INSERT INTO hits SELECT * FROM file('hits_*.parquet')" --max-insert-threads $(( $(nproc) / 4 ))
 
+echo 'Start Final Sync'
+echo `date --utc +%FT%T.%3NZ`
+sync
+echo 'Finish Load'
+echo `date --utc +%FT%T.%3NZ`
+
 # Run the queries
 
 ./run.sh "$1"

[alexey-milovidov]

I've also found this observation.

Downloading the dataset with wget also brings it to the page cache. That's why the subsequent loading does not use disk IO on machines with 16 GB of memory or more.

It is okay to measure the "hot" load time this way, as it makes the difference more visible.

[Mytherin]

Just to clarify - the issue here is not with the read not using I/O - as that is consistent across all systems. The issue here is with the write not doing any I/O for systems that do not explicitly call fsync on some of the machines. Only ClickHouse (and maybe a few other systems like MongoDB) appear to not be calling fsync. All other systems appear to be calling fsync to force data to be written to disk.

Because the disk is so slow there's not really any meaningful comparison possible between purely in-memory loads and loads that write to disk, as the write to disk dominates the loading time for systems that call fsync.

The issue with allowing loads to effectively be "no fsync" in such a broad benchmark is that this is a system-specific setting that needs to be configured / added to every single system that participates in the benchmark to make load times meaningfully comparable between systems. By contrast, forcing all data to be written to disk can be easily done by adding a call to sync after the load. This is completely system agnostic.

[alexey-milovidov]

Yes, let's add at least a sync call after the load.

[ChrisWint]

Any updates on this @alexey-milovidov @rschu1ze ?

[rschu1ze]

Sorry, haven't had time to work on this yet. If someone likes to change the logic for some submissions, please send a PR.