@Rocky CPU weight in resource groups seems to do nothing? I set a particular user to have cpu weight of 1. Total weight is 72. But when it executed complex query, cpu saturated to 100% for prolonged periods of time. Other users were unable to query anything and timed out. Backup jobs failed to. WHat is the point of cpu weight? Seems like I have to weight for an issue to happen and change the culprit user to reserved cpu cores to ensure it never touches a certain number of cpu cores

In StarRocks,

cpu_weight

is a soft limit based on relative scheduling. It does not act as a "ceiling" that prevents a query from using 100% of the CPU if the node has idle capacity. Here is why you saw 100% saturation and why other users timed out: 1. How

cpu_weight

Works (Soft Isolation) * Borrowing Idle Resources: If other resource groups are idle, a query with a low

cpu_weight

(like 1 out of 72) is allowed to "borrow" all available CPU cores to finish faster. It only gets restricted to its 1/72 share when there is simultaneous competition from other groups. * The "Slow-Motion" Contention: Even if other users start querying, the "culprit" query may have already saturated the pipeline or caused secondary bottlenecks (like IO or lock contention) that make the system unresponsive before the scheduler can effectively throttle it back down to 1.4%. 2. Why Backup Jobs and Other Users Failed * System Overload: When a single query consumes 100% CPU, it can cause high latency in the StarRocks internal heartbeat and RPC (Remote Procedure Call) mechanisms. * RPC Timeouts: If the Backend (BE) is too busy to respond to the Frontend (FE) heartbeats or RPC requests within the timeout window, the FE may think the BE is down or simply time out the query, even if the resource group scheduler was technically trying to give them a share. * Internal Tasks: Background tasks like backup/restore and compaction are often not strictly governed by user-defined resource groups and can be starved of CPU cycles during total node saturation. 3. Recommended Solutions To prevent a single user from crashing the cluster, you should use Hard Isolation or Circuit Breakers rather than just weights: * Hard Isolation (

exclusive_cpu_cores

): If you are on version v3.3.5 or later, you can use

exclusive_cpu_cores

. This reserves a fixed number of cores for a group and prevents that group from ever exceeding that limit.

sql
    ALTER RESOURCE GROUP your_group_name WITH (
        'exclusive_cpu_cores' = '4' -- Hard limit: this group can never use more than 4 cores
    );

_Note: You cannot set both

cpu_weight

and

exclusive_cpu_cores

for the same group._ * Big Query Circuit Breakers: Instead of just slowing the query down, you can automatically kill it if it exceeds certain thresholds. This is often more effective for "culprit" users:

sql
    ALTER RESOURCE GROUP your_group_name WITH (
        'big_query_cpu_second_limit' = '60', -- Kill if it uses > 60s of CPU time
        'big_query_scan_rows_limit' = '100000000', -- Kill if it scans > 100M rows
        'big_query_mem_limit' = '4294967296' -- Kill if it uses > 4GB RAM
    );

* Check Pipeline Engine: Resource isolation relies on the Pipeline Engine. Ensure it is enabled globally:

sql
    SET GLOBAL enable_pipeline_engine = true;

Summary: Your suspicion was correct—`cpu_weight` is a "best effort" share, not a hard cap. If you need to ensure a user never touches more than a certain number of cores, you must use

exclusive_cpu_cores

(Hard Limit) or use

big_query_..._limit

parameters to kill the query before it saturates the node. References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • administration/management/monitoring/metrics.md • best_practices/audit_log_resource_group.md

@Rocky Is it possible or likely that the user took all idle CPU to begin its query, and this lasts a long time. So the entire BE was busy to respond to FE. So the resource group cpu weight couldnt take effect? Like the FE resource group scheduling couldnt work mid query?

Yes, your intuition is correct. While StarRocks is designed to adjust resources mid-query, there are specific technical reasons why a heavy query can "drown out" the system before the

cpu_weight

can effectively save it. Here is the breakdown of why the resource group scheduling might have failed to protect your cluster in this scenario: 1. The "Reclamation Lag" When a query starts while the cluster is idle, the Pipeline Engine allows it to use all available CPU cores. When a second query from a different resource group arrives, the BE must reclaim those resources. * Gradual Adjustment: Reclamation is not instantaneous. It typically takes tens of milliseconds to a few seconds for the scheduler to throttle the first query and ramp up the second. * The Saturation Gap: In those few seconds, if the first query is extremely "heavy" (e.g., massive joins or complex aggregations), the CPU remains at 100%. If the other users' queries are small or if the backup job is sensitive to latency, they may time out or fail before the scheduler successfully "squeezes" the culprit query. 2. Management Plane Starvation (The Heartbeat Problem) This is likely why your backup jobs and other users timed out entirely. * Shared Thread Pools: All "Shared" resource groups (those using

