< Rocky> we run into this error in 3 5 1 however same query StarRocks #questions-and-troubleshooting

<@U0A71G31CDV> we run into this error in 3.5.1 how...

Mahesha Subrahamanya

08/26/2026, 3:13 PM

@Rocky we run into this error in 3.5.1 however same query with same configuration with 4.1.1 works fine so wondering how to address this in 3.5.1 with honoring the query_mem_limit and running in a batching fashion to get this query works and results.

Copy code

SQL Error [1064] [42000]: Memory of Query94a97a23-a156-11f1-8dff-c2833f6bc006 exceed limit. Pipeline Backend: starrocks-cn-0.starrocks-cn-search.abc-db.svc.cluster.local, fragment: 94a97a23-a156-11f1-8dff-c2833f6bc00d Used: 2329679640, Limit: 2147483648. Mem usage has exceed the limit of single query, You can change the limit by set session variable query_mem_limit.: BE:11150

Rocky

08/26/2026, 3:13 PM

To address the memory limit error in StarRocks 3.5.1 while honoring your

query_mem_limit

(currently set at 2GB) and running in a "batching fashion," the most effective approach is to enable Spill to Disk. This feature allows StarRocks to offload intermediate results (from joins, aggregations, or sorts) to disk when the memory limit is approached, preventing the query from being terminated with an OOM error. 1. Enable Spill to Disk (Recommended) This is likely the "batching" behavior you observed in later versions where memory management might be more aggressive or defaults have changed. Step 1: Configure BE/CN nodes Ensure your Compute Nodes (CN) or Backend nodes (BE) have a directory configured for spilling in

be.conf

cn.conf

Copy code

properties
# Example: Specify a local directory for intermediate data
spill_local_storage_dir=/data1/spill;/data2/spill

Restart the nodes after changing this configuration. Step 2: Enable spilling for the session Run these variables before executing your query:

Copy code

sql
-- Enable the spilling feature
SET enable_spill = true;

-- 'auto' triggers spilling when memory reaches a threshold
SET spill_mode = 'auto';

2. Reduce the Execution "Batch" Size If you want to reduce the memory footprint of each operator instance (the internal "batching"), you can tune the

vector_chunk_size

. This variable controls the number of rows processed in a single chunk. Lowering it reduces peak memory usage per operator at a slight cost to CPU efficiency.

Copy code

sql
-- Default is 4096. Try reducing it to 2048 or 1024.
SET vector_chunk_size = 2048;

3. Check for specific 3.5.1 Operators In StarRocks 3.5.x, most heavy operators support spilling: • Hash Joins (Inner, Outer, Semi, Anti) • Aggregations (Group By) • Sorts (Order By) • CTE (Common Table Expressions) If your query uses a very large

IN

clause or a specific window function that doesn't spill as efficiently, these settings might still struggle with a tight 2GB limit. Why does it work in "4.1.1"? While 4.1.1 is likely a future or internal version relative to current public documentation, StarRocks continues to optimize its Global Memory Management and Pipeline Engine. Later versions often have better "fine-grained" spilling or more efficient memory trackers that avoid over-reporting "used" memory that hasn't actually been committed yet. Summary for 3.5.1: If you must honor the 2GB limit, enabling

enable_spill = true

is the standard way to ensure the query completes by trading some I/O performance for memory stability. References • administration/management/resource_management/Memory_management.md • administration/management/BE_configuration.md • faq/resource_isolation_faq.md • administration/management/resource_management/query_queues.md • sql-reference/System_variable.md • administration/management/resource_management/Query_management.md

Mahesha Subrahamanya

08/26/2026, 3:31 PM

SQL Error [1193] [HY000]: Unknown system variable 'vector_chunk_size', the most similar variables are {'chunk_size', 'connector_huge_file_size', 'connector_max_split_size'} get this error on 3.5.1

Mahesha Subrahamanya

08/26/2026, 3:34 PM

@Rocky help with this error

Rocky

08/26/2026, 3:34 PM

To address the memory limit error in StarRocks 3.5.1 while honoring your

query_mem_limit

be.conf

cn.conf

Copy code

properties
# Example: Specify a local directory for intermediate data
spill_local_storage_dir=/data1/spill;/data2/spill

Restart the nodes after changing this configuration. Step 2: Enable spilling for the session Run these variables before executing your query:

Copy code

sql
-- Enable the spilling feature
SET enable_spill = true;

