http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> ```2026-04-30T13:51:05.007Z ERROR [...
# questions-and-troubleshooting
m
@Rocky
Copy code
2026-04-30T13:51:05.007Z	ERROR	[StarRocks] flush failed after retries	{"schema": "conv_primary", "label": "sr_requests_1777557060000370803_22360", "rows": 8, "batch_bytes": 1218, "error": "stream load returned status \"Fail\": Failed to write data to stream load pipe, num retry: 10, write_data: 31 us, rpc: 3571us, wait_pipe: 5000664 us, last error: Capaticy limit exceeded: No available stream load pipe"}
2026-04-30T13:51:05.007Z	ERROR	[StarRocks] flush failed after retries	{"schema": "click", "label": "sr_requests_1777557060000145171_22357", "rows": 112, "batch_bytes": 17088, "error": "stream load returned status \"Fail\": Failed to write data to stream load pipe, num retry: 10, write_data: 21 us, rpc: 3954us, wait_pipe: 5000651 us, last error: Capaticy limit exceeded: No available stream load pipe"}
2026-04-30T13:51:05.047Z	ERROR	[StarRocks] flush failed after retries	{"schema": "win", "label": "sr_requests_1777557060000145007_22356", "rows": 8149, "batch_bytes": 1388913, "error": "stream load returned status \"Fail\": Failed to write data to stream load pipe, num retry: 10, write_data: 24 us, rpc: 2376us, wait_pipe: 5000657 us, last error: Capaticy limit exceeded: No available stream load pipe"}
2026-04-30T13:51:05.083Z	ERROR	[StarRocks] flush failed after retries	{"schema": "loss", "label": "sr_requests_1777557060000139731_22355", "rows": 17038, "batch_bytes": 3179534, "error": "stream load returned status \"Fail\": Failed to write data to stream load pipe, num retry: 10, write_data: 35 us, rpc: 2184us, wait_pipe: 5000661 us, last error: Capaticy limit exceeded: No available stream load pipe"}
2026-04-30T13:51:05.118Z	ERROR	[StarRocks] flush failed after retries	{"schema": "win", "label": "sr_requests_1777557060000009582_22348", "rows": 5447, "batch_bytes": 928692, "error": "stream load returned status \"Fail\": Failed to write data to stream load pipe, num retry: 10, write_data: 28 us, rpc: 4669us, wait_pipe: 5000672 us, last error: Capaticy limit exceeded: No available stream load pipe"}
My backends are all alive. THey have no memory or CPU usage issues. Disk usage hit ~0.94 and shortly after ingestion has been failing ever since. Disk usage are all now below 0.8. Why is ingestion failing
r
The error message 
"Capaticy limit exceeded: No available stream load pipe"
 (note the typo "Capaticy" in the internal code) indicates that the StarRocks Backend (BE) is rejecting the ingestion request because it believes the disk capacity has reached a critical threshold. Even though your disk usage has dropped below 0.8 (80%), here is why ingestion might still be failing and how to resolve it: 1. Storage Flood Stage Threshold StarRocks has a safety mechanism controlled by the BE configuration parameter 
storage_flood_stage_usage_percent
. * Default Value: Usually 95% (but may vary by version or environment). * Behavior: When disk usage on any storage path of a BE exceeds this threshold, the BE enters a "flood stage" and rejects all new load requests to prevent the disk from completely filling up, which could corrupt metadata. * Why it's still failing: You mentioned usage hit 0.94. If your environment has a lower threshold (e.g., 90%) or if a specific disk partition is still hovering near the limit, the BE will continue to reject loads. 2. OS Disk Usage vs. StarRocks Data Usage StarRocks calculates disk usage based on the filesystem. There is often a discrepancy between what StarRocks reports as "data used" and what the OS reports (
df -h
): * Trash Directory: When you delete data in StarRocks, files are moved to a 
trash
directory on the BE for a period (default 3 days, controlled by 
trash_file_expire_time_sec
). These files still occupy physical disk space. * Log Files: Large BE logs (especially if 
sys_log_level
is set to 
DEBUG
) can consume significant space. * Check OS: Verify the actual disk usage on the BE nodes using 
df -h
. If the OS sees the disk at >90-95%, StarRocks will block ingestion regardless of how much actual table data you have. 3. "Sticky" State and Frontend Status The Frontend (FE) maintains the state of each BE. If a BE reports it is full, the FE might mark it as "Unwritable." * Check Status: Run the following command in your MySQL client:
Copy code
sql
    SHOW BACKENDS\G
* What to look for: Check the 
MaxDiskUsedPct
and the 
CanWrite
columns. If 
CanWrite
is 
false
, the FE will not schedule load tasks to that BE. * Refresh: The BE sends heartbeats to the FE every few seconds. If the disk is truly below the threshold now, the status should eventually flip back to 
true
. If it doesn't, the BE might be stuck in a state where it thinks the disk is still full. Recommended Actions 1. Check Physical Disk Space: Run 
df -h
on your BE nodes. If any partition used by StarRocks is over 90-95%, you must free up OS-level space. 2. Clean the Trash: If you recently deleted data to free up space, you can force-clear the trash or wait for the expiration. You can also temporarily reduce 
trash_file_expire_time_sec
in 
be.conf
and restart. 3. Adjust Threshold (Temporary Fix): If you are certain the disk is safe, you can temporarily increase the threshold to resume ingestion. In `be.conf`:
Copy code
properties
    storage_flood_stage_usage_percent = 98
(Note: This requires a BE restart or a dynamic update via the 
curl
API). 4. Check BE Logs: Check the 
<http://be.INFO|be.INFO>
or 
be.WARNING
logs on the nodes. Search for "capacity" to see exactly which disk path is triggering the limit. 5. Restart BE: If 
SHOW BACKENDS
still shows 
CanWrite: false
and the disk is clearly empty, a BE restart will force a state refresh. Summary The system likely triggered a protection mode when usage hit 94%. Check your OS-level disk usage rather than just StarRocks data metrics, as hidden files like logs or the 
trash
directory are likely keeping the physical filesystem usage above the 
storage_flood_stage_usage_percent
threshold. References • administration/management/monitoring/metrics.md
m
@Rocky
Copy code
ob_id=-1, txn_id: -1, label=sr_requests_1777558067783257785_22819, db=requests
Apr 30 14:08:53 ip-172-30-81-176.ec2.internal starrocks-be[53225]: W20260430 14:08:53.311751 22558980138560 stream_load.cpp:164] Fail to handle streaming load, id=0e4c027e54f9e2a4-78e098796f8836b4 errmsg=Failed to write data to stream load pipe, num retry: 10, write_data: 7 us, rpc: 2365us, wait_pipe: 5000619 us, last error: Capaticy limit exceeded: No available stream load pipe id=0e4c027e54f9e2a4-78e098796f8836b4, job_id=-1, txn_id: -1, label=sr_requests_1777558072943921210_22610, db=requests
These is the journalctl error in my be nodes.
Copy code
storage_flood_stage_usage_percent=95
THis is configured across all nodes. Disk usage is now down to all less than 80%. Why am I still being throttled/load rejected?
r
The error 
"Capaticy limit exceeded: No available stream load pipe"
 (containing the "Capaticy" typo) is a specific error string from the StarRocks Backend (BE) that indicates the BE has entered a "flood stage" due to disk space protection. Even though you see your overall disk usage below 80%, there are several reasons why StarRocks might still believe it is in a capacity-limited state: 1. The "100 GB" Hard Limit StarRocks uses two parameters together to determine the flood stage. By default: * 
storage_flood_stage_usage_percent
= 95% * 
storage_flood_stage_left_capacity_bytes
= 107374182400 (exactly 100 GB) The Catch: In many versions, if the remaining space on a disk falls below 100 GB, the BE will trigger the flood stage regardless of whether the percentage is below 95%. * Example: If you have a 400 GB disk and it is 80% full, you have 80 GB left. Since 80 GB < 100 GB, the BE will reject loads even though the usage is only 80%. * Action: Check the absolute bytes remaining on your disks (
df -h
). If any StarRocks data partition has less than 100 GB free, this is your cause. 2. Multi-Disk Imbalance StarRocks checks disk capacity for each individual path defined in your 
storage_root_path
. * If you have multiple disks (e.g., 
/data1
, 
/data2
), and any single one of them is still above the limit (or has < 100GB left), the entire BE can be marked as unwritable for new load pipes. * Action: Run 
ls -lh
or 
df -h
on every specific path listed in your 
be.conf
. 3. Verification via 
SHOW BACKENDS
 The most reliable way to see what the cluster "thinks" is the disk status is to run this in your MySQL client:
Copy code
sql
SHOW BACKENDS\G
Look for these specific columns: * 
MaxDiskUsedPct
: If this shows a high value (or reflects the old 94% state), the BE hasn't updated its metadata yet. * 
CanWrite
: If this is 
false
, the FE will refuse to coordinate loads to this BE. * 
DataUsedCapacity
vs 
DiskCapacity
: Check if the math aligns with your OS-level 
df -h
. 4. The "Trash" Directory When you delete data to free up space, StarRocks doesn't immediately delete the files from the disk. They are moved to a 
trash/
directory within your storage root path and kept for (by default) 24–72 hours. * The OS sees this space as "used." * Action: Check the 
storage/trash
folder in your BE data directory. If it's huge, you can manually delete its contents or wait for the 
trash_file_expire_time_sec
to pass. 5. Persistent Index (Primary Key Tables) If you are using Primary Key tables, StarRocks uses a Persistent Index that takes up disk space. If the disk hit 94%, the index might have struggled to flush or expand. This usually resolves once space is cleared, but check if the BE logs mention "Persistent Index" errors. How to fix it immediately: If you have verified that the disks have enough space (e.g., more than 100GB and < 90% used) and it's still failing, you can force the BE to lower its guard by reducing the "left capacity" threshold. Run this on each BE (or via 
curl
if enabled): 1. Lower the 100GB reserve to 10GB (for example):
Copy code
bash
    curl -X POST http://<be_ip>:<be_http_port>/api/update_config?storage_flood_stage_left_capacity_bytes=10737418240
2. Ensure the usage percent is high enough:
Copy code
bash
    curl -X POST http://<be_ip>:<be_http_port>/api/update_config?storage_flood_stage_usage_percent=98
3. Restart the BE: If the BE is "stuck" in a capacity-exceeded state, a restart will force it to recalculate the disk space and re-register with the FE as 
CanWrite: true
. References • administration/management/BE_configuration.md • administration/management/FE_configuration.md
m
@Rocky All 
MaxDiskUsedPct
below 75%.
Copy code
STARROCKS-CORE-SERVICES-BE-7  98.82.170.212 // (172.30.81.176)  ~  $  df -h
Filesystem        Size  Used Avail Use% Mounted on
devtmpfs          4.0M     0  4.0M   0% /dev
tmpfs              62G     0   62G   0% /dev/shm
tmpfs              25G  8.6M   25G   1% /run
/dev/nvme0n1p1     40G  9.4G   31G  24% /
tmpfs              62G     0   62G   0% /tmp
/dev/nvme0n1p128   10M  1.3M  8.7M  13% /boot/efi
/dev/nvme1n1      738G  509G  191G  73% /mnt/starrocks/data1
/dev/nvme3n1      738G  497G  204G  71% /mnt/starrocks/data2
/dev/nvme2n1      738G  470G  231G  68% /mnt/starrocks/data3
/dev/nvme4n1      738G  337G  363G  49% /mnt/starrocks/data4
tmpfs              13G     0   13G   0% /run/user/0
tmpfs              13G     0   13G   0% /run/user/1000
This was from a representative node (highest disk usage) All EBS volumes have more than 100GB available.
r
The error message 
"Capaticy limit exceeded: No available stream load pipe"
 is a very specific error in StarRocks (complete with the "Capaticy" typo) that occurs when a Backend (BE) has reached its maximum limit of concurrent stream load pipes. Although the error message starts with "Capaticy limit," it is not referring to your disk space capacity. Instead, it refers to the capacity of the BE to handle concurrent loading sessions. Why did this happen after your disk hit 94%? When your disk usage reached the flood stage (94%), the BE started rejecting or stalling write operations. This likely caused: 1. Hanging Sessions: Existing Stream Load sessions became "stuck" waiting for disk I/O or internal locks. 2. Backlog/Leakage: If the client (or ingestion tool) didn't properly close the HTTP connections upon failure, or if StarRocks didn't clean up the "pipes" (the memory buffers for stream loads) quickly enough, those pipes stayed active in the BE's memory. 3. Exhaustion: The BE has a hard limit on how many of these pipes can exist at once. Once that limit was reached, every new request was rejected immediately with the "No available stream load pipe" error. The Root Cause: 
max_stream_load_pipe_count
 StarRocks BEs have a configuration parameter called 
max_stream_load_pipe_count
. * Default Value: 1000. * Meaning: A single BE can only handle 1000 concurrent stream load "pipes" (active data streams). * Status: Even though your disk is now at 75%, those 1000 slots are still "occupied" by the stalled or failed sessions from the 94% spike. How to Diagnose and Fix 1. Verify the current Pipe Count Check the metrics on your BE nodes to see if the pipe count is actually at the limit. Run this on one of the failing BEs:
Copy code
bash
curl <http://localhost:<be_http_port>/metrics> | grep stream_load_pipe_count
If the value is 1000 (or whatever your config is set to), you have confirmed the exhaustion. 2. Clear the state (Fastest Fix) The most reliable way to clear the abandoned/stalled pipes and reset the internal counter is to restart the BE process one by one. This will force all hanging connections to close and empty the pipe map. 3. Increase the Limit (Temporary Workaround) If you cannot restart the BE immediately, you can try to increase the limit dynamically to allow new loads to sneak through:
Copy code
bash
curl -X POST http://<be_ip>:<be_http_port>/api/update_config?max_stream_load_pipe_count=2000
Note: This just kicks the can down the road; if the sessions are leaked, they will eventually hit 2000 as well. 4. Check for Stalled Transactions The Frontend (FE) might also be holding onto "PREPARE" state transactions. Check if there is a massive backlog of transactions:
Copy code
sql
SHOW PROC '/transactions';
Look for transactions associated with the 
requests
database that are very old. Summary Your disk spike caused the ingestion to "constipate," filling up all 1000 available stream load slots. Because those slots didn't clear out automatically when the disk space was freed, the BE is still rejecting new work. Restarting your BEs will clear these pipes and resolve the issue. References • faq/loading/Stream_load_faq.md • loading/StreamLoad.md • faq/loading/Routine_load_faq.md • administration/management/monitoring/metrics.md • faq/loading/Loading_faq.md
m
@Rocky
Copy code
STARROCKS-CORE-SERVICES-BE-7  98.82.170.212 // (172.30.81.176)  ~  $  curl <http://localhost:8040/metrics> | grep stream_load_pipe_count
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0   0     0   0     0     0     0  --:--:-- --:--:-- --:--:--     0# TYPE starrocks_be_stream_load_pipe_count gauge
starrocks_be_stream_load_pipe_count 0
100 230641 100 230641   0     0 61708k     0  --:--:-- --:--:-- --:--:-- 75078k
Pipe count is 0.
r
The error message 
"Capaticy limit exceeded: No available stream load pipe"
 (containing the "Capaticy" typo) is an internal error string returned by the StarRocks Backend (BE) when it refuses to process data for a Stream Load. Even though your 
stream_load_pipe_count
is 0 and your disk usage is at 75%, you are likely hitting a "sticky" flood stage or a secondary capacity limit. Here is why this is happening and how to fix it: 1. The "Flood Stage" is checked at the Disk level Even if the Frontend (FE) shows 
CanWrite: true
, the Backend performs its own check every time data is written to a "pipe" (the memory buffer for Stream Load). StarRocks triggers the "flood stage" (refusing writes) if either of these is true for any one of your configured storage paths: * Usage % > 
storage_flood_stage_usage_percent
(Default: 95) * Free Space < 
storage_flood_stage_left_capacity_bytes
(Default: 100 GB) The Issue: While your disks are at 75% and have >100GB free now, the BE may have "latched" into a protection mode when you hit 94%. In some versions, if the disk was extremely close to full, the internal state of the 
StorageEngine
can occasionally fail to refresh its "available" status promptly, or a background process (like a large compaction) is still reserving space that 
df
doesn't see clearly. 2. Check for "Reserved" Space StarRocks calculates "Available Capacity" as: 
Actual Disk Free - Reserved Space for Compaction/In-flight writes
. When you hit 94%, StarRocks likely scheduled many compactions to try and reduce space. These tasks "reserve" disk space. If those tasks are still in the queue or stuck, the BE might think the "Effective" free space is still below the threshold. 3. Verify the BE's Internal View To see exactly what the BE thinks about its disks (and why it might be rejecting writes), run this on the problematic BE:
Copy code
bash
curl <http://localhost:8040/api/show_runtime_conf> | grep flood
And check the BE logs (
<http://be.INFO|be.INFO>
) for lines containing: 
is_flood_stage
or 
has_no_capacity
. How to resolve this: Option A: Force a Configuration Refresh (Recommended) You can "nudge" the BE to resume by temporarily lowering the threshold. This often clears the "Capaticy limit" error immediately:
Copy code
bash
# Lower the 100GB reserve to 10GB temporarily
curl -X POST <http://localhost:8040/api/update_config?storage_flood_stage_left_capacity_bytes=10737418240>
# Ensure the usage percent is high
curl -X POST <http://localhost:8040/api/update_config?storage_flood_stage_usage_percent=98>
After running these, try a small Stream Load. If it works, the BE has cleared its internal lock. You can then set them back to defaults. Option B: Check the "Trash" and "Log" folders When you cleared space to get down to 75%, if you did it by deleting files in the StarRocks data directory, they are moved to a 
trash/
subfolder. StarRocks still counts this 
trash/
folder toward its "capacity" limit. _ Check 
/mnt/starrocks/data_/storage/trash/
. _ If there is a lot of data there, delete it manually: 
rm -rf /mnt/starrocks/data_/storage/trash/*
. Option C: Restart the BE If 
starrocks_be_stream_load_pipe_count
is 0 but you still get "No available pipe," it implies the Pipe Manager is in a broken state due to the previous disk exhaustion. A restart of the BE process is the fastest way to reset the memory state and the capacity checks. Summary The BE is rejecting the write because its internal 
is_flood_stage
flag is likely still 
true
. This happens because the BE is more sensitive than the OS 
df
command. Restarting the BE or dynamically updating the 
storage_flood_stage_left_capacity_bytes
to a smaller value via the 
curl
API will usually fix this. References • administration/management/BE_configuration.md
m
@Rocky
Copy code
curl <http://localhost:8040/api/show_runtime_conf> | grep flood
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    90 100    90   0     0 46632     0  --:--:-- --:--:-- --:--:-- 90000
What does this indicate
r
The error 
"Capaticy limit exceeded: No available stream load pipe"
 (containing the "Capaticy" typo) is an internal StarRocks Backend (BE) error. It indicates that the BE is refusing to create or maintain the ingestion buffer (the "pipe") for your Stream Load because it believes the system is in a flood stage (disk protection mode). Even though your 
df -h
shows disk usage at ~75% and available space > 100 GB, there are three common reasons why the BE is still rejecting loads: 1. The 100 GB Hard Limit (
storage_flood_stage_left_capacity_bytes
) By default, StarRocks enforces two thresholds for the flood stage: * `storage_flood_stage_usage_percent`: 95% * `storage_flood_stage_left_capacity_bytes`: 107,374,182,400 bytes (100 GB) The Issue: In many versions, the BE triggers the flood stage if either condition is met for any single storage path. While your disks have ~190 GB free, StarRocks calculates "available capacity" by subtracting reserved space (for compactions and in-flight writes) from the OS-reported free space. If the BE has many background compactions queued after the 94% spike, it may believe the effective free space is actually below 100 GB. 2. FE-Side Throttling The Frontend (FE) also has its own flood stage parameters that must match the BE. If the FE still thinks the BE is full, it will fail the load coordination. Check your FE configuration (via MySQL):
Copy code
sql
ADMIN SHOW FRONTEND CONFIG LIKE '%storage_usage%';
Ensure 
storage_usage_hard_limit_percent
is set to 95 and 
storage_usage_hard_limit_reserve_bytes
is at or below 107374182400. 3. "Sticky" Flood Stage Status The BE checks disk space periodically (every 10 seconds). However, if it enters a critical flood stage, the internal 
is_flood_stage
flag can occasionally become "sticky" or fail to reset if background tasks (like the Trash cleaner or Compaction) are still holding onto file handles. ────────── How to Fix and Verify Step 1: Verify the BE's actual configuration The 
show_runtime_conf
endpoint you used might be empty if the parameters weren't changed at runtime. Use this instead to see the active config:
Copy code
bash
curl <http://localhost:8040/api/show_config?include_not_set_to_default=true> | grep storage_flood
Step 2: Manually lower the threshold to force recovery You can "nudge" the BE to resume by lowering the absolute capacity requirement. Run this on each BE:
Copy code
bash
# Reduce the 100GB requirement to 20GB temporarily
curl -X POST <http://localhost:8040/api/update_config?storage_flood_stage_left_capacity_bytes=21474836480>
Wait 30 seconds and try a load. If it works, the BE was simply being more conservative than 
df -h
suggested. Step 3: Check for "Trash" space When you deleted data to reach 75%, StarRocks may have moved those files to a 
trash/
folder. The BE still counts the 
trash/
folder as "used" until it is physically purged. _ Check 
/mnt/starrocks/data_/storage/trash/
. _ If they are full, you can manually empty them: 
rm -rf /mnt/starrocks/data_/storage/trash/*
. Step 4: Restart the BE If 
starrocks_be_stream_load_pipe_count
is 0 but you still get "No available pipe," it means the 
StreamLoadPipeMgr
is refusing to even start a session. A restart of the BE process is the most reliable way to force a fresh recalculation of disk space and reset the Pipe Manager's state. Summary The BE is likely rejecting the write because its internal "Available Capacity" calculation (which includes background task reservations) is still dipping below the 100 GB floor. Restart the BE nodes or reduce the 
storage_flood_stage_left_capacity_bytes
via the 
curl
API to resolve the value like 
53687091200
(50 GB) to unblock the ingestion. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
m
@Rocky
Copy code
ERROR 1064 (HY000): Build Exec OlapScanNode fail, scan info is invalid
This error also from trying to query table. Possibly related to flood stage?
r
Yes, this error is directly related to the disk capacity issues you experienced. When you see 
ERROR 1064 (HY000): Build Exec OlapScanNode fail, scan info is invalid
, it is the StarRocks Frontend (FE) telling you that it cannot find a healthy, queryable replica for at least one part of your table. Why did this happen? When your disks hit 94% (approaching the 95% flood stage), several things likely went wrong simultaneously: 1. Version Mismatch: When the "No available stream load pipe" error occurred, some data loads may have partially succeeded or failed to commit. This can lead to tablets having inconsistent "versions." 2. Replica Failure: StarRocks may have marked the replicas on the full Backend (BE) as "unhealthy" because they couldn't complete background tasks like compaction or metadata updates. 3. The "Flood Stage" Read Block: While StarRocks typically allows reads during a flood stage, if all replicas of a specific tablet are on nodes that hit the critical limit or became desynchronized, the FE will conclude there are "no queryable replicas" and fail the query with this "scan info is invalid" error. How to diagnose and fix 1. Identify the unhealthy tablets The most important command to run now is:
Copy code
sql
SHOW PROC '/statistic';
Look at the output columns. If you see any numbers in 
UnhealthyTabletNum
 or 
InconsistentTabletNum
, your table is physically missing "good" copies of data. 2. Find the specific "Bad" Tablet Check your FE leader's log (
fe.log
or 
fe.warn.log
). You will almost certainly see an error like: 
Failed to get scan range, no queryable replica found in tablet <tablet_id>
Once you have a 
<tablet_id>
, you can inspect it:
Copy code
sql
SHOW TABLET <tablet_id>;
Look at the 
Version
and 
IsBad
columns for each replica. 3. How to recover Since your disk space is now back to 75%, the cluster should theoretically heal itself, but it may need a "nudge": * Wait for self-healing: StarRocks has a background repair thread. If the disk is now below the limit, the FE will eventually re-synchronize the versions. This can take 5–15 minutes depending on the number of tablets. * Force a BE Check: If the BEs still think they are in flood stage (as discussed in the previous step), they won't report themselves as "queryable" to the FE. Ensure you have lowered the 
storage_flood_stage_left_capacity_bytes
or restarted the BEs as recommended previously. * Check for "Bad" replicas: If 
SHOW TABLET
shows replicas marked as 
IsBad: true
, the BE has likely detected physical corruption or a disk IO error during the 94% spike. You may need to manually drop the bad replicas or wait for the FE to clone new ones. Summary The "No available pipe" error blocked your writes, and now "scan info is invalid" is blocking your reads because the metadata is out of sync following the disk exhaustion. Immediate Action: 1. Run 
SHOW PROC '/statistic';
to see the scale of the health issue. 2. Restart the BE nodes one by one. This is the most effective way to clear the internal "flood stage" flags and force the BEs to report their latest data versions to the FE. 3. Once the BEs are back up and healthy, the 
UnhealthyTabletNum
should drop to 0, and your queries will work again. References • faq/Sql_faq.md • release_notes/release-2.5.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky
Copy code
10001,_statistics_,12,29,29,158,474,0,0,0,0
23753,default,0,0,0,0,0,0,0,0,0
1,information_schema,0,0,0,0,0,0,0,0,0
44945,requests,1,25,25,2642,2756,7,0,0,0
100,sys,0,0,0,0,0,0,0,0,0
Total,5,13,54,54,2800,3230,7,0,0,0
Unhealthy tablets not decreasing. Is this causing the read and write failures? I've already temporarily paused ingestion. What causes unhealthy tablets? My table has no replication specified (1 only)
r
This confirms the root cause: Your queries and loads are failing because 7 tablets in your 
requests
database are completely unavailable. When you set 
replication_num = 1
, you have no redundancy. If the single BE node hosting one of those tablets enters a "Flood Stage" (disk protection) or marks the tablet as "Bad," that part of the table becomes offline. This is exactly why you are seeing `Build Exec OlapScanNode fail`—the Frontend (FE) cannot find a single healthy copy of those 7 tablets to read from. Why did they become unhealthy? 1. Disk Flood Stage: When your disk hit 94%, the BE flagged itself as "unable to write." For a table with 1 replica, if an ingestion was in progress, that tablet's version became stuck or "stale" compared to the FE metadata. 2. Version Mismatch: Since there is only one replica, if it missed a version update due to the "No available stream load pipe" error, the FE now considers it "unhealthy" because it doesn't match the expected version in the metadata. 3. Replica Count Anomaly: Your output shows 
TabletNum: 2642
and 
ReplicaNum: 2756
. This indicates you have 114 extra replicas. This happens when StarRocks detects a problem and tries to "repair" a tablet by cloning it to another node. However, if those clones are failing (due to the disk capacity issues), they remain in an unhealthy state. ────────── Step-by-Step Recovery 1. Find the 7 Unhealthy Tablet IDs Run this command using the 
DbId
(44945) from your output:
Copy code
sql
SHOW PROC '/statistic/44945';
This will return a list like 
[12345, 12346, ...]
. These are the IDs of the tablets that are blocking your queries. 2. Inspect a specific Tablet Pick one ID from the list and run:
Copy code
sql
SHOW TABLET <tablet_id>;
Copy the string in the 
DetailCmd
 column and run it. Look at the following columns: * 
State
: Is it 
NORMAL
? * 
IsBad
: Is it 
true
? (If true, the BE thinks the data is corrupted or disk failed). * 
Version
: Does it match what the FE expects? 3. Force the BE to "Wake Up" Since your disk is now at 75%, the "Flood Stage" should be over, but the BE might be stuck in a protection state. * Recommended: Restart the BE process on the node(s) hosting these tablets. This forces the BE to re-scan the disks and report a healthy status to the FE. * Alternative (Dynamic): Tell the BE to ignore the 100GB reserve limit (since you only have ~190GB free, it might still be too close for comfort):
Copy code
bash
    curl -X POST <http://localhost:8040/api/update_config?storage_flood_stage_left_capacity_bytes=10737418240>
4. How to fix "stuck" unhealthy tablets If the tablets remain unhealthy even after a BE restart: * Check FE logs: Look for 
tablet <id> has no queryable replica
. * ADMIN REPAIR: You can try to tell the FE to prioritize repairing these tablets:
Copy code
sql
    ADMIN REPAIR TABLE requests_table_name;
* The "Replication 1" Risk: If the BE actually marked the replica as 
IsBad: true
during the disk spike and you only have 1 replica, that data may be lost. You might have to drop the partition or truncate the table and re-ingest if the repair doesn't work. Summary Ingestion is failing because the BE is protecting itself from a disk that it thinks is still "at capacity" (the 100GB limit). Queries are failing because those same capacity issues caused 7 tablets to fall out of sync. Immediate Fix: Restart your BE nodes. If the 
UnhealthyTabletNum
in 
SHOW PROC '/statistic'
doesn't start dropping toward 0 after the restart, you will likely need to re-ingest the data for those specific tablets. Pro-tip: For production tables, always use 
replication_num = 3
. If you had 3 replicas, StarRocks would have simply ignored the "full" node and used the other 2 healthy copies, and you would have never seen a query failure. References • faq/Sql_faq.md
m
Copy code
ReplicaId,BackendId,Version,VersionHash,LstSuccessVersion,LstSuccessVersionHash,LstFailedVersion,LstFailedVersionHash,LstFailedTime,SchemaHash,DataSize,RowCount,State,IsBad,IsSetBadForce,VersionCount,PathHash,MetaUrl,CompactionStatus,IsErrorState
4129028,171908,2511,0,2511,0,2515,0,2026-04-29 22:49:57,2126326592,1946658587,1132631,NORMAL,false,false,11,5451311888450748204,<http://172.30.65.224:8040/api/meta/header/3244192,http://172.30.65.224:8040/api/compaction/show?tablet_id=3244192&schema_hash=2126326592,false>
3244193,110783,2515,0,2515,0,-1,0,,2126326592,2092643697,1132631,DECOMMISSION,false,false,8,654214853617252752,<http://172.30.68.175:8040/api/meta/header/3244192,http://172.30.68.175:8040/api/compaction/show?tablet_id=3244192&schema_hash=2126326592,false>
@Rocky Sample output
r
The sample output you provided is the "smoking gun." It shows exactly why your queries and loads are failing, even though you intended to have only 1 replica. 1. Why are there two replicas? Even though your table is 
replication_num = 1
, StarRocks has created a second replica because it detected an issue with the first one. * Replica 1 (Backend 171908): Status is 
NORMAL
, but it is stuck at Version 2511. * Replica 2 (Backend 110783): It managed to reach Version 2515, but its state is 
DECOMMISSION
. What happened: When your disk hit the flood stage, a load (Version 2515) likely failed to commit on Backend 171908 but succeeded (or partially succeeded) on Backend 110783. Because the versions are now out of sync and one backend is in a "Decommission" state (likely because the FE is trying to move data away from a "full" or "unstable" node), the FE cannot find a single Healthy + Normal + Up-to-date replica to query. 2. Why the query fails (
scan info is invalid
) The FE metadata believes the table should be at Version 2515. * It looks at Backend 171908: "You are at 2511. You are too old (Version Lag). I can't use you." * It looks at Backend 110783: "You are at 2515, but you are in 
DECOMMISSION
state. I can't send queries to a node that is being retired/moved." * Result: 0 healthy replicas found = Error 1064. 3. How to fix this Step A: Check your Backends Check if Backend 
110783
is actually being decommissioned (which happens if someone ran 
ALTER SYSTEM DECOMMISSION BACKEND
or if the system auto-triggered it due to disk errors):
Copy code
sql
SHOW BACKENDS;
Look at the 
SystemDecommissioned
and 
ClusterDecommissioned
columns for Backend 
110783
. If it is 
true
, the system is trying to empty that node. Step B: Force the Version (Quickest Fix) If you are okay with losing the data from the last failed load (the jump from 2511 to 2515), you can force the FE to "forget" about version 2515 and go back to the healthy 2511. This will immediately make the 
NORMAL
replica queryable again. 1. Find the 
PartitionId
for the table (from the 
SHOW TABLET
output). 2. Run the following (replace with your actual table/partition names):
Copy code
sql
-- Force the metadata to match the version on the 'NORMAL' BE
ADMIN SET TABLE <table_name> PARTITION (<partition_name>) VERSION TO 2511;
Note: After running this, the 
UnhealthyTabletNum
should drop, and queries should work again. Step C: Repair the "Decommission" replica If you need the data in version 2515 and the backend 
110783
is not actually supposed to be decommissioned, you can try to "re-activate" the replica by marking it as OK:
Copy code
sql
ADMIN SET REPLICA STATUS PROPERTIES("tablet_id" = "3244192", "backend_id" = "110783", "status" = "ok");
Summary Recommendation 1. Run the 
ADMIN SET TABLE ... VERSION TO 2511
 command. This is the most reliable way to clear the "scan info is invalid" error when you have a single-replica table that got out of sync during a disk flood. 2. Restart the BEs once the versions are aligned to clear the "No available stream load pipe" error. 3. Long-term: Change your table to 
replication_num = 3
. In a 1-replica setup, a single disk-full event on one node can take your entire database offline. With 3 replicas, StarRocks would have simply ignored the "full" node and kept your queries running on the others. References • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_SET_PARTITION_VERSION.md • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_SET_REPLICA_STATUS.md • deployment/upgrade.md
m
@Rocky
Copy code
[3244192, 3901640, 2733537, 3244268, 3901686, 3901766, 2733591]
These are my unhealthy tablets
Copy code
ReplicaId,BackendId,Version,VersionHash,LstSuccessVersion,LstSuccessVersionHash,LstFailedVersion,LstFailedVersionHash,LstFailedTime,SchemaHash,DataSize,RowCount,State,IsBad,IsSetBadForce,VersionCount,PathHash,MetaUrl,CompactionStatus,IsErrorState
4129047,171908,3139,0,3139,0,3150,0,2026-04-29 23:19:58,2126326592,3003200459,1763053,NORMAL,false,false,20,5451311888450748204,<http://172.30.65.224:8040/api/meta/header/2733591,http://172.30.65.224:8040/api/compaction/show?tablet_id=2733591&schema_hash=2126326592,false>
2733592,203697,3150,0,3150,0,-1,0,,2126326592,3003520001,1763053,DECOMMISSION,false,false,10,-2029043970693695594,<http://172.30.77.163:8040/api/meta/header/2733591,http://172.30.77.163:8040/api/compaction/show?tablet_id=2733591&schema_hash=2126326592,false>
Sample output from show proc What exactly step by step should I do
r
Based on the tablet metadata you provided, here is the exact step-by-step recovery plan. The Situation For tablet 
2733591
: * Replica 1 (BE 171908): Version 3139, State NORMAL. * Replica 2 (BE 203697): Version 3150, State DECOMMISSION. Because your table is 
replication_num = 1
, the Frontend (FE) only expects one copy. It likely tried to move the data (creating the 3150 version on a new node), but the move got stuck or the new node was marked for decommissioning. Now, the FE thinks the table should be at version 3150, but your only "Normal" replica is stuck at 3139. This version mismatch is why you get the "scan info is invalid" error. ────────── Step 1: Get the Table and Partition Info Run this for any of your unhealthy tablet IDs (e.g., 
2733591
):
Copy code
sql
SHOW TABLET 2733591;
Note down the 
TableName
, 
PartitionName
, and 
PartitionId
. They are likely the same for all 7 unhealthy tablets. Step 2: Force the Metadata Rollback We are going to tell the StarRocks Frontend to "forget" version 3150 and treat version 3139 (which your healthy BE has) as the current truth. This will make the 
NORMAL
replicas queryable again. Run this command (replace placeholders with the info from Step 1):
Copy code
sql
-- Use either PartitionName or PartitionId
ADMIN SET TABLE <your_table_name> PARTITION (<your_partition_name>) VERSION TO 3139;
Note: If your other 6 tablets are stuck at a different version (e.g., 3140), check their 
Version
in 
SHOW PROC
first and pick the highest version that is in 
NORMAL
state across all 7 tablets. Step 3: Verify the Fix After running the command, check the cluster health again:
Copy code
sql
SHOW PROC '/statistic';
The 
UnhealthyTabletNum
should drop. Once it hits 0, your queries and loads will immediately start working again. Step 4: Clean up (If necessary) If you see that the 
DECOMMISSION
replicas are still hanging around, check if a backend is actually being retired:
Copy code
sql
SHOW BACKENDS;
If 
BackendId 203697
shows 
SystemDecommissioned: true
and you didn't intend to remove it, you might need to "re-add" it or wait for it to finish. If you don't care about that node and just want your table back, Step 2 is enough. ────────── Why did this happen? 1. Disk Flood Stage: When your disk hit 94%, the BE entered a "read-only" protective state. 2. Stuck Migration: The FE tried to move your "at-risk" data to another node (BE 203697). 3. Metadata Desync: The move reached version 3150 but failed to complete or the target node was also restricted. Since you have no replication (1 copy only), there was no "third" healthy copy to act as a tie-breaker. Crucial Recommendation: Once you are back online, change your table to 
replication_num = 3
.
Copy code
sql
ALTER TABLE <table_name> MODIFY PARTITION (*) SET ("replication_num" = "3");
If you had 3 replicas, StarRocks would have automatically switched to a healthy copy when the disk got full, and you would have never seen a query failure. Ingestion or Query failures. References • administration/management/resource_management/Replica.md • administration/Meta_recovery.md
m
@Rocky [42000][1064] Getting syntax error at line 1, column 44. Detail message: No viable statement for input 'SET TABLE'
Copy code
ADMIN SET TABLE requests PARTITION (p2026042900) VERSION TO 3139;
r
The syntax error 
[42000][1064] ... No viable statement for input 'SET TABLE'
indicates that your current version of StarRocks does not recognize the 
ADMIN SET TABLE
command. This specific command (
ADMIN SET PARTITION VERSION
) was introduced in StarRocks v3.3. If you are running v3.2, v3.1, or v2.5, this command is not available in your SQL grammar, which is why the parser is failing. Since you are running with 
replication_num = 1
, you are in a "deadlock" state: the Frontend (FE) thinks the data should be at Version 3150, but your only "Normal" replica is at Version 3139. The FE is trying to find Version 3150 on the "Decommissioned" node, but it can't query it there. Here is how to resolve this based on your version: Option 1: Force the FE to use the Healthy Replica (Workaround for v3.2 and below) Since you cannot use the 
SET PARTITION VERSION
command, you need to tell the Frontend that the replica on the "bad" node is unusable. This may force the FE to fall back to the 3139 version or at least clear the metadata block. Run this for each unhealthy tablet (using the IDs from your list):
Copy code
sql
-- Mark the "Decommissioned" replica (BE 203697) as BAD
ADMIN SET REPLICA STATUS PROPERTIES(
    "tablet_id" = "2733591", 
    "backend_id" = "203697", 
    "status" = "bad"
);
Warning: Since you only have 1 replica, marking it as 
bad
tells StarRocks the data is lost. If the other replica (3139) is considered "too old" by the FE, it may still refuse to query it. Option 2: Drop and Re-ingest (The Cleanest Fix) Because you have a single replica and the metadata is out of sync, the safest way to get the table back online is to drop the problematic partition and re-load the data for that time range.
Copy code
sql
-- This removes the metadata and the broken tablet
ALTER TABLE requests DROP PARTITION p2026042900;

-- Then, re-run your ingestion job for that specific partition.
Option 3: Check for typos (If you are on v3.3+) If you are certain you are on StarRocks 3.3 or higher, try the command without the 
TABLE
keyword, as some documentation versions had a typo:
Copy code
sql
-- Try this alternative syntax
ADMIN SET PARTITION VERSION requests PARTITION (p2026042900) VERSION TO 3139;
Why "Unhealthy" isn't decreasing With 
replication_num = 1
, StarRocks cannot automatically repair unhealthy tablets. * In a 3-replica setup, if one copy is "bad," StarRocks copies data from the 2 healthy ones. * In your 1-replica setup, there is no "healthy" source to copy from. The system is stuck waiting for a version (3150) that only exists on a node it is no longer allowed to talk to. Final Recommendation 1. Check your version: Run 
SELECT VERSION();
. 2. Increase Replication: As soon as you are back online, run: 
ALTER TABLE requests MODIFY PARTITION (*) SET ("replication_num" = "3");
This will prevent a single disk-full event or node flap from taking your table offline inoperable in the future. References • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_SET_PARTITION_VERSION.md • table_design/data_distribution/expression_partitioning.md • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md
m
@Rocky Only 1/8 of my nodes sporadically jumps to 600 compaction score. Otherwise, they all mostly remain below 200. How to diagnose. Something that stood out is memory usage of that node is noticeably lower than the rest. However, BE Update MEM bytes is similar to the others. No other metric is standing out.
r
The scenario where a single node has a significantly higher compaction score (600) but lower memory usage than other nodes is a classic indicator that compaction is not running (or is stuck) on that specific node. While other nodes are actively using memory to merge files (keeping their scores low), the problematic node is likely failing to initiate or complete compaction tasks, causing versions to pile up without consuming the memory typical of an active compaction process. Here is the step-by-step diagnostic plan: 1. Identify the "Guilty" Tablets First, find the specific tablets on that node that are driving the high score:
Copy code
sql
-- Identify the high-score partition
SELECT * FROM information_schema.partitions_meta 
WHERE MAX_CS > 500 
ORDER BY MAX_CS DESC;

-- Use the PARTITION_ID from above to find tablets on the specific Backend
-- Replace <db_id>, <table_id>, etc. from SHOW PROC '/dbs'
SHOW PROC '/dbs/<db_id>/<table_id>/partitions/<partition_id>/<index_id>';
Look for tablets assigned to the BackendId of the problematic node. 2. Check Tablet Compaction Status via API Once you have a 
tablet_id
from the high-score node, query that Backend directly using its HTTP port (default 
8040
):
Copy code
bash
curl http://<BE_IP>:8040/api/compaction/show?tablet_id=<TABLET_ID>
* What to look for: Look at the 
rowsets
list. If you see hundreds of small rowsets and the 
cumulative layer point
hasn't moved, the BE is failing to merge them. 3. Check for "Stuck" Tasks or Queue Bottlenecks Even if the BE is receiving data ("Update MEM bytes" is similar), the compaction tasks might be stuck in the queue.
Copy code
sql
SHOW PROC '/compactions';
* Filter for the problematic node. * Check the 
Error
column. If you see error messages like 
timeout
or 
failed to publish
, that's your smoking gun. * Check 
in_queue_sec
in the 
Profile
column. If it's consistently high (e.g., > 60s), the node has the tasks but isn't executing them. 4. Why the Lower Memory? (The Diagnostic Hint) Active compaction in StarRocks (especially for the Primary Key or Unique model) consumes a significant amount of memory for loading metadata, bloom filters, and merging rowsets. * Lower Memory + High Score usually means the node has stopped compacting. * Possible Reason: The 
compact_threads
on that node might be hung, or the node has hit a "soft" resource limit (like 
compaction_memory_limit_per_node
) that is preventing new tasks from starting. 5. Check BE Logs for Failures Search the 
<http://be.INFO|be.INFO>
log on the problematic node for these specific keywords: * 
fail to commit compaction
* 
tablet log recovery
* 
not enough disk space
(Even if usage looks okay, a single bad disk path can stop compaction). * 
too many versions
6. Tuning & Quick Fixes If the node is healthy but simply lagging: * Increase Threads: Ensure 
compact_threads
is consistent across nodes. If you have spare CPU, you can temporarily increase it:
Copy code
sql
    UPDATE information_schema.be_configs SET value = "16" WHERE name = "compact_threads";
* Check Disk Health: Sporadic jumps in score often point to I/O Wait. Check if that specific node has higher disk latency (
iostat -x
) than the others. A single slow disk in a RAID or JBOD setup will bottleneck compaction. * Restart the BE: If the memory is low and the score is high, a thread might be deadlocked. A restart will clear the thread pool and force the FE to reschedule compaction tasks. Summary: Your "Update MEM bytes" shows data is arriving, but the "Compaction Score" shows it's not being processed. Focus on the BE logs and the BE Compaction API to see why the merge process is failing to start on that specific node. References • administration/management/compaction.md
m
@Rocky It's not always 600. When I query for MAX_CS above 500 I get nothing. It occassionally spikes to 500-600. ~10 times an hour
r
The fact that your compaction score spikes and then clears ~10 times an hour on only one node—while that node has *lower memory usage*—points toward a disk I/O or queueing bottleneck rather than a data volume problem. Because your "Update MEM bytes" is consistent across nodes, the amount of data arriving is the same. However, this specific node is failing to process that data into merged files as efficiently as the others, causing "versions" to stack up briefly (the spike) until the BE finally catches up. Here is how to diagnose the "Single Node Spike" scenario: 1. Check for Disk I/O Skew Sporadic spikes in compaction scores are most often caused by a single slow disk or high I/O wait. Compaction is a "Read-Merge-Write" operation. If the disk latency on Node A is higher than Node B, Node A will accumulate more unmerged rowsets during an ingestion burst. * Diagnose: On that specific node, run 
iostat -x 1 10
during a spike. * Look for: High 
%util
or high 
await
(latency) compared to other nodes. StarRocks compaction scores are sensitive to how fast the BE can commit new rowsets to disk. 2. Verify Compaction Thread Health If memory usage is lower, it might be because the compaction threads are idle or blocked (active compaction consumes memory for buffers). * Check if the compaction thread count is consistent:
Copy code
sql
    -- Run this and compare the 'value' for the problematic BE vs others
    SELECT * FROM information_schema.be_configs WHERE name = 'compact_threads';
* If the value is the same, check the BE logs (
<http://be.INFO|be.INFO>
) for that specific node during the spike for: * 
fail to commit compaction
* 
disk usage reach limit
(if one disk path is at 90%+, compaction stops on that path even if the node has space elsewhere). 3. Check Tablet Skew (The "Hot Tablet" Problem) Even if the node has the same amount of data, it might have more tablets from a specific "high-churn" table. * Run this to see if the problematic node has a higher number of tablets for the table you are loading:
Copy code
sql
    -- Look for the BackendId of the spiking node
    SHOW TABLETS FROM <table_name>;
* If that node has significantly more tablets for a high-frequency ingestion table, it will naturally spike more often when the FE triggers a "Cumulative Compaction" cycle. 4. Why is Memory Lower? This is the most interesting clue. In StarRocks, memory usage is often driven by Primary Key indexes and Metadata cache. * Scenario A: If you are using the Primary Key model, the BE loads indexes into memory. If memory is lower on this node, it might have fewer "hot" tablets in memory, leading to more "Cold Starts" during compaction (reading metadata from disk), which slows down the compaction and causes the version spike. * Scenario B: The node has a smaller Data Cache or Page Cache allocation, forcing compaction to rely more on physical disk reads. 5. Capture the Spike (Diagnostic Script) Since 
MAX_CS > 500
returns nothing when you check manually, you need to catch it in the act. You can use the metrics endpoint to see the historical peak: 1. Navigate to 
http://<BE_IP>:8040/metrics
on the problematic node. 2. Search for 
starrocks_be_tablet_max_compaction_score
. 3. Compare 
starrocks_be_compaction_mem_bytes
across nodes. If this value is near zero while the score is 600, the node's compaction is queued but not running. Summary Recommendation 1. Check Disk Latency: This is the #1 cause of single-node CS spikes. 2. Compare `compact_threads`: Ensure that node hasn't been manually throttled. 3. Check 
<http://be.INFO|be.INFO>
logs: Look for 
[Compaction] failed
or 
timeout
errors around the time of the spikes. 4. Balance Tablets: If the node has more tablets, use 
ADMIN REBALANCE
(though StarRocks usually does this automatically).
If you find that the disk is healthy, try increasing the compaction threads specifically on that node to help it clear the 600-score queue faster:
Copy code
sql
-- Increase threads on a specific BE via the config API
-- (Replace <BE_IP> and <BE_PORT> with the problematic node's info)
curl -X POST http://<BE_IP>:<BE_HTTP_PORT>/api/update_config?compact_threads=16
References • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky I found it. compact_threads was 4 for that particular node. Is this confiugrable in be.conf? Where I configure other variables like update_compaction_check_interval_seconds
r
Yes, both variables are configurable in the 
be.conf
 file located in your BE 
conf/
directory. However, since they are marked as mutable (dynamic), you have two ways to manage them depending on whether you want the change to be instant or persistent. 1. Persistent Configuration (
be.conf
) To ensure the settings persist after a process restart, add or update the lines in `be.conf`:
Copy code
bash
# Example settings in be.conf
compact_threads = 16
update_compaction_check_interval_seconds = 5
Note: Changes made directly to 
be.conf
usually require a BE restart to take effect, unless you also apply them dynamically. 2. Dynamic Configuration (No Restart Required) Since these parameters are mutable, you can change them on the fly without restarting the node. This is likely how that one node ended up with a different value (someone might have changed it via API). Via SQL (Recommended): You can update the configuration for a specific BE or all BEs using the 
system
or 
information_schema
tables (depending on your version):
Copy code
sql
-- Update for all nodes
UPDATE information_schema.be_configs SET value = "16" WHERE name = "compact_threads";

-- If your version supports it, you can target specific nodes via the HTTP API 
-- (see below) or check current values:
SELECT * FROM information_schema.be_configs WHERE name LIKE '%compaction%';
Via HTTP API: If you want to fix just that one "slow" node immediately:
Copy code
bash
curl -X POST http://<BE_IP>:<BE_HTTP_PORT>/api/update_config?compact_threads=16
curl -X POST http://<BE_IP>:<BE_HTTP_PORT>/api/update_config?update_compaction_check_interval_seconds=5
Important Notes for your scenario: * 
compact_threads
: This controls the thread pool for Base and Cumulative compactions (used by Duplicate/Unique models). In newer versions (v3.1.7+, v3.2.2+), this is fully dynamic. * 
update_compaction_check_interval_seconds
: This is specific to the Primary Key model. If you are seeing spikes on Primary Key tables, decreasing this interval (e.g., from 10 to 2 or 5) makes the BE check for compaction needs more frequently. * 
update_compaction_num_threads_per_disk
: For Primary Key tables, this is often the more important setting. If you have many disks, increasing this allows more concurrent compaction tasks for PK tables. Recommendation: Update your 
be.conf
on all nodes so they are consistent, and then use the 
curl
command or SQL 
UPDATE
to bring that one lagging node up to speed immediately without waiting for a maintenance window. References • administration/management/BE_configuration.md
m
@Rocky Does starrocks support tuple predicate? Like (columnA, columnB) in ((a, b), (c, d)) etc.
r
Yes, StarRocks supports tuple predicates (also known as Row Expressions) in the 
WHERE
clause. You can use them for multi-column comparisons with constant lists or subqueries. Supported Syntax Examples 1. Tuple with 
IN
and Constant Values You can compare multiple columns against a list of specific value pairs (tuples):
Copy code
sql
SELECT * 
FROM my_table 
WHERE (columnA, columnB) IN (('a', 'b'), ('c', 'd'));
2. Tuple with 
IN
and Subqueries This is often used to find rows that match composite keys in another table:
Copy code
sql
SELECT * 
FROM table1 
WHERE (id, version) IN (SELECT id, MAX(version) FROM table2 GROUP BY id);
3. Equality Comparisons You can also use tuples with basic comparison operators:
Copy code
sql
SELECT * 
FROM orders 
WHERE (region, priority) = ('West', 'High');
Performance & Optimization * Primary Key Tables: If the columns in your tuple predicate are part of the Primary Key, StarRocks can often use these predicates to perform efficient index lookups (point lookups), significantly speeding up the query. * Partition Pruning: If the tuple contains partition columns, StarRocks can use them for partition pruning, though it is generally recommended to use simple predicates (e.g., 
columnA = 'a' AND columnB = 'b'
) for the most reliable pruning across all versions. * Version Note: While basic tuple support has existed for a long time, expanded support for the 
row()
or 
struct()
constructor functions (which are related to how these tuples are handled internally) was specifically enhanced in version 3.1+. Important Note on 
NULL
 As with standard SQL, if any element within the tuple or the comparison list is 
NULL
, the result of the predicate follows three-valued logic. For example, 
(1, NULL) IN ((1, 2))
will return 
UNKNOWN
rather than 
TRUE
or 
FALSE
. References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • knowledge/usage/best-practice.md • sql-reference/sql-functions/struct-functions/row.md • sql-reference/data-types/semi_structured/STRUCT.md
m
@Rocky
Copy code
SELECT
    network_id,
    campaign_id,
    CASE WHEN site_publisher_id IS NULL OR site_publisher_id = ''
         THEN CONCAT(nativead_id, '_', app_publisher_id)
         ELSE CONCAT(nativead_id, '_', site_publisher_id)
    END AS pub_nativead_key,
    bid_loss_reason,
    COUNT(*) AS cnt,
    SUM(COALESCE(has_won, 0)) AS wins
FROM requests.requests
WHERE received_hour >= DATE_TRUNC('hour', NOW() - INTERVAL 1 HOUR)
  AND received       >= NOW() - INTERVAL 15 MINUTE
  AND (network_id, campaign_id) IN (('85', '2711716'))
GROUP BY 1, 2, 3, 4
[42000][1064] Getting syntax error at line 14, column 2. Detail message: Unexpected input 'AND', the most similar input is {<EOF>, ';'}.
r
The error message 
Unexpected input 'AND', the most similar input is {<EOF>, ';'}
indicates that the StarRocks parser thinks your SQL statement was already complete and didn't expect any more conditions. This is usually caused by one of the following three reasons: 1. StarRocks Version (Most Likely) Tuple predicates (multi-column 
IN
clauses like 
(colA, colB) IN (...)
) were only officially introduced in StarRocks version 3.0. * If you are running v2.5 or earlier, the parser does not recognize the 
(network_id, campaign_id)
syntax. It gets confused and essentially "stops" at the end of the previous line, making the 
AND
on line 14 look like it's starting a new (invalid) statement. * Fix: Rewrite the condition using standard logic:
Copy code
sql
    AND network_id = '85' AND campaign_id = '2711716'
2. Date Math Syntax While StarRocks supports some MySQL-style date math, the preferred and most stable way to handle intervals is using the explicit 
DATE_SUB
or 
DATE_ADD
functions. Sometimes the parser can trip on 
NOW() - INTERVAL
if it's not wrapped in a function. * Recommendation: Wrap your time calculations:
Copy code
sql
    WHERE received_hour >= DATE_TRUNC('hour', DATE_SUB(NOW(), INTERVAL 1 HOUR))
      AND received       >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
3. HTML Entities in the Query Your snippet shows 
>=
instead of 
>=
. If those characters (leftovers from a copy-paste) are literally in your SQL editor, they will cause a syntax error. * Fix: Ensure all 
>=
are replaced with the actual 
>=
operator. Corrected Query Try this version, which is compatible with all StarRocks versions (v2.x and v3.x):
Copy code
sql
SELECT
    network_id,
    campaign_id,
    CASE 
        WHEN site_publisher_id IS NULL OR site_publisher_id = ''
        THEN CONCAT(nativead_id, '_', app_publisher_id)
        ELSE CONCAT(nativead_id, '_', site_publisher_id)
    END AS pub_nativead_key,
    bid_loss_reason,
    COUNT(*) AS cnt,
    SUM(COALESCE(has_won, 0)) AS wins
FROM requests.requests
WHERE received_hour >= DATE_TRUNC('hour', DATE_SUB(NOW(), INTERVAL 1 HOUR))
  AND received       >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
  AND network_id = '85' 
  AND campaign_id = '2711716'
GROUP BY 1, 2, 3, 4;
Note for Multiple Tuples: If you actually have a list of many tuples to check, and you are on StarRocks 3.0+, you can use the 
row()
constructor to make it more explicit:
Copy code
sql
AND row(network_id, campaign_id) IN (row('85', '2711716'), row('90', '1234567'))
References • sql-reference/sql-functions/date-time-functions/seconds_sub.md • sql-reference/sql-functions/date-time-functions/date_add.md • sql-reference/sql-functions/date-time-functions/date_sub.md • sql-reference/sql-functions/date-time-functions/now.md • sql-reference/sql-functions/date-time-functions/seconds_add.md • sql-reference/sql-functions/date-time-functions/microseconds_sub.md • administration/management/timezone.md
m
@Rocky My Update Apply Queue Count is only spiky for one node. How can I check which configs are mis aligned on this node?
r
If your Update Apply Queue Count is spiky on only one node, it usually means that node has a lower capacity to process Primary Key "apply" tasks compared to the others, or it is handling more "heavy" tablets due to data skew. To find the misaligned configs, you can compare them side-by-side across all nodes using SQL. 1. Compare Configs Across All Nodes (SQL) Run this query to identify if the worker thread counts or memory limits differ on the problematic node:
Copy code
sql
SELECT 
    BE_ID, 
    NAME, 
    VALUE, 
    DEFAULT_VALUE -- (In some versions this is 'DEFAULT')
FROM information_schema.be_configs 
WHERE NAME IN (
    'transaction_apply_worker_count', 
    'transaction_apply_thread_pool_num_min',
    'update_compaction_num_threads_per_disk',
    'primary_key_limit_size_per_node'
)
ORDER BY NAME, BE_ID;
2. Key Configurations to Check These are the most common culprits for "Apply Queue" spikes: * 
transaction_apply_worker_count
: * Default: 
0
(which means it uses the number of CPU cores). * The Issue: If one node has fewer CPU cores than others, its "default" capacity will be lower. If you have manually set this to a fixed number (e.g., 
16
) on some nodes but left it at 
0
on the "spiky" node, it may be under-provisioned. * 
transaction_apply_thread_pool_num_min
: * Sets the baseline number of threads. If this is higher on some nodes, they will respond to bursts faster. * 
primary_key_limit_size_per_node
: * If this node has a lower memory limit for Primary Key metadata, it might be triggering frequent "spill-to-disk" or aggressive cleaning, which slows down the apply process and builds up the queue. * 
update_compaction_num_threads_per_disk
: * Controls how many threads can work on PK compaction per disk. If this is lower, compaction falls behind, making the "apply" phase work harder on uncompacted data. 3. Check for Hardware/Environment Mismatches If the configurations look identical in SQL, the "Apply Queue" spike might be caused by external factors on that specific node: 1. CPU Count: Since 
transaction_apply_worker_count
defaults to the CPU count, check if the node actually has fewer cores:
Copy code
sql
    SELECT * FROM information_schema.be_nodes; -- Check CPU_CORE_COUNT column
2. Disk Performance: Primary Key apply involves reading/writing the 
delvec
(delete vectors). If one node's disk is slower or failing, the queue will back up. 3. Data Skew: Check if that node is hosting significantly more "active" tablets for the table you are updating:
Copy code
sql
    -- Check tablet distribution across BEs
    SHOW TABLETS FROM your_table_name;
4. How to align them immediately If you find the problematic node is misconfigured, you can fix it without a restart using the HTTP API or SQL: Via SQL (recommended):
Copy code
sql
UPDATE information_schema.be_configs 
SET VALUE = "desired_value" 
WHERE NAME = "transaction_apply_worker_count" AND BE_ID = <target_be_id>;
Via Curl:
Copy code
bash
curl -X POST http://<PROBLEM_BE_IP>:<BE_HTTP_PORT>/api/update_config?transaction_apply_worker_count=16
Recommendation: After aligning them dynamically, make sure to update the 
be.conf
file on that node so the fix persists after a restart. References • administration/management/BE_configuration.md • administration/management/FE_configuration.md • administration/management/monitoring/metrics.md
m
@Rocky What is the StarRocks planner execution engine size limit? I have over 800k pairs of (network_id, campaign_id) in my query
Copy code
SELECT
    network_id,
    campaign_id,
    CASE WHEN site_publisher_id IS NULL OR site_publisher_id = ''
         THEN CONCAT(nativead_id, '_', app_publisher_id)
         ELSE CONCAT(nativead_id, '_', site_publisher_id)
    END AS pub_nativead_key,
    bid_loss_reason,
    COUNT(*) AS cnt,
    SUM(has_won) AS wins
FROM requests.requests
WHERE received_hour >= DATE_TRUNC('hour', NOW() - INTERVAL 1 HOUR)
  AND received       >= NOW() - INTERVAL 15 MINUTE
  AND row(network_id, campaign_id) IN (row('85', '2711716'))
GROUP BY 1, 2, 3, 4;
r
StarRocks does not have a single "engine size limit," but for a query with 800,000 pairs in an 
IN
clause, you will hit several hard and soft limits in the Frontend (FE) planner and the MySQL protocol layer. Here are the specific limits you are likely to encounter and why your current approach is not recommended: 1. Hard Limits You Will Hit * 
expr_children_limit
(Default: 10,000): This is the most immediate bottleneck. It limits the number of child expressions a single node (like an 
IN
predicate) can have. With 800k 
row()
constructors, you are at 80x the default limit. * 
max_scalar_operator_flat_children
(Default: 10,000): This limits the number of flat children during the optimization phase. 800k items will cause the planner to exceed this. * 
max_allowed_packet
(Default: 32 MB): A query string containing 800k pairs of IDs (e.g., 
'85', '2711716'
) will likely exceed 32MB. For example, if each 
row('...', '...')
snippet is 40 bytes, 800k of them equals ~32MB, plus the rest of your SQL. The client or server will throw a 
PacketTooBigException
. 2. Why 
enable_large_in_predicate
Won't Help Here StarRocks has an optimization called 
LargeInPredicate
(triggered at 100,000 items) that converts a giant 
IN
list into a 
JOIN
to save memory. However: * This optimization is primarily designed for single-column literal lists (e.g., 
id IN (1, 2, 3...)
). * Complex tuple predicates using the 
row()
or 
struct()
constructor usually do not trigger this specific optimization path and are handled as a massive tree of expression objects, which will likely lead to an FE Out-of-Memory (OOM) error or an extremely long planning time. Recommended Best Practice Sending 800k items in a literal SQL string is considered an "anti-pattern" in OLAP databases. Instead of expanding the query string, you should use one of the following methods: Option A: Use a Temporary Table (Highly Recommended) Load your 800k pairs into a temporary table or a permanent "filter" table and then join against it. This is the fastest and most stable method. 1. Load the data: Use Stream Load to put the 800k pairs into a table (e.g., 
temp_filter
). 2. Query with Join:
Copy code
sql
    SELECT r.*
    FROM requests.requests r
    JOIN temp_filter f
      ON r.network_id = f.network_id
     AND r.campaign_id = f.campaign_id
    WHERE ...
Option B: Use a Subquery with an External Table If the 800k pairs are in a file (CSV/Parquet) on S3 or HDFS, you can query that file directly as an external table:
Copy code
sql
WHERE (network_id, campaign_id) IN (
    SELECT network_id, campaign_id FROM s3_external_table
)
Option C: Increase Limits (Not Recommended for 800k) If you absolutely must do this and your list is smaller (e.g., 20k-30k), you can try bumping the session variables, but 800k is too large for this to be stable:
Copy code
sql
SET [GLOBAL] expr_children_limit = 1000000;
SET [GLOBAL] max_scalar_operator_flat_children = 1000000;
-- You must also increase max_allowed_packet in the FE config (fe.conf) 
-- and restart FE for it to take effect.
Summary For 800k pairs, Option A (Join with a table) is the only reliable way to ensure the query finishes quickly without crashing the StarRocks Frontend. References • sql-reference/System_variable.md • administration/management/FE_configuration.md • sql-reference/System_limit.md • sql-reference/sql-functions/struct-functions/row.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md References
m
@Rocky What metric based alerts should I set up for my StarRocks cluster? I want classified into sev-1, sev-2 and sev-3?
r
Setting up a tiered alerting system for StarRocks ensures that your on-call team responds to critical outages immediately while managing long-term cluster health without "alert fatigue." Below is a classified list of recommended alerts categorized by severity (Sev-1 to Sev-3). Sev-1: Critical (Immediate Action Required) Impact: The cluster is unavailable, data is not being ingested, or a total crash is imminent. Alert Name | Metric / Logic | Threshold | Action FE Leader Down | 
starrocks_fe_is_master
| Sum across cluster < 1 | Immediate investigation. No leader means the cluster cannot accept new queries or jobs. BE Majority Down | 
up{group="be"}
| > 50% nodes DEAD | Cluster is likely in a "Read-Only" or "Failure" state. Check network or infrastructure. Disk Space Critical | 
starrocks_be_disks_avail_capacity
| < 5% | StarRocks will stop accepting writes to avoid corruption. Clean up data or add disks immediately. BE OOM Risk | 
starrocks_be_process_mem_bytes
| > 95% of 
mem_limit
| BE node is about to crash. Kill "Big Queries" or scale memory. FE Metadata Full | 
node_filesystem_free_bytes{mountpoint="/meta"}
| < 2GB | If FE metadata disk fills up, the FE will crash and potentially corrupt the BDB logs. ────────── Sev-2: Major (Urgent / Business Hours Action) Impact: Significant performance degradation, partial data staleness, or high risk of escalating to Sev-1. Alert Name | Metric / Logic | Threshold | Action High Compaction Score | 
starrocks_be_max_tablet_compaction_score
| > 500 (Critical at 1000) | Indicates "Too many versions." Slows down queries significantly. Reduce ingestion frequency. Routine Load Lag | 
starrocks_fe_routine_load_max_lag
| > 1,000,000 ms (or custom) | Data in StarRocks is lagging behind Kafka. Scale BE nodes or increase job concurrency. Loading Failure Rate | 
rate(starrocks_fe_txn_failed)
| > 10% for 5 mins | Large percentage of imports are failing. Check FE/BE logs for 
ABORTED
transactions. High JVM Heap | 
starrocks_fe_jvm_heap_usage_percent
| > 85% | FE is struggling with metadata or too many connections. Increase 
Xmx
in 
fe.conf
. Checkpoint Failure | 
starrocks_fe_bdb_log_count
| > 100,000 | BDB logs are not being truncated. Risk of slow FE restarts and metadata bloat. ────────── Sev-3: Minor (Warning / Monitoring Only) Impact: Minor efficiency issues or early warning signs of resource saturation. Alert Name | Metric / Logic | Threshold | Action CPU Saturation | 
starrocks_be_cpu
| > 80% for 15 mins | System is under heavy load. Check for unoptimized queries or consider scaling out. Single BE Node Down | 
up{group="be"}
| 1 node DEAD | StarRocks handles single-node failure via replicas, but check why the process stopped. High Thread Count | 
starrocks_fe_thread_pool_size
| > 3,000 | Often caused by massive 
UNION ALL
queries or connection leaks. Check audit logs. Slow Query Spike | 
starrocks_fe_query_latency_p95
| > 2x baseline | Check if a specific "Large Query" is impacting the cluster via 
SHOW PROC "/current_queries"
. Tablet Clone Failure | 
starrocks_be_clone_failed
| > 0 | Replicas are not balancing correctly. Usually a network or disk I/O bottleneck. How to implement these: 1. Prometheus + Grafana: StarRocks exports these via the 
/metrics
endpoint (FE port 8030, BE port 8040). 2. Audit Loader: For deeper analysis of Sev-3 (Slow Queries), use the Audit Loader plugin to track which specific users or queries are causing the spikes. 3. Grouping: Always group alerts by 
instance
or 
host
so you can identify if the issue is a single bad disk or a cluster-wide configuration mismatch.
Pro-Tip: If you see Compaction Score (Sev-2) and CPU Saturation (Sev-3) rising together, it usually means your ingestion frequency is too high (too many small files), which is forcing the BE to spend all its CPU on background merging files instead of serving queries. References • administration/management/monitoring/Monitor_and_Alert.md • administration/management/monitoring/alert.md
m
@Rocky GIve me the exact prometheus queries that should trigger these alerts
r
Here are the exact Prometheus queries for each alert tier. These queries assume you are using the standard StarRocks Prometheus exporter (FE port 8030, BE port 8040). Sev-1: Critical (Immediate Action) Goal: Identify total service failure or data safety risks. Alert Name | Prometheus Query (PromQL) No FE Leader | 
sum(starrocks_fe_is_master) < 1
BE Majority Down | 
count(up{group="be"} == 0) >= (count(up{group="be"}) / 2)
Disk Capacity < 5% | 
(starrocks_be_disks_avail_capacity / starrocks_be_disks_total_capacity) < 0.05
BE Memory Critical | 
(starrocks_be_process_mem_bytes / starrocks_be_mem_limit) > 0.95
FE Metadata Disk Full | 
node_filesystem_avail_bytes{mountpoint=~".*meta.*"} / node_filesystem_size_bytes < 0.05
────────── Sev-2: Major (Urgent Action) Goal: Prevent performance collapse or significant data lag. Alert Name | Prometheus Query (PromQL) High Compaction Score | 
starrocks_fe_tablet_max_compaction_score > 500
Routine Load Lag | 
starrocks_fe_routine_load_max_lag_of_partition > 1000000
High Load Failure Rate | 
rate(starrocks_fe_txn_failed[5m]) / (rate(starrocks_fe_txn_success[5m]) + rate(starrocks_fe_txn_failed[5m])) > 0.1
FE JVM Heap High | 
starrocks_fe_jvm_heap_usage_percent > 85
BDB Log Bloat | 
starrocks_fe_meta_log_count > 100000
────────── Sev-3: Minor (Warning / Informational) Goal: Proactive monitoring of resource trends. Alert Name | Prometheus Query (PromQL) High BE CPU Usage | 
100 - (avg by (instance) (rate(node_cpu_seconds_total{mode="idle"}[5m])) * 100) > 80
Single BE Node Down | 
count(up{group="be"} == 0) == 1
High FE Connections | 
starrocks_fe_connection_total > 2000
P95 Latency Spike | 
starrocks_fe_query_latency_ms{quantile="0.95"} > 5000
Tablet Clone Failures | 
increase(starrocks_be_engine_requests_total{type="clone", status="failed"}[5m]) > 0
Setup Recommendations: 1. Metric Prefixes: Depending on your Prometheus setup, some metrics might be prefixed with 
starrocks_
or just `be_`/`fe_`. Check your 
/metrics
endpoint to confirm. 2. The 
up
metric: Ensure your Prometheus configuration includes a 
group
label (e.g., 
group="be"
or 
group="fe"
) in the scrape job so the "Node Down" queries work correctly. 3. For Duration: In your Alertmanager or Grafana settings, set the "For" duration to 
30s
or 
1m
for Sev-1 (to avoid noise during restarts) and 
5m
for Sev-3. References • administration/management/monitoring/Monitor_and_Alert.md • administration/management/monitoring/alert.md • administration/management/monitoring/metrics.md References • administration/management/monitoring/alert.md • administration/management/monitoring/metrics.md
Open in Slack
PreviousNext
Powered by