You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2024/10/26 10:52:32 UTC
[I] [EPIC] Improved Externalized / Spilling / Out of core Hash Aggregation [datafusion]
alamb opened a new issue, #13123:
URL: https://github.com/apache/datafusion/issues/13123
### Is your feature request related to a problem or challenge?
This is a collection of items to improve external (spilling) aggregation
# Background
> Abstract—Analytical database systems offer high-performance in-memory aggregation. If there are many unique groups, temporary query intermediates may not fit RAM, requiring the use of external storage. However, switching from an in-memory to an external algorithm can degrade performance sharply
- [Robust External Hash Aggregation
in the Solid State Age (DuckDB external aggregation paper)](https://hannes.muehleisen.org/publications/icde2024-out-of-core-kuiper-boncz-muehleisen.pdf))
DataFusion has supported memory limited / spilling hash aggregation since @kazuyukitanimura added it last year in https://github.com/apache/datafusion/pull/7400.
We can likely improve this feature and @2010YOUY01 is considering working on it
### Describe the solution you'd like
- [ ] https://github.com/apache/datafusion/pull/13090
### Describe alternatives you've considered
_No response_
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] [EPIC] Improved Externalized / Spilling / Large than Memory Hash Aggregation [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13123:
URL: https://github.com/apache/datafusion/issues/13123#issuecomment-2582773039
Here is a PR to optimize the spill format:
- https://github.com/apache/datafusion/issues/14078
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] [EPIC] Improved Externalized / Spilling / Large than Memory Hash Aggregation [datafusion]
Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #13123:
URL: https://github.com/apache/datafusion/issues/13123#issuecomment-3651548032
> Not that I know of
I ended up implementing this in the linked PR. The only gotcha I came across is that in case of partial or full ordering, the output ordering is reported as being that ordering. The implication for spilling is that the spill files need to be sorted and merge accordingly.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] [EPIC] Improved Externalized / Spilling / Out of core Hash Aggregation [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13123:
URL: https://github.com/apache/datafusion/issues/13123#issuecomment-2439546312
> Note that one challenge of TPCH specifically is that it contains many joins and is largely focused on that, so in order to run TPCH-SF1000 we would also need to implement spilling joins
Maybe @comphead 's work to get SMJ working in https://github.com/apache/datafusion/pull/13111 will help this (e.g. we could always use SMJ for the large TPCH queries 🤔 )
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] [EPIC] Improved Externalized / Spilling / Large than Memory Hash Aggregation [datafusion]
Posted by "pepijnve (via GitHub)" <gi...@apache.org>.
pepijnve commented on issue #13123:
URL: https://github.com/apache/datafusion/issues/13123#issuecomment-3642724834
As a followup to #19287 I'm thinking of working on spilling support for `GroupOrdering` values other than `None`. Does anyone know if there are specific pitfalls to look out for when implementing that?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] [EPIC] Improved Externalized / Spilling / Out of core Hash Aggregation [datafusion]
Posted by "2010YOUY01 (via GitHub)" <gi...@apache.org>.
2010YOUY01 commented on issue #13123:
URL: https://github.com/apache/datafusion/issues/13123#issuecomment-2439877368
> Another potential option would be to work on running clickbench with a very small memory (100MB)?
This is a good idea, we should get clickbench work under memory constraints before TPCH
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] [EPIC] Improved Externalized / Spilling / Out of core Hash Aggregation [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13123:
URL: https://github.com/apache/datafusion/issues/13123#issuecomment-2439490207
@2010YOUY01 says in https://github.com/apache/datafusion/pull/13090#issuecomment-2437436375
> Really nice paper, we can implement the same benchmark and compare in the future 😄 They implemented a unified buffer pool for both table data cache and operator (like aggregation) intermediate results, to easily support spilling in various operators. I think they didn't mention any optimization specific to the spilling part of aggregation, and just use simple LRU policy in the buffer pool. Maybe there are some spilling and merging specific optimizations we can explore (all of memory-limited aggregate/SortMergeJoin/Sort can benefit from)
DF doesn't have a buffer pool in the traditional sense, and the way arrow-rs allocates memory directly from the system allocator makes it quite hard to implement. However, I think the fact that we have arrow-rs and the arrow IPC offers lots of opportunity.
> > Also, are you interested in improving DataFusion's external aggregation capabilities? I think it is a non trivial gap at the moment and would be great to improve (and I would be interested in helping do so).
> > if you are, I can start organizing the work into some tickets to see if we can get some others to check it out too
>
> Yes, I'm start to look at related components now. Perhaps we can start with making memory-limited SQL queries more stable (e.g. more tests, make sure TPCH-SF1000 is able to run on laptop correctly), and later optimize.
I think starting with stability and then optimizing is a great idea 💯
Note that one challenge of TPCH specifically is that it contains many joins and is largely focused on that, so in order to run TPCH-SF1000 we would also need to implement spilling joins
Another potential option would be to work on running clickbench with a very small memory (100MB)?
Or maybe we could figure out another large dataset 🤔
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] [EPIC] Improved Externalized / Spilling / Large than Memory Hash Aggregation [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #13123:
URL: https://github.com/apache/datafusion/issues/13123#issuecomment-3644111707
> As a followup to [#19287](https://github.com/apache/datafusion/pull/19287) I'm thinking of working on spilling support for `GroupOrdering` values other than `None`. Does anyone know if there are specific pitfalls to look out for when implementing that?
Not that I know of
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org