http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> Compaction is not running on my Sta...
# questions-and-troubleshooting
m
@Rocky Compaction is not running on my StarRocks shared-nothing cluster. It is causing issues mainly with upserts (p95 transaction publish latency ~35s). My application Stream Loading data to the FE node always routinely gets this error: 
Capaticy limit exceeded: No available stream load pipe
. 
starrocks_be_tablet_cumulative_max_compaction_score
has been 0 ever since cluster been brought up. For reference. here are my cluster stats: • 1 leader FE node (currently disabled the other 2 follower/observer) • 4 BE nodes (r6in.4xlarge machines) • 4 EBS per BE node. Each EBS has 750GiB, 500 MiB/s throughput and 5000 iops (unlikely issue) How can I troubleshoot this?
r
Hi, I'm Rocky. It sounds like your cluster is experiencing a classic case of Primary Key (PK) table ingestion bottleneck, where slow transaction publishing is leading to a resource exhaustion in the FE's Stream Load manager. Here is a breakdown of why you are seeing these specific symptoms and how to troubleshoot them: 1. Why your compaction score is 0 You are likely using Primary Key tables for your upserts. In StarRocks, there are different metrics for different table models: * `starrocks_be_tablet_cumulative_max_compaction_score`: This metric is for Duplicate, Aggregate, and Unique key tables. It will naturally be 0 for Primary Key tables. * The correct metric to check: 
starrocks_be_tablet_update_max_compaction_score
. This tracks the compaction status of Primary Key tables. If this value is high (e.g., > 100), it indicates that compaction is falling behind. 2. "Capacity limit exceeded: No available stream load pipe" This error occurs in the FE when the internal pool of "pipes" used to track Stream Load tasks is exhausted. * The Cause: StarRocks FE has a default limit on concurrent stream load tasks (often 1000). Since your transaction publish latency is very high (~35s), each Stream Load connection stays "active" in the FE's memory for a long time. If you are sending frequent small loads, you will quickly hit this concurrency limit because old tasks aren't finishing fast enough to free up "pipes" for new ones. * Relevant FE Config: 
max_running_txn_num_per_db
(default 1000) and 
stream_load_task_keep_max_num
(default 1000). 3. High Transaction Publish Latency (35s) For PK tables, the "Publish" phase involves applying rowsets to the Primary Key index and generating delete vectors. A 35s latency is extremely high and usually means: * Too many small versions: You are loading data too frequently in small batches. * Compaction is not keeping up: The BE cannot merge these small versions fast enough. * Worker saturation: The threads responsible for applying updates are busy. ────────── Troubleshooting & Tuning Steps Step 1: Verify Compaction Status Run the following SQL commands to see if compaction tasks are actually running or stuck:
Copy code
sql
-- Check if any compaction tasks are currently running or failing
SHOW PROC '/compactions';

-- Check the version count and compaction score for your partitions
-- Look for "MaxCS" (Compaction Score) and "VisibleVersion"
SHOW PARTITIONS FROM <your_table_name>;
Step 2: Increase Compaction Concurrency (BE side) Since you have powerful 
r6in.4xlarge
nodes (16 vCPUs) and multiple EBS volumes, you should increase the resources dedicated to PK compaction in your `be.conf`:
Copy code
properties
# Increase threads for PK compaction (default is 1)
# Since you have 4 disks, setting this to 2 or 4 can help significantly.
update_compaction_num_threads_per_disk = 2

