Refactor: improve query spill

[Dousir9]

Summary

• Refactor aggregate spill
• Reduce hash join spill file size
• Improve sort spill performance feat(query): partition sort spill #16987
• refactor(query): reduce memory usage during aggregate spill #17550

[inviscid]

While the throughput speed to object storage is quite fast, the I/O is not nearly as good as local SSD/NVMe. For spill operations, if they are I/O bound, could we spill to the local cache disk rather than object storage?

We also want to make sure all spill and temp files are cleaned up after the query operation completes or fails. There might be evidence that these temp/spill files persist.

[wubx]

Yes, latest version support spill to local disk

Add config to config file:

[spill]
spill_local_disk_path = "/data1/databend/databend_spill"

[soyeric128]

Yes, latest version support spill to local disk

Add config to config file:

[spill]
spill_local_disk_path = "/data1/databend/databend_spill"

Doc updated: https://docs.databend.com/guides/data-management/data-recycle#spill-data-storage

[rad-pat]

Yes, latest version support spill to local disk

@wubx, this is only for window functions so far, right? The biggest spills we have are aggregation so this won't help yet, is that correct?

[BohuTANG]

Hi @inviscid,

Spill to local disk already supportted, see the doc: https://docs.databend.com/guides/data-management/data-recycle#configuring-spill-storage

[wubx]

Yes, latest version support spill to local disk

@wubx, this is only for window functions so far, right? The biggest spills we have are aggregation so this won't help yet, is that correct?

#17550
There will be a big improvement after this PR merge.

Databend hope that precisely control the memory occupied by each Query, and then queue the data spill, so that for biggest spills also can spill out to s3.

Please feel free to share any suggestions you may have. We would love to hear your thoughts!