Update ClickBench benchmarks with DataFusion 52.0.0

[waynexia]

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
• Update ClickBench benchmarks with DataFusion 51.0.0 #18909

Describe the solution you'd like

Now that DataFusion 52.0.0 is released, It would be great to update ClickBench https://benchmark.clickhouse.com/ with the newest version and highlight and improvement. The current result is still of version 47.0.0

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

[waynexia]

I've updated both datafusion and datafusion-partitioned for four types of instances, PR ClickHouse/ClickBench#749.

For 4xlarge instances, almost all cases are improved, but I observed some regression in small instances (2x or x), like q3 q4 q5 now needs 2~3 seconds to run (here).

[waynexia]

I prefer to update those results as-is, as at least all cases can run on every instance (xlarge has swap space), and the current result is a bit outdated. What do you think? cc @alamb @Omega359

[Omega359]

I am not familiar enough with clickbench to have an opinion on what 'update the results as-is' means. I believe @pmcgleenon was the last person to update the clickbench results, perhaps he would be able to review?

[pmcgleenon]

@Omega359 it looks good to me. Nice work @waynexia !

There's a lot of data now with the 4 instance types, so I fired the before and after results into claude to see if we can get an overview of what's going on

I didn't see any null results in the c6a.xlarge this time, did we already make some improvements to run on a low memory system?

================================================================================
DATAFUSION PERFORMANCE DELTA: v47.0 → v52.0
================================================================================

Format: Cold Δ% | Hot Δ%
Negative % = faster (improvement)
Positive % = slower (regression)

================================================================================
DATAFUSION SINGLE (Parquet, single)
================================================================================

