http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I have 8 r6in.4xlarge nodes with 4 ...
# questions-and-troubleshooting
m
@Rocky I have 8 r6in.4xlarge nodes with 4 EBS volumes each - gp3 with 500 throughput 5000 IOPS. 750GB per EBS. Cluster CPU busy utilization mostly below 60% factoring spikes. Compaction score generally stays below 175. Average number of versions per tablet also below 100. However, my p95 transaction publish latency is 10s. My traffic is 50k QPS, each application node performs it's own batching at 500 MB data per batch. I synchronize upserts at the top of every minute. They are ~11k - 12k QPS, so roughly 660k to 720k sent to the cluster every minute. I have 8 BE nodes currently with replication_num to set 1 only. Is my capacity planning correct? I'm not sure if I'm underprovisioning. Because I know if I increase replication to 2, my cluster is not able to handle it. What would your suggested capacity planning be for my situation? I have already optimized all backend configurations like
Copy code
# Allow each tablet to run compaction more frequently (default 120)
update_compaction_per_tablet_min_interval_seconds=10

# BE checks tablet for compaction more frequently
update_compaction_check_interval_seconds=5

# PKI index compaction parallelism (default 1)
pindex_major_compaction_limit_per_disk=4

# Limits each compaction task to 100 rowsets
max_cumulative_compaction_num_singleton_deltas=100

# Hold up to 1GB of PK indexes in memory, reducing disk lookup for upserts
l0_max_mem_usage=1073741824

update_compaction_num_threads_per_disk = 4
And compact_threads = 12 My FE node CPU utilization is effectively 0. Memory utilization stays below 10%.
r
Hi there! I'm Rocky. Dealing with a 10s P95 publish latency when your CPU and compaction scores look "okay" is a classic sign of a bottleneck in the Primary Key (PK) "Apply" phase, likely exacerbated by hardware throughput limits and traffic synchronization. Here is an analysis of your current capacity and why you are seeing these bottlenecks: 1. The "Apply" Bottleneck & Synchronization In StarRocks Primary Key tables, the Publish stage includes an Apply phase where the BE: 1. Loads the PK index into memory. 2. Looks up the location of old rows. 3. Generates and writes Delete Vectors. By synchronizing your upserts to the "top of every minute," you are creating a massive micro-burst. Even if your average CPU is 60%, during that specific window, the BE threads responsible for "Applying" those 700k records are likely hitting 100% or queuing. * The Replication Factor 1 vs 2 problem: When you move to 
replication_num=2
, the amount of "Apply" work (index lookups and Delete Vector writes) doubles for the cluster. If your nodes are already taking 10s to publish at replication 1, they will likely time out or experience extreme lag at replication 2 because the CPU and I/O overhead for the metadata update is doubled. 2. Hardware: EBS Throughput Throttling You are using 
r6in.4xlarge
instances. Let's look at the AWS limits: * Instance EBS Bandwidth: 10,000 Mbps (~*1,250 MB/s*). * Your Disk Setup: 4 x gp3 @ 500 MB/s = 2,000 MB/s. The Issue: Your aggregate disk throughput (2,000 MB/s) exceeds your instance's maximum allowed EBS bandwidth (1,250 MB/s). During your minute-start burst, when StarRocks is trying to write new rowsets and simultaneously read/write PK indexes and Delete Vectors, you are likely hitting the 1.25 GB/s instance-level cap. This causes I/O wait times to spike, leading to that 10s publish latency. 3. Suggested Capacity Planning & Optimizations A. Desynchronize (The "Jitter" Strategy) Stop the "top of the minute" synchronization. Spread your batches across the minute. If you send the same 720k records spread over 60 seconds (12k/s constantly) rather than all at once, your P95 latency will drop significantly without adding a single node. B. Scale "Up" instead of "Out" For 50k Query QPS + heavy PK Upserts, 
r6in.4xlarge
(16 vCPU) is considered a small node. StarRocks performs better on larger instances because it reduces the number of RPCs between nodes for metadata synchronization. * Recommendation: Move to 
r6in.8xlarge
(32 vCPU, 256GB RAM). * Why? The 
8xlarge
has higher EBS throughput limits (~1,560 MB/s) and more CPU to handle the 50k QPS while the background "Apply" threads work on upserts. C. Configuration Tweaks Your compaction settings are already very aggressive (perhaps too aggressive, potentially stealing I/O from the publish phase). I recommend checking these: * 
transaction_apply_worker_count
: In 
be.conf
, this defaults to your CPU core count. If your CPU is truly under-utilized during the burst, you can try increasing this to allow more parallel "Apply" tasks. * Check Tablet Count: Ensure you don't have too many tablets. If a 500MB batch is spread across thousands of tablets, the metadata overhead for the Publish phase explodes. Aim for tablet sizes of 1GB–10GB. D. Suggested Cluster for Replication Factor 2 To comfortably handle your load with `replication_num = 2`: 1. Nodes: 8–10 nodes of 
r6in.8xlarge
. 2. Storage: Keep the gp3 volumes but recognize the instance limit is the ceiling. 3. Batching: Keep your 500MB batches but jitter the start time by 0–50 seconds at the application layer.
Summary: Your capacity is likely under-provisioned for the burst, not the average. The P95 latency is high because you are hitting the EBS bandwidth limit of the 
4xlarge
instance and saturating the "Apply" threads during the synchronized minute-start spike. Increasing replication to 2 will double this pressure, which is why it currently fails. References • knowledge/usage/load/shared_data_batch_ingestion.md • faq/operation_maintenance_faq.md • faq/Others.md • knowledge/usage/best-practice.md • introduction/Architecture.md • deployment/shared_data/feature-support-shared-data.md
m
@Rocky But my batch upserts are very small. Like every minute during the synchronization because it's less than 10 columns with tiny values, the cumulative payload across all nodes is less than 500 MB for sure. Should I still upscale?
r
Sorry, I encountered an error while processing your request: 429 RESOURCE_EXHAUSTED. {'error': {'code': 429, 'message': 'Resource has been exhausted (e.g. check quota).', 'status': 'RESOURCE_EXHAUSTED'}}
m
@Rocky But my batch upserts are very small. Like every minute during the synchronization because it's less than 10 columns with tiny values, the cumulative payload across all nodes is less than 500 MB for sure. Should I still upscale?
r
Hi! I'm Rocky. Based on your description, you are experiencing a bottleneck in the Primary Key "Apply" phase during your synchronized bursts. Even though your data payload is small (500 MB), the 720,000 records arriving at the exact same moment create a massive metadata challenge. In StarRocks Primary Key tables, the "Publish" stage is not just a file commit; it includes the Apply phase where the system must: 1. Load/Query the Primary Key index for each of the 720k keys. 2. Identify the location of any existing rows. 3. Generate and write Delete Vectors. Here is why your 10s P95 latency is happening and how to address your capacity planning: 1. The "Apply" Thread Pool Saturation Each 
r6in.4xlarge
has 16 vCPUs. By default, the 
transaction_apply_worker_count
(the pool that handles the Apply phase) is equal to the number of CPU cores (16). * The Problem: When you synchronize all nodes to hit at the "top of the minute," you are dumping 720k tasks into these 16 threads across your 8 nodes simultaneously. These tasks queue up (check the metric 
update_apply_queue_count
). * Replication Factor: When you move to 
replication_num=2
, the cluster has to perform this "Apply" work twice (once for each replica). If your 16 threads are already saturated at replication 1, replication 2 will likely cause timeouts or double the latency. 2. Disk I/O & Persistent Index You have 
l0_max_mem_usage
set to 1GB. If your total number of unique keys across your tables exceeds what fits in that 1GB (multiplied by tablet count), StarRocks uses the Persistent Index on disk. * 720k lookups in a few seconds against an on-disk index will consume a significant amount of IOPS. * With 50k QPS (queries) running at the same time, your 5,000 IOPS per EBS volume are likely being exhausted during that one-minute spike. 3. Resource Contention (Compaction vs. Load) Your compaction settings are extremely aggressive: * 
update_compaction_num_threads_per_disk = 4
with 4 disks = 16 threads. * 
compact_threads = 12
. * Total: 28 threads dedicated to compaction on a 16 vCPU machine. During your 1-minute burst, these compaction threads are competing with your 50k QPS and your "Apply" worker threads for the same CPU cycles and Disk I/O. This is why your CPU stays at 60% (average) but the specific "Apply" tasks take 10s to find a gap to finish. Suggested Capacity Planning & Fixes 1. Desynchronize (High Priority) The "top of every minute" sync is your biggest enemy. If you jitter your application batches (e.g., spread the 720k rows over a 60-second window), your publish latency will likely drop to sub-second immediately. StarRocks is designed for continuous streaming rather than micro-batches. 2. Scale "Up" for 50k QPS 50k QPS on 8 nodes (6,250 QPS/node) is quite high for 
4xlarge
instances when combined with heavy PK upserts. * Recommendation: Move to 
r6in.8xlarge
 (32 vCPU, 256GB RAM). * Why? This doubles your 
