Investigate performance tradeoff in compressing spill files

[ding-young]

Is your feature request related to a problem or challenge?

Part of #16065 , Related to #14078

Background

PR #16268 will introduce compression option when writing spill files in disk. These options are limited to compression options to what Arrow IPC Stream Writer supports : zstd, lz4_frame (compression level is not configurable)

To further enhance spilling execution, we need to investigate

• CPU / I/O tradeoff when zstd or lz4_frame compression is enabled i.e. compression ratio, extra latency spent for compression
• Current arrow ipc stream writer always write batch at a time in append_batch. In terms of compression, it is not sure yet how much single batch can benefit from compression.
• whether we need separate Writer or Reader implementation instead of IPC Stream Writer.
• how to introduce sort of adaptiveness.

Describe the solution you'd like

First, we need to track (or update) how many bytes are written in spill files. Datafusion currently tracks spilled_bytes as part of SpillMetrics, but it is calculated based on in memory array size, which would be different from actual spill files size especially when we compress spill files.

Second, update the benchmarks or write a separate benchmarks to see the performance characteristics. One possible way is writing out spill-related metrics to output.json when running benches like tpch with debug option. Another idea is to generate some spill files for microbenchmark testing only spill writing - reading process.

Describe alternatives you've considered

No response

Additional context

No response

[ding-young]

take

[2010YOUY01]

• CPU / I/O tradeoff when zstd or lz4_frame compression is enabled i.e. compression ratio, extra latency spent for compression

It's worth doing some micro-benches to see how those compressions work on Arrow arrays.
We can configure several possible shapes of to-spill intermedia data (single col of different types/very wide batches/etc., perhaps just use the TPCH table), and test how different compression types perform for speed and compression ratio.
It would be interesting to also test how vortex perform.

• Current arrow ipc stream writer always write batch at a time in append_batch. In terms of compression, it is not sure yet how much single batch can benefit from compression.

This looks not so ideal, especially for compressing 'thin' batches with primitive types. If we can confirm there are huge overheads here then we should consider implementing compressing multiple batches at once.

• whether we need separate Writer or Reader implementation instead of IPC Stream Writer.
• how to introduce sort of adaptiveness.

Describe the solution you'd like

First, we need to track (or update) how many bytes are written in spill files. Datafusion currently tracks spilled_bytes as part of SpillMetrics, but it is calculated based on in memory array size, which would be different from actual spill files size especially when we compress spill files.

Second, update the benchmarks or write a separate benchmarks to see the performance characteristics. One possible way is writing out spill-related metrics to output.json when running benches like tpch with debug option. Another idea is to generate some spill files for microbenchmark testing only spill writing - reading process.

🤔 Yes now the spilled_bytes is not implemented correctly if compression happens, it needs a follow-up fix. Adding more micro-benchmarks sounds great.