Update ClickBench benchmarks with DataFusion 44.0.0

[alamb]

Is your feature request related to a problem or challenge?

• Like Update ClickBench benchmarks with DataFusion 43.0.0 #13099
• Related to Release DataFusion 44.0.0 #13334

Describe the solution you'd like

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

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

• Here is the PR that @pmcgleenon made for DataFusion 43.0.0: updated for datafusion release 43.0.0 ClickHouse/ClickBench#251

Additional context

I am especially interested to see the improvements after the vectorized comparison from @Rachelint @jayzhan211 @Dandandan and others in DataFusion aggregate code

• Support vectorized append and compare for multi group by #12996

[alamb]

Someone pointed out to me the other day that DataFusion 43 is no longer on top of the ClickBench Parquet Leaderboard

(actually it was one of the people who has spent substntial time optimizing Hyper...)

Thus I think it is that much more valuable to get some DataFusion 44 numbers on the board

[alamb]

I also filed a ticket to track running clickbench on DataFusion 45 once that is released in a few weeks

• Update ClickBench benchmarks with DataFusion 45.0.0 (When Published) #14246

[Rachelint]

I think Q8, Q16~18, Q35 can be closer to hyper in 44.0, they are improved in #12996
And Q35 can be even much faster when #13617 is merged (unfortunately, it can just be released in 46.0 for my long delay recently...)

But Q23 is unbelievalbely fast in hyper... I think we may need to profile and think how can we improve it.

[alamb]

I think Q8, Q16~18, Q35 can be closer to hyper in 44.0, they are improved in #12996 And Q35 can be even much faster when #13617 is merged (unfortunately, it can just be released in 46.0 for my long delay recently...)

But Q23 is unbelievalbely fast in hyper... I think we may need to profile and think how can we improve it.

I agree -- in case anyone else wants to see hyper reported 5x faster than DataFusion and 6x faster than DuckDB

I think this is Q23

datafusion/benchmarks/queries/clickbench/queries.sql

Line 22 in 11b7b5c

SELECT "SearchPhrase", MIN("URL"), COUNT(*) AS c FROM hits WHERE "URL" LIKE '%google%' AND "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10;

SELECT "SearchPhrase", MIN("URL"), MIN("Title"), COUNT(*) AS c, COUNT(DISTINCT "UserID") FROM hits WHERE "Title" LIKE '%Google%' AND "URL" NOT LIKE '%.google.%' AND "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10;

Profiling it like this:

$ datafusion-cli -c "SELECT \"SearchPhrase\", MIN(\"URL\"), MIN(\"Title\"), COUNT(*) AS c, COUNT(DISTINCT \"UserID\") FROM hits_partitioned WHERE \"Title\" LIKE '%Google%' AND \"URL\" NOT LIKE '%.google.%' AND \"SearchPhrase\" <> '' GROUP BY \"SearchPhrase\" ORDER BY c DESC LIMIT 10;"

26% of the time goes to snappy decompression and 40% of the time to utf8 validation:

Here is the full flamegraph.svg

So by my calculations the snappy decompression time alone in DataFusion (0.26 * 10.28s = 2.6s) takes longer than the hyper reported time of 1.8s 😕

[alamb]

If we wanted to juice our numbers we could turn off ut8 validation too but I feel like that would be cheating (as most/many systems would never run without validtion on)

[Dandandan]

Q23 might be improved if it can utilize filter pushdown? I think a >5x improvement might come from that.

[alamb]

Q23 might be improved if it can utilize filter pushdown? I think a >5x improvement might come from that.

Running without filter pushdown (the default)

set datafusion.execution.parquet.pushdown_filters = false;

SELECT "SearchPhrase", MIN("URL"), MIN("Title"), COUNT(*) AS c, COUNT(DISTINCT "UserID") FROM hits_partitioned WHERE "Title" LIKE '%Google%' AND "URL" NOT LIKE '%.google.%' AND "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10;

