http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> We run StarRocks 4.1 shared-data, 3...
# questions-and-troubleshooting
s
@Rocky We run StarRocks 4.1 shared-data, 3 CN and 3 FE. When we run a large INSERT INTO ... SELECT FROM FILES() (parquet on S3), CN network in/out grows a lot (~500–600 MB/s per CN). CPU stays around 25% and memory looks fine. At the same time our interactive SELECTs on other tables get slow. Some hit a 10s deadline and cancel with ScanRows=0 / ErrorCode CANCELLED — they never start a storage scan. After the load finishes, the same SQL is fast again (hundreds of ms). Loads and queries share the same CNs, same default_warehouse and default_wg. No separate CN group. What do you recommend in 4.1 shared-data to keep large FILES loads from stalling interactive queries? Separate warehouse + CN group? Resource groups on the same CNs? Something else? Any limits or gotchas on a 3-CN cluster?
r
In StarRocks 4.1 shared-data mode, the behavior you're seeing—where queries cancel with 
ScanRows=0
during heavy I/O—usually indicates that the interactive queries are being queued or blocked at the scheduler level before they can even begin scanning data. Since your CPU is low (25%) but network I/O is high (500–600 MB/s), the bottleneck is likely network bandwidth saturation or Scan Thread Pool exhaustion, not raw compute power. Here are the recommended strategies to isolate these workloads in StarRocks 4.1: 1. The Best Practice: Separate Warehouses (Strong Isolation) In shared-data (Cloud Native) StarRocks, Warehouses are the primary unit of compute isolation. Each warehouse consists of its own set of CN nodes. * Recommendation: Create a dedicated warehouse for loading. * Why: Since loads via 
FILES()
and 
INSERT
involve heavy S3 traffic (downloading source files and writing segments), they compete for the same network interface as your 
SELECT
queries (which must download data from S3 into the local cache). * Action:
Copy code
sql
    -- 1. Create a warehouse for loading (e.g., 1 CN node)
    CREATE WAREHOUSE load_warehouse WITH ("num_nodes" = "1");

    -- 2. In your loading script/session, switch to this warehouse
    SET warehouse = 'load_warehouse';
    INSERT INTO target_table SELECT * FROM FILES(...);
Note: For a 3-CN cluster, you could split it into a 2-CN warehouse for queries and a 1-CN warehouse for loads. 2. The Cost-Effective Fix: Resource Groups (Soft Isolation) If you must keep all 3 CNs in the 
default_warehouse
, you must move the load out of the 
default_wg
to prevent it from "starving" the interactive queries. * Recommendation: Create a Resource Group for "Large Loads" and limit its concurrency and CPU weight. * Action:
Copy code
sql
    CREATE RESOURCE GROUP load_group
    PROPERTIES (
        "cpu_core_limit" = "5", -- Limit CPU usage
        "concurrency_limit" = "2", -- Limit concurrent heavy loads
        "type" = "user"
    );

    -- Bind the loading user or session to this group
    SET RESOURCE_GROUP = 'load_group';
* Key Variable: Enable Group-level Query Queuing. This prevents the load from grabbing all available execution slots:
Copy code
sql
    SET GLOBAL enable_group_level_query_queue = true;