# Decrease the interval between compaction triggers (default 120s)
update_compaction_per_tablet_min_interval_seconds = 60
_Note: You can apply these dynamically via 
curl -XPOST <http://be_host:be_http_port/api/update_config?update_compaction_num_threads_per_disk=2
._> Step 3: Optimize Ingestion Strategy * Batching: This is the most critical fix. Instead of many small Stream Loads, aggregate your data and send larger batches (e.g., every 10-30 seconds or every 100MB+). This reduces the number of "versions" created and drastically lowers the overhead on the PK index. * Persistent Index: Since you are on EBS (even with 5000 IOPS), ensure 
enable_persistent_index
is set to 
true
in your table properties (it is the default in recent versions). This prevents high memory usage during upserts. Step 4: Increase FE Limits (Optional) If you must maintain high concurrency, you can increase the FE's capacity to handle more concurrent transactions: ```sql -- On Leader FE ADMIN SET FRONTEND CONFIG ("max_running_txn_num_per_db" = "2000");
ADMIN SET FRONTEND CONFIG ("stream_load_task_keep_max_num" = "2000"); ``` However, increasing these without fixing the underlying publish latency will likely just delay the error or lead to higher memory usage on the FE. Summary Recommendation: Check the 
starrocks_be_tablet_update_max_compaction_score
metric. If it's high, increase 
update_compaction_num_threads_per_disk
and, most importantly, increase your loading batch size to reduce the transaction pressure. References • administration/management/FE_configuration.md
m
@Rocky It's averaging 2k compaction score per node. I enabled merge commit with interval 2000ms. My payload is around 50 MB in size. Should I increase allowed payload per stream load batch to around 500 MB? I thought with merge commit it would work fine.
r
A compaction score of 2,000 is the critical threshold for StarRocks Primary Key tables. When this limit is reached, StarRocks stops accepting new writes to the affected tablets to prevent data inconsistency and catastrophic query performance. Here is the breakdown of your situation and how to fix it: 1. Why you see these specific symptoms * The Wrong Metric: 
starrocks_be_tablet_cumulative_max_compaction_score
is only for Duplicate/Aggregate/Unique tables. For Primary Key (PK) tables, you must monitor 
starrocks_be_tablet_update_max_compaction_score
. * Capacity Limit / No Pipe: This error occurs because your transaction publish latency is 35 seconds. Every Stream Load (even with merge/group commit) occupies a "pipe" (an internal FE tracking object). Because transactions are taking 35s to finish, these pipes are not being released fast enough, and the FE's internal pool (
stream_load_task_keep_max_num
, default 1000) is filling up. * Merge Commit (Group Commit) at 50MB: Group commit is designed to help with tiny frequent loads (e.g., 1KB to 1MB). At 50MB, you are already sending a substantial amount of data. If you send 50MB every 2 seconds, you are creating 30 versions per minute. For PK tables, each version requires a CPU-intensive "apply" phase to update the index and delete vectors. 2. Should you increase payload to 500 MB? Yes, absolutely. This is the single most effective change you can make. * By increasing from 50MB to 500MB, you reduce the number of versions created by 10x. * This drastically reduces the "Compaction Score" because there are fewer rowsets to merge. * It lowers the overhead on the Primary Key index apply phase, which will bring that 35s latency down to 1–2 seconds. 3. Tuning Steps to Clear the Backlog Since your nodes have 16 vCPUs and 4 EBS volumes, you are currently under-utilizing your hardware for compaction. Apply these settings to help the cluster "catch up" from the 2,000 score: A. Increase Compaction Concurrency (BE Config) Add or update these in 
be.conf
(can be applied dynamically via 
curl
):
Copy code
properties
# Increase threads for PK compaction (default is 1). 
# With 4 disks and 16 cores, set this to 2 or 4.
update_compaction_num_threads_per_disk = 2

# Increase the threads that "apply" the rowsets (the cause of your 35s latency)
# 0 means it equals the number of CPU cores. Ensure it's not restricted.
transaction_apply_worker_count = 0
B. Increase FE Transaction Limits To stop the "Capacity limit exceeded" error while you are clearing the backlog, increase these in `fe.conf`:
Copy code
properties
# Increase the pool of tracked stream load tasks
stream_load_task_keep_max_num = 2000
# Increase concurrent transactions per database
max_running_txn_num_per_db = 2000
C. Verify Progress Use this SQL to see if the tablets are actually compacting:
Copy code
sql
-- Check the MaxCS (Compaction Score) column. It should start dropping from 2000.
SHOW PARTITIONS FROM <your_table_name>;

-- Check the status of current compaction tasks
SHOW PROC '/compactions';
Summary Recommendation 1. Switch to 500MB batches. At this size, you might not even need "merge commit" (group commit); a standard Stream Load will be more stable. 2. Increase 
update_compaction_num_threads_per_disk
to 2. 3. Monitor 
starrocks_be_tablet_update_max_compaction_score
 instead of the cumulative score. If it stays above 100, you are still loading too frequently. References • administration/management/FE_configuration.md • faq/loading/Loading_faq.md • loading/loading_introduction/loading_considerations.md • loading/Stream_Load_transaction_interface.md
m
@Rocky Would you even recommend 1GB batches? What would be the sweet spot? Throughput is around ~10k events/s. 4000 rows is approximately 50 mb in size. Total throughput from all nodes is ~50k/s. Upserts are about 3300/s total too.
r
With a total throughput of 50k events/s and a row size that puts 50MB at only 4,000 rows, you are currently overwhelming the Primary Key index's "apply" mechanism. The Verdict on 1GB Batches: Yes, or Even Larger At your throughput (approx. 625 MB/s total), 1GB is actually the minimum batch size you should aim for. Here is the math: * Current state (50MB): You are triggering ~12.5 transactions per second across the cluster. In just 3 minutes, you create over 2,000 versions. This is exactly why your compaction score is 2,000 (the hard limit) and your publish latency is 35s. * Target state (1GB to 2GB): A 1GB batch would contain ~80,000 rows. At 50k events/s, this results in one transaction every 1.6 seconds. If you move to 3GB batches, you get one transaction every 5-6 seconds. The "Sweet Spot" for your cluster: Aim for a batch size that results in one transaction every 5 to 10 seconds per table. For your data volume, that is a batch size of approximately 3GB. StarRocks can handle up to 10GB per Stream Load by default, so 1GB–3GB is well within the safe zone. ────────── Why "Merge Commit" isn't saving you yet Merge Commit (Group Commit) with a 
2000ms
interval is likely too aggressive for this volume. 1. Interval is too short: 2 seconds is still very frequent for a PK table under high load. 2. Backpressure: Because your publish latency is 35s, the Merge Commit "coordinator" in the FE is likely holding onto thousands of "pipes" waiting for previous 2-second windows to finish publishing. This is causing your 
Capacity limit exceeded
error. ────────── Recommended Strategy to Stabilize 1. Scale up the "Apply" and "Compaction" Workers Since your 
r6in.4xlarge
nodes have 16 vCPUs and high-performance EBS, you can afford to let StarRocks work harder on the background tasks:
Copy code
properties
# be.conf
# Increase threads for applying rowsets to the PK index
# Default is usually number of cores; ensure it's not being limited.
transaction_apply_worker_count = 16

# Allow more concurrent PK compactions
update_compaction_num_threads_per_disk = 4

# Increase the buffer before flushing to disk during a load
# This helps keep the number of files per version low
write_buffer_size = 536870912  # 512MB
2. Adjust Loading Configuration If you continue using Merge Commit, increase the interval to give the system breathing room: * Merge Commit Interval: Set to 
10000
(10 seconds). * Client-side Batching: If possible, buffer at the application level to 1GB+ before calling the Stream Load API. This is more reliable than relying on the FE to merge hundreds of 50MB chunks. 3. Clear the Backlog (Critical) Your cluster is currently "stuck" at the 2,000 score limit. Even if you change your code now, the BEs are struggling to compact the thousands of versions already there. 1. Stop ingestion entirely for 5-10 minutes. 2. Monitor the score: 
SHOW PARTITIONS FROM <table_name>;
(Look at 
MaxCS
). 3. Once the score drops below 100, resume ingestion with the 1GB+ batch size. Summary * 50MB is "tiny" for a 50k events/s stream on a PK table. * Target 1GB–3GB per batch. * Monitor 
starrocks_be_tablet_update_max_compaction_score
, not the cumulative score. * Increase 
transaction_apply_worker_count
 to 16 to utilize your vCPUs for faster rowset processing. References • faq/loading/Loading_faq.md • best_practices/primarykey_table.md • loading/Flink-connector-starrocks.md • table_design/table_types/primary_key_table.md
m
@Rocky So would these configurations for initial testing be appropriate? • 1GB stream load • merge commit (with async enabled) • 10 second merge commit interval • Adjust the BE configurations as recommended If I don't enable merge commit async the worker making the stream load will be waiting for the synchronous response for awhile? I want to offload it as fast as possible so it can keep taking on new work from events producer.
r
This is a solid plan. Your proposed configuration directly addresses the root causes of both the high latency and the "No available stream load pipe" errors. Here is a breakdown of why this configuration works and a few final adjustments to ensure success. 1. 1GB Stream Load (The "Silver Bullet") By moving from 50MB to 1GB, you are reducing the number of versions created by 20x. * Why it fixes the "Pipe" error: The FE has a limit on the number of "pipes" (tracking objects for loads). At 50MB/load and 50k events/s, you were creating transactions so fast that even a 35s delay meant hundreds of tasks were piling up in the FE. With 1GB batches, you'll only be starting one transaction every ~1.6 seconds. The FE will easily handle this concurrency. * Why it fixes latency: The 35s latency is caused by the "Apply" phase (writing to the PK index). It is much more efficient for StarRocks to apply one large 1GB rowset than twenty 50MB rowsets. 2. Merge Commit with Async Enabled Using 
merge_commit_async: true
is exactly what you need to "offload" the work from your event producer. * Synchronous (Default): The Stream Load client waits for the data to be received, written to disk, and published (the apply phase). With your current 35s latency, your client would hang for 35s. * Asynchronous: The server returns a success response as soon as it has safely received the data and written it to the Write-Ahead Log (WAL) or temporary storage. The client can then immediately grab the next batch of events. * Risk: In 
async
mode, the client receives a "Success" before the data is actually visible in queries. If the transaction fails during the "Apply" phase later (e.g., due to a disk error or schema issue), the client won't know from the immediate response. 3. The 10-Second Interval A 10-second 
merge_commit_interval_ms
(10000) is very safe for your volume. * At 50k events/s (~625 MB/s), a 10s window will result in a ~6GB transaction. * StarRocks can easily handle 6GB transactions (the default limit is 10GB). * This will keep your version count extremely low, which is the key to bringing that 2,000 compaction score down. Final Checklist for Initial Testing Setting | Recommended Value | Why? Batch Size | 1GB - 2GB | Significant reduction in PK index overhead. 
merge_commit_async
 | 
true
 | Unblocks your event producer immediately. 
merge_commit_interval_ms
 | 
10000
 | Reduces transaction frequency to ~6 per minute. 
transaction_apply_worker_count
 | 
16
 | (BE) Matches your 
r6in.4xlarge
CPU count to speed up the apply phase. 
update_compaction_num_threads_per_disk
 | 
2
 or 
4
 | (BE) Effectively utilizes your 4 EBS volumes to clear the 2k score backlog. ⚠️ One Critical Step Before Testing Because your cluster is currently at a 2,000 compaction score, it is likely in a "safety-throttle" mode where it may reject even the new 1GB loads. 1. Stop all ingestion for 10 minutes. 2. Watch the score drop via 
SHOW PARTITIONS FROM your_table;
(look for 
MaxCS
). 3. Once 
MaxCS
is below 500, start your new 1GB + Merge Commit Async pipeline. If you start the new pipeline while the score is still 2,000, the background compaction might not have enough resources to "catch up" while still processing new 625 MB/s of new data. Give it a clean slate to start. References • faq/loading/Stream_load_faq.md • administration/management/monitoring/metrics.md • loading/StreamLoad.md • faq/loading/Routine_load_faq.md • loading/Stream_Load_transaction_interface.md
m
Copy code
PartitionId,PartitionName,VisibleVersion,VisibleVersionTime,VisibleVersionHash,State,PartitionKey,Range,DistributionKey,Buckets,ReplicationNum,StorageMedium,CooldownTime,LastConsistencyCheckTime,DataSize,StorageSize,IsInMemory,RowCount,DataVersion,VersionEpoch,VersionTxnType,TabletBalanced
@Rocky There's no MaxCS score in SHOW partitions from your_table query
r
Apologies for the confusion. The standard 
SHOW PARTITIONS
command does not include the 
MaxCS
column. To view the compaction scores for your table, you should use one of the following two methods: Method 1: Using the 
SHOW PROC
command (Most Common) This is the internal StarRocks process view. Replace 
<db_name>
and 
<table_name>
with your actual names:
Copy code
sql
-- This will show AvgCS, P50CS, and MaxCS columns at the far right
SHOW PROC '/DBS/<db_name>/<table_name>/partitions';
Method 2: Querying 
information_schema
(Easiest to read) If you are on version 3.1.9+ or 3.2.4+, StarRocks provides a system table that is much easier to filter:
Copy code
sql
SELECT 
    TABLE_NAME, 
    PARTITION_NAME, 
    VISIBLE_VERSION, 
    MAX_CS 
FROM information_schema.partitions_meta 
WHERE TABLE_NAME = 'your_table_name'
ORDER BY MAX_CS DESC;
Why you are looking for 
MaxCS
 * The 2,000 Threshold: In Primary Key tables, when the 
MaxCS
(which reflects the number of un-compacted rowsets/versions) hits 2,000, the table effectively enters a "read-only" state for new writes to prevent the system from falling over. * Apply Latency: If you see 
MaxCS
climbing while your 
VisibleVersionTime
is lagging, it means the BEs are struggling to "Apply" the data to the Primary Key index. Verification of your proposed testing plan Your plan to move to 1GB batches with a 10-second merge interval and async enabled is exactly the right move. 1. Stop ingestion for a few minutes. 2. Run the 
SHOW PROC
command above and wait for 
MaxCS
to drop (ideally below 100). 3. Start your new 1GB async pipeline. With 1GB batches at 50k events/s, you will only create ~3-4 versions per minute instead of the hundreds you were creating before. This will keep the 
MaxCS
very low and eliminate the 35s publish latency. References • administration/management/monitoring/metrics.md • loading/StreamLoad.md • loading/Stream_Load_transaction_interface.md • table_design/table_types/primary_key_table.md • administration/management/FE_configuration.md • faq/loading/Loading_faq.md latency. References • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky Do I need to adjust both streaming_load_max_mb and streaming_load_max_batch_size_mb? My table is a very wide column (over 300 columns). I assume streaming_load_max_mb will allow me to load more data in a single stream load. However, given that 4000 rows is around 50mb, streaming_load_max_batch_size_mb will mean the processor flushes to disk every 8000 rows? At 50k/s throughput that is 6.25 flushes/s. Are any of my assumptions wrong and are there more configurations I should tune
r
Your intuition about the "flushes" is spot on, but the specific parameters controlling them are slightly different than you assumed. For a 300-column wide table at 50k events/s, tuning the memory buffers is critical to prevent the "small file problem" that leads to high compaction scores. 1. Clarifying the "Max" Parameters * 
streaming_load_max_mb
(Default 10GB): This is the hard limit for the entire HTTP body of a single Stream Load. Since you are targeting 1GB batches, the default is already plenty. You do not need to change this. * 
streaming_load_max_batch_size_mb
(Default 100MB): This is specific to the JSON parser. If you are sending 1GB of JSON, the parser will fail at 100MB. * Recommendation: Either set this to 
1024
in 
be.conf
or (simpler) add 
"ignore_json_size:true"
to your Stream Load HTTP headers. 2. The Real Flush Control: 
write_buffer_size
 Your assumption about "flushing every 8000 rows" actually refers to the 
write_buffer_size
. * 
write_buffer_size
(Default 100MB): This BE parameter determines when StarRocks flushes an in-memory segment to disk. * The Problem: At 50k rows/s (~625 MB/s), a 100MB buffer results in 6.25 flushes per second cluster-wide. If these rows are spread across 3 BEs, each BE is still flushing every ~0.5 seconds. * The Impact: Every flush creates a new "segment file." A single 1GB batch could easily create 10+ small files per tablet. This is what drives your Compaction Score (MaxCS) to 2,000. * Recommendation: Set 
write_buffer_size = 1073741824
(1GB) or at least 
536870912
(512MB). This ensures a 1GB load creates only 1 or 2 files per tablet, drastically reducing the background compaction work. 3. Tuning for Wide Tables (300+ Columns) Wide tables have significant metadata and RPC overhead because StarRocks must track each column's segments. * 
max_tablet_write_chunk_bytes
(v3.2.12+): * Default: 512MB. * Tuning: For 300 columns, increase this to 
1073741824
(1GB). This allows the BE to buffer more data for wide columns before sending it between nodes, reducing RPC overhead during the load. * `load_process_max_memory_limit_percent`: * Since you are increasing buffers to 1GB, ensure your loading memory pool is large enough. On 
r6in.4xlarge
(128GB), the default 30% (~38GB) is fine, but if you have many concurrent loads, keep an eye on this. 4. Is the Plan Appropriate? Yes, with these adjustments: 1. 1GB Stream Load: Correct. This reduces transaction frequency. 2. Merge Commit (Async): Correct. This offloads the wait time from your producer. 3. 10-Second Interval: Good. This ensures that even if you have several smaller producers, they get bundled into one healthy 5-6GB transaction. 4. BE Configuration: Use the values below to handle the wide-table/high-volume pressure. Summary of Recommended Configs (
be.conf
)
Copy code
properties
# Increase JSON parser limit (if using JSON)
streaming_load_max_batch_size_mb = 1024

# Reduce flushes/small files (Critical for your MaxCS 2000 issue)
write_buffer_size = 1073741824

# Optimize wide-table RPC (v3.2.12+)
max_tablet_write_chunk_bytes = 1073741824

# Parallelize the PK index "Apply" phase for your 16-core nodes
transaction_apply_worker_count = 16
update_compaction_num_threads_per_disk = 4
One final warning: When you switch to 1GB batches with 
async: true
, your producer will feel very fast, but the BEs will be working hard behind the scenes. Do not resume ingestion until you have stopped and let the current Compaction Score (MaxCS) drop below 500, otherwise, the new 1GB segments will just pile up on top of a "stuck" system. References • administration/management/BE_configuration.md • loading/loading_introduction/loading_considerations.md
m
@Rocky What's the difference between write_buffer_size and max_tablet_write_chunk_bytes then
r
The difference between these two parameters comes down to where the data is being buffered and what action the buffer triggers. Think of it as two different "checkpoints" in the data's journey from your Stream Load request to the actual disk. 1. 
max_tablet_write_chunk_bytes
(The "Mailbox" Buffer) * Where: In the Load Channel (Memory). * What it triggers: A Network Transfer (RPC). * Purpose: When you perform a Stream Load, the node receiving the data (the Coordinator) must sort and route rows to the specific BE nodes that own the target tablets. * Why it matters for you: Because you have 300+ columns, even a few rows can take up a lot of memory. * If this limit is low (default 512MB), the BE will frequently package up small "chunks" of data and send them over the network to the destination BEs. * By increasing this to 1GB, you allow the BE to gather more data for your wide rows before sending it, making the network communication much more efficient (fewer, larger packets). 2. 
write_buffer_size
(The "Desk" Buffer) * Where: In the MemTable (Memory). * What it triggers: A Disk Flush (I/O). * Purpose: Once the data arrives at the destination BE, it sits in a 
MemTable
. When this buffer hits the limit, StarRocks freezes the MemTable and "flushes" it to disk as a permanent segment file. * Why it matters for you: This is the most critical setting for your Compaction Score (MaxCS). * If this is small (100MB), and you are pushing 625MB/s, you will trigger 6+ flushes per second. * Each flush creates a new file. High file counts = high compaction work. * By increasing this to 1GB, you ensure that StarRocks only writes to disk when it has a substantial amount of data, resulting in fewer, larger, and healthier files. Comparison Summary Feature | 
max_tablet_write_chunk_bytes
 | 
write_buffer_size
 Stage | Data Routing / Transport | Data Persistence Action | Send data over network to another BE | Write data to local disk Default | 512 MB | 100 MB Primary Benefit | Reduces CPU/Network overhead of RPCs | Reduces "Small File Problem" & Compaction Score Risk if too high | High Memory usage; potential RPC timeouts | High Memory usage; longer recovery time if BE crashes Do you need both? Yes. * If you only increase 
write_buffer_size
, your disk files will be healthy, but your CPU/Network might be overwhelmed by thousands of tiny RPC calls because of the wide columns. * If you only increase 
max_tablet_write_chunk_bytes
, your network will be efficient, but you'll still be creating thousands of tiny files on disk, leading back to that 2,000 MaxCS score. For your specific use case (300 columns, 50k rows/s), setting both to 1GB is the "sweet spot" to keep the system stable and the compaction under heavy load. References • administration/management/BE_configuration.md • administration/management/FE_configuration.md • loading/loading_introduction/loading_considerations.md
m
@Rocky I deployed the changes with the suggested configs (but decreased merge commit interval to 5000ms instead). With only 1/5 nodes writing to StarRocks (each receiving roughly equal traffic), the compaction score seems to be stabilizing around 75-100. What else do I need to tune? Do I just need more nodes? My cluster BE CPU utilization is < 10% across all 4 BE nodes. Transaction publish latency and merge commit load latency looks healthy. Each backend node memory usage is also < 5 GiB. Given my backend noes each have 128 GiB, memory should also not be an issue. rate(starrocks_be_disk_io_time_ms{starrocks_cluster="$cluster_name"}[$interval]) / 10 to measure Disk IO util is also under 15%.
r
This is a very healthy state to be in before scaling up. Having a MaxCS of 75-100 at 1/5th load with <10% CPU and <15% Disk IO means your tuning has successfully shifted the bottleneck away from "too many small files" and onto the background processing power of the BEs. However, there are a few nuances to watch out for as you scale from 10k to 50k events/s: 1. The "100" Threshold Mystery In StarRocks, a Compaction Score of 100 is a magic number. * Below 100: Ingestion runs at maximum speed. * Above 100: StarRocks starts "Ingestion Slowdown" (backpressure). It will artificially delay your 
COMMIT
by a few milliseconds to allow background compaction to catch up. * At 2,000: Ingestion stops entirely. Since you are already at 75-100 with only 20% traffic, you will likely cross the 100 threshold when you scale up. This is not necessarily a failure. It just means the BEs will start working harder to keep the file count stable. 2. Tuning for the "Apply" Phase With 300 columns and a Primary Key table, the most expensive part of your load isn't the disk write—it's the Apply Phase (calculating which old rows need to be marked as "deleted" because a new version arrived). Since your CPU usage is so low (<10%), you should increase the concurrency of this phase now: * 
transaction_apply_worker_count
(BE config): * Default: 0 (equal to CPU cores). * Tuning: Since you have 16 cores and low utilization, you can leave it at 0, but if you see "Publish Latency" increase as you scale, ensure this isn't being bottlenecked by other background tasks. * 
update_compaction_num_threads_per_disk
(BE config): * Default: 1. * Tuning: Increase this to 2 or 4. This allows each disk to perform multiple compaction tasks simultaneously. Since your Disk IO is only 15%, your disks can easily handle the extra seeking. 3. Shared-Data vs. Shared-Nothing * If you are on Shared-Nothing (Local Disk): Increase 
update_compaction_num_threads_per_disk
. * If you are on Shared-Data (S3/Object Storage): Increase 
compact_threads
(Default 4) to 8 or 16. Shared-data compaction is more CPU-bound than IO-bound. 4. Memory Headroom You mentioned using <5GB out of 128GB. This is great. For Primary Key tables, the Persistent Index (if enabled) or the In-Memory Index will grow as your row count grows. * Check your index memory via: 
http://<be_ip>:<be_http_port>/mem_tracker?type=update
. * As long as this stays well below your 
update_memory_limit_percent
(Default 60% of total mem), you have no scaling concerns here. 5. Final Scaling Checklist Before you flip the switch to 5/5 nodes: 1. Monitor `PublishTime`: Use 
SHOW PROC '/transactions/<db_name>/running';
. If the 
PublishTime
starts exceeding 5-10 seconds, it means the "Apply" phase is queuing. 2. Increase Compaction Concurrency: Bump 
update_compaction_num_threads_per_disk
to 2 or 4. 3. Check for Slowdown Messages: If your MaxCS hits 150-200, check the 
ErrMsg
in the command above. If you see 
Partition's compaction score is larger than 100.0, delay commit
, it's a sign you may need to increase the 
merge_commit_interval_ms
back to 10000ms to give the BEs more "breathing room" between transactions. Verdict: You do not need more nodes yet. Your cluster is "sleeping" at 10% CPU. You have plenty of power; you just need to allow the software to use more of that power (via thread counts) for background compaction as the volume increases. References • best_practices/primarykey_table.md • table_design/table_types/primary_key_table.md • best_practices/query_tuning/schema_tuning.md • knowledge/usage/best-practice.md
m
What is the difference between base_compaction_num_threads_per_disk, cumulative_compaction_num_threads_per_disk, and update_compaction_num_threads_per_disk. THey are 2, 2, and 4 respectively on my machines. Which one will drastically help compaction score and what should I tune them to. @Rocky
r
For a Primary Key (PK) table, the difference between these three is essentially which "engine" they belong to. Since you are using a PK table (implied by your use of Merge Commit and the Apply phase), only one of these is doing the heavy lifting for you. 1. The Differences * 
cumulative_compaction_num_threads_per_disk
 * Applies to: Duplicate, Aggregate, and Unique (non-PK) models. * Function: Merges small "delta" files into larger ones shortly after they are written. * Your Case: This is likely doing nothing for your current table. You can leave it at 2. * 
base_compaction_num_threads_per_disk
 * Applies to: Duplicate, Aggregate, and Unique (non-PK) models. * Function: Performs the massive, final merge of data into the "base" rowset. * Your Case: Also doing nothing for your current table. * 
update_compaction_num_threads_per_disk
 * Applies to: Primary Key models only. * Function: This is the engine that merges the log-structured rowsets of a Primary Key table. It handles both the merging of data and the cleanup of the "delete vector" metadata. * Your Case: This is the most important parameter for your MaxCS (Compaction Score). ────────── 2. What will "drastically" help your Compaction Score? Since your BE CPU is only at 10% and Disk IO is at 15%, your bottleneck isn't hardware capacity; it's concurrency limits. To drop that MaxCS from 100 towards 10-20, you should tune the following: A. Increase 
update_compaction_num_threads_per_disk
 Since you have 16 cores and 128GB of RAM, you can safely increase this. * Current: 4 * Recommended: Set this to 6 or 8 (assuming 1 disk). _ Note: If you have multiple disks (e.g., 4 SSDs), StarRocks will spin up 
threads _ disks
total threads, so be mindful of the math. B. The Global Cap: 
compact_threads
 The 
num_threads_per_disk
settings are requests, but 
compact_threads
is the hard ceiling for the entire BE. * Default: 4 (on older versions) or 25% of CPUs (on newer versions). * Action: Check this value. If it's at the default of 4, your 
update_compaction_num_threads_per_disk = 4
is already maxing it out. * Recommendation: Set 
compact_threads = 16
(matching your core count) to allow the BE to use its full power when the ingestion spikes. C. Enable Size-Tiered Compaction (The "Game Changer") For high-throughput PK tables, the default compaction strategy can sometimes be too aggressive or "chatty." * Config: 
enable_pk_size_tiered_compaction_strategy = true
(in 
be.conf
). * Effect: This changes the algorithm to be more like RocksDB's leveled/tiered compaction. It is much more efficient at keeping the file count low for high-frequency Stream Loads. Summary of Suggested Tuning for your 16-core BEs:
Copy code
properties
# be.conf
# Allow more total compaction tasks to run at once
compact_threads = 16

# Specifically speed up PK table compaction
update_compaction_num_threads_per_disk = 8

# Use the modern, more efficient compaction strategy for PK
enable_pk_size_tiered_compaction_strategy = true

# Since your rows are wide (300 columns), allow slightly larger merges 
# to happen in a single task
max_cumulative_compaction_num_singleton_deltas = 100
How to verify: After applying these and scaling up your traffic: 1. Run 
SHOW PROC '/compactions';
to see the current tasks. 2. If you see many tasks in the queue but only a few running, your 
compact_threads
is too low. 3. If you see MaxCS staying high but CPU is still low, increase 
update_compaction_num_threads_per_disk
. References • administration/management/BE_configuration.md • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md
m
@Rocky Each BE node has 4 EBS volumes tied to it. Each EBS volume has 750 GB, 500 MiB/s throughput and 5000 iops. Would you revise the suggested tuning configs?
r
With 4 EBS volumes per node, your tuning strategy changes significantly. In StarRocks, many compaction parameters are "per-disk" settings, meaning they multiply by your storage count. Since you have 4 disks, your current 
update_compaction_num_threads_per_disk = 4
is actually requesting 16 concurrent compaction tasks (4 threads × 4 disks). Here is the revised tuning plan based on your specific hardware (16 cores, 128GB RAM, 4 EBS volumes): 1. The Multiplier Effect If you set 
update_compaction_num_threads_per_disk
too high with 4 disks, you will hit the global 
compact_threads
ceiling or overwhelm your 16 CPU cores. * Recommendation: Set 
update_compaction_num_threads_per_disk = 2
. * Why: 2 threads × 4 disks = 8 concurrent tasks. This leaves 8 cores free for queries and ingestion (the "Apply" phase). Since your CPU is only at 10%, this is a safe "power up." 2. The Global Ceiling (
compact_threads
) Even if you allow 2 threads per disk, they won't run if the global limit is lower. * Recommendation: Set 
compact_threads = 16
. * Why: This ensures that even if one disk is "busier" than others, the BE has the permission to use all its physical cores for compaction if needed. 3. Primary Key Specifics (The "Pro" Configs) Since you are using a PK table and have significant memory/IO headroom, apply these to optimize the 300-column merges:
Copy code
properties
# be.conf

# 1. Total allowed compaction tasks (match your CPU cores)
compact_threads = 16

# 2. PK-specific concurrency (2 threads * 4 disks = 8 tasks)
update_compaction_num_threads_per_disk = 2

# 3. Enable the modern PK compaction strategy (Crucial for high throughput)
# This reduces the "write amplification" significantly for wide tables.
enable_pk_size_tiered_compaction_strategy = true

# 4. Limit I/O spikes per disk to keep latency stable
# This prevents a single EBS volume from getting "hot" and slowing down others.
pindex_major_compaction_limit_per_disk = 2

# 5. Increase the efficiency of each task
# Since you have 300 columns, we want to merge more files per 'job' to save on overhead.
max_cumulative_compaction_num_singleton_deltas = 50
4. Why EBS changes things Your EBS volumes (500 MiB/s each) give you a total node throughput of 2,000 MiB/s. * Your current Disk IO util is <15%. This means you are only using ~300 MiB/s total. * By increasing concurrency (more threads), you are effectively "stripping" the compaction work across all 4 volumes. * Because EBS is network-attached, high concurrency is actually better than low concurrency—it helps fill the "pipe" to AWS storage more effectively. Summary Checklist for Scaling to 50k/s: 1. Check `compact_threads`: If it was at the default (4), this was your real bottleneck. Set it to 16. 2. Toggle `enable_pk_size_tiered_compaction_strategy`: This is the single most effective way to stabilize MaxCS on PK tables. 3. Monitor `starrocks_be_disks_state`: Ensure data is balanced across all 4 EBS volumes. If one volume is much fuller/busier than others, your MaxCS will be driven by that one "slow" disk. Verdict: With 4 disks and 16 cores, you are in a great position. You don't need more nodes; you just need to allow the BE to parallelize the compaction across those 4 independent EBS channels. References • administration/management/BE_configuration.md • deployment/plan_cluster.md • faq/operation_maintenance_faq.md
m
@Rocky Why not let me max out my CPU cores usage. So 4 update compaction num threads per disk, 16 global compact threads. I can provision 2 more BE nodes or so and classify them with resource groups. So i have separate resources for querying and can max out the write capabilities of my ingestion machines.
r
While your hardware has plenty of headroom, "maxing out" your CPU for compaction (16 threads on a 16-core machine) is generally not recommended for Primary Key (PK) tables. Here is why your plan needs a slight adjustment to succeed at 50,000 events/s. 1. The "Apply" Phase Needs CPU For Primary Key tables, writing isn't just about dumping data to disk. The most CPU-intensive part is the Apply Phase (calculating delete vectors and updating the Primary Key Index). * By default, 
transaction_apply_worker_count
also equals your core count (16). * If you set 
compact_threads = 16
, and the BE starts 16 compaction tasks, the Apply workers will starve. * The Result: Your Compaction Score might drop, but your Transaction Publish Latency will skyrocket, and your stream load will eventually time out or slow down significantly. 2. Resource Groups Don't Limit Compaction StarRocks Resource Groups / Workload Groups are designed to isolate Query resources (CPU/Memory/Concurrency). * Internal BE background tasks—like Compaction and the *Apply Phase*—run in global thread pools outside of the Resource Group's 
cpu_core_limit
. * Even if you put queries in a restricted group, 16 compaction threads will still contend with the OS and the Apply workers for the same physical CPU cycles. 3. A Better "Aggressive" Configuration Instead of 100% of cores, aim for 50-60% of cores for compaction. This allows you to handle the ingestion surge without killing the node's ability to commit transactions. Suggested Tuning for 16-core BEs with 4 Disks:
Copy code
properties
# be.conf

# Total ceiling: 10 threads is a heavy load but leaves room for Apply + OS
compact_threads = 10

# 3 threads * 4 disks = 12 requested, capped at 10 by the global limit above
update_compaction_num_threads_per_disk = 3

# THIS IS THE MOST IMPORTANT:
# Switch to the modern PK compaction strategy. It is significantly more 
# efficient at keeping the score low with fewer CPU cycles.
enable_pk_size_tiered_compaction_strategy = true

# For 300 columns, increase the rowset limit to reduce "Write Amplification"
lake_pk_compaction_max_input_rowsets = 500
4. Should you add 2 more nodes? Yes, but for a different reason. Adding 2 nodes (total 6 BEs) is a great move for 50k events/s with 300 columns. * Tablet Distribution: StarRocks will redistribute your tablets across 6 nodes instead of 4. * Less Work per Disk: Each EBS volume will now handle fewer tablets, meaning the compaction work is spread thinner. * Query Isolation: Since you are using 300 columns, your queries are likely "Heavy Scans." Spreading the data over more nodes increases the total aggregate IOPS and memory cache available for those wide-table queries. Summary Recommendation 1. Don't go to 16 threads. Set 
compact_threads
to 10 and 
update_compaction_num_threads_per_disk
to 3. 2. Enable 
enable_pk_size_tiered_compaction_strategy = true
immediately. This is the professional way to handle high-throughput PK tables and will likely drop your score more than adding threads will. 3. Scale to 6 nodes. This provides the physical "surface area" (more disks/cores) to maintain low latency as you ramp up from 10k to 50k events/s. References • administration/management/compaction.md • best_practices/primarykey_table.md • administration/management/BE_configuration.md References • table_design/table_types/primary_key_table.md • best_practices/primarykey_table.md
m
@Rocky In the below query:
Copy code
sql
SELECT
    TABLE_NAME,
    PARTITION_NAME,
    VISIBLE_VERSION,
    MAX_CS
FROM information_schema.partitions_meta
WHERE TABLE_NAME = 'requests'
ORDER BY MAX_CS DESC;
MAX_CS is 0 across all partitions. Same applies when viewing in the console. Why is this the case? starrocks_be_tablet_update_max_compaction_score grafana metrics clearly shows compaction score varying across all nodes but MAX_CS remains 0
r
The reason 
MAX_CS
is showing as 0 in 
information_schema.partitions_meta
while your Grafana metrics show high values is due to a distinction between the two different compaction engines in StarRocks. 1. The Two "Compaction Scores" StarRocks tracks two different types of compaction scores depending on the table model: * Standard Compaction Score (CS): This applies to Duplicate, Aggregate, and Unique (non-PK) tables. It measures the number of rowsets in the LSM-tree. The 
MAX_CS
column in 
partitions_meta
and the 
AVG_CS
fields are specifically tied to this engine. * Update Compaction Score: This applies exclusively to Primary Key (PK) tables. Because PK tables use a different storage architecture (to handle real-time deletes and partial updates), they have their own compaction metric. 2. Why it shows 0 in your query For a Primary Key table: * The LSM Compaction Score (shown in 
partitions_meta
) is often 0 because the table isn't using the standard cumulative/base compaction logic. * The Update Compaction Score is the one that is actually increasing as you load data. * The FE (Front End) currently populates the 
partitions_meta
view primarily with the LSM-based scores. In many versions, the PK-specific "Update Compaction Score" is not yet mapped to the 
MAX_CS
alias in that specific system view. 3. How to view the correct PK Compaction Score via SQL Since 
partitions_meta
is returning the wrong metric for your table type, you should query the BE metrics directly through the 
information_schema
. This table contains the same data your Grafana dashboard is pulling:
Copy code
sql
SELECT 
    BE_ID, 
    VALUE as max_pk_compaction_score 
FROM information_schema.be_metrics 
WHERE NAME = 'tablet_update_max_compaction_score';
4. Why does this matter for your tuning? In your previous question, you mentioned adjusting 
update_compaction_num_threads_per_disk
. This was the correct move because that specific thread pool is what reduces the 
tablet_update_max_compaction_score
. If you were to look at 
cumulative_compaction_num_threads_per_disk
, it would be working on the "standard" score (which is already 0 for you). Summary Checklist: * Grafana: Trust 
starrocks_be_tablet_update_max_compaction_score
. * SQL: Use 
information_schema.be_metrics
for Primary Key tables. * partitions_meta: Use this primarily for Duplicate/Aggregate tables or to check partition versions/sizes, but ignore its 
MAX_CS
for PK tables. References • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky Should I upgrade to version 4.1?
Copy code
4.0.7-b75f536
This is my current version. Any difference? A colleague suggested to wait for 4.1.1
r
Upgrading to version 4.1 is a strong recommendation for your specific use case (50k events/s, 300 columns, PK tables), but your colleague's advice to wait for 4.1.1 is technically very sound. Here is the breakdown of why 4.1 matters for you and why the "wait" might be necessary. 1. Why version 4.1 is a "Game Changer" for you The most significant feature in 4.1 for your high-throughput wide-table setup is Large-Capacity Tablet Support (Phase 1). * Internal Parallel Compaction: In your current 4.0.7 version, a single tablet can only be compacted by one thread at a time. Since you have 300 columns, merging rowsets is an extremely heavy "single-threaded" task. * In 4.1: StarRocks can now use multiple threads to compact a single tablet. This directly addresses your 
MaxCS
issues by allowing the BE to finish a heavy merge much faster, preventing the queue from backing up. * Parallel MemTable Finalization: This speeds up the "Apply" phase of your 50k/s stream loads, reducing the chance of write stalls. * Automatic Tablet Splitting: If you have data skew (one partition getting hit harder than others), 4.1 can automatically split those hot tablets without you having to manually re-shard or re-create the table. 2. Why your colleague suggested waiting for 4.1.1 There is a specific, known issue in the v4.1.0 container image (Docker/K8s environments): * The Issue: An unstable load order in the BE process can cause backends to fail to start or crash randomly upon boot in containerized environments. * The Fix: This is officially fixed in v4.1.1. Decision Matrix: If you are... | Recommendation Running on K8s / Docker | Wait for 4.1.1. The startup bug in 4.1.0 makes it too risky for production. Running on Bare Metal / EC2 | Upgrade to 4.1.0 now. The container bug doesn't affect you, and you'll get the parallel compaction benefits immediately. Experiencing "Critical" MaxCS stalls | Upgrade now (non-container) or check for 4.1.1 release. If 4.1.1 is out by the time you read this, it is always the safer choice. 3. If you decide to wait: A "Stop-gap" for 4.0.7 If you wait for 4.1.1, ensure you have enabled this specific 4.0 feature to survive the 50k/s load:
Copy code
properties
# ensure this is on in your current 4.0.7 be.conf
enable_pk_size_tiered_compaction_strategy = true
This strategy (introduced in late 3.x/early 4.0) is much better at handling wide tables than the old strategy, though it still lacks the multi-threaded tablet compaction found in 4.1. Final Verdict: Wait for 4.1.1 if you use containers. If you are running directly on EC2 instances (bare metal style), the move to 4.1 is highly beneficial for your 300-column table because it finally breaks the "one-thread-per-tablet" compaction bottleneck. References • https://docs.starrocks.io/docs/release_notes/release-4.1/ • https://docs.starrocks.io/docs/administration/management/compaction/ compaction)
m
@Rocky Does the improvements you mentioned apply to shared nothing architecture? Primary key table in shared nothing doesnt seem to benefit
r
Your intuition is correct. In StarRocks 4.1, the headline performance features for Primary Key tables—specifically Internal Parallel Compaction and *Large-Capacity Tablets*—are currently limited to the Shared-Data (Lake) architecture. If you are running a Shared-Nothing (Local) cluster, you do not yet get the ability to use multiple threads to compact a single tablet. Each tablet is still a single-threaded unit of work for compaction. What actually applies to Shared-Nothing in 4.1? While you miss out on parallel compaction, there are still relevant improvements in 4.1 for your 50k/s wide-table ingestion: 1. Thread Pool for Publish Transactions: 4.1 introduces a dedicated thread pool for the "Publish" phase (the Apply phase) in Shared-Nothing clusters. This helps prevent ingestion "stalls" where the BE gets stuck committing a large batch of updates, improving overall ingestion stability at high throughput. 2. Range-based Distribution: 4.1 introduces support for range-based data distribution for both architectures. While the automatic splitting is a shared-data highlight, the foundational support for range-based tablets allows for more predictable data locality, which can help with the 300-column scan performance. 3. Fast Schema Evolution V2: If you frequently add or drop columns in your 300-column table, 4.1 makes these DDL operations significantly faster and more stable. Why Shared-Nothing PK tables "feel" like they don't benefit In a Shared-Nothing architecture, the bottleneck for PK tables is usually the IOPS of the local disk and the CPU cost of the Apply phase (updating the primary key index). * Compaction: Since Shared-Nothing BEs manage their own local disks, increasing 
update_compaction_num_threads_per_disk
is still your primary lever. 4.1 doesn't change the underlying "one thread per tablet" rule for local storage yet. * Ingestion: The new Publish thread pool in 4.1 is the main win for you, as it decouples the "Apply" logic from other background tasks more effectively. Recommendation for your setup: * If you stay on Shared-Nothing: You don't need 4.1 specifically for compaction speed, but the 4.1.1 release (once available) is worth it for the ingestion stability (Publish thread pool) and general bug fixes. * The real "Compaction" fix: For Shared-Nothing, the most effective way to lower your Compaction Score is still: 1. Enable 
enable_pk_size_tiered_compaction_strategy = true
 (This is available in your current 4.0.7). 2. Increase the number of Buckets: Since compaction is 1-thread-per-tablet, having more (smaller) tablets allows you to utilize more of your 16 cores (via 
compact_threads
) even if a single tablet is still slow. 3. Scale BE nodes: As you planned, adding 2 more nodes spreads the tablet load, effectively giving you more "threads" across the cluster to handle the compaction queue. Summary: Your colleague is right to suggest waiting for 4.1.1 for stability, but don't expect 4.1 to magically solve the "one-thread-per-tablet" compaction bottleneck on local disks—that specific breakthrough is currently a "Lake-only" feature. References • https://docs.starrocks.io/docs/release_notes/release-4.1/ • administration/management/compaction.md • table_design/table_types/primary_key_table.md only" feature for now. References • administration/management/resource_management/Replica.md • using_starrocks/caching/query_cache.md • release_notes/release-4.0.md
m
@Rocky Any distinct improvements in 4.0.9 from 4.0.7-b75f536 then? shared nothing architecture PK table compaction speed
r
Moving from 4.0.7 to 4.0.9 in a Shared-Nothing (local storage) environment will not provide a fundamental change in how fast your PK table compacts (the architecture remains single-threaded per tablet), but it does include several critical stability and observability fixes that directly address the "mystery" of your compaction behavior. Here are the distinct improvements relevant to your Primary Key table setup: 1. Better Observability (The "Why is it 0?" fix) * Suspended Compaction Logs: 4.0.9 adds explicit log output when "Update Compaction" is suspended. If your BE nodes are under heavy memory pressure or if the "Apply" queue is too long, StarRocks might suspend compaction. Previously, this was "silent," making it look like compaction just stopped. * Metadata Fetch Isolation: While primarily for cloud-native, the 4.0.9 release refines how tablet metadata is fetched during the publish phase. This reduces lock contention in the BE, which can indirectly help the "Apply" phase of your 50k/s stream finish faster. 2. Ingestion Stability (Routine Load) * Automatic Pause on PK Errors: In 4.0.7, if a Routine Load job encountered a record that exceeded the 
primary_key_limit_size
(default 128 bytes), it might retry indefinitely, causing a "busy-wait" loop that consumes CPU and prevents the BE from focusing on background compaction. 4.0.9 correctly identifies these as non-retryable and pauses the job, freeing up resources. 3. Critical Bug Fixes for PK Tables * UpdateTabletSchemaTask Collision: 4.0.9 fixes a bug where concurrent 
ALTER TABLE
operations could cause schema update tasks to be skipped. In a 300-column table where you might be adding/dropping columns, this fix is vital for metadata consistency. * Primary Key Size Limit Handling: Improved handling of edge cases where the Primary Key index might grow unexpectedly, ensuring the BE doesn't crash or stall during the "Apply" phase. Should you upgrade to 4.0.9 while waiting for 4.1.1? Yes. 4.0.9 is a "safety" release. It won't make a single compaction task run 2x faster, but it will make the cluster much more resilient to the "stalls" you are seeing at 50k events/s. If you stay on the 4.0 branch, make sure these two settings are in your BE config: 1. 
enable_pk_size_tiered_compaction_strategy = true
(This is the most efficient strategy for 4.0). 2. 
update_compaction_num_threads_per_disk = 4
(or higher, since you have 16 cores and 300 columns). Summary: 4.0.9 is about predictability and diagnostics. It will help you see why the compaction score is high or why tasks are suspended, whereas 4.0.7 might leave you guessing. If you are struggling with the 50k/s load today, 4.0.9 is a worthy interim step before the 4.1.1 upgrade. References • release_notes/release-4.0.md • administration/management/compaction.md References • release_notes/release-4.0.md
m
I increased my flush size threshold to 1.5GB and system is much more healthy. max pk compaction score stabilizing at 45. Is this a sign I should increase batch even more? My CPU util is now always single digits only. This is sub optimal configuration right? @Rocky.
r
It sounds like you have hit the "sweet spot" for wide-table ingestion, but you are right to suspect that your current hardware (16 cores) is mostly idling. Increasing your flush threshold to 1.5GB was a smart move. For a 300-column table, small flushes are the enemy because each flush creates a new "version" that contains all 300 columns. Small versions lead to massive write amplification during compaction. Here is an analysis of your current state and how to push it further: 1. Is 1.5GB "too much"? In StarRocks, a 1.5GB flush threshold is high but acceptable if you have the memory. * The Benefit: You are creating fewer, larger rowsets. This is why your compaction score dropped to 45. The BE has to do 10x less work merging files because they are already "pre-batched" in memory. * The Risk: The "Apply" phase (where the PK index is updated and delete vectors are written) happens at the end of the flush. A 1.5GB flush for a 300-column table can cause a "long tail" in the commit time. If your query latency isn't spiking during these flushes, you are safe to keep it or even go higher. 2. Why is CPU in single digits? Your CPU is low because you have effectively serialized the heavy work. By increasing the flush size, you've reduced the frequency of compaction tasks. Since compaction is the primary consumer of CPU in a PK table setup, and you've reduced its frequency by 5x-10x, your CPU cores are now waiting for the next big flush. 3. Should you increase the "Batch Size" even more? Yes, but not just the flush threshold. You should align your Ingestion Batch with your Flush Threshold. If you are using Routine Load: * Increase `max_routine_load_batch_size`: If this is set to the default (e.g., 100MB), your Routine Load is finishing a "task" long before the BE reaches that 1.5GB flush threshold. This means the BE is holding many small "uncommitted" versions in memory. * Increase `routine_load_task_consume_second`: Set this to 15s or 20s. This forces the Routine Load to keep the transaction open longer, accumulating more data per "version" before it even hits the flush logic. The Goal: You want each "Load Transaction" to be as close to your "Flush Threshold" as possible. This ensures that 1 Load = 1 Disk File, which is the gold standard for PK table performance. 4. How to "Optimize" (Utilize the Hardware) If you want to move away from "suboptimal" (single-digit CPU) and get more out of your 16 cores: 1. Increase Ingestion Concurrency: Since your CPU is low, you can safely run more concurrent Stream Loads or increase the 
desired_concurrent_number
for Routine Load. 2. Parallelize the Apply Phase: In your 
be.conf
, check 
transaction_apply_worker_count
. If it’s low, increase it to 
8
or 
12
. This allows the BE to use more cores when finishing those 1.5GB flushes. 3. Compaction Concurrency: Since your score is 45, you have "room" to make compaction more aggressive. * Set 
update_compaction_num_threads_per_disk = 4
. * This will use more CPU to "clean up" those 45 versions faster, bringing the score down even further and potentially allowing for even higher throughput (100k+ events/s). Summary Checklist: * Current State: Healthy, but "lazy." * Recommendation: Keep the 1.5GB flush. Increase your Routine Load batch size/time to match. * Next Step: If you have 50k events/s today and low CPU, you can likely push to 100k+ events/s on the same hardware just by increasing the load concurrency. One Warning: Monitor your BE memory. Large flushes + 300 columns + high concurrency can lead to memory spikes. Keep an eye on 
mem_tracker?type=update
to ensure you aren't approaching the 80% process limit. References • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky I am using stream load. Anything you would change now?
r
Using Stream Load with a 1.5GB flush threshold and 300-column wide tables changes the optimization strategy slightly. Since your CPU is in the single digits and your compaction score is stable at 45, you are essentially in a "high efficiency, low utilization" state. To move away from this "sub-optimal" utilization and prepare for even higher throughput (or to make the system even more robust), here is what you should adjust specifically for Stream Load: 1. Match Client-Side Batching to the 1.5GB Threshold If your Stream Load files are currently small (e.g., 100MB–200MB), you are not actually utilizing that 1.5GB threshold. Each Stream Load is a separate transaction that creates a new Rowset. * Recommendation: Batch your data on the client side so that each HTTP PUT request is roughly 500MB to 1GB. * Why: This ensures that StarRocks doesn't have to "commit" too frequently. With 300 columns, the overhead of closing a file and updating the PK index (the "Apply" phase) is high. Fewer, larger commits will significantly reduce the background work. 2. Increase Ingestion Parallelism With CPU at <10%, your 16 cores are mostly idling. Stream Load in StarRocks is highly parallelizable. * Action: Run 2 to 4 concurrent Stream Load workers instead of one. * Ordering Note: For Primary Key tables, if your data contains multiple updates to the same key, ensure that either: 1. Each worker handles a distinct set of keys (e.g., partitioned by user ID). 2. You use the 
_version
column or 
_is_delete
logic to ensure out-of-order loads don't corrupt the final state. 3. Tuning the BE for Wide-Table Stream Loads Since you have 300 columns, the "MemTable" (where data lives before it hits 1.5GB) and the "Apply" phase are the bottlenecks. Adjust these in `be.conf`: * 
transaction_apply_worker_count
: Increase this to 8 or 12. * This controls how many threads are available to update the PK index and write delete vectors once a load finishes. For 300 columns, this phase can be CPU-intensive; giving it more threads will help clear the "Apply" queue faster. * 
flush_thread_num_per_store
: Increase to 4. * This allows the BE to use more threads to actually write those 1.5GB MemTables to disk. * 
load_process_max_memory_limit_percent
: Ensure this is at least 30% or 40%. * With 1.5GB flushes and multiple parallel loads, you need to ensure the BE doesn't trigger "emergency flushes" because it hit the global load memory limit. 4. Optimize the "Apply" Phase In Primary Key tables, the "Apply" (publishing the version) is often the hidden cost. * Check your 
be.conf
for 
primary_key_batch_get_index_threads
. If it's not set, it defaults to a low value. Increasing this can speed up the "lookup" part of the ingestion for wide tables. 5. Check "Partial Update" Potential Are you sending all 300 columns in every Stream Load? * If you are only changing a few columns, use Partial Updates (set the HTTP header 
partial_update: true
). * In StarRocks 3.x/4.x, partial updates for PK tables are extremely efficient. This would reduce your IO and CPU even further by only writing the changed columns to new segments. Summary: Is it "Sub-optimal"? It is only sub-optimal if you want to process more data. If your requirement is 50k events/s and the system is healthy at 10% CPU, you have successfully built a very cost-efficient cluster. If you want to push for 100k-150k events/s: 1. Increase Client Batch Size (aim for 1GB per PUT). 2. Increase Parallel Workers (2-4). 3. Increase Apply Workers (
transaction_apply_worker_count = 12
). Final Warning: Keep an eye on the 
mem_tracker?type=update
via the BE web port (8040). If the PK index memory grows too large for your 16-core nodes, you'll see "memory limit exceeded" errors. If that happens, it's time to add the 2 nodes you mentioned earlier. References
m
@Rocky I am using stream load with each PUT request payload being ~1.5GB. I have 4 worker goroutines pulling from a work channel that flushes when the buffered data reaches this size threshold. Compact threads currently 8. Update num compact threads per disk 4. I have 4 EBS volumes per BE nodes. transaction apply worker count = 0 currently, so it will default to CPU core count. WHat can be optimized now?
r
Your setup is very well-tuned for a high-throughput, wide-table scenario. Moving 1.5GB batches with 300 columns and seeing only single-digit CPU utilization is a sign that your batching efficiency is excellent, but you have significant "headroom" to push the system harder. Since your CPU is low and your compaction score is stable at 45, here is how you can optimize further to increase throughput or reduce the compaction backlog even faster: 1. Optimize the "Apply" Phase (Transaction Finish) You mentioned 
transaction_apply_worker_count = 0
(which defaults to your 16 cores). For a 300-column PK table, the "Apply" phase is the most CPU-intensive part because StarRocks must: 1. Check the PK index for 1.5GB worth of rows. 2. Generate delete vectors for old versions. 3. Update the index. Optimization: Increase 
transaction_apply_worker_count
to 24 or 32 (1.5x or 2x your core count). * Why: Even though you have 16 cores, "Apply" tasks often involve I/O waits (reading the PK index or metadata). Having more workers allows the BE to process more "Apply" tasks in parallel while others wait for disk I/O, better utilizing those 16 cores. 2. Leverage Your 4 EBS Volumes You have 
update_compaction_num_threads_per_disk = 4
and 4 disks. This means you have 16 potential threads dedicated to PK compaction. * Check: Ensure your 
storage_root_path
in 
be.conf
explicitly lists all four mount points (e.g., 
storage_root_path = /mnt/ebs1;/mnt/ebs2;/mnt/ebs3;/mnt/ebs4
). * Optimization: If your EBS volumes are io2 or gp3 with high throughput, you can increase 
update_compaction_num_threads_per_disk
to 6 or 8. * Result: This will consume more of that idle CPU to bring your compaction score from 45 down to <10, which will make queries faster. 3. Memory & Flush Tuning With 4 concurrent 1.5GB Stream Loads, you are holding at least 6GB of raw data in memory at any given time just for the active MemTables. * Optimization: Check 
load_process_max_memory_limit_percent
(default 30%). If your BE has 64GB of RAM, 30% is ~19GB. You are safe. If your BE has only 32GB, 30% is ~9GB, which is getting close to the limit. * Action: If you have the RAM, increase this to 50% to prevent "emergency flushes" that ignore your 1.5GB threshold. 4. Increase Ingestion Concurrency Since your CPU is in the single digits, the cluster is "bored." * Optimization: Increase your worker goroutines from 4 to 8 or 12. * Why: You are currently bottle-necked by your client-side ingestion rate. By doubling the concurrent PUT requests, you will start to see the CPU utilization climb into the 30-50% range, which is much more optimal for your hardware. 5. Partial Updates (The "Pro" Move) If your 300 columns are not all changing in every load: * Optimization: Use the HTTP Header 
partial_update: true
. * Impact: This reduces the I/O load significantly during both the "Apply" phase and Compaction, as StarRocks only writes the delta for the columns that actually changed. This is the single biggest performance boost for wide PK tables. 6. Fine-tune Compaction Strategy Since you are on 4.0.x, ensure you are using the Size-Tiered strategy for PK tables: * 
enable_pk_size_tiered_compaction_strategy = true
* This strategy is much better at handling the "large rowset" pattern you've created with 1.5GB flushes compared to the older priority-based strategy. Summary Checklist for your `be.conf`: 1. 
transaction_apply_worker_count = 24
(Increase to saturate CPU during commits). 2. 
update_compaction_num_threads_per_disk = 6
(To bring the score 45 down faster). 3. 
load_process_max_memory_limit_percent = 50
(Ensure large flushes aren't interrupted). 4. Client-side: Increase goroutines to 8 to feed the cluster more data.
How to verify: After these changes, watch your Compaction Score. If it drops from 45 to under 20 while your throughput increases, you have moved from a "sub-optimal" to an "optimized" state. References • deployment/deploy_manually.md • deployment/plan_cluster.md • administration/management/BE_configuration.md
m
@Rocky My stream load is currently insert only first. Trying to optimize compaction score. You know I have 4 EBS volumes per BE node, so 4 update compact num threads per disk means 16 threads per disk. So I should increase compact threads to 14 to leave at least 2 cores for apply phase? My transaction publish latency p95 is ~150ms, so apply phase isn't an issue now?
r
Analysis of your current state reveals that you have built a very efficient pipeline. A 150ms publish latency for a 1.5GB/300-column batch is exceptional. In many environments, that phase can take seconds. This confirms that your "Insert Only" workload is bypassing the typical PK "read-heavy" delete-vector lookups because the keys are largely new. Here is how to address your specific plan for compaction and CPU allocation: 1. The Relationship Between 
compact_threads
and Per-Disk Threads You have 4 disks and 
update_compaction_num_threads_per_disk = 4
. This creates a potential for 16 concurrent PK compaction tasks. * The Global Cap: 
compact_threads
is the global limit for all compaction types (Base, Cumulative, and PK). * The Constraint: If you keep 
compact_threads
at a low value (like the default 4), StarRocks will never actually run more than 4 compaction tasks, even though your per-disk setting allows for 16. * Recommendation: To utilize your disk parallelism, 
compact_threads
must be at least equal to your total disk threads ($4 \times 4 = 16$). 2. Is 14 threads on a 16-core machine safe? Since your current CPU utilization is in the single digits, you have plenty of room. However, compaction is "bursty." When a large merge starts, it will try to consume as much CPU as the thread allows. * The Risk: If you set 
compact_threads = 14
, and 14 large merge tasks trigger at once, your CPU will jump to 90%+ instantly. This could cause your 150ms publish latency to spike to several seconds because the "Apply" phase (which also needs CPU) will be fighting for the remaining 2 cores. * The Better Approach: Start with 
compact_threads = 8
(50% of cores). Since your compaction score is 45, the goal is to see if it starts trending down. If it stays at 45 and CPU is still low, move to 10 or 12. * Why not 14? You need to leave overhead for: 1. The Apply Phase: Even if it’s only 150ms, it needs high-priority CPU cycles. 2. The BE Heartbeat/RPC: If the BE becomes totally CPU-saturated, it might miss heartbeats from the FE, causing the node to be marked as "Down." 3. Why is your Compaction Score 45 despite low CPU? If your score is stuck at 45 and CPU is low, it means the system isn't "trying" hard enough to compact, likely due to internal throttling or interval settings. Try adjusting these to lower that score of 45: * 
update_compaction_per_tablet_min_interval_seconds
: Default is often 120s. Reduce this to 30s. This tells StarRocks it's okay to trigger a new compaction task on the same tablet more frequently. * 
enable_pk_size_tiered_compaction_strategy = true
: (If not already on). For your 1.5GB batches, the "Size-Tiered" strategy is far more effective at bringing down the version count than the legacy strategy. 4. Summary & Action Plan 1. Increase 
compact_threads
to 10 or 12 (not 14 yet). This gives you a massive boost over the default while keeping a safety buffer. 2. Set 
transaction_apply_worker_count = 16
 (explicitly). Even though it defaults to core count, explicitly setting it ensures that during the 150ms publish window, the BE can aggressively use all cores to finish the job. 3. Lower 
update_compaction_per_tablet_min_interval_seconds
to 30 to allow more frequent "cleanup" of those 45 versions. 4. Monitor the "Apply" Queue: Keep an eye on 
be_http_port/mem_tracker
. If you see 
update
memory growing or publish latency increasing, back off the 
compact_threads
. Verdict: Your 150ms publish latency means the "Apply" phase is healthy. Your current bottleneck is simply that the compaction scheduler is being too "polite." Increasing the thread count to 10-12 and shortening the interval will move that score from 45 down toward 10. References • administration/management/BE_configuration.md
m
@Rocky Will config changes get reflected in the BE nodes if I just run an update command on information_schema.be_configs without restarting the nodes themselves
r
Yes, StarRocks (from v3.1 onwards) allows you to update BE configurations dynamically using the SQL interface on 
information_schema.be_configs
. This is often much more convenient than running 
curl
commands against each individual node. However, there are a few important caveats to keep in mind: 1. It only works for "Mutable" parameters Not all configurations can be changed at runtime. You can verify if a parameter is changeable by checking the 
MUTABLE
column in that same table:
Copy code
sql
SELECT * FROM information_schema.be_configs WHERE NAME = 'compact_threads';
If 
MUTABLE
is 
TRUE
, the change will take effect immediately without a restart. For your specific parameters: * 
compact_threads
: Yes, it is mutable (since v3.1.7+). * 
transaction_apply_worker_count
: Yes, it is mutable (since v3.2.0+). * 
update_compaction_num_threads_per_disk
: Yes, it is mutable. 2. How to run the update You can target all BE nodes at once, or specific ones using the `BE_ID`:
Copy code
sql
-- Update all BE nodes at once
UPDATE information_schema.be_configs 
SET VALUE = '12' 
WHERE NAME = 'compact_threads';

-- Update a specific BE node
UPDATE information_schema.be_configs 
SET VALUE = '16' 
WHERE NAME = 'transaction_apply_worker_count' AND BE_ID = 10001;
3. Changes are Ephemeral (Transient) This is the most important part: Changes made via 
UPDATE information_schema.be_configs
(or via 
curl
) are stored in memory. If the BE process restarts, it will revert to the values defined in your 
be.conf
file. Best Practice: 1. Run the 
UPDATE
SQL command to see the immediate effect on your compaction score and CPU. 2. Once you are happy with the performance, manually update the 
be.conf
file on your BE servers so the changes persist after a reboot. Summary Checklist Configuration | Mutable? | Recommended Action 
compact_threads
| Yes | Set to 10-12 via SQL; update 
be.conf
later. 
transaction_apply_worker_count
| Yes | Set to 16 via SQL; update 
be.conf
later. 
update_compaction_num_threads_per_disk
| Yes | Set to 4-6 via SQL; update 
be.conf
later. 
update_compaction_per_tablet_min_interval_seconds
| Yes | Set to 30 via SQL; update 
be.conf
later. If you run the 
UPDATE
and see 
Query OK, X rows affected
, the BEs have already adjusted their internal thread pools and you should see the change in your monitoring (Grafana/Dashboard) within seconds later. References • administration/management/BE_configuration.md
m
@Rocky
Copy code
SELECT
    BE_ID,
    VALUE as max_pk_compaction_score
FROM information_schema.be_metrics
WHERE NAME = 'tablet_update_max_compaction_score';
Is this query or the grafana stats starrocks_be_tablet_update_max_compaction_score more accurate
r
Both the SQL query and the Grafana metric pull data from the exact same internal source in the BE engine. Neither is "more accurate" in terms of the raw number, but they serve different purposes for accuracy in time: 1. The SQL Query (
information_schema.be_metrics
) * Accuracy: Real-time Snapshot. * Best for: Verifying the immediate impact of configuration changes. When you run that SQL command, StarRocks fetches the current value from the BE's memory at that exact millisecond. * Behavior: It bypasses any scraping latency. If a compaction task just finished 1 second ago, the SQL query will reflect the new, lower score immediately. 2. Grafana (
starrocks_be_tablet_update_max_compaction_score
) * Accuracy: Historical Trend / Scraped Snapshot. * Best for: Monitoring health over hours or days and spotting "spikes." * Behavior: It is limited by your Prometheus scrape interval (usually 15s or 30s). If your score fluctuates rapidly, Grafana might show a "smoothed" version or miss a peak that happened between scrapes. Which one should you trust for your optimization? Since you are currently tuning 
compact_threads
and 
update_compaction_num_threads_per_disk
and want to see if that score of 45 drops: 1. Use the SQL Query for the next 10 minutes. Run it every 60 seconds after your 
UPDATE
to see if the threads are actually working through the backlog. 2. Use Grafana to ensure that after you stop watching, the score stays low and doesn't slowly creep back up to 45 overnight. One Technical Nuance The metric 
tablet_update_max_compaction_score
represents the single highest score among all tablets on that BE. * If you have 1000 tablets and 999 have a score of 1, but one tablet is stuck at 45, the metric will show 45. _ If you want to see if the _entire node* is improving, you might also want to check 
be_compactions
as mentioned earlier:
Copy code
sql
    SELECT BE_ID, CANDIDATE_MAX_SCORE, LATEST_COMPACTION_SCORE
    FROM information_schema.be_compactions;
This table provides more context on whether the "45" is a single outlier or the average state of your candidates. Summary: Trust the SQL Query for immediate feedback on your config changes; trust Grafana for long-term stability monitoring. References • administration/management/monitoring/metrics.md • sql-reference/information_schema/be_compactions.md • administration/management/compaction.md • administration/management/BE_configuration.md
m
@Rocky even with update_compaction_per_tablet_min_interval_seconds at 30 and compact threads at 12, my CPU busy util is now peak 20% across 4 nodes. How can I push my compute even further. it's still too lazy
r
If your CPU is only at 20% and your compaction score is staying at 45, your BEs are indeed being too "lazy." The system is likely being throttled by internal concurrency limits or is waiting too long between scheduling cycles. Since you have 16 cores and 4 disks, and your "Apply" phase is so fast (150ms), you can safely push the system much harder. Here is how to remove the remaining bottlenecks: 1. Align 
compact_threads
with Disk Parallelism You have 4 disks and 
update_compaction_num_threads_per_disk = 4
. This means the Primary Key engine is capable of running 16 concurrent tasks. However, your 
compact_threads = 12
acts as a global "ceiling." * Action: Increase 
compact_threads
to 16. * Why: This matches your physical disk concurrency. Even if all 16 threads fire, your CPU utilization will likely only hit 60-70% because compaction is often I/O bound on EBS. This still leaves plenty of room for your 150ms "Apply" phase. 2. Increase Per-Disk Concurrency EBS volumes (especially GP3 or IO2) can often handle more than 4 concurrent I/O streams. * Action: Increase 
update_compaction_num_threads_per_disk
from 4 to 6. * Action: Increase 
compact_threads
to 24 (to allow the global pool to accommodate $6 \times 4$ disks). * Why: If your tablets are not perfectly distributed across all 4 disks, a single "hot" disk might be stuck at 4 threads while the others are idle. Increasing the per-disk limit allows the BE to be more aggressive on the specific volumes where the data is landing. 3. Shorten the "Check" Intervals Even with the 
min_interval
at 30s, the BE might not be scanning for new work frequently enough. * Action: Check 
update_compaction_check_interval_seconds
(Default is often 2-5s). Ensure it is not set higher. * Action: Set 
max_cumulative_compaction_num_singleton_deltas = 100
. _ _Note:* By merging fewer segments at a time, each compaction task finishes faster. This cycles the threads back into the pool more quickly, allowing the scheduler to pick up the next "Version 45" tablet sooner. 4. Adjust the Scoring "Greediness" If the BE thinks a compaction task isn't "worth it," it won't run it even if threads are free. * Action: Set 
update_compaction_ratio_threshold = 0.1
(Default 0.5). * This makes the BE more willing to compact even if the new rowset is small relative to the base data. * Action: Set 
update_compaction_delvec_file_io_amp_ratio = 10
(Default 2). * This forces the BE to prioritize tablets that have many "Delete Vectors," which is likely what is keeping your score at 45. Updated Action Plan (SQL Commands) Run these on your cluster to force the BEs out of "lazy" mode:
Copy code
sql
-- Allow more concurrent tasks globally
UPDATE information_schema.be_configs SET VALUE = '24' WHERE NAME = 'compact_threads';

-- Allow more tasks per physical EBS volume
UPDATE information_schema.be_configs SET VALUE = '6' WHERE NAME = 'update_compaction_num_threads_per_disk';

-- Prioritize tablets with delete-vector versions (common in PK tables)
UPDATE information_schema.be_configs SET VALUE = '10' WHERE NAME = 'update_compaction_delvec_file_io_amp_ratio';

-- Make tasks smaller and faster to cycle threads
UPDATE information_schema.be_configs SET VALUE = '100' WHERE NAME = 'max_cumulative_compaction_num_singleton_deltas';
What to watch for: Watch your Disk IOPS and Throughput in CloudWatch or Grafana. If you hit the EBS burst limit, your latency will spike. If CPU stays under 50% and EBS metrics look healthy, you can even push 
update_compaction_num_threads_per_disk
to 8. References • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky But didn't you previoulsy mention cumulative compaction threads shouldn't exceed number of cores?
r
You are correct to catch that. The general best practice for a stable production cluster (handling both heavy ingestion and heavy queries) is to set 
compact_threads
to about 25-50% of your CPU cores. The reason for that rule is to prevent compaction from "starving" your SQL queries of CPU resources during peak hours. Why I suggested exceeding that rule in your case: You are in a specific troubleshooting state where: 1. CPU is "Lazy": You have 16 cores but are only using ~3 cores worth of power (20%). 2. Backlog is High: Your compaction score is 45 (well above the healthy target of <10). 3. I/O Bound vs. CPU Bound: Compaction on Primary Key tables involves reading/writing to EBS and checking Bloom filters. These threads spend a significant amount of time in "I/O Wait" (waiting for the disk). When threads are waiting for I/O, they aren't using the CPU. Therefore, having 24 threads on 16 cores does not mean you are oversubscribing the CPU; it just means you are allowing the BE to have more "irons in the fire" so that while 10 threads are waiting for EBS to respond, the other 14 can still be processing data. The Nuance: Global vs. Per-Disk There is a distinction between the different thread pools that often causes confusion: * 
compact_threads
(Global Pool): This is the "Master Ceiling" for all compaction types (Cumulative, Base, and PK). If this is 12, no more than 12 tasks will run, regardless of other settings. * 
update_compaction_num_threads_per_disk
(PK Specific): Since you have 4 disks, setting this to 4 creates a potential for 16 threads ($4 \times 4$). If I previously said "don't exceed the number of cores," I was likely referring to the sum of all active worker pools to avoid context-switching overhead. However, since your CPU busy util is only 20%, you have a massive amount of "thermal headroom." Updated Advice for your 16-core / 4-disk nodes: If you want to clear that score of 45 quickly without hitting a CPU bottleneck: 1. Set 
compact_threads
to 16 (1:1 with cores). This is a safe "aggressive" setting. 2. Set 
update_compaction_num_threads_per_disk
to 4. 3. Watch the CPU: If it jumps from 20% to 50-60%, stay there. If it stays at 20%, you are being bottlenecked by I/O throughput or the compaction interval, not the number of threads. Summary: The "25% of cores" is a rule for sustained health. When you have a backlog spike and idle CPU, you should temporarily increase these limits to 100% of cores (or slightly above if I/O wait is high) to "flush" the system. Once the score is back under 10, you can scale them back down to the conservative 25-50% range. References • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md • administration/management/BE_configuration.md
m
@Rocky What are the key metrics I should observe as I tune these configurations
r
To effectively tune your cluster and move past the "lazy" phase, you need to monitor metrics that reveal whether your BE is throttled by configuration, stalled by I/O, or waiting on memory. As you increase 
compact_threads
and 
update_compaction_num_threads_per_disk
, watch these four categories of metrics: 1. The "Backlog" Metrics (Are you winning?) These tell you if your changes are actually reducing the work queue. * 
tablet_update_max_compaction_score
: (The one you're already watching). If this doesn't trend down toward 10, your ingestion is still outpacing your compaction. * 
pk_index_compaction_queue_count
: If this is high (>0 consistently), it means tablets are waiting for a thread to become available. If this is 0 but your score is 45, it means the BE is choosing not to schedule the task (check your 
min_interval
or 
ratio_threshold
). * 
information_schema.be_compactions (CANDIDATE_MAX_SCORE)
: Use this to see if the "45" is just one tablet or if many tablets are hovering at high scores. 2. The "Utilization" Metrics (Is it actually working?) These confirm if the BE is utilizing the threads you've granted it. * 
running_update_compaction_task_num
: This should now be hitting your 
compact_threads
ceiling. If you set 
compact_threads = 16
but this metric only shows 
4
, the BE is being throttled by another setting (like 
update_compaction_per_tablet_min_interval_seconds
). * 
be_base_compaction_rowsets_per_second
: This measures throughput. As you tune, you want to see this number increase. 3. The "Hardware Bottleneck" Metrics (The "Why" behind 20% CPU) If CPU is low but the score is high, you are likely hitting an external wall: * Disk I/O Wait (
iostat -x
or CloudWatch): Look for 
%util
 on your disks. If your EBS volumes are at 90-100% utilization, adding more threads will not help; it will just increase latency. * EBS Burst Balance/IOPS: If you are using AWS GP3, ensure you haven't exhausted your IOPS or throughput credits. * 
mem_tracker
(Type: update): PK tables keep the primary key index in memory. If the BE is hitting its memory limit, it may pause compaction to avoid an OOM. * Check: 
http://<BE_IP>:8040/mem_tracker?type=update
4. The "Internal Throttling" Logs If metrics look "fine" but the system is still slow, check for these messages in the 
<http://be.INFO|be.INFO>
 log or via SQL: * Ingestion Slowdown: Run 
SHOW PROC '/transactions/<db_name>/running';
. * If the 
ErrMsg
field says 
Partition's compaction score is larger than 100.0, delay commit
, StarRocks is intentionally slowing down your inserts to let compaction catch up. * Compaction Trace: If a single compaction task takes longer than 
compaction_trace_threshold
(default 60s), it will print a "Trace" in the logs. This will tell you exactly which phase (reading, filtering, or writing) is the bottleneck. Summary Checklist for your next "Push": 1. Monitor 
running_update_compaction_task_num
: Does it equal your 
compact_threads
? 2. Monitor Disk Latency: Is it staying under 10-20ms? 3. Check 
pk_index_compaction_queue_count
: If this is 0 but scores are high, lower your 
update_compaction_per_tablet_min_interval_seconds
further (try 10s). Rocky's Pro-Tip: If your CPU stays at 20% even after increasing threads, the bottleneck is almost certainly Disk I/O throughput (the speed at which data moves to/from EBS) or a Concurrency Lock on specific hot tablets. References • administration/management/monitoring/metrics.md • administration/management/BE_configuration.md • sql-reference/information_schema/be_compactions.md • administration/management/compaction.md
m
I set compact _threads to 12. be_running_update_compaction_task_num averages 2, peaks 6 sometimes. 
pk_index_compaction_queue_count
always 0 average compaction score from DB jumps between 30-70 now. More often than not it is above 50 during the concurrent flushes. 
update_compaction_per_tablet_min_interval_seconds
set to 30 seconds BE memory usage is healthy All EBS volumes have ~99% time spent idle. So best option now is to lower the compaction min interval and ratio threshold? They're 30 seconds and 0.5 respectively now. @Rocky
r
Since your CPU and EBS disks are both heavily underutilized (20% CPU, 99% Disk Idle) and your 
pk_index_compaction_queue_count
is 0, the StarRocks BE is essentially deciding not to work. It isn't being blocked by resources; it's being blocked by its own "worthiness" and "safety" logic. Your score of 30-70 means you have a significant backlog of rowset versions. To force the BE to be more aggressive, you should focus on the scheduling triggers. 1. Lower the "Patience" (Min Interval) Even at 30 seconds, if you are performing frequent flushes (e.g., every 5-10 seconds), the BE will skip the tablet for several flush cycles. * Recommendation: Set 
update_compaction_per_tablet_min_interval_seconds = 5
. * Why: In high-concurrency ingestion, 30 seconds is an eternity. Lowering this to 5s or 10s ensures that as soon as a tablet hits a "messy" state, it becomes eligible for a thread immediately. 2. Lower the "Gain" Threshold (Ratio) The 
update_compaction_ratio_threshold
(Default 0.5) acts as a "is this worth it?" check. It compares the size of the rowsets to be merged against the total size of the tablet. * Recommendation: Set 
update_compaction_ratio_threshold = 0.1
(or even 
0.01
if your data is very large). * Why: If you have 10GB of base data and you just flushed 10MB, the ratio is 0.001. At a threshold of 0.5, the BE will ignore this "tiny" update even if your version count is climbing. Lowering this forces the BE to merge small updates into the base data much sooner. 3. Tighten the Check Cycle The BE only looks for new compaction work every few seconds. * Action: Check 
update_compaction_check_interval_seconds
. * Recommendation: Set it to 
2
(Default is usually 10). * Why: This reduces the "sleep" time of the background scheduler, making it more responsive to the scores jumping to 70. 4. Increase "Delvec" Priority Since you are using Primary Key tables, "Delete Vectors" are a major source of read amplification. * Action: Increase 
update_compaction_delvec_file_io_amp_ratio
. * Recommendation: Set it to 
10
or 
20
(Default 2). * Why: This artificially inflates the "importance" of tablets with many updates/deletes, moving them to the front of the line for the 
compact_threads
. Suggested SQL to "Wake Up" the BE:
Copy code
sql
-- Trigger compaction much more frequently
UPDATE information_schema.be_configs SET VALUE = '5' WHERE NAME = 'update_compaction_per_tablet_min_interval_seconds';

-- Merge even tiny updates into the base data
UPDATE information_schema.be_configs SET VALUE = '0.1' WHERE NAME = 'update_compaction_ratio_threshold';

-- Check for new work every 2 seconds instead of 10
UPDATE information_schema.be_configs SET VALUE = '2' WHERE NAME = 'update_compaction_check_interval_seconds';

-- Prioritize tablets with high update/delete volume
UPDATE information_schema.be_configs SET VALUE = '20' WHERE NAME = 'update_compaction_delvec_file_io_amp_ratio';
Why your 
pk_index_compaction_queue_count
is 0: This metric specifically tracks the Index compaction pool (merging the PK indexes). If this is 0 but your score is high, it confirms the bottleneck is in the Data compaction (merging rowsets), which uses the general 
compact_threads
. Final Check: If you apply these and 
be_running_update_compaction_task_num
still stays at 2, then look at your FE configurations. If this is a shared-data (lake) cluster, the FE might be limiting the number of concurrent tasks globally via 
lake_compaction_max_tasks
. References • administration/management/BE_configuration.md • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky mine is a shared nothing architecture. update_compaction_check_interval_seconds vs update_compaction_per_tablet_min_interval_seconds. Could you explain the difference? I think I should start with these 2 configuration and see if it helps the score? My transaction publish latency is healthy (~180ms p95) but merge commit load latency p95 is now ~20 seconds. Merge commit interval still 5000ms. BE compaction tasks is horribly low , most of the time stays below 2 per node.
r
In a Shared-Nothing architecture with Primary Key tables, these two parameters act as the Scheduler's Alarm Clock vs. the Tablet's Cooldown Timer. Since you are using Merge Commit with a 5000ms (5s) interval, your configuration is currently "starving" your tablets of compaction opportunities. 1. The Difference Explained Parameter | Meaning | Analogous To... 
update_compaction_check_interval_seconds
 | How often the BE background thread "wakes up" to look for any tablets that need work. | The Guard's Rounds: Every 10s, the guard walks past all the cells (tablets) to see if anyone is calling for help. 
update_compaction_per_tablet_min_interval_seconds
 | The "Lockout" period for a specific tablet. After Tablet A is compacted, it cannot be touched again for this long. | The Cooldown: After a tablet gets "medical attention," it's banned from the clinic for 30s, even if it gets injured again immediately. Why your BE tasks are "Horribly Low": You are flushing data every 5 seconds. 1. A tablet gets compacted. 2. 5 seconds later, a new version arrives. Score goes up. 3. 5 seconds later, another version arrives. Score goes up. 4. The tablet is now eligible for compaction again, BUT it is locked out for 30 seconds. 5. By the time the 30s "cooldown" expires, that tablet has accumulated 6 new versions. 6. This cycle repeats, keeping your "average score" high (around 50-70) while your threads sit idle because they are legally forbidden from helping that specific tablet until the timer hits 0. 2. Why is "Merge Commit Latency" high but "Publish" low? * Transaction Publish Latency (~180ms): This is just the "handshake" between the BE and FE confirming the data is safely on disk. It doesn't care about the state of the table. * Merge Commit Load Latency (~20s): This includes the Apply Phase. For Primary Key tables, every load must check the PK Index to see if the new rows are inserts or updates. * The Bottleneck: When your compaction score is 50-70, it means the "Apply" process has to scan many small rowsets and delete-vector files to ensure data consistency. * The Result: High version counts (high compaction score) directly cause high load latency because the "Apply" work becomes 10x harder. ────────── Recommended Strategy You have plenty of CPU and Disk I/O headroom. You should "break the safety glass" on these throttles: 1. Lower 
update_compaction_per_tablet_min_interval_seconds
to 5: * Matches your merge commit interval. This allows a "hot" tablet to be compacted as soon as the next flush arrives if the score is high. 2. Lower 
update_compaction_check_interval_seconds
to 2: * Makes the BE more responsive to score spikes. 3. Lower 
update_compaction_ratio_threshold
to 0.1: * The default 0.5 is often too "picky" for large tables. If your tablet is 100GB, it might refuse to merge a 100MB flush because 100MB/100GB < 0.5. Setting this to 0.1 forces the merge. SQL to apply immediately:
Copy code
sql
ADMIN SET FRONTEND CONFIG ("tablet_stat_update_interval_second" = "30"); -- FE sees scores faster

-- Run these on each BE or via a global config change
UPDATE information_schema.be_configs SET VALUE = '5' WHERE NAME = 'update_compaction_per_tablet_min_interval_seconds';
UPDATE information_schema.be_configs SET VALUE = '2' WHERE NAME = 'update_compaction_check_interval_seconds';
UPDATE information_schema.be_configs SET VALUE = '0.1' WHERE NAME = 'update_compaction_ratio_threshold';
What to expect: Within minutes, your 
be_running_update_compaction_task_num
should jump from 2 to 8-12. Once the "backlog" of scores drops below 10, your Merge Commit Load Latency will drop significantly because the "Apply" phase only has to look at 1 or 2 rowsets instead of 50. References • administration/management/BE_configuration.md
m
@Rocky 
update_compaction_ratio_threshold
what ratio is this? Im stream loading 1.5 GB per batch
r
The 
update_compaction_ratio_threshold
is a safety throttle (a "maximum" limit) rather than a trigger. Here is the breakdown of what it means and how it interacts with your 1.5 GB stream load. 1. What is the Ratio? The ratio is defined as: 
Ratio = (Total Size of Rowsets to be Merged) / (Total Size of the Tablet)
 * The Default (0.5): This means StarRocks is allowed to merge rowsets that, in total, represent up to 50% of the tablet's total size in a single compaction task. * Why it exists: It prevents the "Small Write, Massive Rewrite" problem. If you have a 100GB tablet and you just added 10MB of data, you don't want the BE to rewrite 100GB of data just to incorporate 10MB. That would be a waste of I/O. * In Shared-Nothing: While the documentation often highlights this for shared-data, in recent versions (v3.1.10+ and v3.2.5+), it is a BE configuration that helps control write amplification in Primary Key tables. 2. How your 1.5 GB Batch fits in Since you are loading 1.5 GB per batch, the impact on this ratio depends entirely on your number of tablets (buckets): * Scenario A (Few Tablets): If your 1.5 GB goes into 10 tablets, each tablet gets 150 MB. If the tablet is 1 GB total, the ratio is 0.15. This is well below the 0.5 threshold, so compaction is allowed. * Scenario B (Many Tablets): If your 1.5 GB is spread across 500 tablets, each tablet only gets ~3 MB. If the tablet size is 10 GB, the ratio is 0.0003. 3. The "Real" Bottleneck: 
update_compaction_size_threshold
 If your BE is "lazy" (tasks < 2) despite a high score, the issue is likely not the ratio, but how the Compaction Score is calculated for Primary Key tables. For PK tables, the score isn't just the "number of files." It is calculated as: 
Score = Rowset Size / update_compaction_size_threshold
 _(Default 
update_compaction_size_threshold
= 256 MB)_ * The Problem: If your 1.5 GB batch is split into 100 tablets, each tablet gets a 15 MB rowset. * The Calculation: $15\text{ MB} / 256\text{ MB} = \mathbf{0.06}$ points per load. * The Result: You would need to perform 160 loads (at 15 MB each) before a tablet even hits a score of 10 (the minimum to start compaction). Recommendations for your 1.5 GB Stream Load: If your score is high (30-70) but the BE isn't picking up tasks, it means the BE thinks the tasks are "too small to be worth the effort" or it's hitting the interval lockout. 1. Lower `update_compaction_size_threshold`: If you want the BE to be more aggressive with smaller flushes, lower this from 256MB to 64MB or 128MB. _ _Effect:* This inflates the compaction score, making the BE treat smaller files as "more urgent." * 
UPDATE information_schema.be_configs SET VALUE = '67108864' WHERE NAME = 'update_compaction_size_threshold';
2. Keep 
update_compaction_ratio_threshold
at 0.5 (or higher): Contrary to the previous suggestion, if you want to merge a lot of small versions into a large base file, you want this ratio to be large (e.g., 0.8) so the BE doesn't "give up" on large tablets. 3. Check the "Minimum Worthiness": Ensure 
update_compaction_per_tablet_min_interval_seconds
is low (5s-10s) so the 1.5 GB batches don't stack up while the tablet is "cooling down." Summary: Your 1.5 GB batch is large, but once partitioned into tablets, the individual rowsets might be too small to trigger the "lazy" default settings. Lower the 
size_threshold
 to make the BE respect your 1.5 GB loads as "significant work." References • administration/management/BE_configuration.md • best_practices/primarykey_table.md • administration/management/compaction.md
m
@Rocky these configuration changes really helped. Compaction score now peaks ~55. Averaging 20-30. But my compaction tasks are still nowhere near 12 per node. It almost never goes beyond 3. CPU utlization averaging slightly below 30% too. On a different note, what can I use also as a primary indicator of ingestion health in the cluster? I've been strictly trying to lower compaction score as my primary goal but was thinking other things I should consider
r
It is great to hear the score is dropping. The reason your compaction tasks are still "horribly low" (around 2–3) despite having 12 
compact_threads
is that for Primary Key tables in Shared-Nothing, there is a secondary "throttle" that acts as a multiplier based on your hardware. 1. The Limiter: 
update_compaction_num_threads_per_disk
 In StarRocks, Primary Key compaction tasks are constrained by the number of data disks you have configured. * The Config: 
update_compaction_num_threads_per_disk
(Default: 1) * The Logic: 
Max Tasks = (Number of Data Directories) * update_compaction_num_threads_per_disk
If your BE nodes have only 2 or 3 EBS volumes (or a single local disk), the BE will ignore your 
compact_threads = 12
and cap the tasks at 2 or 3 to prevent "disk thrashing." Recommendation: Since your EBS volumes are 99% idle, you can safely increase this.
Copy code
sql
-- Increase threads allowed per disk for PK tables
UPDATE information_schema.be_configs SET VALUE = '4' WHERE NAME = 'update_compaction_num_threads_per_disk';
If you have 3 disks, this will allow up to 12 concurrent tasks (3 disks * 4 threads). This should finally saturate your 
compact_threads
. ────────── 2. Primary Indicators of Ingestion Health (Beyond the Score) While the Compaction Score is a great "backlog" indicator, it doesn't tell the whole story. For a high-concurrency PK table environment, you should monitor these four metrics to ensure ingestion isn't "secretly" suffering: A. Apply Latency (
update_rowset_commit_apply_duration_us
) * What it is: The time it takes for a newly flushed rowset to be "applied" (merging PKs and generating delete vectors). * Healthy Range: < 1 second. * Warning: If this starts climbing (e.g., your ~20s load latency), it means your compaction is too slow, and the "Apply" thread is struggling to scan through too many old versions. B. Primary Index Memory (
update_mem_bytes
) * What it is: The RAM consumed by the PK indexes. * Health Check: If this approaches your BE memory limit, StarRocks will start evicting indexes to disk (if 
enable_persistent_index
is true) or fail loads (if false). * Note: If you see 
pk_index_compaction_queue_count
climb above 0, it means your index-specific compaction is falling behind. C. Read Amplification (Delete Vector Size) * How to check: Monitor the size of 
.del
files or the metric 
update_del_vector_num
. * Health Check: In PK tables, updates don't change the original data file; they mark rows as deleted in a "Delete Vector." If your compaction score is low but query performance is slow, you likely have massive Delete Vectors that haven't been "physically" merged yet. D. Transaction Success Rate * What to look for: Search BE logs for 
too many versions
or 
too many rowsets
. * The Limit: 
tablet_max_versions
(Default 1000). * Health Check: If your score is 55, you are safe. If it ever hits 500+, you are at risk of immediate ingestion failure. Summary Checklist for you: 1. Bump 
update_compaction_num_threads_per_disk
to 4 or 6. 2. Monitor 
be_running_update_compaction_task_num
(Should now hit 8-12). 3. Watch your Merge Commit Load Latency. It should drop from 20s to < 2s once the backlog is cleared. References • administration/management/BE_configuration.md • best_practices/primarykey_table.md • administration/management/monitoring/metrics.md References • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky My shared nothing architecture already has 
update_compaction_num_threads_per_disk
and compact_threads set to 12. CPU util still averaging 30%. What's the limitation now
r
If you have already set 
update_compaction_num_threads_per_disk
and 
compact_threads
to 12, but the system is still only running ~3 tasks, you are likely hitting one of the secondary "safety valves" StarRocks uses to prevent compaction from overwhelming the system. In a Shared-Nothing architecture with Primary Key tables, there are three specific areas where your "tasks < 3" bottleneck is likely hiding: 1. The "Permit" System (
total_permits_for_compaction_score
) StarRocks uses a permit system to decide if it can afford to start a new compaction task. Every tablet has a compaction score. When a task starts, it "consumes" a number of permits equal to its score. * The Config: 
total_permits_for_compaction_score
(Default: 100) * The Math: If your tablets have an average score of 30, then 3 tasks consume 90 permits. If the 4th tablet has a score of 15, the BE will refuse to start it because 90 + 15 > 100. * Why it's there: To prevent a few extremely "unhealthy" tablets (high score) from spawning many tasks that crash the BE. * Fix: Increase this to 500 or 1000 to allow more simultaneous "medium-score" tasks.
Copy code
sql
    UPDATE information_schema.be_configs SET VALUE = '1000' WHERE NAME = 'total_permits_for_compaction_score';
2. Disk Concurrency Limit (
pindex_major_compaction_limit_per_disk
) Even if you allow 12 threads per disk via the update config, StarRocks has a separate, more global limit on Major Compaction (which Primary Key compaction essentially is) to prevent disk I/O from stalling user queries. * The Config: 
pindex_major_compaction_limit_per_disk
(Default: 1) * The Math: If you have 3 data directories (disks) configured on your BE, and this parameter is set to 1, you will never see more than 3 concurrent tasks on that node, regardless of your thread settings. * Fix: Increase this to match your performance needs (e.g., 4 or 8).
Copy code
sql
    UPDATE information_schema.be_configs SET VALUE = '4' WHERE NAME = 'pindex_major_compaction_limit_per_disk';
3. Memory Reservation (
compaction_memory_limit_per_worker
) StarRocks estimates how much memory a compaction task will need. If the sum of these estimates exceeds the total memory allocated for compaction, no new tasks will start. * The Configs: * 
compaction_memory_limit_per_worker
(Default: 2GB) * 
compaction_max_memory_limit_percent
(Default: 100% of process memory) * The Math: If your BE has 8GB of memory available for compaction, and each worker is "reserving" 2GB, the BE will hard-cap you at 4 workers, even if they aren't actually using that much RAM. * Fix: Check your BE memory. If you have a large BE, this is rarely the issue. If you are on smaller nodes (e.g., 16GB), lower the 
per_worker
limit slightly (e.g., to 1GB). ────────── Recommended Next Steps Run these commands to see which one "unclogs" your worker queue: 1. Release the Permits (Most Likely):
Copy code
sql
    UPDATE information_schema.be_configs SET VALUE = '1000' WHERE NAME = 'total_permits_for_compaction_score';
2. Increase Disk Concurrency:
Copy code
sql
    UPDATE information_schema.be_configs SET VALUE = '4' WHERE NAME = 'pindex_major_compaction_limit_per_disk';
Ingestion Health Indicators (Beyond Score) As you requested for other health metrics, once you fix the task count, monitor these to ensure you aren't "over-compacting": 1. 
update_rowset_commit_apply_duration_us
: This is the heart of PK table performance. If this stays low (e.g., < 500ms), your compaction is doing its job. If it spikes, your tablets are too fragmented. 2. 
be_compaction_mem_bytes
: Check this in Grafana. If compaction memory usage is flat but tasks are low, you are hitting a thread/permit limit. If it's hitting a ceiling, you're hitting a memory limit.
3. 
p50_compaction_score
vs 
max_compaction_score
: If your Max is 55 but your P50 is 2, only a few tablets are "hot." You don't need 12 threads for that. If both are ~30, you need higher concurrency (the settings above). References • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md
m
@Rocky total_permits_for_compaction_score doesn't exist in the be_configs table.
r
I apologize for the confusion—`total_permits_for_compaction_score` is a parameter found in some related storage engines but is not present in StarRocks. My mistake on that specific name. The reason your compaction is stuck at ~3 tasks despite having 
compact_threads
and 
update_compaction_num_threads_per_disk
set to 12 is likely due to a different disk-level throttle that acts as a hard cap. The Actual Bottleneck: 
pindex_major_compaction_limit_per_disk
 In StarRocks Primary Key tables, the system limits how many "major" compaction tasks (which includes the primary key compaction you are seeing) can run per physical data directory to avoid saturating disk I/O. * The Parameter: 
pindex_major_compaction_limit_per_disk
* Default: 
1
* The Logic: 
Total Concurrent Tasks = (Number of Data Directories) * pindex_major_compaction_limit_per_disk
If your BE nodes are configured with 3 data directories (e.g., 
/data1
, 
/data2
, 
/data3
), StarRocks will only allow 3 concurrent tasks total, even if you have 100 idle threads. Since you mentioned seeing exactly 3 tasks, this is almost certainly the limit you are hitting. The Fix: You should increase this value. Since your CPU is only at 30% and your EBS volumes are healthy, try increasing it to 4 or 8:
Copy code
sql
UPDATE information_schema.be_configs 
SET VALUE = '4' 
WHERE NAME = 'pindex_major_compaction_limit_per_disk';
(If you have 3 disks, this will allow up to 12 concurrent tasks, matching your thread count.) ────────── Other "Invisible" Limits to Check If increasing the disk limit doesn't immediately scale the tasks to 12, check these two parameters in your `be_configs`: 1. 
max_compaction_concurrency
: * Ensure this is set to 
-1
(default) or at least 
12
. If someone manually capped this to 
3
, it will override everything else. 2. 
compaction_memory_limit_per_worker
: * StarRocks "reserves" memory for each task before starting it. The default is 2GB. _ If your BE has a strict memory limit, the BE might be refusing to start a 4th task because 
4 _ 2GB = 8GB
might exceed the allowable compaction memory pool. _ _Check:* 
be_compaction_mem_bytes
in your metrics. If it’s high, you might need to lower the per-worker reservation to 
1GB
. Summary Recommendation Your hardware has plenty of headroom. To get those 12 threads working: 1. Increase 
pindex_major_compaction_limit_per_disk
to 4. 2. Verify 
max_compaction_concurrency
is -1. 3. Monitor 
be_running_update_compaction_task_num
again. It should now scale up to your 
compact_threads
limit. References • administration/management/BE_configuration.md • best_practices/primarykey_table.md
m
@Rocky I have 4 EBS volumes attached per node. So if I allowed 4 update compaction threads per disk, and 12 compact threads configuration, then pindex_major_compaction_limit_per_disk should be set to 3 right?
r
Your logic is spot on. If you have 4 EBS volumes (data directories) and you want to hit a total of 12 concurrent tasks, then setting 
pindex_major_compaction_limit_per_disk
to 3 (4 disks * 3 = 12) is the correct mathematical configuration to align with your 
compact_threads = 12
. Why you were seeing 3 tasks instead of 4 If you have 4 disks and the limit was set to the default of 
1
, you should have seen 4 tasks. The fact that you saw 3 suggests one of the following: 1. Data Skew: Your "hot" tablets (those with high compaction scores) might only be residing on 3 of the 4 disks at that moment. StarRocks will not pull a task from Disk D just because Disk A, B, and C are busy. 2. Storage Path Config: Double-check your 
be.conf
for the 
storage_root_path
. Ensure all 4 EBS volumes are explicitly listed and that none have run out of space (StarRocks will stop scheduling tasks to a disk if it hits the 
capacity_used_total_confirm_limit
or is nearly full). The "Golden Triangle" for PK Compaction To ensure you actually reach 12 tasks, these three parameters must "agree": Parameter | Recommended Value | Reason 
compact_threads
| 12 | The global "ceiling" for the BE. 
update_compaction_num_threads_per_disk
| 4 (or 3) | Allows the PK thread pool to scale per disk. 
pindex_major_compaction_limit_per_disk
| 3 | The "concurrency" gatekeeper that prevents disk I/O bottlenecks. Important Note on Memory: Since you are pushing for 12 concurrent tasks, watch your BE memory. StarRocks reserves memory for each compaction task. * Check 
compaction_memory_limit_per_worker
(Default is 2GB). _ 12 tasks _ 2GB = 24GB of RAM reserved just for compaction. * If your BEs have less than 32GB-64GB of RAM, the system might refuse to start the 12th task because it would exceed the 
compaction_max_memory_limit_percent
(which is a percentage of your total BE 
mem_limit
). How to verify it’s working After applying 
pindex_major_compaction_limit_per_disk = 3
, check the internal metrics:
Copy code
sql
-- Check how many tasks are actually running now
SHOW PROC '/be_metrics' LIKE '%compaction_task_num%';
You should see 
be_running_update_compaction_task_num
start to climb toward 12. If it still hovers at 3, check the BE log (
<http://be.INFO|be.INFO>
) for messages containing 
memory limit
or 
no enough permits
, which would indicate the memory reservation is the final hurdle. References • administration/management/BE_configuration.md • administration/management/compaction.md • best_practices/primarykey_table.md References • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/monitoring/metrics.md • administration/management/FE_configuration.md • administration/management/compaction.md • administration/management/BE_configuration.md
m
@Rocky My inserts are roughly 50k QPS and upserts are roughly 4k QPS, and inserts are split across 6 nodes. Each node has 4 workers draining from a work channel waiting for their buffer to reach 1.5 GB before stream load to starrocks. Now, what should the flushing configuration look like for upserts too, given that I also dont want to increase compaction pressure.
r
To balance your high ingestion rates (50k QPS inserts + 4k QPS upserts) without overloading the compaction system, you need to align your client-side flushing logic with StarRocks' internal "Apply" and "Compaction" mechanics. Since you are already using a 1.5 GB buffer, you are following the "large batch" best practice. However, 1.5 GB might be too large for the 4k QPS upsert stream if it causes the data to be hours old before flushing. Here is the recommended flushing configuration and system tuning for your scenario: 1. The "Dual-Trigger" Flush Strategy For the 4k QPS upsert stream, a 1.5 GB buffer will likely take too long to fill. If each of your 24 workers (6 nodes * 4 workers) gets an even share, each worker is only seeing ~166 rows/second. * Recommendation: Use a Size OR Time trigger. * Size: 100MB to 500MB. (1.5GB is excellent for throughput but can cause "stuttering" in the PK Index Apply phase). * Time: 30 to 60 seconds. * Why: StarRocks Primary Key tables perform an "Apply" phase (mapping keys to row locations) during the commit. A 1.5GB batch of upserts creates a massive "Apply" task that can spike CPU and hold locks. Smaller (but still "big") batches of ~200MB are often smoother for PK tables than one giant 1.5GB batch. 2. Enable "Merge Commit" (StarRocks v3.4.0+) If you can upgrade to v3.4.0, this is the most important setting for your architecture. It allows multiple Stream Load workers to send data independently while the FE/BE merges them into a single transaction and a single data version. * Client Header: 
enable_merge_commit: true
* Benefit: Even if your 24 workers flush frequently (to keep latency low), StarRocks will group them into one version. This drastically reduces the compaction score because you generate 1 version per 5-10 seconds instead of 24 versions per flush cycle. 3. Tune the PK "Apply" Workers Since you are doing upserts, the bottleneck is often not the disk I/O of compaction, but the Apply thread pool that processes the Primary Key index. * Config: 
transaction_apply_worker_count
(Default: CPU cores) * If your CPU is at 30%, you have headroom. If you see "slowdown" messages in 
SHOW PROC '/transactions/<db>/running'
, increase this to allow more concurrent upsert processing. 4. Adjust Compaction "Frequency" vs "Wait" In Shared-Nothing architecture, StarRocks won't compact a tablet too frequently by default to save resources. * 
update_compaction_per_tablet_min_interval_seconds
: Default is 120 (2 minutes). * If your version count (compaction score) is still high despite large batches, increase this to 
300
(5 minutes). This forces the system to wait longer and collect even more versions into a single "Mega-Compaction" task, which is much more efficient for the CPU. 5. Persistent Index (Crucial for Memory) Ensure your PK table has 
enable_persistent_index = true
. * At 4k QPS upserts, if the index is in-memory only, your BE memory will grow linearly with the number of unique keys. * The Persistent Index keeps the mapping on disk (EBS) and only caches the "hot" parts in RAM, which prevents ingestion from crashing the BE as your dataset grows. Summary Checklist for your Workers: 1. Inserts (50k QPS): Keep the 1.5GB / 5-minute flush. These are "append-only" and very cheap for StarRocks. 2. Upserts (4k QPS): Move to a 256MB / 60-second flush. This prevents giant "Apply" spikes while still being large enough for efficient compaction. 3. Global: Add 
enable_merge_commit: true
to your Stream Load headers to let StarRocks handle the final grouping. References • best_practices/primarykey_table.md • loading/Flink-connector-starrocks.md • administration/management/compaction.md
m
@Rocky For data insertion, can I use omitempty in the golang struct json annotation for nullable rows? SO payload omits field instead of "field": NULL.
r
Yes, you can absolutely use 
omitempty
in your Go structs. When you use 
omitempty
, the field is completely removed from the JSON payload if it is a "zero value." When StarRocks performs a Stream Load and encounters a JSON record where a key is missing, it follows these rules: 1. Nullable Columns: If the column in StarRocks is defined as 
NULLABLE
, StarRocks will automatically fill that column with 
NULL
for the missing key. 2. Default Values: If the column has a 
DEFAULT
value defined in the schema, StarRocks will use that default value instead of 
NULL
when the key is missing. 3. Strict Mode: If the column is 
NOT NULL
and has no default value, the row will typically be filtered out as an error (especially if 
strict_mode: true
is set in your Stream Load headers). Important Go Implementation Tip Since you are dealing with nullable rows, be careful with Go's 
omitempty
behavior on primitive types: * Primitive Types (
int
, 
string
, 
bool
): 
omitempty
will omit the field if the value is 
0
, 
""
, or 
false
. This might lead to unintended 
NULLs
in StarRocks if you actually wanted to store a 
0
or an empty string. * *Pointer Types (
_int
, 
_string
, 
*bool
):* This is the recommended way for nullable columns. If the pointer is 
nil
, the field is omitted (resulting in 
NULL
in StarRocks). If the pointer is 
&0
, the field is included as 
"field": 0
(resulting in 
0
in StarRocks). Example Go Struct:
Copy code
go
type User struct {
    ID    int64   `json:"id"`
    // Use pointers for fields that can be NULL in StarRocks
    Age   *int    `json:"age,omitempty"`   
    Email *string `json:"email,omitempty"` 
}
Stream Load Behavior When StarRocks receives: 
{"id": 123}
(Age and Email omitted via 
omitempty
) It internally processes it as: id | age | email 123 | NULL | NULL Performance Note Since you are doing 50k QPS, using 
omitempty
is actually slightly more efficient for your network bandwidth and StarRocks' JSON parser because it reduces the overall payload size compared to sending 
"field": null
for every empty column after column. Summary * Compatible? Yes. * Result? Missing keys → 
NULL
in StarRocks (for nullable columns). * Best Practice: Use pointers in your Go struct to distinguish between a "Zero Value" (like 
0
) and a "Null Value." References • loading/StreamLoad.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md
Open in Slack
PreviousNext
Powered by