I get:

Elapsed 2.232 seconds.
Elapsed 2.252 seconds.
Elapsed 2.236 seconds.

When I enabled filter pushdown it goes 15% faster.

set datafusion.execution.parquet.pushdown_filters = true;

SELECT "SearchPhrase", MIN("URL"), MIN("Title"), COUNT(*) AS c, COUNT(DISTINCT "UserID") FROM hits_partitioned WHERE "Title" LIKE '%Google%' AND "URL" NOT LIKE '%.google.%' AND "SearchPhrase" <> '' GROUP BY "SearchPhrase" ORDER BY c DESC LIMIT 10;

I get:
Elapsed 1.981 seconds.
Elapsed 1.953 seconds.
Elapsed 1.966 seconds.

Still not 5x though 🤔

Though it gives me new motivation tohelp @XiangpengHao get the pushdown improvements over the line in

apache/arrow-rs#6921

[Rachelint]

@alamb 🤔 Q23 seems to be SELECT * FROM hits WHERE "URL" LIKE '%google%' ORDER BY to_timestamp_seconds("EventTime") LIMIT 10 ?

[alamb]

@alamb 🤔 Q23 seems to be SELECT * FROM hits WHERE "URL" LIKE '%google%' ORDER BY to_timestamp_seconds("EventTime") LIMIT 10 ?

🤔 you are right indeed 🤦 -- sorry about that (I went the wrong direction)

datafusion/benchmarks/queries/clickbench/queries.sql

Line 24 in 11b7b5c

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

I will profile that and report back

[alamb]

And in this case enabling predicate pushdown results in a 2x speedup

set datafusion.execution.parquet.pushdown_filters = false;
SELECT * FROM hits_partitioned WHERE "URL" LIKE '%google%' ORDER BY to_timestamp_seconds("EventTime") LIMIT 10;

Elapsed 4.108 seconds.
Elapsed 5.430 seconds.
Elapsed 4.659 seconds.

set datafusion.execution.parquet.pushdown_filters = true;
SELECT * FROM hits_partitioned WHERE "URL" LIKE '%google%' ORDER BY to_timestamp_seconds("EventTime") LIMIT 10;

Elapsed 2.415 seconds.
Elapsed 2.070 seconds.
Elapsed 2.279 seconds.

Here is the flamegraph for no pushdown:

It would be cool to test with @XiangpengHao 's change to the parquet decoder here:

• Experimental parquet decoder with first-class selection pushdown support arrow-rs#6921

[Dandandan]

It seems it could also benefit from some further utf8 validation speed up, filed it here apache/arrow-rs#7014

[Rachelint]

@alamb Excited to see further optmization about late materialization, it is really an important feature as I think !
I tried to use it in HoraeDB last year, and found the same problem mentioned in #6921 and it is frustrated...

I will profile again with setting datafusion.execution.parquet.pushdown_filters = true;, and see what optimizations we can do in datafusion.

[alamb]

@alamb Excited to see further optmization about late materialization, it is really an important feature as I think ! I tried to use it in HoraeDB last year, and found the same problem mentioned in #6921 and it is frustrated...

I will profile again with setting datafusion.execution.parquet.pushdown_filters = true;, and see what optimizations we can do in datafusion.

Thanks @Rachelint

For this case I believe the core change needs to happen in the Parquet reader. The background as I understand it is described here

• Adaptive Parquet Predicate Pushdown arrow-rs#5523

@XiangpengHao has a prototype in the following PR

• Experimental parquet decoder with first-class selection pushdown support arrow-rs#6921

A good next step would be to measure how much faster DataFusion is with that PR -- the previous measurements we had a few other optimizations mixed in.

[Rachelint]

Here is the flamegraph for pushdown, 36+% time costs in decompression.

[alamb]

BTW @pmcgleenon -- is there any chance you might have time to run the ClickBench benchmarks for DataFusion 44? If not no worries, we'll find someone else