transaction_apply_worker_count
and gives more CPU "headroom" so that queries and background apply tasks don't starve each other. Larger nodes also have higher EBS bandwidth limits. 3. Optimize Tablet Count If you have too many tablets (e.g., 500MB batch spread across 2000 tablets), the overhead of the Publish phase increases exponentially. Aim for tablets to be roughly 1GB to 10GB in size. 4. Tune Down Compaction To prioritize your "Publish" latency, reduce the compaction pressure so it doesn't steal I/O during the burst: * Set 
update_compaction_num_threads_per_disk = 1
or 
2
. * Set 
compact_threads
to 
4
or 
8
. * Increase 
update_compaction_per_tablet_min_interval_seconds
back to 
30
or 
60
. 5. Monitoring Metrics to check: * `update_apply_queue_count`: If this is > 0, your apply threads are the bottleneck. * `update_rowset_commit_apply_duration_us`: This tells you exactly how long the Apply phase is taking.
* `disk_io_util`: Check if this hits 100% at the top of the minute. Summary: Your current nodes are likely under-provisioned for the concurrency and burst of your workload, specifically the metadata/Apply phase of the Primary Key model. To support 
replication_num=2
or 
3
(highly recommended for production), you should upscale to 
8xlarge
nodes and stop the synchronized batch synchronization. References • faq/operation_maintenance_faq.md • best_practices/primarykey_table.md • administration/management/resource_management/Replica.md • table_design/table_types/primary_key_table.md
m
@Rocky My update apply queue count spikes to 300-500 regularly.Roughly 25 times in the last 3 hours. How should I fix it? I thought if I enabled merge commit and synchronized my upserts at the top of the minute, I reduce disk lookups for the PK index since we first sort the primary keys of rows to update and perform the lookup? So less round trips to disk
r
Hi! I'm Rocky. Your observation of the 
update_apply_queue_count
spiking to 300-500 is the "smoking gun." This confirms that your cluster is suffering from thread pool exhaustion during your synchronized bursts. Here is why your "synchronization" strategy is actually the root cause of the 10s latency, and how to fix your capacity planning: 1. The Fallacy of Synchronization Your logic that synchronizing at the top of the minute reduces disk lookups is correct for a single batch, but it is counter-productive for cluster-wide concurrency. Even if merge-commit sorts the keys to make I/O sequential, by hitting the cluster with everything at the same second: * The Queue Problem: You have 8 nodes with 16 vCPUs each. By default, StarRocks uses 16 threads for the "Apply" phase. When you send 720k records across all nodes at once, you are generating hundreds of "Apply" tasks (one for each tablet being updated). * The Result: If you have 500 tasks and only 16 threads, task #500 has to wait for 31 other tasks to finish before it even starts. This is why your latency is 10s even if the actual disk I/O is "efficient." 2. Why Replication Factor 2 Fails When you increase 
replication_num
to 2: 1. Every upsert must be Applied on two different BE nodes. 2. The number of tasks in that 
update_apply_queue
effectively doubles. 3. If your queue is already 500 at replication 1, it will hit 1000+ at replication 2, leading to RPC timeouts and cluster instability. 3. Immediate Technical Fixes A. Increase the Apply Thread Pool Since your CPU utilization is only at 60%, you have "headroom" to allow more concurrent Apply tasks. Increase this in your `be.conf`:
Copy code
properties
# Increase from default (CPU cores) to handle the burst
transaction_apply_worker_count = 32 
# Also increase the index loading pool
get_pindex_worker_count = 64
B. Implement "Jitter" (Critical) Stop the top-of-the-minute synchronization. If you spread those 720k records over a 60-second window (approx 12k records/sec continuously), your 
update_apply_queue_count
will likely stay near zero. Continuous streaming is the "Golden Rule" for StarRocks Primary Key tables. C. Check your Tablet Count If your "small" 500MB payload is spread across too many tablets, it creates too many small Apply tasks. * Check your 
DISTRIBUTED BY HASH(...) BUCKETS
setting. * Target: You want each tablet to be between 1GB and 10GB. If your tablets are 100MB, you have 10x more metadata overhead than necessary. 4. Suggested Capacity Planning For a high-QPS (50k) environment with heavy PK upserts and a requirement for High Availability (Replication 2 or 3): 1. Node Size: 
r6in.4xlarge
is too small for this concurrency level. The 16 vCPUs get split between 50k Queries/sec and the background Apply/Compaction tasks. * Recommendation: Move to 
r6in.8xlarge
 (32 vCPU, 256GB RAM). * Reasoning: This doubles your default thread pools and provides much higher EBS burst bandwidth, which is essential for the Delete Vector writes that happen during "Apply." 2. Storage: Your 4 x gp3 volumes are fine, but ensure you aren't hitting the Instance-level EBS throughput limit (which is lower on the 
4xlarge
than the 
8xlarge
). 3. Compaction: You should actually dial back your aggressive compaction settings. Your current configs are likely "stealing" I/O and CPU from the Apply phase. * Reduce 
update_compaction_num_threads_per_disk
to 2. * Reduce 
compact_threads
to 8. Summary: Your cluster isn't necessarily "too small" for the data volume, but it is too small for the concurrency of your current synchronized load pattern. If you jitter your loads and increase the 
transaction_apply_worker_count
, you will see that 10s latency drop to < 1s, and you'll finally be able to enable Replication 2 safely. References
m
@Rocky I upped my transaction apply worker count to 32 and get pindex worker count to 64. The update apply queue count did stay relatively low. However, the transaction publish latency did not change much. Is this expected? Am I just tuning for data freshness right now?
r
Hi! I'm Rocky. Yes, you are exactly right—by tuning the 
transaction_apply_worker_count
, you have primarily tuned for Data Freshness (reducing the time data spends "waiting in line" to be processed) rather than the absolute Transaction Publish Latency. Here is why your latency didn't drop significantly despite the queue being lower, and what is likely happening behind the scenes: 1. "Apply" vs. "Publish": What are you measuring? In StarRocks Primary Key tables, the Publish phase is where the "heavy lifting" occurs. If you are looking at the FE metric 
starrocks_fe_txn_publish_latency_ms
, it is broken down into: * Schedule Latency: Time waiting for the FE to send the "Publish" task to the BEs. * Execute Latency: The actual time the BE spends doing the Apply (Primary Key index lookups + Delete Vector generation). * Ack/Finish Latency: Final metadata updates in the FE. By increasing the worker count, you reduced the Schedule portion (tasks no longer wait in a queue), but the Execute portion remains the same because it is now gated by hardware limits, not thread availability. 2. The 720k Row "Small Batch" Trap You mentioned your payload is only 500MB, but the 720,000 rows is the critical number. For a Primary Key table, 500MB of data is easy to write to disk, but 720,000 rows requires 720,000 index lookups. * If your Persistent Index is on disk (EBS), even with a low queue, those 720k lookups require thousands of random I/O operations. * The 10s Latency: If each lookup takes even a fraction of a millisecond, 720k lookups spread across your 16-32 threads will still take several seconds of pure wall-clock time. * Tuning won't help I/O: If your disks (gp3) are hitting their IOPS/Throughput limit during that "top of the minute" burst, more threads will just wait faster for the disk. 3. The "Merge Commit" Latency Floor If you are using Merge Commit, the latency you see at the client level is artificially inflated by design: _ Merge Commit waits for either a time window (
merge_commit_interval_ms
) or a data size before it even _starts* the transaction. * If your 
merge_commit_interval_ms
is set to 10 seconds (the default in some Flink configurations), your "latency" will always be at least 10 seconds, regardless of how fast the BEs are. 4. Recommendation: How to move the needle To actually reduce the 10s publish latency, you need to shift from "tuning for throughput" to "tuning for speed": 1. Check FE Latency Breakdown: Run this in your FE to see where the 10s is actually spent:
Copy code
promql
    starrocks_fe_txn_publish_execute_latency_ms  # This is the BE "Apply" work
    starrocks_fe_txn_publish_schedule_latency_ms # This is FE queueing
