Repository navigation
Update ClickBench benchmarks with DataFusion 44.0.0 #13983
Description
Activity
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
I also filed a ticket to track running clickbench on DataFusion 45 once that is released in a few weeks
I think Q8, Q16~18, Q35 can be closer to
hyperin 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 think Q8, Q16~18, Q35 can be closer to
hyperin 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
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 😕
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)
Q23 might be improved if it can utilize filter pushdown? I think a >5x improvement might come from that.
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
@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)
I will profile that and report back
Reacted by Daniël Heres and kamilleAnd 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:
Reacted by Daniël Heres, kamille and Jay ZhanIt seems it could also benefit from some further utf8 validation speed up, filed it here apache/arrow-rs#7014
Reacted by Bruce Ritchie@alamb Excited to see further optmization about
late materialization, it is really an important feature as I think !
I tried to use it inHoraeDBlast 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 indatafusion.@alamb Excited to see further optmization about
late materialization, it is really an important feature as I think ! I tried to use it inHoraeDBlast 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 indatafusion.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
@XiangpengHao has a prototype in the following PR
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.
Reacted by kamilleHere is the flamegraph for pushdown, 36+% time costs in decompression.
Reacted by Andrew LambBTW @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
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 queryinstead ofread queryin therun.shscriptthe clickbench
run.shscript reports the result[null, null, null]for that specific query with both single and partitionedDo you think this a Datafusion bug?
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 queryinstead ofread queryin therun.shscriptThanks for checking -- I was not aware of that change
the clickbench
run.shscript reports the result[null, null, null]for that specific query with both single and partitionedDo 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
hi @Dandandan @alamb here are the results I've got. Looks like
44.0.0has registered improvements compared with43.0.0
Attached is the updated latest Clickbench web page if you want to compare datafusion with the other DBs
If this looks ok, I can create a PR on clickbench to update the results
Reacted by Andrew Lamb and Daniël HeresIf 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
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.0to be available in the next few days -- see #14008 -- and it has yet more improvements!)Reacted by Patrick McGleenonI 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
PR raised to update Clickbench results for datafusion
44.0.0ClickHouse/ClickBench#301Reacted by Andrew LambI'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.0has regressed compared to43.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.zipReacted by Andrew Lamb and Daniël Hereshi folks, the Clickbench PR to update the results for
44.0.0has 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.0release!! 🚀Reacted by Jay ZhanThanks @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 !
Reacted by Patrick McGleenonDiscussion about making more improvements:
Is your feature request related to a problem or challenge?
43.0.0#1309944.0.0#13334Describe 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
Additional context
I am especially interested to see the improvements after the vectorized comparison from @Rachelint @jayzhan211 @Dandandan and others in DataFusion aggregate code