cpu_weight

) run in the same internal BE thread pools (named

pip_exec_com

,

pip_scan_com

). * RPC & Heartbeats: Even though the scheduler tries to divide CPU cycles, the BE node itself becomes so overloaded that it struggles to respond to FE Heartbeats or RPC (Remote Procedure Call) requests. * The Result: The Frontend (FE) might decide the Backend (BE) is unresponsive and kill the connection, or the backup job (which relies on consistent metadata heartbeats) fails because the BE's "management" logic couldn't get enough CPU cycles to say "I'm still here." 3. Non-Preemptible Tasks While the Pipeline Engine is highly asynchronous, certain operations (like massive memory allocations, complex local sorting, or specific IO-bound tasks) can occasionally "block" a pipeline thread for a short duration. If many threads are blocked simultaneously by a massive query, the scheduler cannot "preempt" them to give those cycles to a new query immediately. Why "Exclusive Cores" is the fix for you The behavior you experienced is exactly why StarRocks introduced

exclusive_cpu_cores

(Hard Isolation). * With

cpu_weight

(Soft): All groups share the same "bucket" of threads. One query can fill the bucket and make the whole BE "sluggish" even if the scheduler is trying to be fair. * With

exclusive_cpu_cores

(Hard): StarRocks creates separate physical thread pools bound to specific CPU cores. * If you give the "culprit" group 60 cores and reserve 12 cores for everyone else, the culprit group cannot touch those 12 cores, even if they are idle. * This ensures that the BE always has "breathing room" to respond to heartbeats, RPCs, and backup tasks, regardless of how hard the complex query is running. Recommendation If you have a "bully" user or a specific type of query that consistently causes timeouts: 1. Switch to

exclusive_cpu_cores

for that user's resource group to "cage" them. 2. Enable a Big Query Limit (e.g.,

big_query_cpu_second_limit

) to automatically kill queries that run for too long, rather than just trying to slow them down. _Note:

exclusive_cpu_cores

is available in version 3.3.5 and later._ References • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md • administration/management/BE_configuration.md

@Rocky {"level":"fatal","error":"failed to query high CTR domains: Error 1064 (HY000): Failed to allocate resource to query: pending timeout [300s], you could modify the session variable [query_queue_pending_timeout_second] to pending more time","time":"2026-07-30T000524Z","message":"failed to query high CTR domains"} Is this query queued for this long and timed out or query execution timed out

This error message indicates that your query was queued and timed out while waiting for a slot to begin execution. It never reached the "RUNNING" state. Key details from the error: * Status: The query was in a

PENDING

state. * Cause: The StarRocks Query Queue feature is enabled, and the cluster was likely under heavy load (high CPU, high memory usage, or max concurrency reached). * The 300s Limit: This is the default value of the session variable

query_queue_pending_timeout_second

. Because the query could not be allocated resources within 5 minutes, the Frontend (FE) automatically rejected it. Why did this happen? Since you mentioned a "culprit" user saturating the CPU in your previous message, this is a direct side effect. When the system detects that CPU or memory usage has exceeded a threshold (controlled by

query_queue_cpu_used_permille_limit

or

query_queue_mem_used_pct_limit

), it places all new incoming queries into a queue to prevent the cluster from crashing. In your case, because the complex query kept the CPU at 100%, new queries were stuck in the queue until they hit the 300-second limit and died. How to verify: You can check the FE audit log (

fe.audit.log

). Look for the

PendingTimeMs

field for this specific query ID. It will likely be around

300000

(300 seconds), confirming it spent the entire time waiting. Immediate Fixes: 1. Increase Queue Timeout (Not recommended as a long-term fix):

sql
    SET GLOBAL query_queue_pending_timeout_second = 600; -- Increase to 10 mins

2. Use Hard Isolation: As discussed before, moving the "heavy" user to a resource group with

exclusive_cpu_cores

will ensure that CPU doesn't stay at 100% for everyone, allowing the queue to clear faster for other users. 3. Check Queue Triggers: You can see what triggered the queue by looking at your global settings:

sql
    SHOW VARIABLES LIKE '%query_queue%';

