[Performance] Performance degrade when query for tons of small query

[Ma1oneZhang]

DataFusion Performance Degradation with Small Batches

We've identified a significant performance degradation in DataFusion when querying small data batches or data sources that return no data. Our investigation has pinpointed two primary causes for this issue:

1. Excessive Metrics Overhead: DataFusion's extensive metrics collection adds considerable overhead, particularly when the query processing time is minimal. The time spent recording and managing these metrics becomes a dominant factor, disproportionately impacting performance on small tasks.

2. Fragmented Data Blocks: Processing numerous small, fragmented data blocks (even empty ones) leads to inefficiencies. The overhead of managing these individual fragments, rather than the data itself, consumes valuable processing time, exacerbating the performance bottleneck.

Proposed Solution

To address this problem, i believe that add a new configuration option that allows users to disable metrics collection. By setting an environment variable or a configuration flag, users can choose to bypass the metrics system entirely. This change will significantly reduce the overhead associated with metrics, leading to improved performance for workloads involving small data batches or empty data sources.

I believe this solution offers a practical way to balance the need for performance with the utility of having detailed metrics, giving users the flexibility to optimize DataFusion for their specific use cases.

To Reproduce

Create some aggregations SQL, and just run it in an empty dataset.

Expected behavior

No response

Additional context

No response

[Ma1oneZhang]

That's the flame-graph i drew yesterday. I use my server@Xeon Gold 6532Y with 2 sockets and 12 channels memory.

We can see the left side is the RepartitionMetrics::new method takes up a lot of time, and on the right side, the memory deallocation becomes a new bottleneck again.

[Ma1oneZhang]

@alamb Sorry to bother you, but could you help me determine if this is a defect or just an unexpected performance degradation?

[alamb]

To address this problem, i believe that add a new configuration option that allows users to disable metrics collection. By setting an environment variable or a configuration flag, users can choose to bypass the metrics system entirely. This change will significantly reduce the overhead associated with metrics, leading to improved performance for workloads involving small data batches or empty data sources.

I believe this solution offers a practical way to balance the need for performance with the utility of having detailed metrics, giving users the flexibility to optimize DataFusion for their specific use cases.

I think turning off metrics might be treating the symptom of the problem rather than the underlying cause

By default DataFusion is designed to maximize the parallelism to all cores -- so if you have 32 cores, each plan by default will split the data into 32 partitions.

However, if your data is small, the overhead of splitting to enable parallelization can dominate the actual computation, which is what you might be seeing.

You can find out how many cores are being used using EXPLAIN .. to look at the plan

You can see how much faster/slower the plan goes if you limit it to a single partition via a config setting

Specifcally, set datafusion.execution.target_partitions to 1

If this helps, can you please let us know -- it would be good to document this as you are not the first person to notice something like this

[alamb]

I made a PR with this information here

• Docs: Add Tuning Guide for small data / short queries #17040

[Ma1oneZhang]

Thanks for your help, i will try.

[Ma1oneZhang]

We do really solve the problem, thank you! @alamb

[alamb]

Thank you for the update @Ma1oneZhang

Let's call this ticket closed by #17040