Update ClickBench benchmarks with DataFusion 50.0.0

Is your feature request related to a problem or challenge?

Follow on to

Requires

Release DataFusion 50.0.0 (Aug/Sep 2025) #16799

Describe the solution you'd like

Now that DataFusion 500.0 is released, It would be great to update ClickBench https://benchmark.clickhouse.com/ with the newest version and highlight and improvement

ClickBench is a benchmark heavy on filtering and aggregation that we have used as an optimization target for the last several releases.

Describe alternatives you've considered

No response

Additional context

No response

@alamb

Now that there are multiple instance types for datafusion, I've created some automation to run the clickbench tests. If anyone would like to try it out, I would be interested in your feedback!

Originally the ClickBench datafusion tests only used 1 instance type: c6a.4xlarge
Recently the ClickHouse team added new instance types into the results for datafusion c6a.xlarge, c6a.2xlarge and c8g.4xlarge

The instance type c6a.xlarge only has 8GB RAM (the clickbench dataset is 15GB) and there are a number of issues with it, including

datafusion compilation fails on this instance. We can workaround this by using brew install
OOM errors happens during test execution, with results reported as null for several tests
the machine becomes unresponsive, with ssh and shell not working during test execution

I see two options here:

report results for the c6a.xlarge and use the brew install workaround to get around the compilation issues. In this case some of the results will be null. I didn't see a way to specify a particular datafusion version with brew install, so it will always pick up the latest version (currently 50.3.0)
remove c6a.xlarge from the results until datafusion becomes functional on the 8GB RAM machine. We would have the option to compile datafusion with target-cpu=native to squeeze out some more performance

I think we should avoid reporting results for c6a.xlarge until the issues are resolved, particularly since there is such a negative impact on the server when running the tests

@alamb @Dandandan and everyone else - interested in your opinion on the way forward here

@pmcgleenon

First of all, thank you @pmcgleenon for looking into this. I keep dreaming about the next generation of ClickBench numbers but haven't had a chance to comment recently

I think we should avoid reporting results for c6a.xlarge until the issues are resolved, particularly since there is such a negative impact on the server when running the tests

@alamb @Dandandan and everyone else - interested in your opinion on the way forward here

I think this makes sense to me. I will file a ticket about the issue too (and hopefully try and reproduce)

I will file a ticket about the issue too (and hopefully try and reproduce)

I filed some tickets:

@pmcgleenon

@pmcgleenon do you think you will be able to run ClickBench on DataFusion 50 (for the instances where it works) any time soon?