2. Verify Persistent Index Performance: Look at the BE metric 
update_rowset_commit_apply_duration_us
. If this number is high while 
update_apply_queue_count
is low, your disks are the bottleneck. You may need to increase gp3 IOPS or move to 
r6in.8xlarge
for better EBS bandwidth. 3. Jitter your "Top of the Minute" Sync: Synchronizing 720k rows into a single second is a "thundering herd" problem. If you spread that load over 60 seconds (12k rows/sec), the BEs can process the lookups continuously using the CPU/Disk cache effectively, and your latency will likely drop to sub-second. 4. Check Merge Commit Interval: If you are using Flink or Stream Load with Merge Commit, reduce 
merge_commit_interval_ms
to 
1000
(1 second). This will trade off some "efficiency" for much lower latency. Summary: You’ve successfully cleared the "traffic jam" (the queue), but the "speed limit" (Disk I/O and the Merge Window) is still 10 seconds. To go faster, you need to either spread out the load or increase disk performance. References • administration/management/monitoring/metrics.md
m
@Rocky So if my avg(num_version) stays below 100, compaction score ~150 sustained, CPU and memory stable, disk IOPS and throughput not being exceeded, only thing that remains is tuning for data freshness? So if the ~20-30 second delay in total is acceptable, then I can just accept my cluster as is?
r
Hi! I'm Rocky. Based on your metrics, the short answer is yes, you can accept your cluster as it is, provided your query performance meets your current requirements. Here is the technical breakdown of why your current state is stable, even if it looks "red" on some dashboards: 1. Version Count vs. Compaction Score * 
num_version
< 100: This is a very healthy number. In StarRocks Primary Key tables, the default limit for versions is 1,000 (
tablet_max_versions
). Since you are at 10% of that limit, you are nowhere near a "load failure" state. * Compaction Score ~150 (Sustained): While a score over 100 in Shared-Data clusters triggers an ingestion slowdown, in a Shared-Nothing cluster (like yours), it is simply an indicator of Read Amplification. A score of 150 means StarRocks is reading roughly 150 small rowsets/files to reconstruct the data for a query. * The Verdict: If your 50k QPS queries are still finishing within your required SLAs, a score of 150 is "stable debt." As long as it is sustained (not growing indefinitely), your background compaction is successfully keeping up with the ingestion rate. 2. Is "Data Freshness Tuning" the only thing left? Yes. Since your CPU, Memory, and Disk I/O are stable, you have reached the "Efficiency Frontier" for your current hardware: * The 20-30s Delay: This is the cumulative time of your Merge Commit window (likely 5-10s) + Apply Latency (the 10s you're seeing) + FE metadata overhead. * Upscaling is optional: Moving to 
8xlarge
nodes would likely cut that 10s Apply latency in half because of the faster single-thread performance and higher EBS burst limits, but it won't change the nature of the workload. 3. When SHOULD you upscale? You only need to upscale if any of the following occur: 1. Query Latency Degrades: If you notice your 50k QPS queries getting slower over time because they have to "sieve" through the 150 versions. 2. Compaction Score Starts Climbing: If 150 becomes 300, 500, etc., it means your background threads can't keep up with the volume, and you will eventually hit the 
tablet_max_versions
wall. 3. The "Apply Queue" Returns: If your 
update_apply_queue_count
starts spiking again despite your worker count being at 32, it means the 16 vCPUs on your 
4xlarge
are physically saturated. Rocky's Final Recommendation: If a 30-second end-to-end delay is acceptable for your business, stick with what you have. You have successfully tuned the "Apply" bottleneck by increasing the worker counts. One minor "Safety" tweak: To ensure that "sustained" 150 score doesn't creep up, consider slightly increasing your compaction priority if you have spare CPU:
Copy code
properties
# BE configuration
# Allow compaction to work slightly harder if it's staying behind
update_compaction_per_tablet_min_interval_seconds = 30
You've done the hard work of identifying the thread pool bottleneck—now you can enjoy the cost savings of the 
4xlarge
nodes! References • best_practices/primarykey_table.md • administration/management/BE_configuration.md • administration/management/monitoring/metrics.md • table_design/table_types/primary_key_table.md References • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky If my update apply queue count is low, and my EBS volumes are nowhere near their throughput and IOPS limit, why is my 
update_rowset_commit_apply_duration_us
high? It ranges from 2.5s to 5s during spikes. On another note, does replication only help when a single node fails in shared-nothing? So data is duplicated across different nodes so if node A fails, we can still query the missing data from an alive node B.
r
The reason your 
update_rowset_commit_apply_duration_us
remains high (2.5s–5s) even with a low queue and healthy disk metrics is that this metric measures the execution time of the Apply task itself, which involves several high-latency steps beyond just raw disk throughput. 1. Why Apply Duration is High (The "Hidden" Bottlenecks) Even if your gp3 volumes are not hitting their IOPS/MBps ceilings, the Apply phase is gated by latency-sensitive operations: * PK Index Lookups (The IOPS Latency Wall): For every one of your 720k rows, StarRocks must find where the old version lives. Even with a "Persistent Index," StarRocks has to perform lookups. If the index isn't fully cached in memory, it performs random-access reads from disk. gp3 has a baseline latency (typically 1–2ms). Doing hundreds of thousands of lookups, even in parallel across 32 threads, can easily consume several seconds of wall-clock time. * Tablet Lock Contention: During the Apply phase, each tablet being updated must be locked to ensure consistency while the index is updated and the Delete Vector is written. If your 720k rows are concentrated in a few tablets, your 32 worker threads are all fighting for the same tablet locks. The time spent waiting for a tablet lock is included in the apply duration. * Delete Vector Generation: StarRocks doesn't just "overwrite" data; it marks old rows as deleted in a 
DelVector
. Generating this bitmask and writing it to disk is a metadata-heavy operation. If your rowsets have many small segments, this process becomes significantly slower. * CPU Context Switching: With 32 apply workers on a 16-vCPU machine, you are over-subscribing your cores. While this helps keep the queue low, it introduces context-switching overhead, which stretches the duration of each individual task. 2. The Role of Replication in Shared-Nothing You are correct: in a Shared-Nothing architecture, replication is primarily about High Availability (HA) and Fault Tolerance. * Node Failure: If Node A crashes (hardware failure, OOM, etc.), the FE (Frontend) knows that Node B and Node C have the same tablets. It will automatically reroute all queries to the surviving nodes. Without replication (
replication_num = 1
), the data on Node A becomes a "black hole," and any query needing that data will fail. * Data Integrity: If a disk on Node A becomes corrupted, StarRocks can "repair" the corrupted tablet by cloning a healthy copy from Node B. * Query Load Balancing: While not its primary purpose, having multiple replicas allows the FE to spread the 50k QPS across different nodes that hold the same data, preventing any single node from being the "hot spot." * The "Write Quorum": With replication, a load is successful once a majority of replicas (e.g., 2 out of 3) have acknowledged the write. This prevents a single slow node from hanging your entire ingestion pipeline. Rocky's Summary & Advice: * The Latency: Your 2.5s–5s apply duration is actually quite "normal" for a burst of 720k rows on 
4xlarge
instances. It’s the cost of the Delete+Insert logic that makes StarRocks Primary Key tables so fast for queries. * The "Fix": If you want that 5s to become 1s, the only way is to increase Tablet count (to reduce lock contention) or move to 
8xlarge
 (to get more CPU cache and higher EBS burst performance for index lookups). * Replication: Do not run in production with replication 1. If you lose one node, your entire 50k QPS service goes down. Now that you've stabilized the apply queue, you should move to 
replication_num = 2
(or 3) immediately. Useful Metrics to verify: Check the BE metric 
update_primary_index_num_disk_read
and 
update_primary_index_num_disk_write
. If these are spiking during your "top of the minute" loads, it confirms that the latency is coming from Persistent Index disk I/O, even if you aren't hitting the gp3 limits. References
m
@Rocky Is it just a limitation of EBS or the cluster CPUs when i enable replication
r
Hi! I'm Rocky. That is a great question. When you enable replication in a Shared-Nothing architecture, you aren't just copying data—you are multiplying the workload across your cluster. The short answer is: It is both, but for different reasons. Here is the breakdown of why enabling replication affects your 
update_rowset_commit_apply_duration_us
and where the actual "wall" is. 1. The "Work Multiplier" Effect In StarRocks Primary Key tables, the Apply phase is not a simple file copy. It is a compute-and-I/O-intensive operation. * Without Replication (Rep=1): One node does 720k PK lookups and generates one 
DelVector
. * With Replication (Rep=3): Three different nodes each perform those same 720k PK lookups and each generate their own 
DelVector
. Even though the nodes work in parallel, you have tripled the total CPU cycles and total Disk I/O operations required by the cluster to finish that one transaction. 2. Why it feels like a CPU Limitation Your 
4xlarge
instances have 16 vCPUs. By setting 
transaction_apply_worker_count
to 32, you have over-subscribed your physical cores. * When replication is 1, those 32 workers have the whole 16-vCPU "pie" to themselves. * When replication is 3, if your tablets are distributed across the cluster, multiple "Apply" tasks for different tablets might land on the same node simultaneously. * The Result: Your CPUs spend more time "context switching" (swapping between the 32 worker threads) than doing actual work. This inflates the 
duration_us
metric even if the CPU % looks stable. 3. Why it feels like an EBS (I/O) Limitation This is usually the "latency floor" you are hitting. Even if you aren't hitting the throughput limit (MB/s) of gp3, you are likely hitting the latency limit of random I/O. * Random Lookups: The Primary Key index is essentially a massive hash table. To find if a row exists, StarRocks does a random read. * gp3 Latency: EBS volumes (even gp3) typically have a 1ms–2ms latency for random I/O. * The Math: Doing 720k random lookups—even when parallelized across 32 threads—will physically take several seconds because of the "round-trip" time to the EBS storage. * Replication's impact here: With replication, the transaction's success depends on the Write Quorum (e.g., 2 out of 3 replicas). Your transaction publish latency will always be as slow as the second-fastest node. If one EBS volume has a slight "latency spike," it drags the whole transaction duration with it. 4. Is it a "Limitation"? It's less of a limitation and more of a Hardware SLA. * To get that 2.5s–5s duration down to < 1s, you would need hardware that reduces the "per-lookup" latency. This usually means moving to NVMe-based instances (like 
r6id
or 
i4i
series) where local disk latency is measured in microseconds rather than milliseconds. * Alternatively, you can increase the number of tablets. This spreads the 720k rows across more "lock buckets," allowing your 32 workers to work more efficiently without fighting over the same tablet locks. Summary Recommendation If your current 20–30s end-to-end freshness is acceptable, don't change the hardware. The fact that your queue is low means your 
4xlarge
nodes are keeping up with the volume. However, if you must reduce the 5s apply duration: 1. Check Tablet Count: Ensure you have enough tablets (at least 2x or 3x the number of BE cores) to prevent lock contention. 2. EBS gp3: If you are at the default 3,000 IOPS, try bumping one volume to 10,000 IOPS as a test. If the duration drops, your bottleneck is EBS random I/O latency. 3. Replication: Always keep replication at 2 or 3 for production. The small latency trade-off is worth the fact that your cluster won't die if one AWS rack has a bad day. References • administration/management/resource_management/Replica.md
m
@Rocky What are key prometheus metrics I should add to my dashboard to monitor ingestion health and query health? Give me a comprehensive list, delineated by appropriate sections (grafana rows)
r
To monitor the ingestion and query health of your StarRocks cluster effectively, I recommend organizing your Grafana dashboard into the following rows. This structure allows you to correlate "Data Freshness" (Ingestion) directly with "User Experience" (Query Health). Row 1: Cluster Overview (Availability & High-Level Health) * FE/BE Status: 
starrocks_fe_status
and 
starrocks_be_status
(Should be 1 for alive). * Total QPS: 
sum(irate(starrocks_fe_query_total[1m]))
* Query Error Rate: 
sum(irate(starrocks_fe_query_err[1m]))
(Monitor for spikes during ingestion bursts). * Total Connections: 
starrocks_fe_connection_total
(Helps identify connection leaks). Row 2: Query Health (Performance & Latency) * Query Latency (P99, P95, P50): 
starrocks_fe_query_resource_group_latency
(Use 
{quantile="0.99"}
). High P99 here usually indicates resource contention or large "Data Debt" (high version counts). * Query Throughput by Resource Group: 
irate(starrocks_fe_query_resource_group[1m])
* Scan Throughput (Bytes/s): 
irate(starrocks_be_scan_bytes_total[1m])
(Indicates how much data the BEs are physically reading). Row 3: Ingestion Health (Throughput & Success) * Transaction Load QPS: 
sum(irate(starrocks_fe_txn_total_latency_ms_count[1m]))
by 
type
(Stream Load, Routine Load, etc.). * Transaction Success/Failure: Use 
starrocks_fe_txn_total_latency_ms_count
to track the ratio of committed vs failed transactions. * Merge Commit Throughput: 
irate(merge_commit_request_total[1m])
(Crucial for users using the Stream Load "Merge" feature). * Ingestion Bytes/s: 
irate(starrocks_be_load_bytes_total[1m])
Row 4: Primary Key Engine & Data Freshness (Deep Dive) * Apply Duration (P99): 
update_rowset_commit_apply_duration_us
(The metric you've been monitoring—keep an eye on the 2.5s-5s range). * Apply Queue Size: 
update_apply_queue_count
(If > 0, your worker threads are saturated). * Transaction Publish Latency: 
starrocks_fe_txn_publish_latency_ms
(The delay between "Commit" and "Visible"). * Persistent Index Memory: 
update_mem_bytes
(Monitor this to ensure the PK index fits in memory). Row 5: Data Debt & Compaction (The "Health" of your Storage) * Max Compaction Score: 
starrocks_fe_tablet_max_compaction_score
(Target < 100; alerts at > 500-800). * Max Tablet Version Count: 
starrocks_be_tablet_version_count
(Monitor this against your 
tablet_max_versions
limit). * Compaction Throughput: 
irate(update_compaction_outputs_total[1m])
(Are compactions actually finishing?). * Compaction Duration: 
update_compaction_duration_us
(Increasing duration often means the background I/O is getting slower). Row 6: System Resource Saturation * CPU Idle: 
starrocks_be_cpu_idle
(Alert if < 10% sustained). * BE Process Memory: 
starrocks_be_mem_bytes
(Monitor against 
mem_limit
). * Disk I/O Utilization: 
starrocks_be_disk_util
(Percentage of disk busy time). * Disk Available Capacity: 
starrocks_be_disks_avail_capacity
(Percentage). * FE JVM Heap: 
starrocks_fe_jvm_heap_size_bytes
(Critical for metadata-heavy clusters). Rocky’s Pro-Tip for your Dashboard: When setting up your Apply Duration and Compaction Score panels, use a Dual-Y-Axis chart. Plot 
update_rowset_commit_apply_duration_us
on one axis and 
update_apply_queue_count
on the other. This will show you instantly if a latency spike is due to a single slow transaction or a backup in-cluster queuing!formation task or a backup in the worker queue. References • administration/management/monitoring/metrics.md • administration/management/monitoring/Monitor_and_Alert.md • administration/management/BE_configuration.md
m
@Rocky My column definition is
Copy code
alter table requests
    add has_bid int default 0 not null
This introduced an issue during stream load where the error indicated 
NULL value in non-nullable column 'has_bid'
. I thought if a row value was null, the insertion will default it to 0, hence not null is not an issue?
r
I understand the confusion—it's a common misconception in SQL that a 
DEFAULT
value acts as a "fallback" for 
NULL
data. However, in StarRocks (and most SQL databases), these two constraints behave differently: 1. Why you got the error The 
DEFAULT
value is only triggered when the column is omitted from the load entirely. It is not a fallback for when the source data contains an explicit 
NULL
. * Scenario A (Column Omitted): If your Stream Load 
columns
header does not include 
has_bid
at all, StarRocks will automatically fill every row with 
0
. This works perfectly. * Scenario B (Column Included but Data is NULL): If your Stream Load mapping includes 
has_bid
(either explicitly in the 
columns
header or implicitly by position in a CSV), and the source data contains a 
NULL
value (like 
\N
in CSV or 
null
in JSON), StarRocks attempts to insert that 
NULL
. * The Conflict: Because you defined the column as 
NOT NULL
, the insert operation fails with the error you saw. The 
DEFAULT
value is ignored because you provided a value (which happened to be 
NULL
), rather than providing nothing. 2. How to handle this in Stream Load If your source data contains `NULL`s and you want them converted to 
0
during the load, you must handle the transformation in your 
columns
parameter using the 
ifnull()
function. Update your Stream Load headers (or your Flink/Spark/Python load script) to include a mapping like this:
Copy code
bash
# Example for CSV where has_bid is the 5th column
-H "columns: col1, col2, col3, col4, temp_bid, has_bid = ifnull(temp_bid, 0)"
What this does: 1. Maps the raw source data for that column to a temporary variable (
temp_bid
). 2. Uses 
ifnull(temp_bid, 0)
to check if the value is null. 3. If it is null, it assigns `0`; otherwise, it keeps the original value. 4. The final result is stored in the actual 
has_bid
column. 3. Summary Checklist * Is the data missing from the file? If the field is completely absent from your JSON/CSV and you remove it from your load's 
columns
mapping, the 
DEFAULT 0
will work automatically. * Is the data present but 
NULL
? You must use 
ifnull(col, 0)
in your load transformation. * Strict Mode: If you have 
strict_mode=true
in your load, StarRocks is even more aggressive about failing on 
NULL
values for non-nullable columns. Rocky's Advice: Even with a 
DEFAULT
defined, I always recommend using 
NOT NULL
with a transformation like 
ifnull()
in your ingestion pipeline. It makes your data quality much more predictable! References • loading/StreamLoad.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md
m
@Rocky
Copy code
type BidRequestSRData struct {
    ReceivedHour                       time.Time  `json:"received_hour,omitempty"`
    AuctionId                          *string    `json:"auction_id,omitempty"`
    ImpKey                             *string    `json:"imp_key,omitempty"`
    ImpId                              *string    `json:"imp_id,omitempty"`
    NumRequests                        int        `json:"num_requests,omitempty"`
    HasBid                             int        `json:"has_bid,omitempty"`
This is part of the go struct. Anything wrong with it? I do not specify column headers. My payload is a list of json objects that were marshalled from this struct. I have over 500 fields
r
Yes, there is a specific issue with your Go struct that perfectly explains the 
NULL value in non-nullable column
error you encountered. The Problem: 
omitempty
on Integer Zero Values In Go, the 
json:"...,omitempty"
tag tells the marshaler to omit the field from the resulting JSON if it has a "zero value." * For an 
int
, the zero value is 
0
. * If 
HasBid
is 
0
, Go will completely remove the 
"has_bid"
key from the JSON string. * StarRocks Behavior: When you use Stream Load in "Simple Mode" (mapping by JSON keys) and a key is missing from the JSON object, StarRocks attempts to insert a 
NULL
for that column. * The Conflict: Because your column is defined as 
NOT NULL
, the load fails. StarRocks does not automatically use the table's 
DEFAULT 0
for missing keys in a JSON object; it treats them as 
NULL
. How to Fix It You have two main options depending on how you want to handle your data: Option 1: Remove 
omitempty
(Recommended) If 
0
is a valid value that you want to store in StarRocks, remove the 
omitempty
tag from your 
int
fields. This ensures that 
"has_bid": 0
is always sent in the JSON.
Copy code
go
type BidRequestSRData struct {
    // ...
    HasBid int `json:"has_bid"` // Removed omitempty
}
Option 2: Use Pointers (If you need to distinguish NULL from 0) If you want the field to be 
0
sometimes and 
NULL
other times, you should use a pointer (
*int
). However, since your column is 
NOT NULL
, this probably isn't what you want for 
has_bid
. Option 3: Handle at Ingestion (Tedious for 500 fields) You could keep the Go struct as-is and use 
ifnull
in your Stream Load headers, but as you noted, doing this for 500+ fields is not scalable: 
H "columns: ..., tmp_has_bid, has_bid = ifnull(tmp_has_bid, 0)"
Other Potential Issues with 500 Fields 1. 
time.Time
Zero Value: The 
ReceivedHour
field also has 
omitempty
. In Go, the zero value for 
time.Time
is 
0001-01-01 00:00:00
. If you don't set this field, it will be omitted, and StarRocks will try to insert 
NULL
into that column as well. If that column is 
NOT NULL
, it will fail. 2. Case Sensitivity: By default, StarRocks JSON mapping is case-sensitive. Ensure your JSON tags (
json:"auction_id"
) match your StarRocks column names exactly. 3. JSON Size Limit: With 500 fields per row, your JSON objects might be large. * If a single JSON object (one row) exceeds 4GB, it will fail (unlikely). * If your batch (the whole payload) exceeds 100MB, you must set 
-H "ignore_json_size:true"
in your Stream Load command. Rocky's Summary The "smoking gun" is the 
omitempty
on your 
int
fields. When your Go code sets 
HasBid = 0
, it disappears from the JSON, StarRocks assumes you are trying to insert 
NULL
, and the 
NOT NULL
constraint blocks it. Remove 
omitempty
from your required integer and time fields. References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/StreamLoad.md
m
@Rocky What about fields not included in the payload (like HasWon is not in the go struct) but is a valid column in my schema
Copy code
alter table requests
    add has_won int default 0 null;
r
If a field like 
has_won
exists in your StarRocks schema but is completely missing from your Go struct (and thus missing from the JSON payload), the behavior depends on the column's nullability, but it might not be what you expect based on standard SQL 
INSERT
statements. 1. The "Missing Key" Behavior In StarRocks Stream Load (specifically for JSON), if a key is missing from the JSON object: * StarRocks defaults the value to 
NULL
. * It does NOT automatically trigger the table's 
DEFAULT
value. Even though your column is defined as 
DEFAULT 0 NULL
, because the key is missing in the JSON, StarRocks will attempt to insert a literal 
NULL
. Since the column is nullable, the insert will succeed, but the value will be 
NULL
, not 
0
. 2. The Difference from Standard SQL In a standard 
INSERT INTO requests (col1) VALUES (val1)
, if you don't mention 
has_won
, the database uses the 
DEFAULT
. However, in Stream Load's JSON "Simple Mode" (where StarRocks maps JSON keys to columns automatically): 1. StarRocks looks for a key named 
has_won
. 2. If it doesn't find it, it assumes the value is 
NULL
. 3. It then tries to put that 
NULL
into the column. 4. If the column is 
NOT NULL
, the load fails (as you saw with 
has_bid
). 5. If the column is 
NULL
(like 
has_won
), it stores 
NULL
. 3. How to make it use 
0
 If you want 
has_won
to be 
0
when it's missing from the payload, you have two options: Option A: Add it to the Go Struct This is the cleanest way. If you add it to the struct without 
omitempty
, it will always send 
0
.
Copy code
go
HasWon int `json:"has_won"`
Option B: Use a Transformation in Stream Load You can explicitly tell StarRocks to use the default value if the JSON key is missing by using the 
columns
header:
Copy code
bash
-H "columns: ..., has_won=ifnull(has_won, 0)"
_Note: This works because StarRocks first tries to map the JSON key 
has_won
to a temporary variable. If the key is missing, that variable becomes 
NULL
, and 
ifnull
then converts it to 
0
._ Summary Table Scenario | Table Column | Go Struct Status | Result in StarRocks 
has_bid
 | 
INT NOT NULL DEFAULT 0
| 
int
+ 
omitempty
(Value is 0) | FAIL (
NULL
in 
NOT NULL
column) 
has_won
 | 
INT NULL DEFAULT 0
| Not in struct | 
NULL
 
has_won
 | 
INT NULL DEFAULT 0
| Added to struct (no 
omitempty
) | 
0
 Rocky's Conclusion: For JSON loading in StarRocks, the table's 
DEFAULT
clause is essentially ignored if the key is missing from the JSON; the system defaults to 
NULL
instead. To ensure a 
0
, you must either send it from Go or use 
ifnull()
in your load command. References • loading/StreamLoad.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md CREATE_TABLE) • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • loading/StreamLoad.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md
Yes, Merge Commit will still work technically, but using 
omitempty
makes your ingestion much more fragile when Merge Commit is active. Here is the breakdown of how Merge Commit interacts with your Go struct and JSON payload: 1. Does 
omitempty
break "Homogeneity"? No. In the context of StarRocks Merge Commit, "homogeneous" refers to the HTTP Headers and Stream Load parameters, not the specific keys present inside your JSON objects. As long as every request sends the same headers, such as: * 
-H "format: json"
* 
-H "enable_merge_commit: true"
* 
-H "strip_outer_array: true"
(if applicable) * The same database and table URL. ...then StarRocks considers them homogeneous and will merge them into a single transaction. It does not inspect the keys of your JSON objects to decide whether to merge. 2. The Danger: "All or Nothing" Failure The real issue with 
omitempty
and Merge Commit is how StarRocks handles errors. The documentation states:
"Merge Commit merges multiple loading requests into a single transaction. If one request contains data quality issues, all requests in the transaction will fail."
Because you are using 
omitempty
on 
int
fields like `HasBid`: 1. Request A: 
HasBid
is 1. The JSON contains 
"has_bid": 1
. (Valid) 2. Request B: 
HasBid
is 0. Go omits it. The JSON is missing the key. StarRocks tries to insert 
NULL
. (Invalid for 
NOT NULL
column) 3. The Result: StarRocks merges Request A and Request B into one transaction. Because Request B fails the 
NOT NULL
constraint, the entire transaction is rolled back. You lose the data from Request A as well. 3. Recommendation for your Go Struct To make Merge Commit reliable with 500+ fields, you should: 1. Remove 
omitempty
from all 
NOT NULL
fields: This ensures the key is always present in the JSON (even if it's 
0
or 
""
), preventing the "NULL value in non-nullable column" error. 2. Keep 
omitempty
ONLY for 
NULL
fields: If a column in StarRocks is defined as 
NULL
(like your 
has_won
example), it is safe to keep 
omitempty
. If the key is missing, StarRocks will simply insert a 
NULL
. 3. Avoid Pointers unless necessary: For 
NOT NULL
columns, use plain types (
int
, 
string
, 
bool
) without 
omitempty
. For 
NULL
columns, pointers (
_int
, 
_string
) are appropriate. Summary Checklist for Merge Commit Success: * Headers: Ensure your Go client sends identical HTTP headers for every batch. * Data Integrity: Since one bad row kills the whole merged batch, you must be stricter with your Go tags. If the column is 
NOT NULL
in StarRocks, remove 
omitempty
in Go. * Version: Ensure you are on StarRocks v3.4.0 or later, as that is when Merge Commit was introduced. Rocky's Tip: Since you have 500 fields, manually managing this is tough. If you can't change the Go struct, you might consider adding 
-H "columns: ..."
to your load to explicitly handle defaults, but the cleanest fix is definitely in the Go JSON tags. References • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Json_loading.md References • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Flink-connector-starrocks.md
m
@Rocky How to diagnose what is causing starrocks_be_jemalloc_allocated_bytes to rise?
r
To diagnose why 
starrocks_be_jemalloc_allocated_bytes
is rising, you need to move from high-level metrics to specific memory "trackers" and finally to low-level heap profiling. Here is the recommended step-by-step diagnostic path: 1. Identify the Growing Memory Pool (Quick Check) StarRocks categorizes memory into different pools. Use the 
mem_tracker
endpoint on the BE node to see which functional area is consuming the most memory. Access the endpoint:
Copy code
bash
curl http://<BE_IP>:<BE_HTTP_PORT>/mem_tracker
Look for the following categories to see which one has a high or rising `current_consumption`: * 
query_pool
: High memory due to complex or large-scale queries. * 
load
: Memory used by data ingestion (MemTables). * 
table_meta
: Metadata for tablets, rowsets, and indices. If this is rising, it may be due to too many tablets/versions. * 
compaction
: Memory used during background data merging. * 
storage_page_cache
: The data cache (if enabled). 2. Check for "Large Alloc" in BE Logs If the rise is sudden or spikey, it is often caused by a specific query or load task. * Search logs: Grep your 
<http://be.INFO|be.INFO>
or 
be.WARNING
logs for the string 
large alloc
. * Action: If found, the log will typically include a 
query_id
. You can then cross-reference this ID in the FE audit log to find the exact SQL statement. 3. Detailed Jemalloc Stats For a lower-level view of how jemalloc is managing memory (e.g., distinguishing between allocated memory and memory held in the jemalloc cache/metadata), use the 
memz
endpoint:
Copy code
bash
curl http://<BE_IP>:<BE_HTTP_PORT>/memz
* 
MALLOC: ... Bytes in use by application
: This corresponds to your 
allocated_bytes
. * 
MALLOC: ... Bytes in malloc metadata
: If this is high, jemalloc itself is using significant memory to manage your allocations (common with many small objects). 4. Deep Dive: Jemalloc Heap Profiling (Recommended for leaks) If memory increases slowly and never drops (suggesting a leak), use the built-in heap profiler (available in v3.1.6+). This has a small performance impact but tells you exactly which C++ function allocated the memory. Step A: Enable profiling via SQL
Copy code
sql
-- Get BE ID from 'SHOW BACKENDS'
ADMIN EXECUTE ON <be_id> 'System.print(HeapProf.getInstance().enable_prof())';
Step B: Wait for memory to rise, then dump a snapshot
Copy code
sql
ADMIN EXECUTE ON <be_id> 'System.print(HeapProf.getInstance().dump_dot_snapshot())';
Step C: Visualize The output of 
dump_dot_snapshot
is a DOT-formatted graph text. 1. Copy the resulting text. 2. Paste it into an online visualizer like GraphvizOnline. 3. The resulting flame graph/chart will show the function call stacks responsible for the memory growth. 5. Check for Versioning Issues (Metadata Bloat) If 
table_meta
is the culprit: * Check for "Version Count" issues: 
SHOW TABLET <tablet_id>
to see if compaction is falling behind. * Check if you have an excessive number of partitions or tablets, which inflates the metadata memory. Summary Checklist Symptom | Probable Cause | Diagnostic Tool Spiky rise | Large Query or Load | 
<http://be.INFO|be.INFO>
(large alloc) + FE Audit Log Steady rise (Querying) | Query Memory Leak | 
mem_tracker?type=query_pool
Steady rise (System) | Metadata or Internal Leak | 
mem_tracker
(table_meta) or Jemalloc Profiling High metadata | Too many tablets/versions | 
mem_tracker
+ 
SHOW BACKENDS
(look at TabletCount)` References • administration/management/resource_management/Memory_management.md • knowledge/trouble_shooting/fe_memory_problems.md • faq/fe_mem_faq.md
m
Copy code
level	Label	Parent	Limit	Current Consumption	Peak Consumption
1	process		105G	76G	85G
2	jemalloc_metadata	process	none	1239M	1240M
2	query_pool	process	95G	84M	40G
2	load	process	31G	326M	13G
2	compaction	process	105G	0	8G
2	schema_change	process	none	0	0
2	page_cache	process	none	18G	21G
2	jit_cache	process	none	9K	9K
2	clone	process	none	0	32M
2	consistency	process	10G	0	168
2	datacache	process	none	18G	21G
2	replication	process	none	0	0
2	metadata	process	none	1393M	1659M
2	update	process	63G	41G	43G
@Rocky
r
The 
update
memory pool is the culprit here, consuming 41G (over 50%) of your currently allocated 76G. In StarRocks, the 
update
pool is dedicated to Primary Key (PK) tables. It stores the Primary Key Index (a hash table mapping your primary keys to their physical row locations) and Delete Vectors. 1. Identify which Table/Tablet is responsible The memory tracker allows you to drill down into the 
update
pool to find exactly which tables are eating the memory. Run this command against the BE node:
Copy code
bash
# Get a more detailed breakdown of the 'update' pool
curl http://<BE_IP>:<BE_HTTP_PORT>/mem_tracker?type=update&upper_level=4
* Look for large values in the output. It will break down memory usage by Tablet ID. * Once you find a high-usage Tablet ID, find its table name: 
SHOW TABLET <tablet_id>;
2. Common Causes for High 
update
Memory Based on your 41G usage, you likely have one of the following scenarios: A. Persistent Index is Disabled By default, StarRocks tries to keep the PK Index in memory for performance. If you have a massive number of rows (hundreds of millions or billions) in a PK table and 
enable_persistent_index
is set to 
false
, the index stays entirely in RAM. * Fix: Check your table properties. If it's false, alter the table:
Copy code
sql
    ALTER TABLE my_table SET ("enable_persistent_index" = "true");
This will move the majority of the index to disk (ideally SSD), drastically reducing RAM usage. B. Primary Key Columns are too "Fat" The memory cost of the PK Index is directly proportional to the size of your primary key columns. * Formula: 
(key_size + 9 bytes) _ row_count _ replicas * 1.5
* If you use long strings (
VARCHAR(255)
) as primary keys instead of 
BIGINT
or short strings, memory usage will balloon. C. High "Apply" Load The 
update
tracker also includes memory used during the Apply phase of ingestion (writing new data and marking old data as deleted). * If you have very high-frequency small loads, many "Apply" tasks might be queued. * Check 
l0_max_mem_usage
in 
be.conf
(default is 100MB per tablet). If you have thousands of tablets being loaded simultaneously, this adds up. 3. Immediate Mitigation Steps If your BE is nearing its memory limit (105G), you can tune these 
be.conf
settings: 1. 
update_memory_limit_percent
: This limits what percentage of BE memory the 
update
pool can take (default is usually 60%). You can lower this to prevent the BE from crashing, but it may cause loads to slow down or fail if the limit is hit. 2. 
l0_max_mem_usage
: Reduce this (e.g., to 
67108864
or 64MB) if you have many tablets to reduce the memory footprint of active loads. 3. 
skip_pk_preload
: Set to 
true
in 
be.conf
to prevent StarRocks from aggressively loading PK indexes into memory on startup. Rocky's Summary: Your memory growth is tied to your Primary Key tables. Use the detailed 
mem_tracker
to find the table, and ensure Persistent Index is enabled for any large datasets. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • administration/management/monitoring/metrics.md
m
@Rocky How do I count number of buckets in my primary key table? Per BE node?
r
To count the number of buckets and see their distribution across your BE (Backend) nodes, you can use the following methods. 1. The Quickest Way (Visual Distribution) The most direct command to see how many tablet replicas (which are the physical manifestation of your buckets) are on each BE node is:
Copy code
sql
ADMIN SHOW REPLICA DISTRIBUTION FROM your_table_name;
Example Output: BackendId | ReplicaNum | Graph | Percent 10001 | 120 | > | 33.3% 10002 | 120 | > | 33.3% 10003 | 120 | > | 33.3% * ReplicaNum: This is the count of tablets for this specific table currently residing on that BE node. * Note: If your replication factor is 3, the sum of 
ReplicaNum
across all nodes will be 
Total Buckets * 3
. ────────── 2. Count Total Logical Buckets To see the logical number of buckets defined for each partition of your table, use:
Copy code
sql
SHOW PARTITIONS FROM your_table_name;
Look for the 
Buckets
 column. * If your table is not partitioned, it will show one row. * If it is partitioned, you may have different bucket counts per partition. Summing this column gives you the total "logical" buckets. ────────── 3. Advanced SQL Method (For Scripting/Monitoring) If you want to use a standard SQL query (e.g., for a Grafana dashboard or a report), you can query the 
information_schema
. First, you need to find your table's internal ID:
Copy code
sql
SELECT TABLE_ID 
FROM information_schema.be_tablets 
-- We filter by a known partition or use a join if names are needed
LIMIT 1;
_Tip: You can also find the TableId via `SHOW PROC '/dbs/<db_name>/<table_name>';`_ Once you have the 
TABLE_ID
, run:
Copy code
sql
SELECT 
    BE_ID, 
    COUNT(TABLET_ID) AS bucket_count
FROM information_schema.be_tablets 
WHERE TABLE_ID = <your_table_id>
GROUP BY BE_ID;
Why does this matter for Primary Key tables? As you noted in your previous question about memory usage, the 
update
pool memory is tied to the number of tablets (buckets) on a BE. * If you see one BE has a significantly higher 
ReplicaNum
than others, you have tablet skew. * If all BEs have a high count but memory is still too high, you might need to increase your bucket size (aim for 1GB - 10GB per bucket) to reduce the total number of tablets, which reduces the metadata overhead in the 
update
pool. Rocky's Tip: If you have 500+ fields in a Primary Key table, each bucket's index will be larger. If the number of buckets per BE is very high (e.g., > 10,000), it can significantly bloat the memory in your 
update
tracker. References • sql-reference/sql-statements/cluster-management/tablet_replica/ADMIN_SHOW_REPLICA_DISTRIBUTION.md • administration/management/resource_management/Replica.md
m
@Rocky
Copy code
SELECT
    BE_ID,
    COUNT(TABLET_ID) AS bucket_count
FROM information_schema.be_tablets
WHERE TABLE_ID = 183530
GROUP BY BE_ID;
This yielded ~700-800 buckets per BE node. Is this acceptable or could be optimized? I was thinking of using colocation group, hence I need to specify the number of buckets on the distribution key. I was going for 128 buckets but seeing what StarRocks itself has implemented for me has me concerned.
r
Having 700-800 tablets per BE node for a single table is likely the reason your 
update
memory pool has reached 41GB. This is considered high, especially for Primary Key tables where every tablet carries its own index metadata. Here is the breakdown of why this is happening and how to optimize it for your colocation group plan. 1. Why is the count so high? The 
information_schema.be_tablets
view counts physical tablets. For a Primary Key table, the total number of tablets per BE is calculated as: $$\text{Tablets per BE} = \frac{\text{Partitions} \times \text{Buckets per Partition} \times \text{Replicas}}{\text{Number of BEs}}$$ If you have 700-800 tablets per node, and you have (for example) 3 replicas and 3 BEs, you have roughly 700-800 logical buckets across all partitions. If you have 30 partitions (e.g., one month of daily data), that means each partition has about 25 buckets. 2. Is this "Acceptable"? It is "functional" but unoptimized. * Memory Pressure: Each tablet in a PK table maintains a Primary Key Index. Even if the tablet is small, there is a fixed memory overhead for the index structures and delete vectors. Having thousands of tablets cluster-wide (800 * BE count) significantly bloats the 
update
tracker. * Compaction Overhead: StarRocks must manage background compaction for every tablet. 800 tablets per node for one table (plus your other tables) can lead to high CPU/IO from compaction tasks. 3. Your Plan: 128 Buckets + Colocation Moving to a Colocation Group with 128 buckets is a good strategic move, but you must distinguish between "Total Buckets" and "Buckets per Partition": * If your table is NOT partitioned: 128 buckets total is much better. With 3 BEs, that's ~42 tablets per node, a ~15x reduction from your current state. * If your table IS partitioned: 128 buckets per partition might still be too many if you have hundreds of partitions. _ _Example:* 100 partitions $\times$ 128 buckets = 12,800 tablets. This would likely crash your BEs due to memory exhaustion in the 
update
pool. Recommendations for Optimization 1. Aim for larger Tablets: For Primary Key tables, aim for tablet sizes between 1GB and 10GB. If your current tablets are only a few hundred MBs, you have too many buckets. 2. Reduce Bucket Count: If you have many partitions, reduce the 
BUCKETS
value in your 
DISTRIBUTED BY
clause. For many use cases, 16-32 buckets per partition is sufficient to utilize cluster parallelism without overwhelming memory. 3. Colocation Group Tip: When using a Colocation Group, the number of buckets must be identical for all tables in the group. Choose a number that balances your largest table's needs with the memory constraints of having multiple tables in that group. 4. Persistent Index: Ensure 
enable_persistent_index
is 
true
. This is the single most important setting to keep that 41GB 
update
memory from growing further, as it offloads the PK index to disk. Rocky's Verdict: 700-800 tablets per BE for a single table is a strong signal to consolidate. Switching to 128 buckets (total, or a smaller number per partition) will significantly reduce your 
update_memory_usage
and improve the stability of your BEs your BE nodes. To check your average tablet size:
Copy code
sql
SELECT 
    AVG(DATA_SIZE)/1024/1024 AS avg_tablet_size_mb 
FROM information_schema.be_tablets 
WHERE TABLE_ID = 183530;
If this value is < 100MB, you definitely have too many buckets. References • using_starrocks/Colocate_join.md • best_practices/bucketing.md • table_design/data_distribution/Data_distribution.md
m
@Rocky My table has 24 partitions, delimited by hour, hence resulting in 24 hour TTL. So with 24 hours of historical data, does than mean on average 58-67 buckets on the distribution key?
r
Your math is correct. If you are seeing ~700–800 tablets per BE, have 24 partitions, and are running a cluster with roughly 6 BE nodes (with 3 replicas), that equates to approximately 60–67 buckets per partition. 1. Is 128 buckets a good idea? No, moving to 128 buckets will likely make your memory problem worse. In StarRocks, the number of buckets you specify in the 
DISTRIBUTED BY
clause is per partition. * Current State: ~67 buckets $\times$ 24 partitions = 1,608 logical buckets. * Proposed State: 128 buckets $\times$ 24 partitions = 3,072 logical buckets. By switching to 128 buckets, you are nearly doubling the number of tablets in your cluster. Since your 
update
memory pool is already at 41GB (partially due to the metadata overhead of managing these tablets), doubling the tablet count will cause that memory usage to climb even higher, potentially leading to BE OOM (Out of Memory) crashes. 2. The "Tablet Tax" in Primary Key Tables In Primary Key tables, every tablet is expensive. Each one maintains: * An Index (mapping PKs to locations). * Delete Vectors (tracking which rows are updated). * Compaction Tasks (background threads processing data versions). If your 24 partitions are small (e.g., each hour only has a few GBs of data), having 67 buckets per partition means your tablets are tiny. Tiny tablets are inefficient and "leak" memory via metadata. 3. Recommendations for your Colocation Group If you are setting up a Colocation Group, you must pick a single bucket count that all tables in that group will share. Step A: Check your actual Tablet Size Run this to see if your tablets are too small:
Copy code
sql
SELECT 
    AVG(DATA_SIZE)/1024/1024 AS avg_tablet_size_mb 
FROM information_schema.be_tablets 
WHERE TABLE_ID = <your_table_id>;
* If < 100MB: You have way too many buckets. Reduce the count. * If 1GB - 10GB: This is the "sweet spot" for StarRocks performance. * If > 50GB: You have too few buckets; your queries might be slow due to lack of parallelism. Step B: Pick the Bucket Count Instead of 128, consider a lower number that divides well by your BE count. If you have 6 BEs: * 30 or 60 buckets are usually better than 128. * If your table grows significantly, StarRocks can handle large tablets (up to 10GB+) much better than it can handle "tablet bloat" (thousands of tiny tablets). Step C: The "Real" Fix for 41GB Memory The number of buckets is part of the problem, but the Persistent Index is the solution. If you haven't already, check if 
enable_persistent_index
is `true`:
Copy code
sql
SHOW CREATE TABLE your_table_name;
If it is 
false
, your 41GB of 
update
memory is almost entirely the Primary Key index sitting in RAM. Turning this on will move that 41GB to your SSDs, leaving only a tiny fraction (a few GBs) in RAM. Summary Checklist 1. Don't go to 128 buckets unless each of your hourly partitions is larger than 128GB. 2. Aim for ~32 or ~64 buckets per partition for a 24-partition table. 3. Ensure Persistent Index is ENABLED to reclaim that 41GB of memory. 4. Colocation Group: Ensure all tables joined in the group use the same 
DISTRIBUTED BY HASH(...)
columns (in the same order) and the same bucket count. References • best_practices/bucketing.md • using_starrocks/Colocate_join.md • table_design/table_types/primary_key_table.md References • best_practices/primarykey_table.md • administration/management/monitoring/metrics.md • table_design/table_types/primary_key_table.md
m
@Rocky I already fixed my memory usage issue. l0_max_mem_usage was set to 1GB. I lowered it back to 100 MB. My average tablet size is 644.9144007815988 MB. I have 8 BE nodes. How should I optimize?
r
Great job on fixing the 
l0_max_mem_usage
setting—that 1GB limit was definitely letting the memory-intensive PK index grow too large before flushing. With 8 BE nodes, an average tablet size of ~645 MB, and 24 partitions, here is how you should optimize your bucket count for your Colocation Group: 1. The Math: Current vs. Proposed Currently, you have about 67–80 buckets per partition (based on your count of 700–800 tablets per BE). * Total Tablets (Physical): $24 \text{ partitions} \times \sim 77 \text{ buckets} \times 3 \text{ replicas} = 5,544$. * Tablets per BE: $5,544 / 8 = 693$. If you move to 128 buckets per partition: * Total Tablets (Physical): $24 \times 128 \times 3 = 9,216$. * Tablets per BE: $1,152$. * Avg Tablet Size: $\sim 340 \text{ MB}$. 2. Is 128 Buckets the right choice? I recommend 64 buckets instead of 128. Here is why 64 buckets is the "sweet spot" for your 8-node cluster: 1. Multiple of BE Count: 64 is perfectly divisible by 8. This ensures every BE node handles exactly 8 tablets for every partition, preventing "hot nodes" (skew). 2. Healthy Tablet Size: At 64 buckets, your average tablet size will rise slightly to ~780 MB. StarRocks recommends 100MB to 10GB for tablets, with 1GB being the ideal target for performance/metadata balance. 128 buckets would drop your tablet size to ~340MB, which is starting to get "small" for a high-column-count PK table. 3. Reduced Metadata Overhead: By moving from 128 buckets down to 64, you cut the number of tablets in half cluster-wide. This significantly reduces the memory pressure on your 
update
tracker and the number of background compaction tasks. 4. Sufficient Parallelism: With 24 partitions, a 64-bucket distribution gives you 1,536 logical buckets ($24 \times 64$). On an 8-node cluster, that is 192 logical buckets per node. Unless your BE nodes have more than 192 CPU cores each, 64 buckets provides more than enough parallelism to saturate your hardware. 3. Colocation Group Strategy Since you are setting up a Colocation Group: * Bucket Count: Set 
BUCKETS 64
for all tables in the group. * Distribution Key: Ensure the order and types of the columns in 
DISTRIBUTED BY HASH(...)
are identical for all tables. * Replication Factor: Ensure the 
replication_num
is the same for all tables in the group. Optimization Summary Metric | Current (~77 buckets) | 64 Buckets (Recommended) | 128 Buckets (Too many) Tablets per BE | ~700 | 576 | 1,152 Tablet Size | 645 MB | ~780 MB | ~340 MB Parallelism | High | Very High | Overkill Memory Risk | Medium | Low | High Rocky's Final Recommendation: Use 64 buckets. It aligns perfectly with your 8 BE nodes, keeps your tablets at a healthy size (~780MB), and reduces the "tablet tax" on your BE memory. To verify the distribution after your change:
Copy code
sql
ADMIN SHOW REPLICA DISTRIBUTION FROM your_new_table;
Every node should now show an identical 
ReplicaNum
. icaNum`. References • faq/Deploy_faq.md • deployment/plan_cluster.md • faq/Others.md • knowledge/usage/best-practice.md
m
@Rocky How does adding more columns hurt upsert performance?
r
Adding more columns to a Primary Key table impacts upsert performance primarily because of how StarRocks handles updates internally using its Delete+Insert strategy and Partial Update mechanisms. Here is the breakdown of how "wide" tables (many columns) hurt performance: 1. Increased Write I/O (The "Row Mode" Tax) In StarRocks, the default behavior for a partial update is Row Mode. When you upsert data into a table with 100 columns but only provide values for 5 of them: * StarRocks must read the existing 95 columns from disk. * It combines them with your 5 new values to form a complete new row. * It writes the entire 100-column row to a new segment file. * The Impact: As you add more columns, the amount of data StarRocks has to "rewrite" for every single upsert increases linearly. A table with 200 columns will take roughly twice the I/O of a 100-column table for the same upsert operation. 2. Compaction Overhead StarRocks background processes (compaction) constantly merge small data files into larger ones to keep queries fast. * Compaction is essentially a "read-and-rewrite" operation. * If your table is very wide, each compaction task must process much more data. If the compaction can't keep up with your upsert rate because the rows are too wide, you will see "too many versions" errors or write throttling. 3. CPU and Memory for Row Reconstruction During an upsert (specifically a partial update in Row Mode), the BE node performs a "lookup and merge": 1. It looks up the Primary Key in the index to find the old row's location. 2. It reads the old version of the row into memory. 3. It replaces the updated columns. * The Impact: More columns mean more memory is required during this "reconstruction" phase, and more CPU cycles are spent decompressing and re-compressing the wider data blocks. 4. Column Mode vs. Row Mode Thresholds StarRocks has a Column Mode for partial updates (optimized for updating a few columns in many rows). * Column mode avoids rewriting the entire row by creating separate "delta" files for just the changed columns. * However, if you add too many columns to your table, the ratio of "updated columns" to "total columns" might drop below the threshold where StarRocks can efficiently use Column Mode, or it might make the metadata management of those columns more complex. Summary: Why it slows down Factor | Effect of Adding Columns Write Amplification | Higher. You write the full row (including unchanged columns) for every update. Read Amplification | Higher. StarRocks must read the old version of all columns to reconstruct the row. Compaction | Heavier. Moving wide rows between files consumes more Disk I/O and CPU. Memory usage | Increased. Loading wide rows during the "Commit" phase of a load uses more buffer memory. How to mitigate this: 1. Enable Persistent Index: Ensure your index is on SSD so the "lookup" part of the upsert is fast, even as the table grows. 2. Use Column Mode for Batch Updates: If you are updating a few columns across millions of rows, set 
partial_update_mode = 'column'
to avoid the "Row Mode" rewrite tax. 3. Flat Table Design: Avoid adding "junk" columns or metadata that isn't frequently queried. The leaner the row, the faster the upsert. 4. Use the correct `l0_max_mem_usage`: As you've already discovered, keeping your memory limits tuned prevents wide rows from bloating your memory pool before they are flushed. References • loading/Load_to_Primary_Key_tables.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/UPDATE.md
m
@Rocky My CPU, DIsk I/O, transaction publish latency all suddenly spiked at 11am. No spike in traffic/upserts detected. What could've possibly caused it? How to diagnose? It's 12.45 pm now
r
A sudden spike in CPU, Disk I/O, and Transaction Publish Latency without an increase in traffic is almost always caused by background maintenance tasks. Given your specific symptoms, there are three primary suspects. 1. The Most Likely Suspect: Base Compaction StarRocks has two types of compaction: Cumulative (small files) and Base (large-scale merging). * Why it matches: Base compaction is highly resource-intensive. It reads nearly all data in a partition and rewrites it. This causes massive Disk I/O and CPU spikes. * The Publish Latency Link: Compaction tasks are themselves transactions. If a massive Base Compaction is trying to "Commit" and "Publish" its new version, it can clog the FE's 
PublishVersionDaemon
, causing your regular upsert transactions to wait in the queue. How to diagnose: Run this to see if any large compaction tasks were active or finished around 11:00 AM:
Copy code
sql
-- Check overall compaction history/status
SHOW PROC '/compactions';

-- Check for partitions with high "MaxCS" (Compaction Score)
-- A high score (>100) indicates a partition is long overdue for compaction
SELECT * FROM information_schema.partitions_meta 
ORDER BY MAX_CS DESC LIMIT 10;
2. Tablet Metadata & Index Cleaning Since you are using Primary Key tables and recently adjusted 
l0_max_mem_usage
, the system might have triggered a background "flushing" or "metadata vacuuming" event. * If you had a build-up of old row versions, the system might have reached a threshold where it decided to purge "Delete Vectors" or older metadata segments. How to diagnose: Check your BE nodes' 
<http://be.INFO|be.INFO>
logs specifically for the 11:00 AM window. Look for: * 
Tablet meta checkpoint
* 
Delete vector
* 
Compaction
or 
Merge
tasks. 3. Clone/Repair Tasks If one of your 8 BE nodes was briefly "jittery" or lost connection to the Leader FE, StarRocks may have initiated Tablet Repair/Balance. This involves copying large amounts of data between nodes to maintain the replication factor (3). How to diagnose:
Copy code
sql
-- Check if there are active or failed tablet scheduling tasks
SHOW PROC '/cluster_balance/history';
SHOW PROC '/statistic'; -- Look for 'UnhealthyTablets'
────────── Step-by-Step Diagnostic Checklist Step 1: Confirm the "Publish" Bottleneck Transaction publish latency spikes when the FE can't get an "OK" back from the BEs fast enough. Check these FE metrics (via Prometheus/Grafana or FE 
/metrics
endpoint): * `starrocks_fe_txn_publish_latency_ms`: Is it the Execute phase or the Queue phase that spiked? * `publish_version_queue_count`: If this is high, your FE thread pool is saturated. Step 2: Check BE Task Concurrency If your BEs are overwhelmed by background tasks, you can see how many are running:
Copy code
sql
-- Run on your BE nodes via CLI or check be_configs
SELECT * FROM information_schema.be_configs 
WHERE name IN ('max_compaction_threads', 'max_cumulative_compaction_threads');
Step 3: Investigate the "Update" Memory Even though you lowered 
l0_max_mem_usage
, a massive background compaction will still pull data into the 
update
and 
compaction
memory pools. * Check your monitoring for a spike in the Compaction Memory Pool at 11:00 AM. Final Recommendation: If you find that Base Compaction caused the spike, you can "smooth out" these spikes by lowering the concurrency of compaction so it takes longer but uses less CPU/IO:
Copy code
sql
-- Example: Reduce max compaction threads on BEs (mutable)
-- Default is often linked to CPU cores; try lowering it if IO is the bottleneck.
ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "4"); -- For shared-data
-- Or via BE config:
update_be_config("max_compaction_threads", "2");
Rocky's Tip: Check if your "hourly" partitions are being consolidated into "daily" or "full" versions at that time. If you have 24 partitions, and the 25th hour triggers a TTL drop of the oldest partition, the metadata cleanup for that drop can also cause a momentary blip in-but-sharp resource spike. References • knowledge/admin_and_operation/shared_data_compaction.md
m
Copy code
SHOW PROC '/cluster_balance/history_tablets';
TabletId,Type,Medium,Status,State,OrigPrio,DynmPrio,SrcBe,SrcPath,DestBe,DestPath,Timeout,Create,LstSched,LstVisit,Finished,Rate,FailedSched,FailedRunning,LstAdjPrio,VisibleVer,VisibleVerHash,CmtVer,CmtVerHash,ErrMsg
3626135,BALANCE,HDD,INTER_NODE_TABLET_DISTRIBUTION,CANCELLED,LOW,LOW,110783,115820814208651375,203701,-2396125465657593808,180000,2026-04-29 14:22:47,2026-04-29 14:22:58,2026-04-29 14:22:58,2026-04-29 14:22:58,,11,0,,2,0,2,0,consecutive full clone needs to delay
3626238,BALANCE,HDD,INTER_NODE_TABLET_DISTRIBUTION,CANCELLED,LOW,LOW,95876,8896334408091080403,203701,-7904894648172036624,180000,2026-04-29 14:22:47,2026-04-29 14:22:58,2026-04-29 14:22:58,2026-04-29 14:22:58,,11,0,,2,0,2,0,consecutive full clone needs to delay
3626015,BALANCE,HDD,INTER_NODE_TABLET_DISTRIBUTION,CANCELLED,LOW,LOW,203699,-2722856283349366906,171908,3155724088209648910,180000,2026-04-29 14:22:47,2026-04-29 14:22:58,2026-04-29 14:22:58,2026-04-29 14:22:58,,11,0,,2,0,2,0,consecutive full clone needs to delay
3626013,BALANCE,HDD,INTER_NODE_TABLET_DISTRIBUTION,CANCELLED,LOW,LOW,203701,-7904894648172036624,171908,6701421038239065139,180000,2026-04-29 14:22:47,2026-04-29 14:22:58,2026-04-29 14:22:58,2026-04-29 14:22:58,,11,0,,2,0,2,0,consecutive full clone needs to delay
3626103,BALANCE,HDD,INTER_NODE_TABLET_DISTRIBUTION,CANCELLED,LOW,LOW,11001,-2747615129444470249,110783,-2070209762952543285,180000,2026-04-29 14:22:47,2026-04-29 14:22:58,2026-04-29 14:22:58,2026-04-29 14:22:58,,11,0,,2,0,2,0,consecutive full clone needs to delay
3626039,BALANCE,HDD,INTER_NODE_TABLET_DISTRIBUTION,CANCELLED,LOW,LOW,11001,-565333295495016893,110783,115820814208651375,180000,2026-04-29 14:22:47,2026-04-29 14:22:58,2026-04-29 14:22:58,2026-04-29 14:22:58,,11,0,,2,0,2,0,consecutive full clone needs to delay
3626107,BALANCE,HDD,INTER_NODE_TABLET_DISTRIBUTION,CANCELLED,LOW,LOW,203701,-2396125465657593808,95876,-7732845823978407637,180000,2026-04-29 14:22:47,2026-04-29 14:22:58,2026-04-29 14:22:58,2026-04-29 14:22:58,,11,0,,2,0,2,0,consecutive full clone needs to delay
3626035,BALANCE,HDD,INTER_NODE_TABLET_DISTRIBUTION,CANCELLED,LOW,LOW,203700,8334634478427944144,171908,5451311888450748204,180000,2026-04-29 14:22:47,2026-04-29 14:22:58,2026-04-29 14:22:58,2026-04-29 14:22:58,,11,0,,2,0,2,0,consecutive full clone needs to delay
3626047,BALANCE,HDD,INTER_NODE_TABLET_DISTRIBUTION,CANCELLED,LOW,LOW,203699,-3651728917538207946,203697,450975306831170146,180000,2026-04-29 14:22:47,2026-04-29 14:22:58,2026-04-29 14:22:58,2026-04-29 14:22:58,,11,0,,2,0,2,0,consecutive full clone needs to delay
3626037,REPAIR,HDD,REDUNDANT,FINISHED,NORMAL,NORMAL,-1,-1,-1,-1,0,2026-04-29 14:22:47,2026-04-29 14:23:01,2026-04-29 14:23:01,2026-04-29 14:23:01,,14,0,,2,0,2,0,redundant replica is deleted
3626334,REPAIR,HDD,REDUNDANT,FINISHED,NORMAL,NORMAL,-1,-1,-1,-1,0,2026-04-29 14:22:47,2026-04-29 14:23:01,2026-04-29 14:23:01,2026-04-29 14:23:01,,14,0,,2,0,2,0,redundant replica is deleted
3626137,REPAIR,HDD,REDUNDANT,FINISHED,NORMAL,NORMAL,-1,-1,-1,-1,0,2026-04-29 14:22:47,2026-04-29 14:23:01,2026-04-29 14:23:01,2026-04-29 14:23:01,,14,0,,2,0,2,0,redundant replica is deleted
3625946,REPAIR,HDD,REDUNDANT,FINISHED,NORMAL,NORMAL,-1,-1,-1,-1,0,2026-04-29 14:22:47,2026-04-29 14:23:01,2026-04-29 14:23:01,2026-04-29 14:23:01,,14,0,,2,0,2,0,redundant replica is deleted
@Rocky
r
The logs you provided from 
SHOW PROC '/cluster_balance/history_tablets';
are the "smoking gun." They confirm that your cluster underwent a significant Replica Repair and Rebalancing event. Here is exactly what happened and why it caused your symptoms: 1. The Diagnosis: "Replica Storm" The logs show two critical activities that explain your resource spikes: * REPAIR (REDUNDANT): You have many entries where 
redundant replica is deleted
. This means StarRocks found more than 3 replicas for your tablets (likely 4 or 5) and was actively deleting the extras. * BALANCE (CANCELLED): Many balance tasks were cancelled with the error 
consecutive full clone needs to delay
. The Chain Reaction: 1. The Trigger (around 11:00 AM): Something caused the FE to think one or more BE nodes were offline or their data was "stale" (lagged). This could be a network blip, a BE process restart, or a disk being momentarily unresponsive. 2. Massive Repair: The FE immediately started Full Clones to restore the replication factor. Since your tablets are ~645 MB each, moving hundreds of these simultaneously created a massive Disk I/O and CPU spike. 3. The Publish Latency Spike: Transaction publishing requires the FE to talk to all (or a quorum of) BEs to confirm a version change. Because the BEs were saturated with I/O from cloning and the FE was busy managing thousands of repair tasks, the "Publish" requests timed out or sat in a queue, causing your upsert latency to skyrocket. 4. Throttling: The message 
consecutive full clone needs to delay
is a safety mechanism. StarRocks realized it was trying to move too much data too fast and paused the rebalancing to prevent the cluster from crashing entirely. 2. Why did it happen "Suddenly"? If you recently changed 
l0_max_mem_usage
or were performing maintenance, you might have inadvertently triggered a node "heartbeat" failure. Alternatively, if your disks reached a High Watermark (e.g., 75-80% full), StarRocks will suddenly trigger a "Balance" operation to move data to emptier nodes, which looks like a sudden resource spike. 3. How to prevent/diagnose further: Check for Node Flapping Look at your FE leader log (
fe.log
) around 11:00 AM for these strings: * 
Node [backend_id] is down
* 
transfer to offline
If a node went offline and then came back online 10 minutes later, it would explain why you first had "Repair" tasks (to replace it) and then "Redundant" tasks (to delete the extras when it returned). Check Disk Watermarks Run this to see if any BE is close to its limit:
Copy code
sql
SHOW PROC '/backends';
Look at the 
DiskUsedPct
column. If some nodes are at 80% and others at 40%, the FE will aggressively move data, causing the spikes you saw. Tune the "Repair" Speed To prevent future "Rebalance Storms" from killing your production performance, you can throttle the background tasks:
Copy code
sql
-- Reduce the number of concurrent clone tasks allowed per BE disk (Default is usually 2 or 8)
ADMIN SET FRONTEND CONFIG ("tablet_sched_slot_num_per_path" = "1");