Query    c6a.2xlarge              c6a.4xlarge              c6a.xlarge               c8g.4xlarge
-------------------------------------------------------------------------------------------------------------------------
Q0        -47.9% |  -97.8%         -36.7% |  -98.3%         -37.4% |  -97.9%         -37.5% |  -97.7%
Q1        -16.3% |  -53.1%         -18.1% |  -57.9%         -24.1% |  -45.4%         -26.5% |  -62.9%
Q2        -17.0% |  -36.3%         -16.1% |  -48.0%         -17.7% |  -29.9%         -10.2% |  -40.1%
Q3       +377.2% |  -31.2%          -3.9% |  -40.5%        +197.0% |  -17.3%          +4.7% |  -38.6%
Q4        +76.6% |  -20.5%          +1.1% |  -13.8%         +11.3% |  -20.8%          +7.8% |  -26.1%
Q5        +95.0% |   +1.7%          +2.9% |   +1.3%         +20.5% |   +6.7%          -1.0% |   -2.3%
Q6        -50.9% |  -98.2%         -40.4% |  -98.5%         -65.3% |  -98.6%         -52.9% |  -98.0%
Q7        -30.1% |  -55.4%         -22.5% |  -58.2%         -39.2% |  -55.6%         -39.1% |  -65.5%
Q8        +49.8% |  -14.0%          -4.3% |   -4.8%          -7.4% |  -24.6%          -6.8% |   -8.4%
Q9        +74.0% |   -8.0%          +3.6% |   -7.3%          -4.2% |  -24.1%          +5.5% |   +4.7%
Q10      +154.2% |  -36.9%         -10.9% |  -29.5%         +75.8% |  -31.6%          -7.8% |  -31.3%
Q11      +184.5% |  -38.7%          +9.3% |  -30.3%         +61.9% |  -43.8%         +14.2% |  -28.7%
Q12      +109.0% |  -11.7%          +7.1% |   -2.2%         +10.6% |  -17.4%         +13.7% |  -11.5%
Q13       +95.0% |  -14.3%         -10.7% |  -10.7%         +49.4% |  -21.5%          -6.8% |  -22.0%
Q14      +109.5% |  -10.9%          -2.3% |   -2.4%         +16.7% |  -17.5%          -6.1% |  -11.8%
Q15       +13.9% |  -27.5%          -9.0% |  -17.1%         -29.3% |  -37.8%         -10.1% |  -22.3%
Q16       +87.9% |  -15.0%          +0.2% |   -5.8%         +14.4% |  -24.6%          -3.0% |  -19.2%
Q17       +97.4% |   -1.6%          -1.3% |   -3.4%         +38.6% |   -3.3%          -1.7% |  -12.8%
Q18       +78.6% |  -15.4%          +0.2% |   -3.6%        +160.6% |    N/A           +0.3% |  -12.2%
Q19      +344.0% |  -31.4%         -11.0% |  -40.9%        +146.2% |  -18.2%         -21.5% |  -36.2%
Q20      +109.5% |   +5.7%          +1.6% |   -8.2%        +114.8% |  +10.4%          +1.2% |   -4.9%
Q21      +107.0% |   +0.9%          -0.4% |   -7.4%        +107.7% |   -7.0%          -0.4% |   -8.9%
Q22      +103.2% |  +57.7%          +0.4% |  +15.4%        +103.4% | +126.1%          -0.0% |  -10.7%
Q23      +100.5% | +117.1%          -0.0% |   -3.7%        +101.7% | +111.0%          -0.0% |  -10.1%
Q24      +129.1% |   -4.7%          -0.6% |   -9.6%        +132.8% |   -7.7%          +0.0% |  -22.2%
Q25      +193.5% |   -9.3%          -4.8% |  -10.7%         +66.5% |  -12.9%         +17.4% |  -32.1%
Q26      +127.9% |  -11.6%          +0.1% |  -18.4%        +130.6% |  -18.3%          +9.3% |  -27.3%
Q27      +111.3% |  -15.1%          +0.9% |  -25.7%        +111.0% |  -27.3%          +2.0% |  -16.7%
Q28        -2.1% |  -12.7%          -4.1% |   -6.8%         -23.2% |  -24.3%          +3.6% |   -5.8%
Q29        -7.8% |   -2.6%          -6.9% |  -10.7%          -4.4% |   -4.2%          -7.1% |   -9.4%
Q30      +167.0% |   -2.8%         +21.4% |   -2.4%        +110.8% |  -18.6%         +22.7% |  -10.0%
Q31      +131.3% |   -0.5%          +7.7% |   -7.0%        +127.5% |  -14.1%          +8.1% |   -5.6%
Q32      +114.2% |  -10.0%          +6.0% |   -2.7%         +91.2% |    N/A           +6.9% |   -6.1%
Q33       +93.3% |  -12.1%          -0.4% |   -9.5%           N/A  |    N/A           -0.9% |   -8.2%
Q34       +93.3% |  -13.2%          -0.2% |  -10.1%           N/A  |    N/A           +0.3% |   -8.2%
Q35       -14.7% |  -16.1%          -4.3% |   -9.0%         -24.6% |  -29.8%          -5.6% |   -5.1%
Q36       -14.0% |  -18.0%         -12.7% |  -31.8%         -14.1% |  -32.1%         -19.3% |  -26.0%
Q37       -13.2% |  +12.2%          -5.2% |   +0.0%          -8.5% |   -6.1%         -31.1% |  -35.2%
Q38        -8.3% |  -27.2%         -14.6% |  -37.0%         -14.9% |  -27.6%          -9.6% |  -29.4%
Q39        +9.2% |  -10.2%          -6.1% |  -22.3%         -11.0% |  -18.0%          -1.5% |  -18.9%
Q40       -14.5% |  -53.7%         -22.2% |  -49.2%         -24.9% |  -54.5%         -23.3% |  -49.6%
Q41       -23.9% |  -52.0%         -29.3% |  -55.3%         -27.6% |  -59.8%         -26.4% |  -45.5%
Q42       -22.1% |  -56.6%         -26.6% |  -55.4%         -23.8% |  -58.6%         -29.2% |  -54.1%


================================================================================
DATAFUSION PARTITIONED (Parquet, partitioned)
================================================================================