(people are asking me about what DataFusion's performance looks like)

@alamb

@alamb I'm hoping to run this over the next couple of days, I'll post any initial results here so you can take a look

@alamb I'm hoping to run this over the next couple of days, I'll post any initial results here so you can take a look

THANK YOU 🙏

@alamb

@alamb here are the initial results. report.zip

I've added them to the Clickbench html page so they can be compared with the previous version "DataFusion-old". version 47 looks to be the previous version tested. I needed to Select Metric "Hot Run" to get the hot run results

For partitioned, the results are similar to the previous version for c8g.4xlarge and c6a.x4large
For single, v50 looks faster overall

Q23 looks a lot slower on c6a.2xlarge compared to the other instances for both single and partitioned files. I didn't analyse it but it seems like it could be hitting the memory limit on the 16GB RAM machine and works better on the higher spec machines

Let me know how these look, if anything looks weird shout out and I can rerun it. If it seems ok I can create a PR for the clickbench repository

Am I reading this correctly?

This would suggest mostly regressions, including in Q23 which is very surprising to me

This would suggest mostly regressions, including in Q23 which is very surprising to me

Yes indeed this is also surprising to me

Q23 is

SELECT * FROM hits WHERE "URL" LIKE '%google%' ORDER BY "EventTime" LIMIT 10;

Namely the query that benefits the most from the new dynamic filter pushdown

In my unscientific experiments locally (Mac M3) I see q23 going significantly faster on 50.3.0 than 47.0.0:

time ~/Software/datafusion-cli/datafusion-cli-47.0.0 -c "SELECT * FROM hits WHERE \"URL\" LIKE '%google%' ORDER BY \"EventTime\" LIMIT 10;"

0m5.377s
0m4.507s
0m5.144s

time ~/Software/datafusion-cli/datafusion-cli-50.3.0 -c "SELECT * FROM hits WHERE \"URL\" LIKE '%google%' ORDER BY \"EventTime\" LIMIT 10;"

0m5.016s
0m3.652s
0m3.927s

Yeah I would expect it to be 10-15% faster on a multi-core machine without filter pushdown, congruent with the blog post:

did you select Metric = Hot Run? The default for metric is Combined, which I think is a mixture of Hot run + cold run.

For partitioned parquet files on c6a.4xlarge the overall result looks the same as the previous version

Q23 is faster in v50, along with Q24 and Q26

I ran Q23 a few times on a c6a.4xlarge instance and got these results

# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
9.190
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
9.106
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
9.310
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
9.076

The grep Elapsed is taking the two Elapsed times from the output and sed -n 2p takes the second from the CLI output

datafusion-cli -f create.sql q23.sql |grep Elapsed
Elapsed 0.042 seconds.
Elapsed 9.186 seconds.

If you are seeing different results on the ClickBench AWS instance types I'm happy to re-run any of these

quick update on this, after enabling the configuration mentioned in the blog there is an improvement for Q23 from 9s to 0.8

export DATAFUSION_EXECUTION_PARQUET_REORDER_FILTERS='true';
export DATAFUSION_OPTIMIZER_ENABLE_DYNAMIC_FILTER_PUSHDOWN='true';
export DATAFUSION_EXECUTION_PARQUET_PUSHDOWN_FILTERS='true';

# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
0.801
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
0.798
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
0.814
# datafusion-cli -f create.sql q23.sql 2>^1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }'
0.819

Is this configuration we'd want to enable by default?

@alamb

Is this configuration we'd want to enable by default?

I don't know what the logistics are on this... my understanding is that ClickBench wants engines to run "default" configs, which means that in reality we would have to make it the default for everyone. And currently it's something like 10x faster in a lot of cases but 2x slower in some cases... @alamb is working hard on getting to be the default everywhere, but I don't know if we are ready yet.

I think it's okay if Q23 is only 15% faster for now and we make it 10x faster next release or something. As long as it's not a regression...

@hhhizzz

Is this configuration we'd want to enable by default?

I don't know what the logistics are on this... my understanding is that ClickBench wants engines to run "default" configs, which means that in reality we would have to make it the default for everyone.

Yes, that is my understanding too (it is at least the spirit of the benchmark)

Currently it's something like 10x faster in a lot of cases but 2x slower in some cases... @alamb is working hard on getting to be the default everywhere, but I don't know if we are ready yet.

Yeah, actually i am working with @hhhizzz on this in apache/arrow-rs#8733 (well, I am reviewing and @hhhizzz is doing all the work)

I think it's okay if Q23 is only 15% faster for now and we make it 10x faster next release or something. As long as it's not a regression...

Yes this is my thinking too.

I will try and run some queries manually on c6a.4xlarge and see if I can reproduce the results

(interesting to me is that c6a.2xlarge is a graviton processor, which are not known for being the fastest CPUs around)

https://github.com/ClickHouse/ClickBench?tab=readme-ov-file says

By default, all tests are run on c6a.4xlarge VM in AWS with 500 GB gp2.

The docs about gp2 say their max throughput is 250MB/sec

https://docs.aws.amazon.com/ebs/latest/userguide/ebs-volume-types.html

So doing some more IO optimization may improve things. I'll play around more and report

My manual results are consistent with my local timings -- 47 takes around 9.8 seconds for Q23 and 50 takes around 8.5 seconds.

Flushing caches:

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

run in loop 5 times:

for i in seq 1 5 ; do ~/datafusion-50/target/release/datafusion-cli -f create.sql q23.sql | grep Elapsed ; done

Elapsed 0.131 seconds.
Elapsed 106.409 seconds.
Elapsed 0.041 seconds.
Elapsed 8.568 seconds.
Elapsed 0.041 seconds.
Elapsed 8.407 seconds.
Elapsed 0.041 seconds.
Elapsed 8.697 seconds.
Elapsed 0.041 seconds.
Elapsed 8.546 seconds.

Now with datafusion 47

# Flushing caches:
sync
echo 3 | sudo tee /proc/sys/vm/drop_caches >/dev/null
# run in loop 5 times:
for i in `seq 1 5` ; do ~/datafusion-47/target/release/datafusion-cli -f create.sql q23.sql  | grep Elapsed ; done

Elapsed 0.103 seconds.
Elapsed 111.752 seconds.
Elapsed 0.039 seconds.
Elapsed 9.922 seconds.
Elapsed 0.040 seconds.
Elapsed 9.869 seconds.
Elapsed 0.040 seconds.
Elapsed 9.804 seconds.
Elapsed 0.040 seconds.
Elapsed 9.858 seconds.

I am going to try and rebuild the binaries a little differently and see if that makes a difference

(interesting to me is that c6a.2xlarge is a graviton processor, which are not known for being the fastest CPUs around)

Um, no? "Amazon EC2 C6a instances are powered by 3rd generation AMD EPYC processors"

https://aws.amazon.com/ec2/instance-types/c6a/

🤔 that is what I get from doing too many things at a time

I have no idea where I got it into my head that the instances were graviton

@pmcgleenon

@pmcgleenon can you maybe run (if it is not too much) the benchmarks for 48 and 49 to see roughly where is the regression?

Since we have now released DataFusion 51, let's focus on that one:

Update ClickBench benchmarks with DataFusion 51.0.0 #18909

I'll see if I can run it sometime this week

Update here is I was able to reproduce the (lack) of improvement between 47-51 -- I spent quite a while analyzing queries today. Updates here: #18909 (comment)

Navigation Menu

Repository navigation

Update ClickBench benchmarks with DataFusion 50.0.0 #17721

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Flushing caches:

run in loop 5 times:

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Update ClickBench benchmarks with DataFusion 50.0.0 #17721

Description

Is your feature request related to a problem or challenge?

Describe the solution you'd like

Describe alternatives you've considered

Additional context

Activity

pmcgleenon commented on Nov 1, 2025

alamb commented on Nov 3, 2025

alamb commented on Nov 3, 2025

alamb commented on Nov 4, 2025

alamb commented on Nov 4, 2025

pmcgleenon commented on Nov 4, 2025

alamb commented on Nov 4, 2025

pmcgleenon commented on Nov 6, 2025

adriangb commented on Nov 6, 2025

alamb commented on Nov 6, 2025

adriangb commented on Nov 6, 2025

pmcgleenon commented on Nov 6, 2025

pmcgleenon commented on Nov 7, 2025

adriangb commented on Nov 7, 2025

alamb commented on Nov 7, 2025

alamb commented on Nov 7, 2025

alamb commented on Nov 7, 2025

alamb commented on Nov 7, 2025

alamb commented on Nov 7, 2025

Flushing caches:

run in loop 5 times:

Omega359 commented on Nov 7, 2025

alamb commented on Nov 10, 2025

rluvaton commented on Nov 15, 2025

alamb commented on Nov 24, 2025

alamb commented on Nov 24, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions