Preventing Disk Space Issues Due to Query Spilling

{question}

 Is there a way to limit spilling to disk to prevent running out of disk space?

{question}

{answer}

Yes, there are multiple ways to manage disk spilling and avoid running out of disk space:

1. Use spilling_maximum_disk_percent
   This global variable regulates the maximum percentage of disk space that can be utilized for spilling.

   • Default value: -1 (uses a heuristic approach based on system conditions).

   • Valid values: Between 0 and 1 (inclusive).

   • Setting it to 0 disables query spilling entirely.

2. Use spilling_minimal_disk_space
   This variable determines the minimum amount of free disk space needed.

   • If the available disk space falls below this threshold, queries that attempt to spill will fail rather than consume more space.

Behavior When Limits Are Reached

• If disk space is belowspilling_minimal_disk_space, spilling is aborted, and the query fails with an error.

• If spilling usage exceeds the limit set byspilling_maximum_disk_percent, the query is also aborted with an error.

Recommended Approach

• Leave spilling_maximum_disk_percent at the default (-1) to rely on built-in heuristics.

• Monitor spilling activity usingSHOW STATUS EXTENDED.

• Set an appropriate value forspilling_minimal_disk_spaceto maintain a safety margin and avoid full disk usage.

Disk Spilling.

{answer}