If

query_queue_cpu_used_permille_limit

is set to something like

800

(80%), and your big query is using 100%, every other query will be forced to wait indefinitely until the big query finishes. References • administration/management/resource_management/query_queues.md • administration/management/monitor_manage_big_queries.md • sql-reference/System_variable.md References • faq/loading/Loading_faq.md • faq/Sql_faq.md • administration/management/resource_management/query_queues.md • administration/management/monitor_manage_big_queries.md • sql-reference/System_variable.md • faq/operation_maintenance_faq.md

@Rocky What if I just disable queue? It needs to eventually execute

If you disable the query queue while your CPU is already at 100%, the queries will not necessarily "eventually execute" faster. Instead, they will likely fail for more catastrophic reasons. The query queue is a self-protection mechanism. Here is what happens if you disable it (by setting

SET GLOBAL enable_query_queue_select = false;

): 1. Risk of OOM (Out of Memory) Crashes This is the biggest danger. * Queued: A query in the queue consumes almost zero memory. * Executing: As soon as a query starts, it allocates memory for hash tables, buffers, and intermediate results. If you have 50 queries that were waiting in the queue and you suddenly allow them all to run simultaneously on a node already at 100% CPU, they will all compete for memory at once. This frequently leads to BE process crashes (OOM) or queries being killed with

Memory limit exceeded

. 2. The "Thundering Herd" and Latency When the CPU is at 100%, adding more concurrent queries doesn't create more "work power." It just forces the CPU to spend more time context switching between thousands of threads. * Instead of Query A finishing in 10s and Query B starting and finishing in 10s (Total 20s), both might run simultaneously and take 60s each because they are fighting for the same cycles. * Other lightweight tasks (like heartbeats and metadata updates) will become even slower, potentially leading to the entire cluster becoming "Grey" or unresponsive in the FE. 3. Will they "Eventually Execute"? If you disable the queue, they will eventually start, but they are much less likely to finish. On a saturated system, a query that could have finished if it had 10% of the CPU might simply time out or be killed by the memory tracker because it's running so slowly that its resource footprint stays open for too long. Better Alternatives to Disabling the Queue: If your goal is to ensure queries eventually run without timing out in the queue, try these instead: * Increase the Queue Timeout: If you are okay with queries waiting a long time as long as they eventually run, just increase the patience of the queue:

sql
    SET GLOBAL query_queue_pending_timeout_second = 3600; -- Wait up to 1 hour

* Increase Queue Capacity: If you are seeing "queue full" errors, increase the max number of allowed waiting queries:

sql
    SET GLOBAL query_queue_max_queued_queries = 2000;

* Use Hard Isolation (Recommended): As mentioned before, move the "culprit" user to a resource group with

exclusive_cpu_cores

. This "cages" the heavy query to a specific set of cores, ensuring the CPU never hits 100% for the rest of the system. This prevents the Global Queue from ever being triggered by that one user’s heavy load, allowing other users to bypass the queue entirely. Summary: Disabling the queue on a saturated cluster usually trades "Pending Timeout" errors for "Memory Limit Exceeded" errors or total node instability. It usually makes the system less stable, not more. References • administration/management/resource_management/query_queues.md • administration/management/monitor_manage_big_queries.md • sql-reference/System_variable.md • faq/resource_isolation_faq.md References • administration/management/resource_management/query_queues.md • sql-reference/System_variable.md • administration/management/monitor_manage_big_queries.md

@Rocky What is the element limit for IN predicate?

The limit for elements in an

IN

predicate depends on the type of statement and the version/configuration of StarRocks you are using. 1. Standard Queries (SELECT) For standard queries, the limit is primarily governed by the number of child expressions allowed in a single expression: * Default Limit: 10,000 elements. * Controlled by: The FE configuration parameter

expr_children_limit

. * Behavior: If you exceed this without specific optimizations enabled, the FE will throw an error during the planning phase. 2. Large IN Predicate Optimization (Recommended) In modern versions of StarRocks, there is a feature specifically for handling massive

IN

lists (e.g., 100k+ items) by converting them into a temporary internal join: * Threshold: 100,000 elements (default). * Controlled by: * `enable_large_in_predicate`: Set to

true

by default. * `large_in_predicate_threshold`: Default is

100000

. * How it works: When an

IN

list exceeds this threshold, StarRocks bypasses the standard expression tree (which is memory-heavy for the FE) and transforms the list into a

LEFT_SEMI_JOIN