3. Tuning the 
FILES()
Load Session The 
FILES()
function can be very aggressive. You can throttle the load's impact on the network by reducing its parallelism: * 
pipeline_dop
: Reduce this in the loading session (e.g., to 
2
or 
4
) to limit the number of parallel scan/write threads. * 
parallel_fragment_exec_instance_num
: Lowering this reduces the number of instances per CN node. * Network Bandwidth: 500-600 MB/s is roughly 4-5 Gbps. If your CN instances have a 10 Gbps limit, the combination of "S3 Read (Files)" + "S3 Write (Insert)" + "S3 Read (Interactive Query)" can easily saturate the link, leading to the RPC timeouts and cancellations you observed. Why 
ScanRows=0 / ErrorCode CANCELLED
? This specific error in StarRocks typically means: 1. Queue Timeout: The query was put in a queue (due to 
default_wg
limits) and hit the 
query_timeout
or a hard internal deadline (often 10s for the scheduler) before a CN could accept it. 2. RPC Congestion: The FE failed to send the query plan to the CNs because the CN's network buffers were full or the RPC threads were blocked by the heavy data movement of the load. Summary Checklist for a 3-CN Cluster: 1. Split Warehouses: Try a 2-node warehouse for queries and a 1-node warehouse for loads. This provides physical network and I/O isolation. 2. Throttling: If staying on one warehouse, use 
SET RESOURCE_GROUP
for loads and lower the 
pipeline_dop
for the 
INSERT
session.
3. Monitor Network: Check if your cloud provider's network throughput limits (e.g., AWS EBS/Network bandwidth) are being hit. 600 MB/s per node is high for many mid-tier instance types. References • loading/loading_introduction/troubleshooting_loading.md • loading/hdfs_load.md
s
@Rocky Follow-up on our setup — we are not CelerData Cloud. We run kube-starrocks operator 1.11.4, community image 4.1.4 (not enterprise). 3 FE + 3 CN, all in default_warehouse / default_wg. No StarRocksWarehouse CR and no enterprise CN image, so CREATE WAREHOUSE WITH ("num_nodes"=...) is not something we can run today. Given that, what works better for isolating large FILES() loads from interactive SELECTs
r
Since you are using the StarRocks 4.1 Community Edition (not Enterprise/CelerData Cloud), you do not have access to the 
CREATE WAREHOUSE
or 
StarRocksWarehouse
CRD for multi-warehouse physical isolation. In your 3-CN cluster, all nodes belong to the 
default_warehouse
. To prevent large 
FILES()
loads from stalling interactive queries, you should use Resource Groups with "Hard" CPU isolation and Query Queuing. 1. Hard CPU Isolation with 
exclusive_cpu_cores
 In v3.3.5+, StarRocks introduced Exclusive Resource Groups which provide "hard" CPU isolation. This prevents a heavy load from stealing all CPU cycles from interactive queries. * How it works: You reserve a specific number of CPU cores for a group. These cores are not shared with other groups. * Recommendation: Create an "Interactive" group with reserved cores and a "Load" group that uses the remaining "Shared" cores.
Copy code
sql
-- 1. Create an Exclusive Group for Interactive Queries (e.g., reserve 8 cores per CN)
CREATE RESOURCE GROUP interactive_rg
PROPERTIES (
    "exclusive_cpu_cores" = "8", -- Reserved for SELECTs
    "mem_limit" = "0.5",         -- 50% Memory
    "concurrency_limit" = "50"
);

-- 2. Create a Shared Group for Loads
CREATE RESOURCE GROUP load_rg
PROPERTIES (
    "cpu_weight" = "1",         -- Lower weight relative to other groups
    "mem_limit" = "0.3",        -- 30% Memory
    "concurrency_limit" = "2"   -- Limit concurrent FILES() loads
);

-- 3. Bind users to these groups
ALTER USER 'loader_user' SET PROPERTIES ("resource_group" = "load_rg");
2. Prevent "ScanRows=0" with Query Queuing The 
ScanRows=0 / CANCELLED
error usually means your interactive queries are being rejected or timing out because the 
default_wg
is overwhelmed. * Action: Enable Resource Group-level Query Queuing. This allows queries to wait in a queue instead of failing immediately with RPC errors when the CN is busy downloading data from S3.
Copy code
sql
-- Enable queuing globally
SET GLOBAL enable_query_queue_select = true;
SET GLOBAL enable_group_level_query_queue = true;

-- Adjust the interactive group to have a high priority queue
ALTER RESOURCE GROUP interactive_rg PROPERTIES (
    "concurrency_limit" = "100"
);
3. Throttling the 
FILES()
Load (Session Level) The 
FILES()
function is extremely aggressive with network and threads. Since you see 500–600 MB/s per CN, you are likely saturating the node's Network Bandwidth (especially on cloud instances like AWS where 10Gbps is a common limit). Before running your 
INSERT INTO ... SELECT FROM FILES()
, set these variables in the loading session:
Copy code
sql
-- Reduce parallelism of the load to leave 'slots' for SELECTs
SET pipeline_dop = 2; -- Default is usually 0 (auto-scaled to all cores)
SET parallel_fragment_exec_instance_num = 2;