[pmcgleenon]

hi @alamb I could pick this up towards the end of the week / early next week, does that work?

by the way last time I checked it (a few weeks back) I noticed that this change in ClickBench has introduced a regression. One of the Clickbench queries fails when using read -r query instead of read query in the run.sh script

the clickbench run.sh script reports the result [null, null, null] for that specific query with both single and partitioned

Do you think this a Datafusion bug?

[alamb]

hi @alamb I could pick this up towards the end of the week / early next week, does that work?

Yes of course! Thank you so much!

by the way last time I checked it (a few weeks back) I noticed that this change in ClickBench has introduced a regression. One of the Clickbench queries fails when using read -r query instead of read query in the run.sh script

Thanks for checking -- I was not aware of that change

the clickbench run.sh script reports the result [null, null, null] for that specific query with both single and partitioned

Do you think this a Datafusion bug?

I am not sure to be honest -- I think we would need to run the query and see what the results are now 🤔

I will put this on my list of things to look at

[pmcgleenon]

hi @Dandandan @alamb here are the results I've got. Looks like 44.0.0 has registered improvements compared with 43.0.0

Attached is the updated latest Clickbench web page if you want to compare datafusion with the other DBs

clickbench-latest.html.zip

If this looks ok, I can create a PR on clickbench to update the results

[alamb]

If this looks ok, I can create a PR on clickbench to update the results

First of all, thank you so much @pmcgleenon -- this is super helpful as always 🙏

I looked at the results and they are inline with my expectations. Specifically most of the major improvements mirror the improvement made by @jayzhan211 #12996 (they are similar improvements as reported in #12996 (comment))

There are a few queries that appear to have gotten slower (q36 and q37)

datafusion/benchmarks/queries/clickbench/queries.sql

Lines 37 to 38 in 0d9f845

	SELECT "URL", COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate"::INT::DATE >= '2013-07-01' AND "EventDate"::INT::DATE <= '2013-07-31' AND "DontCountHits" = 0 AND "IsRefresh" = 0 AND "URL" <> '' GROUP BY "URL" ORDER BY PageViews DESC LIMIT 10;
	SELECT "Title", COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate"::INT::DATE >= '2013-07-01' AND "EventDate"::INT::DATE <= '2013-07-31' AND "DontCountHits" = 0 AND "IsRefresh" = 0 AND "Title" <> '' GROUP BY "Title" ORDER BY PageViews DESC LIMIT 10;

I will file a separate ticket for looking into them.

But from my perspective these results are ready to go 🚀

(BTW I expect DataFusion 45.0.0 to be available in the next few days -- see #14008 -- and it has yet more improvements!)

[alamb]

I will file a separate ticket for looking into them.

• Filed Slowdown in ClickBench Q36-Q37 between DataFusion 43.0.0 and 44.0.0 #14481 in case anyone is interested in taking a look

[pmcgleenon]

PR raised to update Clickbench results for datafusion 44.0.0 ClickHouse/ClickBench#301

[pmcgleenon]

I've re-run this again following the discussion on the ClickBench PR

Q28 needed an update, since the regex was not working correctly

Query 28: 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;

It needs to include an additional escape character \ for :www\\. and \\1:

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;

After that change, the Q28 performance for datafusion 44.0.0 has regressed compared to 43.0.0. Based on the discussion over on ClickBench I think is expected since the Q28 results for all previous datafusion releases are invalid.

@Dandandan @alamb I've attached the full clickbench here if you want to take a look
clickbench-latest 2.html.zip

[pmcgleenon]

hi folks, the Clickbench PR to update the results for 44.0.0 has been merged. Thanks @alamb for helping figure out the Q28 problems with the regex expression!

Looking forward to see further improved results with the 45.0.0 release!! 🚀

[alamb]

Thanks @pmcgleenon ! I am closing this one down.

It turns out we have just released datafusion-45 this morning so we are ready for the run whenever you are !