Query    c6a.2xlarge              c6a.4xlarge              c6a.xlarge               c8g.4xlarge
-------------------------------------------------------------------------------------------------------------------------
Q0        -23.5% |  -90.5%         -27.6% |  -87.5%         -38.7% |  -94.2%         -30.9% |  -81.8%
Q1        -29.9% |  -34.2%         -29.3% |  -34.7%         -25.8% |  -35.4%         -37.1% |  -17.5%
Q2       +162.4% |  -21.6%         -11.5% |  -24.4%         +69.7% |  -24.3%         -24.1% |   -8.8%
Q3       +418.9% |   -2.7%         +43.0% |   -9.8%        +275.6% |  -13.2%         +90.7% |  -16.2%
Q4        +77.7% |  -16.9%         +10.3% |   -9.2%          +6.8% |  -24.0%         +20.2% |  -22.9%
Q5        +81.3% |   +0.5%          +8.4% |   -3.8%         +28.4% |  -18.2%          +7.9% |   +8.6%
Q6        -57.7% |  -94.7%         -37.2% |  -92.3%         -67.7% |  -96.9%         -47.0% |  -89.5%
Q7        -30.4% |  -36.3%         -20.0% |  -33.8%         -29.9% |  -48.6%         -37.4% |  -18.2%
Q8        +73.2% |  -14.3%          -1.5% |   -4.3%          -2.5% |  -25.3%          +9.9% |  +00.2%
Q9       +106.2% |   -6.5%         +27.0% |   -2.8%         +13.5% |  -24.4%         +29.1% |  +15.2%
Q10      +169.5% |  -29.2%         +11.9% |  -18.5%         +85.7% |  -39.5%         +13.0% |  -22.8%
Q11      +167.1% |  -31.2%         +25.4% |  -14.3%         +67.6% |  -44.1%         +28.0% |  -23.8%
Q12       +90.9% |  -14.4%          +8.9% |   -9.3%         +15.2% |  -34.1%         +17.6% |   -5.8%
Q13       +98.7% |  -14.8%          -4.0% |   -7.3%         +46.1% |  -40.2%          +0.4% |   -3.0%
Q14       +99.1% |  -13.9%          +4.7% |   -4.8%         +26.9% |  -31.0%          +5.8% |  -11.5%
Q15       +38.9% |  -19.3%          -4.3% |  -12.6%         -28.0% |  -35.2%          +5.8% |  -19.8%
Q16       +93.1% |  -16.5%          +2.4% |   -6.6%         +16.4% |  -31.9%          +3.4% |  -10.5%
Q17      +111.6% |   -8.3%          +2.7% |   -3.5%         +37.7% |  -15.3%          +3.9% |   -3.2%
Q18       +85.9% |  -13.7%          +6.3% |   -3.5%           N/A  |    N/A           +8.2% |  -10.5%
Q19      +185.8% |   -9.5%          +5.8% |  -12.3%        +141.6% |  -11.2%          -3.5% |   -8.4%
Q20      +116.6% |   -6.0%          +2.4% |  -11.5%        +111.5% |  -12.0%          +2.3% |   +3.1%
Q21      +109.5% |  -11.2%          +1.2% |  -10.6%        +111.6% |  -25.9%          +0.9% |   +3.5%
Q22      +106.1% |  -17.6%          +0.8% |  -10.3%        +106.9% | +111.8%          +0.9% |   -6.6%
Q23       +96.0% | +103.0%          -4.8% |  -17.8%         +95.9% | +104.8%          -2.9% |  -17.2%
Q24       -47.9% |  -73.9%          -4.9% |  -70.1%         -54.4% |  -84.3%         -15.7% |  -64.7%
Q25      +228.5% |  -41.2%         +50.5% |  -37.2%        +122.4% |  -51.0%         +65.5% |  -30.8%
Q26       -45.7% |  -78.5%         -16.5% |  -75.9%         -54.4% |  -85.3%         -80.3% |  -65.6%
Q27      +118.9% |  -17.8%          +6.8% |  -12.7%        +121.4% |  -31.5%          +8.1% |   -9.6%
Q28        -0.5% |  -14.6%          -1.6% |   -9.0%         -23.0% |  -24.7%          -3.3% |   -7.5%
Q29        -9.9% |   -5.5%          -5.4% |   -1.5%          -7.3% |   -4.9%          -9.1% |   -8.4%
Q30      +192.3% |  -16.0%         +30.9% |   -3.6%         +95.0% |  -19.5%         +29.7% |   +3.0%
Q31      +142.4% |   -8.9%         +12.6% |   -1.9%        +140.6% |  -18.3%         +13.0% |   +3.1%
Q32      +126.3% |   -7.9%          +9.7% |   -1.9%           N/A  |    N/A          +11.0% |   -7.3%
Q33      +100.5% |  -13.8%          +0.1% |   -4.9%           N/A  |    N/A           +0.7% |   -5.3%
Q34      +101.4% |  -13.9%          +1.4% |   -5.0%           N/A  |    N/A           +0.3% |  -13.6%
Q35       -11.6% |   -9.1%          -6.3% |   -6.8%         -22.9% |  -35.5%         -11.3% |   -2.6%
Q36       -51.7% |  -54.3%         -48.9% |  -51.8%         -48.9% |  -57.5%         -48.6% |  -47.7%
Q37       -41.9% |  -35.1%         -36.0% |  -32.7%         -41.8% |  -47.7%         -36.1% |  -19.5%
Q38       -45.4% |  -47.3%         -48.6% |  -47.2%         -48.0% |  -59.4%         -47.0% |  -47.4%
Q39        -1.3% |  -52.1%         -50.3% |  -50.6%         +23.8% |  -62.6%         -46.8% |  -43.8%
Q40       -39.3% |  -54.2%         -49.2% |  -48.2%          -9.3% |  -62.0%         -51.8% |  -44.6%
Q41       -28.5% |  -54.3%         -46.2% |  -56.1%         -21.1% |  -70.8%         -49.3% |  -50.0%
Q42       -46.0% |  -57.6%         -37.1% |  -55.6%         -38.4% |  -70.0%         -41.7% |  -46.2%