-- Throttling the scan (Shared-data specific)
SET scan_thread_pool_queue_size = 100;
4. Why this happens on 3-CN clusters * RPC Congestion: In 4.1 shared-data, 
FILES()
downloads data to local cache and then the 
INSERT
writes it back to S3. This creates a "double-hit" on the network interface. If the network is saturated, FE-to-CN heartbeats and query plan delivery (RPC) will time out, causing queries to cancel before they even start (hence 
ScanRows=0
). * Cache Contention: The load is likely flushing the data cache of your interactive tables. Check the 
datacache_read_throughput
 and 
datacache_hit_ratio
 metrics during the load. Summary Checklist for Community 4.1: 1. Isolate CPU: Use 
exclusive_cpu_cores
for your interactive resource group. 2. Throttle Network: Manually lower 
pipeline_dop
for the 
FILES()
session to reduce the 600MB/s spike. 3. Check Instance Specs: Verify your K8s node's network throughput limits. If you are hitting 5-6 Gbps consistently per node, you may need larger instance types or more CN nodes to spread the I/O.
s
@Rocky Subject: INSERT FROM FILES causing query latency spikes on lake storage Setup: 3 CNs, lake storage (S3), PRIMARY KEY tables, 
merge_condition
, 
files_pipeline_dop=4
, resource group with 
mem_limit=30%
+ 
concurrency_limit=10
. Observation: Large INSERT FROM FILES (18.9M rows, 25.8 GB) causes FE query latency to spike 10–55x across all FEs, taking 7+ minutes to recover. CPU is flat during the insert. No thread queue saturation. Network send spikes 25x but also spikes at other times without slowness. Questions: 1. Is there a known interaction between large INSERT FROM FILES with 
merge_condition
on PRIMARY KEY lake tables and concurrent read query degradation? What resource is actually being contended? 2. What's the right diagnostic to run during the INSERT to identify the bottleneck — is there a SHOW PROC path or internal metric we should capture in real time?
r
Based on your observations and the internal architecture of StarRocks 4.1 shared-data (lake) mode, here is the breakdown of why a large 
INSERT FROM FILES
with 
merge_condition
on a Primary Key (PK) table is causing cluster-wide read degradation. 1. Interaction Analysis: Why the Spike? In StarRocks, Primary Key tables on lake storage use a "Read-on-Write" mechanism. When you run an 
INSERT
with a 
merge_condition
, the system does more than just append data; it performs a complex metadata synchronization. * The "Double Hit" on FE Metadata Locks: In version 4.1, every load job has a Commit Stage. For a 25GB/18.9M row PK load, the FE must process a massive amount of "Delete Vectors" and update the "Persistent Index" metadata. * Contention: During the 
PUBLISH
phase of the transaction, the FE may hold an EXCLUSIVE lock on the Database or Table metadata. Since your interactive 
SELECT
queries need a SHARED lock to plan the query and get the latest data version, they are queued behind the load's commit. This explains why latency spikes across all FEs—they are all waiting on the Leader FE to release the metadata lock. * Persistent Index & Cache Pressure: PK tables in shared-data mode store their index in S3 but cache it on the CN's local disk/memory. A 25GB load likely forces the CNs to fetch large portions of the PK index from S3 to perform the 
merge_condition
lookups. * Contention: Even if CPU is flat, the Scan Thread Pool and Local I/O (used for the persistent index cache) can be saturated. If the interactive queries need to scan the same table, they compete for the same local cache and I/O bandwidth. * Network "Send" Spike: The 25x network spike is the CNs writing the new segments plus the updated Delete Vectors and Index files back to S3. This saturates the RPC channels used for FE-to-CN communication, leading to the 
ScanRows=0
(query timed out before it could start) symptoms. 2. Recommended Diagnostics To prove exactly where the bottleneck is, run these three commands while the load is running and queries are slow: A. Check for Metadata Lock Contention (FE) This will tell you if queries are stuck waiting for the load to finish its commit.
Copy code
sql
SELECT lock_type, lock_object, lock_mode, hold_time_ms, waiter_list 
FROM sys.fe_locks 
WHERE hold_time_ms > 1000;
_If you see an 
EXCLUSIVE
lock on your table with a long 
waiter_list
, the FE Lock Manager is the bottleneck._ B. Check for Ingestion Slowdown (FE) StarRocks will intentionally slow down loads if the back-end compaction can't keep up.
Copy code
sql
-- Replace 'your_db' with your database name
SHOW PROC '/transactions/your_db/running';
Look at the 
ErrMsg
column. If it says 
Partition's compaction score is larger than 100.0, delay commit
, the system is stalling the load (and potentially affecting metadata) because it's overwhelmed. C. Analyze a "Slow" Query Profile Capture the profile of a simple 
SELECT
that should take <1s but is taking 10s+:
Copy code
sql
SET is_report_success = true;
-- Run your query
SHOW PROFILE;
*Look for 
QueryScheduleTime
 or 