. This allows you to scale to very large lists (hundreds of thousands) without crashing the FE. 3. Delete Statements The limit is stricter for

DELETE

operations to prevent metadata and locking overhead: * Default Limit: 10,000 elements. * Controlled by:

max_allowed_in_element_num_of_delete

(FE configuration). 4. Performance Limit (Pushdown) Even if the query executes, there is a "performance limit" for storage-level filtering: * Pushdown Limit: 1,024 elements. * Controlled by:

max_pushdown_conditions_per_column

(BE configuration). * Behavior: If your

IN

list has more than 1,024 items, StarRocks may stop "pushing down" the filter to the storage engine (where it's fastest) and instead filter the data in the executor layer. This won't fail the query, but it will make it slower. Summary Table Context | Default Limit | Key Configuration Standard SELECT | 10,000 |

expr_children_limit

Large IN Optimization | 100,000+ |

large_in_predicate_threshold

DELETE Statement | 10,000 |

max_allowed_in_element_num_of_delete

Storage Pushdown | 1,024 |

max_pushdown_conditions_per_column

Pro-tip: If you find yourself needing an

IN

list with millions of elements, it is often more performant to load those values into a temporary/side table and use a standard

JOIN

instead. References • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • sql-reference/System_variable.md • administration/management/BE_configuration.md

@Rocky So my current filter has a batch IN size of 1000. I should increase it to 10000 for efficiency?

Actually, increasing your batch size from 1,000 to 10,000 might decrease performance unless you also change some backend configurations. While larger batches reduce the number of round trips (network/RPC overhead), StarRocks has specific thresholds that optimize how

IN

predicates are handled: 1. The Storage Pushdown Limit (1,024) The most important limit is

max_pushdown_conditions_per_column

(a BE configuration), which defaults to 1,024. * At 1,000 elements: StarRocks "pushes down" the filter to the storage engine. It can use Zone Maps, Bloom filters, and bitmap indexes to skip reading irrelevant data from the disk. This is extremely fast. * At 10,000 elements: You cross the 1,024 limit. The storage engine will likely scan all data in the relevant columns and send it to the execution layer to be filtered. This significantly increases I/O and CPU usage on the BE. 2. FE Planning Overhead The default

expr_children_limit

in the Frontend is typically 10,000. * If you send exactly 10,000 elements, you are right at the edge. If your SQL includes other filters or logic, you might hit this limit and get a "Too many expressions" error. 3. Partition Pruning (100) If the column you are filtering on is a Partition Key, the limit is even lower. The variable

range_pruner_max_predicate

(default 100) controls how many values can be used to skip partitions. Beyond this, StarRocks may stop pruning partitions effectively. Recommendation: * Stick with 1,000: Your current size of 1,000 is a "sweet spot" because it fits perfectly under the storage pushdown limit (1,024). It is very efficient. * If you must go to 10,000: You should first increase the BE configuration

max_pushdown_conditions_per_column

to

10000

(or higher) to ensure you don't lose the index/storage-level filtering. * If you have >100,000 values: Do not use

IN

. Load the values into a temporary table or a separate table and use a

JOIN

. StarRocks is highly optimized for joins, and a

JOIN

will be significantly faster and more memory-efficient than a massive

IN

list. Summary: 1,000 is actually safer and likely faster for the database than 10,000. Unless your network latency is very high, the benefit of fewer queries is outweighed by the loss of storage-level optimizations. References • administration/management/BE_configuration.md • sql-reference/System_variable.md • administration/management/FE_configuration.md References • sql-reference/System_variable.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • data_source/catalog/elasticsearch_catalog.md • using_starrocks/caching/query_cache.md

@Rocky What if I already partition prune to 15 minutes of data?

Even if you have already pruned your search to a 15-minute window, the choice between 1,000 and 10,000 elements in an

IN

predicate still involves a trade-off between metadata-level pruning and storage-level indexing. Here is how the 15-minute pruning changes the math: 1. Partition Pruning is "Stage 1" (Frontend) By filtering for a 15-minute window, you have already performed Partition Pruning. The Frontend (FE) has successfully discarded 99% of your data before the query even reaches the storage nodes (BEs). This is the most "expensive" work already done. 2. Storage Pushdown is "Stage 2" (Backend) Even within that 15-minute window, StarRocks needs to find your specific rows. * With 1,000 elements: These are pushed down to the storage engine. The BE will use Zone Maps, Bloom Filters, and Bitmap Indexes to pinpoint the exact rows inside the 15-minute files. It only reads the data it needs. * With 10,000 elements: You exceed the

max_pushdown_conditions_per_column

limit (1,024). The BE will scan the entire 15-minute slice of data for that column and filter it in the execution memory. When should you move to 10,000? Since you've already narrowed the data to 15 minutes, the "penalty" for scanning the remaining data is much smaller than scanning a whole day. You should consider 10,000 only if: 1. High Network Latency: If the round-trip time between your application and StarRocks is high, sending 1 query of 10k is better than 10 queries of 1k. 2. Small Data Volume: If 15 minutes of data is very small (e.g., only a few MBs or a few thousand rows per tablet), the cost of "scanning" that data is actually lower than the overhead of planning 10 separate queries. 3. Modified BE Config: If you decide to go to 10,000, you should ideally update your cluster configuration so you don't lose the storage index:

sql
    -- Set this globally so the 10,000 elements are still pushed to the index
    SET GLOBAL max_pushdown_conditions_per_column = 10000;

When should you stay at 1,000? 1. High Concurrency: If 15 minutes of data still represents millions of rows (common in high-ingestion logs), scanning that whole 15-minute chunk 100 times a second will spike your CPU. 2. Default Configs: If you cannot change the BE configuration, stay at 1,000 to keep your queries using the indexes. Summary If your 15-minute window is dense (millions of rows), stay at 1,000. If your 15-minute window is sparse (tens of thousands of rows), move to 10,000 to save on query overhead, but make sure to set

max_pushdown_conditions_per_column = 10000

to keep it efficient. References • administration/management/BE_configuration.md • sql-reference/System_variable.md • best_practices/partitioning.md • unloading/Spark_connector.md • administration/management/FE_configuration.md • using_starrocks/caching/query_cache.md References • best_practices/partitioning.md • sql-reference/System_variable.md • using_starrocks/caching/query_cache.md • knowledge/usage/best-practice.md • release_notes/release-2.4.md

@Rocky What command to run to see transactions that failed or rejected or cancelled

To view transactions or load jobs that have failed, rejected, or been cancelled, you can use the following commands depending on the type of operation you are investigating. 1. General Load Jobs (Broker Load, Spark Load, Insert) For most high-level load tasks, you can filter the load history by state:

sql
-- View failed or cancelled jobs in the current database
SHOW LOAD WHERE State = 'CANCELLED';

If you are looking for a specific failure reason, check the ErrorMsg and TrackingSQL columns in the result. 2. Stream Loads Stream Loads are typically tracked through logs or the

information_schema

(in newer versions). You can also view historical Stream Load tasks via:

sql
-- Shows recent stream load tasks
SHOW STREAM LOAD;

Look for status

CANCELLED

or

FAIL

. 3. Routine Loads (Kafka) For continuous loading from Kafka, the job might be "Paused" if it encounters too many errors:

sql
-- Check the state and error messages of Routine Load jobs
SHOW ROUTINE LOAD;

Look at the

State

,

Msg

, and

ErrorLogUrls

columns to find out why it stopped. 4. Low-Level Transaction Details (Internal) If you are working with the Transaction API (multi-table loads) and want to see the status of specific transactions (like those that are

ABORTED

): 1. Find the Database ID:

sql
    SHOW PROC '/transactions';

2. List transactions for that Database: Replace

<db_id>

with the ID found in the previous step. You can usually drill down into

running

or

finished

(which includes aborted) states:

sql
    -- Example for DB 10005
    SHOW PROC '/transactions/10005';

3. Inspect a specific transaction: If you have a specific

TransactionId

, you can see the exact reason for failure:

sql
    SHOW TRANSACTION WHERE id = 12345;

5. Checking Data Integrity/Rejection (Error Log) If a transaction finished but rejected certain rows (e.g., due to data format errors), StarRocks provides a URL to the error log in the

ErrorLogUrls

column of the

SHOW LOAD

or

SHOW ROUTINE LOAD

output. You can open that URL in a browser to see the specific rows that were rejected. Summary of States * CANCELLED / ABORTED: The transaction failed or was manually stopped. * FINISHED / VISIBLE: The transaction was successful. * PREPARE / COMMITTED: The transaction is in progress or awaiting visibility. References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • sql-reference/sql-statements/loading_unloading/SHOW_LOAD.md • sql-reference/sql-statements/loading_unloading/SHOW_TRANSACTION.md • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD.md References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md