-- Lower the priority of balance tasks so they don't interfere with Load/Publish
ADMIN SET FRONTEND CONFIG ("tablet_sched_balance_strategy" = "low_priority");
Summary of what to do now: 1. Check FE Logs: Confirm if a BE node was marked "Dead" at 11:00 AM. 2. Verify Disk Balance: If your 
DiskUsedPct
is uneven across the 8 BEs, the cluster will continue to try and balance itself until it's finished. 3. Ignore the "Cancelled" tasks: Those are actually a good thing; it means the system throttled itself to save your cluster from a total I/O lockdown. Since your average tablet size is ~650MB and you have 3 replicas, moving just 100 tablets means moving ~195 GB of data across your network—this is why your I/O and CPU spiked so hard. References • administration/management/resource_management/Replica.md
• administration/management/FE_configuration.md • administration/management/BE_configuration.md • administration/management/monitoring/metrics.md • knowledge/trouble_shooting/fe_deadlock_problems.md • administration/management/resource_management/Replica.md • administration/management/FE_configuration.md • administration/management/BE_configuration.md • administration/management/monitoring/metrics.md • knowledge/trouble_shooting/fe_deadlock_problems.md • administration/management/resource_management/Replica.md • administration/management/FE_configuration.md • administration/management/BE_configuration.md • administration/management/monitoring/metrics.md • knowledge/trouble_shooting/fe_deadlock_problems.md References • administration/management/resource_management/Replica.md • knowledge/trouble_shooting/fe_deadlock_problems.md
m
@Rocky fe.audit.log fe.big_query.log fe.dump.log fe.features.log fe.internal.log fe.log fe.out fe.profile.log fe.warn.log proc_profile Which log file to check
r
For your specific situation (CPU/IO spike and replica repair/rebalancing), you should focus on 
fe.log
 and 