PendingTime
. If these are high, the query is stuck in an FE queue or waiting for a CN fragment slot. If 
GetPartitionInfoTime
 is high, it's a metadata lock issue.* 3. Immediate Mitigations for 4.1 Community 1. Lower the Load Concurrency: Even if you have 
files_pipeline_dop=4
, the sheer size of the transaction is the issue. Try setting 
SET chunk_size = 2048;
and lowering 
pipeline_dop
to 2 for the load session to reduce the "burst" of metadata the FE has to process at once. 2. Enable Table-Level Locking: Ensure the FE is using the modern lock manager to avoid locking the entire database: * Check 
ADMIN SHOW FRONTEND CONFIG LIKE 'lock_manager_enabled';
. If 
false
, set it to 
true
(requires FE restart in some versions, check your specific 4.1.x minor release notes).
3. Increase Compaction Threads (CN): In 
be.conf
, increase 
compact_threads
(default is often 4) to 8 or 16 to help the CNs process the PK "Read-on-Write" overhead faster. 4. Batch the Insert: If possible, break the 25GB file load into 5-10 smaller 
INSERT
statements. Large PK transactions in shared-data mode create very "heavy" versions that take longer for the FE to publish. References • sql-reference/sys/fe_locks.md • knowledge/trouble_shooting/fe_deadlock_problems.md • knowledge/usage/best-practice.md
s
@Rocky on StarRocks 4.1.6 shared-data we have an unpartitioned PK table as you suggested:
Copy code
PRIMARY KEY (session_id, integration_id)
ORDER BY (last_activity, session_id)
UI query:
Copy code
SELECT ...
FROM sessions_list_poc
WHERE last_activity >= :from AND last_activity < :to
ORDER BY last_activity DESC, session_id DESC
LIMIT 50;
EXPLAIN ANALYZE
shows: • Filter prune works (2.2M → 454K when the window is smaller) — good • But 
OLAP_SCAN OutputRows
= all rows in the filter, then 
TOP_N
cuts to 50 • Not “seek to newest on sort key and stop after 50” Is that expected for PK tables in 4.1.6? Can Top-N on the leading sort key short-circuit the scan, or do we always scan the full filter range then Top-N? Any knobs/hints to make 
LIMIT 50
read only ~50 rows?
r
In StarRocks 4.1.6, the behavior you are seeing—where the 
OLAP_SCAN
node outputs all rows matching the 
WHERE
clause before the 
TOP_N
operator filters them—is generally expected behavior for unpartitioned tables, even with a sort key. Here is the breakdown of why this happens and how to optimize for it: 1. Why it doesn't "Short-Circuit" automatically While StarRocks uses the 
ORDER BY
columns to build a Prefix Index (Short Key Index) for range pruning, it does not currently support a "reverse index scan" that starts from the end of a range to satisfy a 
DESC
limit query with zero-overhead. * Scan vs. Filter: The 
OLAP_SCAN
node's job is to identify all segments and data pages that could contain the data in your 
WHERE
range (
last_activity >= :from
). * Storage Order: Data is physically stored on disk in ascending order of your sort key. To find the "top 50 DESC" rows, the engine must identify the qualifying range and then find the largest values. In the execution plan, this usually results in the Scan node materializing all rows in that range and passing them to the Top-N operator. * Vectorized Execution: StarRocks processes data in chunks (typically 4096 rows). Even if it could stop early, it will often finish processing the current chunk or segment before terminating, leading to a higher 
OutputRows
count in the profile than the 
LIMIT
. 2. Can Top-N push down? StarRocks does have a Top-N Filter Pushdown optimization (introduced/enhanced in v3.3+), but its effectiveness depends on specific factors: * 
enable_topn_runtime_filter
: Ensure this is set to 
true
(it is by default). This variable allows the 
TOP_N
operator to send a dynamic filter back to the 
SCAN
node. As the query finds "better" top-50 candidates, it tells the scan to ignore anything smaller/larger than the current 50th value. * Sort Key Match: Pushdown is most effective when the query 
ORDER BY
matches the table's 
ORDER BY
prefix perfectly. Because you are querying 
DESC
while the storage is likely 
ASC
, the engine has to "work backwards" from the end of the range, which is less efficient than a forward short-circuit. 3. Recommended Knobs and Strategies To minimize the number of rows read and processed: A. Enable Late Materialization If your 
SELECT
list includes many columns (especially large strings), StarRocks might be reading them all for every 454K rows. You can force the engine to only read the sort-key columns first:
Copy code
sql
SET enable_late_materialization = true;
This ensures the engine only fetches the non-sort-key columns for the final 50 rows after the Top-N sorting is done. B. Verify the Plan for "TOP-N Pushed Down" Run 
EXPLAIN VERBOSE
on your query. Look at the 
OLAP_SCAN
operator. * If you see 
TOP-N pushdown: true
 or a 