[waynexia]

I didn't see any null results in the c6a.xlarge this time, did we already make some improvements to run on a low memory system?

I'm not very sure if I disabled swap (for building datafusion) before running queries 🙈 re-running on xlarge to double check...

[waynexia]

It turns out that I forgot to disable swap for queries... q18 (here) will be killed without swap memory 🥲

[pmcgleenon]

It turns out that I forgot to disable swap for queries... q18 (here) will be killed without swap memory 🥲

That's the danger of using swap. From what I understand swap is needed to compile datafusion-cli on the c6a.xlarge instance type which only has 8GB of RAM. I think we should remove c6a.xlarge from the Clickbench results. It can be added back in when the underlying issues get resolved

Any thoughts @alamb @Omega359 @waynexia ?

[waynexia]

I think we should remove c6a.xlarge from the Clickbench results. It can be added back in when the underlying issues get resolved

+1. On the other hand, c6a.xlarge takes lots of time to run (compile + bench). For what I got this time, c6a.xlarge and c6a.2xlarge are reflecting a similar trend, keeping only c6a.2xlarge and above looks good to me.

[alamb]

I prefer to update those results as-is, as at least all cases can run on every instance (xlarge has swap space), and the current result is a bit outdated. What do you think? cc @alamb @Omega359

I agree we should update the results to reflect the current state of DataFusion. If the results are poor or regressed on the smaller instances, I think it is fine to post those results here (and then we can file tickets to improve the results in subsequent runs)

That's the danger of using swap. From what I understand swap is needed to compile datafusion-cli on the c6a.xlarge instance type which only has 8GB of RAM. I think we should remove c6a.xlarge from the Clickbench results. It can be added back in when the underlying issues get resolved

I personally think we should leave them in (it will make it clearer where we need to work to improve)

[alamb]

For 4xlarge instances, almost all cases are improved, but I observed some regression in small instances (2x or x), like q3 q4 q5 now needs 2~3 seconds to run (here).

@waynexia I suspect this is a result of using more memory somehow (and this swapping more).

What I think need to happen is that DataFusion's "out of core" story needs to improve (aka we shouldn't need swap space, we should be using the memory manager and spill mechanism).

However, when I last tried this I encountered issues running some of the queries

• Running Clickbench query 18 when spilling fails with "failed to fill whole buffer" error #19425

[alamb]

So what I think we should do is

1. "publish the numbers"
2. Then work in parallel on improving performance for the "can't fit everything into RAM" case