Update ClickBench benchmarks with DataFusion 51.0.0

[alamb]

Is your feature request related to a problem or challenge?

Follow on to

• Update ClickBench benchmarks with DataFusion 50.0.0 #17721
• Update ClickBench benchmarks with DataFusion 49.0.0 #16643
• Update ClickBench benchmarks with next DataFusion  #14587

Related to

• [EPIC] Make DataFusion the top of the ClickBench Parquet leaderboard #18489

Describe the solution you'd like

Now that DataFusion 51.0.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]

It looks like DataFusion currently has parquet partitioned runs for the following hardware

Here are the ones in the c6a class:

• c6a.4xlarge
• c6a.2xlarge
• c6a.xlarge
• c6a.large

Here is the c8g.4xlarge category
c8g.4xlarge

Some other interesting ones would be to run on t3a.small (2 vcpus)

• t3a.small

[alamb]

Looks like

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

https://github.com/ClickHouse/ClickBench/tree/main?tab=readme-ov-file#systems-included

Also note @pmcgleenon 's code: I've created some automation to run the clickbench tests.

I am going to run it manually on the c6a.4xlarge initially and update the instructions, etc. Then I will make the results for the other machine types

[alamb]

I am actively running / testing here. I will write up my findings hopefully later today

[alamb]

I spent quite a while today running DataFusion with the ClickBench scripts on a c6a.4xlarge VM to try and understand the performance results that @pmcgleenon was seeing

Here is all the raw data if you want to look at it yourself (open index.html)

• report.zip

Conclusions:

1. I can reproduce the lack of improvement between DataFusion 47 and 51 (not sure the root cause, yet)
2. Cache parquet metadata will improve performance by 20% (details below).
3. There is significant variance between runs -- I saw up to 7% variance between runs on DataFusion 51 on c6a.4xlarge.

My next steps will be to more carefully profile what is going on in datafusion 47 vs 51 for some of the queriers and see where the time is being spent

[alamb]

Background

The datafusion clickbench scripts build datafusion-cli like this: https://github.com/ClickHouse/ClickBench/tree/main/datafusion-partitioned

CARGO_PROFILE_RELEASE_LTO=true RUSTFLAGS="-C codegen-units=1" cargo build --release --package datafusion-cli --bin datafusion-cli

Then it clears the filesystem cache like this:

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

Then it runs the queries like this:

    ./datafusion/target/release/datafusion-cli -f create.sql  -f /tmp/query.sql

For example when running q0, the scripts look like this:

create.sql:

CREATE EXTERNAL TABLE hits
STORED AS PARQUET
LOCATION 'partitioned'
OPTIONS ('binary_as_string' 'true');

/tmp/query.sql

SELECT COUNT(*) FROM hits;

Implications:

1. Each query is run "cold" in the sense that datafusion-cli is started fresh and there is no metadata cache from previous runs. See notes below for how we could improve this if we wanted

[alamb]

Metadata caching study

Given the way the scripts run, the metadata cache is not used for each run.

I confirmed that metadata caching makes a pretty significant difference by "prewarming" the cache by adding select count(*) from hits; to create.sql.

diff --git a/datafusion-partitioned/create.sql b/datafusion-partitioned/create.sql
index 3b3330c1..a01baa64 100644
--- a/datafusion-partitioned/create.sql
+++ b/datafusion-partitioned/create.sql
@@ -2,3 +2,5 @@ CREATE EXTERNAL TABLE hits
 STORED AS PARQUET
 LOCATION 'partitioned'
 OPTIONS ('binary_as_string' 'true');
+-- prewarm metadata cache
+select count(*) from hits;
diff --git a/datafusion-partitioned/run.sh b/datafusion-partitioned/run.sh
index 0c5ed184..74bcb0c2 100755
--- a/datafusion-partitioned/run.sh
+++ b/datafusion-partitioned/run.sh
@@ -11,11 +11,11 @@ cat queries.sql | while read -r query; do

     echo -n "["
     for i in $(seq 1 $TRIES); do