limit: 50
property directly inside the 
OLAP_SCAN
node, the optimization is active. * If you see a 
partitionLimit
or 
tabletLimit
in the scan, it means StarRocks is trying to limit rows at the storage level. C. The "Seek" Method (Keyset Pagination) If the 454K row scan is still too slow for a UI, the most reliable "knob" isn't a system variable, but a query pattern change. Instead of just using a time range, use the last-seen value from your UI:
Copy code
sql
WHERE last_activity <= :last_seen_activity 
  AND (last_activity < :last_seen_activity OR session_id < :last_seen_session_id)
ORDER BY last_activity DESC, session_id DESC
LIMIT 50;
By adding the 
last_activity <= :last_seen_activity
predicate, you allow the Prefix Index to prune almost all rows "newer" than your current page, which will dramatically reduce the 
OLAP_SCAN OutputRows
. Summary * Is it expected? Yes, especially with a 
DESC
order on 
ASC
storage. * Does it read all rows? Usually, it reads all rows within the 
WHERE
range and then sorts.
* Diagnostic: Check 
ShortKeyFilterRows
in the Query Profile. If this is high, the Prefix Index is working. If 
OutputRows
is still high, the Top-N filter is not effectively stopping the scan early. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • knowledge/usage/best-practice.md
s
Hey @Rocky , quick one on the scan ghost-delete. 
INSERT INTO live (pk, __op) SELECT … LEFT ANTI JOIN live_scan
fails on our cluster with 
Unknown column '__op'
. Looks like 
__op
only works in Stream/Broker/Routine Load, not 
INSERT … SELECT
. We switched to a PK-table delete: 
DELETE FROM live WHERE integration_id = ? AND ingest_time < ? AND pk NOT IN (SELECT pk FROM live_scan WHERE integration_id = ?)
pk is 
NOT NULL
on both sides, and staging is partitioned by 
integration_id
. Is this OK at our scale, or would you do it differently (
NOT EXISTS
, 
DELETE … USING
, something else)? Anything to watch for with the DELETE running while NRT Stream Loads write to the same table?
r
Correct—in StarRocks, the 
__op
"magic column" is a feature of the Load Sink (Stream/Broker/Routine Load) and is not currently recognized as a special control column in a standard 
INSERT INTO ... SELECT
statement. To perform a "ghost-delete" (removing rows from the target that are missing in your staging set) on a Primary Key table at your scale, here is the breakdown of your proposed 
DELETE
vs. alternatives. 1. The 
NOT EXISTS
/ 
NOT IN
Approach Your proposed 
DELETE
query is valid, but I would recommend using 
NOT EXISTS
 over 
