@Rocky What is the difference between the BE node settings "spill_local_storage_dir" and the setting "query_scratch_dirs*"* they both seem to do the same thing

Although

spill_local_storage_dir

and

query_scratch_dirs

both involve storing temporary data on local disks to manage memory usage, they serve different components and were introduced at different stages of StarRocks' evolution. Summary Comparison Setting | Primary Use Case | Introduced | Relevant Engine/Feature

spill_local_storage_dir

| Unified Spilling Framework. Specifically used for spilling intermediate data from memory-intensive operators (Aggregations, Sorts, Hash Joins) when

enable_spill

is set to

true

. | v3.0.1 | Pipeline Engine / Spiller Framework

query_scratch_dirs

| General Scratch Space. Used for temporary files produced by other query execution components, including legacy external sort/join paths and general-purpose temporary storage. | v3.2.0 | TmpFileMgr / General Query Execution ────────── Detailed Differences 1. spill_local_storage_dir (The "Spiller") This is the setting most users are looking for when they want to enable the "Spill to Disk" feature to avoid Out-Of-Memory (OOM) errors for large queries. * Targeted Operators: Aggregates, Sorts, Hash Joins, and CTEs. * Mechanism: It works with the Unified Spilling Framework (the "Spiller"). When a query reaches its memory limit and spilling is enabled (

SET enable_spill = true

), the data is structured and written to these directories in a way that allows the query to resume by reading from disk. * Documentation: This is the directory featured in the official Spill to Disk documentation. 2. query_scratch_dirs (The "Scratch Manager") This setting was introduced in v3.2 to provide a more flexible, multi-directory management system for general-purpose temporary files. * Targeted Use: It is used by the

TmpFileMgr

(Temporary File Manager). While it also mentions "external sorts and hash joins," it is often used for: * Temporary files for internal operations that don't use the new "Spiller" framework. * Legacy execution paths or specific operators that require raw file scratch space. * Intermediate data for certain exchange or materialization tasks. * Defaults: If not specified, it defaults to the

${STARROCKS_HOME}

directory. Which one should you use? In modern versions of StarRocks (v3.0+), you should prioritize setting

spill_local_storage_dir

if your goal is to handle large-scale data processing and prevent OOMs via the spilling feature. * Best Practice: In a production environment, it is highly recommended to point both to dedicated, high-performance disks (preferably SSDs) and keep them separate from your data storage disks (

storage_root_path

) to prevent I/O contention. * Example Configuration:

properties
    # For operator-level spilling (Spiller framework)
    spill_local_storage_dir=/mnt/ssd1/spill;/mnt/ssd2/spill

    # For general temporary query files (TmpFileMgr)
    query_scratch_dirs=/mnt/ssd1/scratch;/mnt/ssd2/scratch

References • administration/management/BE_configuration.md • sql-reference/System_variable.md • administration/management/resource_management/spill_to_disk.md

@Rocky How big should both disks be proporitionally

There isn't a hard-coded mathematical formula in the StarRocks documentation for the exact size of these disks, as it depends entirely on your query complexity and data volume. However, based on best practices for high-performance MPP systems and StarRocks' own architecture, you can use the following sizing guidelines. 1. General Rule: Proportional to RAM The most common "rule of thumb" for spill and scratch space is based on your node's memory capacity rather than your total persistent storage: * Recommended Size: 1x to 2x the total RAM of the node. * Example: If your BE node has 256 GB of RAM, you should allocate roughly 256 GB to 512 GB of dedicated SSD/NVMe space for these directories. Why? Spilling is triggered when a query exceeds its memory limit (e.g., 80%–100% of the

query_pool

memory). If you have multiple large queries spilling concurrently, the disk must be large enough to hold all the intermediate rows that would have otherwise lived in memory across those queries. 2. Proportional to "Largest Query" Data Volume If you are performing massive ETL (e.g.,

INSERT INTO SELECT

from a multi-terabyte table), the scratch space should be proportional to the intermediate result set size of your largest query. * Guideline: Ensure the disk has enough space to hold at least one full copy of your largest table's uncompressed intermediate data (usually the "probe" side of a join or the data being sorted). 3. Allocation Between the Two Settings In modern StarRocks (v3.2+), you do not necessarily need separate physical disks for each setting. They serve different parts of the engine but perform the same functional role: * Point them to the same disks: You can point both

spill_local_storage_dir

and

query_scratch_dirs

to the same high-performance SSD mount points. They will share the total capacity of that disk. * Priority: If you have limited high-speed storage, prioritize

spill_local_storage_dir

if you are on v3.0+ for modern operator spilling, and

query_scratch_dirs

for v3.2+ general scratch work. 4. Critical Best Practices * Dedicated SSDs/NVMe: Do not put these directories on the same disks as your data (

storage_root_path

). Spilling creates massive, bursty I/O write patterns. If they share a disk with your data, your standard query performance (scans) will plummet when a spill occurs. * Avoid HDDs: Spilling to HDDs is often so slow that queries may time out before they finish. Use SSDs to keep the "latency penalty" of spilling manageable. * Cleanup: StarRocks automatically cleans up these directories when queries finish. If you see them filling up, it usually means queries are failing to complete or the BE crashed before it could clean up. Summary Configuration Example If you have two dedicated 500 GB NVMe drives mounted at

/mnt/nvme1

and

/mnt/nvme2

, your

be.conf

should look like this:

properties
# Both sharing the same high-speed temporary pool
spill_local_storage_dir=/mnt/nvme1/spill;/mnt/nvme2/spill
query_scratch_dirs=/mnt/nvme1/scratch;/mnt/nvme2/scratch

References • administration/management/resource_management/spill_to_disk.md • administration/management/BE_configuration.md • sql-reference/System_variable.md