Update benchmarks on clickbench

[dudzicp]

In the clickbench Datafusion is currently ~5x slower than DuckDB on comparable machine. I think it would make sense to update the benchmark results with the latest improvements so that datafusion will be a viable/reasonable option for new projects

[ozankabak]

I think this is something we should do as soon as possible. We should not needlessly create a false impression of "slow" in the eyes of the community at large. Why try to correct it later when we can avoid it?

Our benchmark was not run on a comparable machine, and it is a very old version. I suggest we run it on c6a.metal, 500gb gp2 (that's the machine the majority is gravitating towards) with our current version. After that, we can identify which queries we are slow at, investigate the reasons and improve.

We (the Synnada team) is happy to help with investigations and take on improvement tasks.

[alamb]

I agree this would be great to do. @waitingkuo and @andygrove have worked on this in the past and perhaps they have thoughts.

[andygrove]

I wasn't really too involved with this. I have been focussing more on TPC-H and TPC-DS, and I also see DuckDB performing about 5x faster than DataFusion. I have not spent time determining why this is the case yet.

The latest results are here. This is yet another half-finished project of mine, so I apologize for the lack of configuration info. I am mostly running with default configs.

https://sqlbenchmarks.io/sqlbench-h/results/env/workstation/sf10/single_node/

[waitingkuo]

the codes to reproduce the benchmark are here https://github.com/ClickHouse/ClickBench/tree/main/datafusion
The way to update the data in the clickbench website is to update the json file in the results folder and send a PR https://github.com/ClickHouse/ClickBench/tree/main/datafusion/results

The current result listed in the website was benchmarked by datafusion v11, which was release around 6 months ago.

to imporve:

• 1. use datafusion v18 to rerun the benchmark codes and send the PR. t

• 2. at the time I wrote the benchmark codes, datafusion didnt support some features. e.g. it didn't support schema from parquet, so for some data type like timestamp, we need to load it as string and then cast it to timestamp explicitly. e.g.

this is the original sql queries

SELECT URLHash, EventDate, COUNT(*) AS PageViews FROM ...

https://github.com/ClickHouse/ClickBench/blob/main/duckdb-parquet/queries.sql#L41

this is the modified version for datafusion

SELECT "URLHash", "EventDate"::INT::DATE, COUNT(*) AS PageViews FROM

https://github.com/ClickHouse/ClickBench/blob/main/datafusion/queries.sql#L41

I did modify some quries so that it works in datafusion. To fix this, we need to verify whether the new datafusion work or not. If so we could update the quries. If not, we could fire the issue to improve

I'll do the first option soon (update to v18), it should be a quick improvement.
It'll take more time to do the second approach. Welcome for the contribution. I'll get back to this if there's no one work on this.

[ozankabak]

@waitingkuo, if you create the issues that identify what is required to run the queries properly, we can help with addressing them.

[waitingkuo]

@ozankabak thank you, i'll submit another ticket to do so.

I just submit the pr to update it to v18
ClickHouse/ClickBench#77

here's the summary:

1 of the query get 20 times boost
1 of the query remain 0.1 times performance
1 of the query still not working

except from these 3 outliers, we have 20% improvement in average

the histogram chart
x axis means: old execution time / new execution time the larger the more improvement, 1.0 means no change

here's the result query by query

Query 0: 0.9776802049030369
Query 1: 1.707491082045184
Query 2: 1.2640586797066016
Query 3: 0.9203807875378623
Query 4: 0.9241755388210855
Query 5: 0.94172723106135
Query 6: 1.649497487437186
Query 7: 1.8705738705738708
Query 8: 0.9532640949554897
Query 9: 0.09891081294396213
Query 10: 0.9790727043117446
Query 11: 1.0668953687821612
Query 12: 1.0256627922162727
Query 13: 0.9520421488719772
Query 14: 1.0535746389404925
Query 15: 1.1839474435875463
Query 16: 1.2163045644807655
Query 17: 1.0196377825847225
Query 18: 1.2491737143968116
Query 19: 0.9741868869385649
Query 20: 1.2916241203256522
Query 21: 0.9167798624671026
Query 22: 0.9743192470077857
Query 23: 1.0809550243337944
Query 24: 1.0715070085911764
Query 25: 1.868698910081744
Query 26: 1.8450380677343134
Query 27: 0.8860306599462013
Query 28: 18.859884645982497
Query 29: 2.9667709147771695
Query 30: 1.0043050430504306
Query 31: 0.9922544851934578
Query 32: doesn't work
Query 33: 1.2395144767868875
Query 34: 1.2316816529558063
Query 35: 1.1153739994479712
Query 36: 0.9655502392344499
Query 37: 0.9329896907216495
Query 38: 0.979089790897909
Query 39: 1.0419161676646707
Query 40: 0.9942028985507246
Query 41: 1.028301886792453
Query 42: 1.039426523297491

outlier queries:

Query 9: 0.09891081294396213 -> 10 times worse

SELECT "RegionID", SUM("AdvEngineID"), COUNT(*) AS c, AVG("ResolutionWidth"), COUNT(DISTINCT "UserID") FROM hits GROUP BY "RegionID" ORDER BY c DESC LIMIT 10;

i have no clue for now, perhaps due to DISTINCT
@alamb @tustvold @comphead do you have any idea for this?

Query 28: 18.859884645982497 -> almost 20 times better

SELECT REGEXP_REPLACE("Referer", '^https?://(?:www\.)?([^/]+)/.*$', '\1') AS k, AVG(length("Referer")) AS l, COUNT(*) AS c, MIN("Referer") FROM hits WHERE "Referer" <> '' GROUP BY k HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;

i think this is benifit from the improvement of regexp_replace

Query 32: query doesn't work

SELECT "WatchID", "ClientIP", COUNT(*) AS c, SUM("IsRefresh"), AVG("ResolutionWidth") FROM hits GROUP BY "WatchID", "ClientIP" ORDER BY c DESC LIMIT 10;

when i run it, it's killed (same as the previous version)

willy@willybench:~/ClickBench/datafusion$ datafusion-cli -f create.sql q33.sql 
DataFusion CLI v18.0.0
0 rows in set. Query took 0.039 seconds.
Killed

[dudzicp]

With results from @andygrove being consistent with clickbench (~5x difference compared to duckdb), I think it make more sense to focus more on profiling to narrow the gap, rather than re running the benchmarks. Also when running the benchmarks, make sure you enable important config options mentioned by @korowa: #5142 (comment)

[comphead]

Query 9: 0.09891081294396213 -> 10 times worse

SELECT "RegionID", SUM("AdvEngineID"), COUNT(*) AS c, AVG("ResolutionWidth"), COUNT(DISTINCT "UserID") FROM hits GROUP BY "RegionID" ORDER BY c DESC LIMIT 10;
i have no clue for now, perhaps due to DISTINCT
@alamb @tustvold @comphead do you have any idea for this?

@waitingkuo I can try to experiment if this is due to distinct.

[ozankabak]

@waitingkuo, I have a few quick questions: (1) Which machine did your new results run on, is it the same machine? (2) Which features are missing to run the queries as is?

[waitingkuo]

@ozankabak i used azure's Standard_F16s_v2 vm to do so which has 16 vcpu and 32GB memory
https://learn.microsoft.com/en-us/azure/virtual-machines/fsv2-series

i didnt looks into the issue that the process killed yet. my quick guess is that it runs out of memory

[waitingkuo]

@comphead thank you
the quickest way is pull clickbench github repo and go tho this folder
https://github.com/ClickHouse/ClickBench/tree/main/datafusion

and then

# Download benchmark target data
wget --continue https://datasets.clickhouse.com/hits_compatible/hits.parquet
# launch datafusion-cli
datafusion-cli
DataFusion CLI v18.0.0
> CREATE EXTERNAL TABLE hits STORED AS PARQUET LOCATION 'hits.parquet';
> SELECT "RegionID", SUM("AdvEngineID"), COUNT(*) AS c, AVG("ResolutionWidth"), COUNT(DISTINCT "UserID") FROM hits GROUP BY "RegionID" ORDER BY c DESC LIMIT 10;

to run the full benchmark, you could do bash benchmark.sh which contains

1. install latest datafusion
2. download benchmark dataset
3. execute 3 times per query
   it take around 20-30 minutes for me for a full benchmark generation

[jhorstmann]

AFAIK one big difference regarding performance is that most other engines in the clickbench suite first load the data into memory or some other internal format, while datafusion scans directly from the parquet files. Extending the benchmark runner to support both use cases and report separate results would be very useful. The tcph benchmark runner for example has such a feature

[waitingkuo]

@jhorstmann thank you for pointing out this

some system like duckdb or clickbench does provide two kinds of setting. e.g.

the original comparison @dudzicp posted is to compare duckdb(parquet) with datafusion. I think it's fair enough. Perhaps we could rename datafusion to datafusion(parquet) so that other people could easily understanding what they're comparing. (btw, we can judge it by loading time and datasize as well)

[alamb]

Perhaps we could rename datafusion to datafusion(parquet) so that other people could easily understanding what they're comparing. (btw, we can judge it by loading time and datasize as well)

I think this would be very helpful. Yes I think we should be only comparing datafusion performance to parquet files