NOT IN
for performance and safety reasons (even with 
NOT NULL
columns, 
NOT EXISTS
is generally more robust in the MPP optimizer):
Copy code
sql
DELETE FROM live 
WHERE integration_id = ? 
  AND ingest_time < ? 
  AND NOT EXISTS (
      SELECT 1 FROM live_scan 
      WHERE live_scan.integration_id = live.integration_id 
        AND live_scan.session_id = live.session_id
  );
* Optimizer: StarRocks will plan this as a 
LEFT ANTI JOIN
. * Scale: For ~20M rows, this is perfectly fine. The PK table's "Read-on-Write" architecture handles this by generating a Delete Vector. It doesn't actually rewrite the data files; it just marks those IDs as "dead" in metadata. * Composite PK: Since your PK is 
(session_id, integration_id)
, ensure the join inside the 
NOT EXISTS
uses both columns. If you only join on one, you'll delete far more than you intended. 2. Is there a "Better" way? (
DELETE ... USING
) StarRocks supports 
DELETE ... USING
, but it is primarily designed for Inner Joins (deleting rows that do exist in another table). Doing a "ghost-delete" (Anti-Join) with the 
USING
syntax is technically possible with a 
LEFT JOIN ... WHERE staging.pk IS NULL
, but it is often less readable than 
NOT EXISTS
. Stick with 
NOT EXISTS
. 3. Concurrency: DELETE vs. NRT Stream Loads Since you have active Stream Loads hitting the same table, watch for these three things: 1. Metadata Lock Contention: Both the 
DELETE
and the 
Stream Load
are transactions. A very large 
DELETE
might hold the Database/Table lock during the 
Commit
phase longer than a small Stream Load. If the 
DELETE
takes 30s to commit, your Stream Loads will queue up. _ _Tip:* Keep the 
DELETE
scoped to a single 
integration_id
(as you are doing) to keep transaction times short. 2. Compaction Score: Every 
DELETE
creates a new "version." If your NRT loads are already creating hundreds of versions, a massive 
DELETE
adds pressure to the CN's compaction threads. Monitor `show proc '/compactions'`—if the score stays >100, your 
SELECT
queries will start to slow down. 3. The "Late Arrival" Race Condition: _ If a Stream Load inserts a record for 
integration_id=123
_during* your 
DELETE
transaction, but that record wasn't in your 
live_scan
staging table, it might get deleted if your 
WHERE ingest_time < ?
filter isn't precise enough. _ Recommendation: Always use a strictly defined timestamp or a "batch ID" to ensure the 
DELETE
only targets data that was _already there* when your 
live_scan
was created. 4. Summary Recommendation * Use 
NOT EXISTS
 with the full composite PK in the subquery join. * Limit the batch size: If you are deleting millions of rows at once, try to chunk it by 
integration_id
or another logical dimension to avoid massive single transactions. * Avoid `INSERT OVERWRITE`: While faster, it is partition-level. If you only want to delete some rows within a partition based on a join, 
DELETE
is the correct tool for PK tables. References • sql-reference/sql-statements/table_bucket_part_index/DELETE.md • table_design/table_types/primary_key_table.md • faq/operation_maintenance_faq.md
Open in Slack
PreviousNext
Powered by