fe.warn.log
. 1. 
fe.log
(Primary) This is the main runtime log. It records cluster state changes (nodes going up/down) and the decisions made by the Tablet Scheduler. * What to look for: Search for the timestamp 
11:00
to 
11:15
to see what triggered the event. * Keywords to grep: * 
"is down"
or `"transfer to offline"`: To see if a BE node was disconnected. * `"tablet scheduler"`: To see when the FE started mass-repairing tablets. * `"Publish version timeout"`: To confirm why your upserts were slow. 2. 
fe.warn.log
(Quick Check) Since this is a subset of 
fe.log
that only contains warnings and errors, it is much faster to read. * What to look for: Any 
WARNING
or 
ERROR
messages during the 11:00 AM window. If a disk was failing or a network link was saturated, it will show up here clearly. 3. 
fe.internal.log
(Secondary) Check this only if you suspect background Statistics Collection caused the load. StarRocks periodically runs internal SQL to update table stats. * Keywords: 
analyze
. ────────── Recommended Diagnostic Commands: Run these on your FE leader node to quickly pinpoint the cause: Check if any node was marked "Down" at 11am:
Copy code
bash
grep "2026-04-29 11:" fe.log | grep -E "is down|offline|dead"
Check for tablet scheduling "storms":
Copy code
bash
grep "2026-04-29 11:" fe.log | grep "tablet scheduler" | head -n 20
Check for Publish Latency/Timeout errors:
Copy code
bash
grep "2026-04-29 11:" fe.warn.log | grep "Publish"
Summary of other files (for context): * `fe.audit.log`: Skip. This just shows who ran what SQL query. * `fe.out`: Skip. Usually only useful if the FE process itself crashed or has JVM memory issues. * `fe.profile.log`: Skip. Contains query execution plans/profiles; not useful for system-level I/O spikes. Rocky's Advice: If you see 
"is down"
in 
fe.log
at 11:00 AM, check the 
<http://be.INFO|be.INFO>
 logs on that specific backend node. It will tell you if the BE process restarted (look for a fresh startup header) or if it was simply too busy to respond to the FE heartbeats. References • administration/management/logs.md • administration/management/FE_configuration.md
Open in Slack
PreviousNext
Powered by