-- 'auto' triggers spilling when memory reaches a threshold (usually 80% of limit)
SET spill_mode = 'auto';

2. Reduce the Execution "Batch" Size If you want to reduce the memory footprint of each operator instance (the internal "batching"), you can tune the

vector_chunk_size

. This variable controls the number of rows processed in a single chunk. Lowering it reduces peak memory usage per operator at a slight cost to CPU efficiency.

Copy code

sql
-- Default is 4096. Try reducing it to 2048 or 1024.
SET vector_chunk_size = 2048;

3. Lower Parallelism The memory limit is checked per query, but high parallelism (

pipeline_dop

) can cause many instances of operators to compete for memory simultaneously. Reducing parallelism can lower the memory spike.

Copy code

sql
-- Check your current pipeline_dop (default is often 0, which means auto-scaling)
-- Try setting it to a lower fixed value like 4 or 8 to limit concurrent memory usage.
SET pipeline_dop = 8;

4. Check for unsupported operators In StarRocks 3.5.x, most heavy operators support spilling: • Hash Joins (Inner, Outer, Semi, Anti) • Aggregations (Group By) • Sorts (Order By) • CTE (Common Table Expressions) However, if your query uses very large Window Functions or complex Expression Evaluations (e.g., massive

CASE WHEN

IN

lists), these do not always support spilling and may still hit the 2GB limit. Why does it work in 4.1.1? StarRocks 4.1.x introduced improved memory accounting and Adaptive Memory Management. It can more accurately track memory and trigger spilling or internal batching more efficiently than 3.5.1. Specifically, 4.1.x includes fixes for memory "leakage" in the Analytor and better enforcement of limits during column upgrades. Summary for 3.5.1: To honor the 2GB limit, enabling

enable_spill = true

is the standard way to ensure the query completes by trading some I/O performance for memory stability. References • sql-reference/System_variable.md • administration/management/resource_management/spill_to_disk.md • administration/management/FE_configuration.md

Mahesha Subrahamanya

08/26/2026, 3:35 PM

@Rocky help with this error SQL Error [1193] [HY000]: Unknown system variable 'vector_chunk_size', the most similar variables are {'chunk_size', 'connector_huge_file_size', 'connector_max_split_size'} get this error on 3.5.1

Rocky

08/26/2026, 3:35 PM

In StarRocks 3.5.1,

vector_chunk_size

is a BE/CN configuration parameter (set in

be.conf

cn.conf

), not a session variable. This is why you received the "Unknown system variable" error when trying to use

SET

. To achieve the "batching" behavior at the session level without restarting your nodes, you should use the variable chunk_size
. 1. Correct Session Variable Instead of

vector_chunk_size

, use

chunk_size

to reduce the memory footprint of each processing batch:

Copy code

sql
-- Default is 4096. Try reducing it to 2048 or 1024.
SET chunk_size = 2048;

2. Full Recommended Configuration for 3.5.1 To honor a strict 2GB limit while ensuring the query finishes, combine

chunk_size

with Spilling:

Copy code

sql
-- 1. Enable spilling to handle memory pressure by writing to disk
SET enable_spill = true;
SET spill_mode = 'auto';

-- 2. Reduce internal batch size to lower peak memory per operator
SET chunk_size = 2048;

-- 3. (Optional) Limit parallelism if the query has many concurrent operators
-- Default is 0 (auto-scaled). Setting it to a fixed lower value can help.
SET pipeline_dop = 4;

-- Run your query
SELECT ...

Why the confusion? * vector_chunk_size
: A static BE/CN configuration that sets the global default for the engine. It requires a node restart to change. * chunk_size
: The corresponding session variable that allows you to override this behavior for a specific query or session. * batch_size
: This is often used as an alias in older documentation or specific hints, but chunk_size
is the standard variable in the 3.x series. If you continue to hit the 2GB limit even with spilling, please check if your query contains Window Functions or very large IN
lists, as these specific operators have limited spill support in 3.5.1 compared to the more advanced versions likey "4.x" development branches. References • sql-reference/System_variable.md • administration/management/resource_management/Memory_management.md • administration/management/BE_configuration.md • administration/management/resource_management/spill_to_disk.md References • administration/management/BE_configuration.md • administration/management/resource_management/Memory_management.md • release_notes/release-2.4.md • sql-reference/System_variable.md • table_design/indexes/vector_index.md

3 Views

Open in Slack

Previous Next