-        # 1. there will be two query result, one for creating table another for executing the select statement
-        # 2. each query contains a "Query took xxx seconds", we just grep these 2 lines
-        # 3. use sed to take the second line
+        # 1. there will be three query result, one for creating table, one to load metadata cache, and another for executing the select statement
+        # 2. each query contains a "Query took xxx seconds", we just grep these 3 lines
+        # 3. use sed to take the third line
         # 4. use awk to take the number we want
-        RES=$(datafusion-cli -f create.sql /tmp/query.sql 2>&1 | grep "Elapsed" |sed -n 2p | awk '{ print $2 }')
+        RES=$(datafusion-cli -f create.sql /tmp/query.sql 2>&1 | grep "Elapsed" |sed -n 3p | awk '{ print $2 }')
         [[ $RES != "" ]] && \
             echo -n "$RES" || \
             echo -n "null"

Making this change results in a 20% benchmark improvement for DataFusion 51 on c6a.4xlarge

[alamb]

Variance study

I also ran the benchmark for DataFusion 51 5 times in a row to spot check the variance between runs. I ran the same benchmark with the same script on the same machine type like this:

# Test variance by running the benchmark for DataFusion 51 5 times in a row
rm -f result.csv && bash benchmark.sh && ./save-result.sh 51-run1-c6a.4xlarge
rm -f result.csv && bash benchmark.sh && ./save-result.sh 51-run2-c6a.4xlarge
rm -f result.csv && bash benchmark.sh && ./save-result.sh 51-run3-c6a.4xlarge
rm -f result.csv && bash benchmark.sh && ./save-result.sh 51-run4-c6a.4xlarge
rm -f result.csv && bash benchmark.sh && ./save-result.sh 51-run5-c6a.4xlarge

My results varied by 7% between runs:

[Omega359]

Variance study

My results varied by 7% between runs

EBS? We've never found it to be very stable performance wise.

[alamb]

Variance study

My results varied by 7% between runs

EBS? We've never found it to be very stable performance wise.

Yeah, according to https://github.com/ClickHouse/ClickBench?tab=readme-ov-file#systems-included

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

Which is what I ran my tests on too

[alamb]

I did some profiling on q0:

ubuntu@ip-172-31-45-69:~/ClickBench/datafusion-partitioned$ cat create.sql 
CREATE EXTERNAL TABLE hits
STORED AS PARQUET
LOCATION 'partitioned'
OPTIONS ('binary_as_string' 'true');

ubuntu@ip-172-31-45-69:~/ClickBench/datafusion-partitioned$ cat q0.sql
SELECT COUNT(*) FROM hits;
Then run
datafusion-cli -f create.sql -f q0.sql

And then looked at it with samply like this to mimic a "warm" run

ubuntu@ip-172-31-45-69:~/ClickBench/datafusion-partitioned$ datafusion-cli -f create.sql -f q0.sql && samply record datafusion-cli -f create.sql -f q0.sql

My analysis is that 75% of the time is spent calculating statistics

And in fact if you run the query again after having run (and collected statistics initially):

ubuntu@ip-172-31-45-69:~/ClickBench/datafusion-partitioned$ datafusion-cli -f create.sql -f q0.sql && samply record datafusion-cli -f create.sql -f q0.sql

The second run goes 10x faster(3ms vs 30ms)

+----------+
| count(*) |
+----------+
| 99997497 |
+----------+
1 row(s) fetched. 
Elapsed 0.031 seconds.

+----------+
| count(*) |
+----------+
| 99997497 |
+----------+
1 row(s) fetched. 
Elapsed 0.003 seconds.

I'll mess around with the code and see if there is anything I can do to improve this (I suspect there is)

[alamb]

Something seems off however, as according to https://datafusion.apache.org/user-guide/configs.html

datafusion.execution.collect_statistics	true	Should DataFusion collect statistics when first creating a table. Has no effect after the table is created. Applies to the default ListingTableProvider in DataFusion. Defaults to true.

In other words the statistics should have been gathered as part of the CREATE TABLE statement, but they appear to be collected as part of running the first COUNT(*) for some reason 🤔