http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> system vs user starrocks_be_cpu mod...
# questions-and-troubleshooting
m
@Rocky system vs user starrocks_be_cpu modes. differnetiate them
r
The 
starrocks_be_cpu
metric (exported by the StarRocks Backend) provides a breakdown of CPU time spent in various modes, sourced from the host's 
/proc/stat
. When differentiating between system and user modes, the distinction lies in whether the CPU is executing internal StarRocks code or handling operating system (kernel) tasks: 1. User Mode (
mode="user"
) * Definition: CPU time spent executing code in user space. * What it represents in StarRocks: This is the "actual work" done by the StarRocks process. It includes: * Query execution (filtering, joining, aggregating data). * Data compression and decompression. * Background tasks like compaction and tablet metadata management. * High User CPU: Typically indicates that the cluster is busy processing queries or performing heavy background data maintenance (like compaction). 2. System Mode (
mode="system"
) * Definition: CPU time spent executing code in kernel space (system calls). * What it represents in StarRocks: This is the overhead of the operating system supporting the StarRocks process. Common causes include: * I/O Operations: Significant disk reads/writes or high network traffic (handling sockets). * Context Switching: Too many threads competing for CPU cycles, leading the kernel to spend more time switching between them than executing them. * Memory Management: Intensive page allocations or kernel-level memory handling. * High System CPU: Often suggests a bottleneck outside of the core query logic, such as network congestion, disk latency, or thread contention (too many 
pipeline_exec_thread_num
). Comparison Summary Feature | User Mode (
user
) | System Mode (
system
) Location | User space (StarRocks BE process) | Kernel space (OS level) Primary Driver | Query logic, data processing, compaction | I/O, networking, thread scheduling, interrupts Optimization Focus | SQL tuning, indexing, data modeling | OS tuning, I/O hardware, thread count configuration Usage in Prometheus You will typically see these used together to calculate total CPU utilization. For example, to find the percentage of time the CPU is not idle:
Copy code
promql
# Total CPU Busy % (excluding idle and iowait)
(sum(rate(starrocks_be_cpu{mode!~"idle|iowait"}[5m])) by (instance)) / 
(sum(rate(starrocks_be_cpu[5m])) by (instance)) * 100
_Note: If 
enable_system_metrics
is set to 
false
in your 
be.conf
, these system-level metrics may not be collected._ References • administration/management/monitoring/Monitor_and_Alert.md • administration/management/proc_profile.md • deployment/plan_cluster.md • administration/management/monitoring/alert.md • administration/management/BE_configuration.md
m
@Rocky So ingestion via stream load runs in system and user loads? iowait is itme spent waiting for EBS response? so like for insertion, paertial update, IO contention et.
r
Yes, ingestion via Stream Load consumes both User and System CPU, and your understanding of 
iowait
regarding EBS and IO contention is correct. 1. Stream Load: User vs. System CPU Stream Load is a synchronous, HTTP-based ingestion method. It splits work between the two modes as follows: * User CPU (
mode="user"
): * Parsing & Validation: Converting incoming CSV, JSON, or Parquet data into StarRocks' internal columnar format. * Data Transformation: Executing any 
WHERE
clauses or column mapping expressions defined in the load. * Compression: Compressing data blocks before they are written to disk. * Indexing: Building internal indexes (like zone maps or bitmap indexes) for the new data segment. * System CPU (
mode="system"
): * Network Handling: Stream Load uses HTTP. The kernel spends CPU cycles managing the sockets and moving data from the network interface to the StarRocks process. * VFS / Page Cache: Writing data to the local filesystem involves system calls. The kernel manages the transition of data into the OS page cache. * Context Switching: If you have many concurrent Stream Loads, the kernel spends more time switching between threads. 2. 
iowait
and EBS Performance 
iowait
is specifically the time the CPU is idle while waiting for an outstanding disk I/O request to complete. * EBS Response Time: In an AWS environment, 
iowait
is the primary indicator of EBS latency. If your EBS volume (e.g., gp2, gp3) reaches its IOPS or Throughput limit, the response time for 
write()
or 
fsync()
calls increases, causing the 
iowait
metric to spike. * Contention Sources: * Heavy Insertion: During the "flush" phase of a Stream Load, StarRocks writes data segments to disk. If the write volume exceeds the EBS bandwidth, 
iowait
will rise. * Partial Updates (Primary Key Tables): Partial updates are particularly IO-intensive. Even though they may update only a few columns, the system must often perform "Read-Modify-Write" cycles or manage persistent indexes, leading to higher random IOPS compared to simple append-only inserts. * Compaction: This is the most common cause of high 
iowait
. Background threads constantly read multiple small files and merge them into larger ones to optimize query performance. Compaction is very "heavy" on both disk reads and writes. Summary of CPU/IO States during Ingestion Metric Mode | Action in StarRocks | Practical Meaning User | Parsing, Column Mapping, Compression | The BE is busy "thinking" about the data. System | Networking (bRPC/HTTP), Kernel Flushes | The BE is busy "moving" data through the OS. IOWait | Waiting for EBS/Disk to confirm a write | The BE is "stuck" waiting for the hardware. Rocky's Tip: If you see high 
iowait
during Stream Loads, check the 
iotop
tool on the BE node. If StarRocks is at the top, consider increasing your EBS IOPS/throughput or enabling Load Spilling (introduced in v3.5) to better handle memory-to-disk transitions during large batch loads. References • loading/loading_introduction/troubleshooting_loading.md • administration/management/monitoring/Monitor_and_Alert.md • administration/management/monitoring/alert.md • loading/StreamLoad.md References • administration/management/monitoring/Monitor_and_Alert.md • faq/operation_maintenance_faq.md • administration/management/BE_configuration.md
m
@Rocky Will reducing compaction frequency help partial update speed since reduce EBS contention? Overall better ingestion health? How can I monitor
r
Reducing compaction frequency can indeed reduce EBS contention in the short term, but for Partial Updates on Primary Key (PK) tables, this is a double-edged sword. 1. Does it help partial update speed? Short answer: It depends on where your bottleneck is. * How it helps: Compaction is a background I/O-intensive process. By reducing its frequency, you free up EBS bandwidth and IOPS for the "Write" phase of your Stream Load/Partial Update. This can prevent the "I/O wait" spikes that slow down ingestion. * The Downside (The Trap): Partial updates in StarRocks require the system to locate existing rows to update them. If you reduce compaction, the number of "versions" (rowsets) increases. * Higher Metadata Overhead: Each partial update must check more rowsets/versions, which can actually slow down the update performance over time. * Ingestion Slowdown: If the "Compaction Score" (number of unmerged versions) gets too high, StarRocks will automatically trigger an Ingestion Slowdown (delaying commits) or even reject loads to protect system stability. 2. How to reduce compaction frequency If you want to trade query/update latency for lower EBS contention, you can adjust these parameters: For Primary Key Tables (Shared-nothing): * 
update_compaction_per_tablet_min_interval_seconds
 (BE config): _ _Default: 120*. Increase this (e.g., to 300 or 600) to ensure a single tablet isn't compacted too frequently. * 
tablet_max_versions
 (BE config): _ _Default: 1000*. Increasing this allows more uncompacted versions to exist before StarRocks stops ingestion. This "buys time" during peak load but will eventually need to be cleared. For Shared-data (Cloud Native): * 
lake_compaction_score_selector_min_score
 (FE config): _ _Default: 10.0*. Increase this (e.g., to 30 or 50) so compaction only starts when more data has accumulated. * 
lake_ingest_slowdown_threshold
 (FE config): _ _Default: 100*. Increase this if you want to prevent StarRocks from artificially slowing down your loads when scores are high. 3. How to monitor Ingestion Health To see if compaction is actually the cause of your contention, use these tools: * Check for Ingestion Slowdown:
Copy code
sql
    -- Check if any transactions are being delayed due to high compaction scores
    SHOW PROC '/transactions/<db_name>/running';
Look at the 
ErrMsg
column. If you see 
"Partition's compaction score is larger than 100.0, delay commit"
, compaction is falling behind. * Check Compaction Status:
Copy code
sql
    -- View the history and scores of recent compactions
    SHOW PROC '/compactions';
* Key Prometheus Metrics: * `starrocks_be_compaction_bytes_total`: Total bytes processed by compaction. * `starrocks_be_update_compaction_task_byte_per_second`: Specifically for PK table compaction throughput. * `starrocks_be_disks_iowait`: Monitor this alongside the above to see if 
iowait
spikes align with compaction activity. Summary Recommendation Instead of just reducing frequency, try limiting concurrency first. This keeps compaction running but prevents it from "bursting" and saturating your EBS: * Set 
compact_threads
 (BE/CN config) to a lower value (e.g., 25% of CPU cores) to ensure background tasks don't starve your foreground ingestion for I/O and CPU. References • best_practices/primarykey_table.md • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/BE_configuration.md
m
@Rocky Is it normal to have 10-15% of CPU spent on iowait in my shared nothing cluster? EBS read time is 2-6s, wrtie time is 200ms to 1s. EBS Volume performance throughput and volume iops rarely exceeded, but if so only by single digit milliseconds. Ec2 volume performance throughput never exceeded. These are happening on 32 core r6in.8xlarge machines on shared nothing cluster with 4 EBS gp3 volumes attached, Disk IO util already close to 100%, hovering near there. There's one problematic user that is doing inefficient offset based paginations but I've already limited that resource group to 4 reserved cpu cores only.
r
No, 10-15% 
iowait
on a 32-core machine is not normal for a healthy StarRocks cluster. More importantly, your reported EBS read latencies of 2–6 seconds are extremely high (normal EBS latencies should be in the 1–20ms range). This indicates your storage layer is saturated at the queue level, even if you aren't hitting the provisioned throughput or IOPS limits of the gp3 volumes. 1. Why 
iowait
is high despite low Throughput/IOPS You mentioned Disk IO Utilization is near 100%. This is the "Saturation" metric. In a columnar database like StarRocks: * Small, Random I/O: Queries with high 
OFFSET
or those scanning many small files (due to high compaction scores) generate many small I/O requests. * Queue Depth: Even if you only use 10MB/s of throughput, if you have thousands of tiny read requests per second, the "wait queue" for the disk fills up. The OS reports 100% utilization because the disk is never idle, even if it's not "moving" much data. * EBS "Micro-bursting": AWS metrics often average over 1-minute intervals. You might be hitting the peak IOPS of gp3 in sub-second bursts that CloudWatch doesn't show, leading to those multi-second latencies. 2. The Pagination Problem (The "Smoking Gun") Limiting the user to 4 reserved CPU cores does not limit their I/O impact. * How OFFSET works: To execute 
LIMIT 100 OFFSET 1,000,000
, StarRocks must still read and skip the first 1,000,000 rows from the disk. * I/O Leakage: Even if the query is restricted to 4 cores, those 4 cores can still hammer the disks with read requests as fast as the I/O scheduler allows. * Recommendation: Force that user to use Cursor-based pagination (using a unique key like 
WHERE id > last_seen_id ORDER BY id LIMIT 100
). This allows StarRocks to use its indexes to "jump" to the data rather than scanning everything from the start. 3. Check for "Compaction Debt" High read latency (2-6s) is often caused by the BE trying to read from too many small files simultaneously. * Run 
SHOW PROC '/compactions';
and look at the Compaction Score. * If the score is high (>100), your disk is likely struggling with the "Read-Modify-Write" overhead of background compaction combined with the inefficient pagination queries. 4. Hardware & Configuration Tips * RAID 0 vs. Multiple Paths: Are your 4 gp3 volumes in a software RAID 0? StarRocks performs best when multiple volumes are striped into one large logical drive (using LVM or MDADM). If you listed them as separate paths in 
storage_root_path
, StarRocks manages them, but a single tablet's I/O will still be limited to the performance of a single volume. * EC2 Nitro Limits: r6in.8xlarge has a dedicated EBS bandwidth (around 10Gbps / 1,250 MB/s). Check if 
DiskWriteBytes
and 
DiskReadBytes
in CloudWatch/Prometheus are nearing this limit during your 2-6s latency spikes. * Resource Group I/O: StarRocks v3.x introduced I/O weights in resource groups, but these are primarily for shared-data (Cloud Native) modes. In shared-nothing, the best way to protect the disk is to fix the query logic or lower the 
concurrency_limit
for that specific user. Diagnostic Command Check which threads are causing the I/O wait on the BE node:
Copy code
bash
# See which StarRocks threads are currently stuck in D-state (I/O wait)
ps -eo state,tid,pid,cmd | grep "^D"
If you see many threads in state 
D
, your storage is definitely the bottleneck. References • administration/management/monitoring/Monitor_and_Alert.md • administration/management/compaction.md • best_practices/primarykey_table.md • administration/management/resource_group.md
m
@Rocky Can I see which system process or resource group user is to blame for hgh IO?
r
To identify the specific culprit for high I/O, you need to look at ScanBytes (data read from disk) and Compaction (background merge I/O). StarRocks provides several ways to see this in real-time and historically. 1. Identify "Bad Queries" in Real-Time The most direct way to see which query is hammering your disks right now is using 
SHOW PROC
. * View all running queries and their I/O footprint:
Copy code
sql
    SHOW PROC '/current_queries';
Look at the 
ScanBytes
 column. If a query has a massive 
ScanBytes
relative to its 
ExecTime
, it is likely the cause of your disk saturation. * Deep Dive into a specific QueryId: If you find a query that looks suspicious, see how much I/O it is doing on specific BE nodes:
Copy code
sql
    SHOW PROC '/current_queries/<QueryId>/hosts';
This will show you the 
ScanBytes
per node. If the data is skewed, one node might be at 100% I/O while others are idle. 2. Identify the "Bad User" via Audit Logs If the queries are short-lived but frequent, use the internal audit table to aggregate I/O consumption by user or resource group over a period:
Copy code
sql
-- This requires the internal audit loader to be enabled
SELECT 
    user,
    resource_group,
    SUM(scanBytes) / 1024 / 1024 / 1024 AS total_scan_gb,
    COUNT(*) as query_count,
    AVG(scanBytes) / 1024 / 1024 AS avg_scan_mb
FROM starrocks_audit_db__.starrocks_audit_tbl__
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY user, resource_group
ORDER BY total_scan_gb DESC;
* Why this matters: Your resource group limit of 4 cores only restricts CPU. If a user runs a "Big Query" that scans 1TB of data, it will still saturate the disk IOPS/bandwidth even if it's running on a single core. 3. Check for Background "System" I/O (Compaction) If 
ScanBytes
from user queries is low but 
iowait
is high, background tasks are the culprit. * Check Compaction Scores:
Copy code
sql
    SHOW PROC '/compactions';
Look for tablets with high 
Score
. A high score (e.g., >100) indicates many rowsets need merging. The I/O spent here is "System" I/O and competes directly with user queries for EBS bandwidth. * Check Background Tasks:
Copy code
sql
    SHOW PROC '/tablet_sched/running_tasks';
This shows you if StarRocks is currently moving data for rebalancing or repairs, which is another hidden source of I/O. 4. OS-Level Identification (Thread Level) Since the 
starrocks_be
process is a single entity, 
top
won't help much. Use 
iotop
to find the specific thread types causing I/O:
Copy code
bash
# Run on the BE node
sudo iotop -Pa -b -n 1
* Look at the 
COMMAND
or thread names. * Threads starting with 
pull_
 or 
scan_
 are user query read threads. * Threads named 
compaction_
 are background maintenance. Summary of Actions 1. Fix the Query: If the user is doing 
OFFSET
based pagination, the 
ScanBytes
will be enormous. Force them to use 
WHERE id > last_id
 to leverage indexes and reduce 
ScanBytes
to nearly zero. 2. Add I/O Limits: In StarRocks v3.1+, you can set a 
scan_thread_num
 in the resource group to limit how many concurrent read threads a group can use, which indirectly throttles their disk impact. 3. Increase gp3 Throughput: Since you are at 100% Disk Util, your gp3 volumes might be "stalled" at their baseline throughput (125 MB/s). Increasing the Throughput setting (to 250 or 500 MB/s) on the AWS console is often more effective than increasing IOPS for StarRocks. References • administration/management/monitor_manage_big_queries.md • best_practices/audit_log_resource_group.md • administration/management/resource_management/resource_group.md
• administration/management/compaction.md • administration/management/monitoring/Monitor_and_Alert.md References • administration/management/monitoring/metrics.md • administration/management/monitoring/Monitor_and_Alert.md • administration/management/resource_management/resource_group.md • administration/management/monitor_manage_big_queries.md • best_practices/audit_log_resource_group.md • faq/resource_isolation_faq.md • administration/management/logs.md • knowledge/usage/best-practice.md • administration/management/compaction.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md • loading/loading_introduction/troubleshooting_loading.md • sql-reference/sql-statements/cluster-management/resource_group/SHOW_USAGE_RESOURCE_GROUPS.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md • administration/management/monitoring/alert.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • administration/management/monitoring/metrics.md • administration/management/resource_management/resource_group.md • administration/management/monitoring/Monitor_and_Alert.md • administration/management/monitor_manage_big_queries.md • best_practices/audit_log_resource_group.md • faq/resource_isolation_faq.md • administration/management/logs.md • knowledge/usage/best-practice.md • administration/management/compaction.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md
• loading/loading_introduction/troubleshooting_loading.md • sql-reference/sql-statements/cluster-management/resource_group/SHOW_USAGE_RESOURCE_GROUPS.md • administration/management/monitoring/alert.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • administration/management/monitoring/metrics.md • administration/management/monitoring/Monitor_and_Alert.md • administration/management/resource_management/resource_group.md • administration/management/monitor_manage_big_queries.md • best_practices/audit_log_resource_group.md • faq/resource_isolation_faq.md • administration/management/logs.md • knowledge/usage/best-practice.md • administration/management/compaction.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md • loading/loading_introduction/troubleshooting_loading.md • sql-reference/sql-statements/cluster-management/resource_group/SHOW_USAGE_RESOURCE_GROUPS.md • administration/management/monitoring/alert.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md References • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md
m
@Rocky
Copy code
'ml_zeppelin', 'ml_zeppelin', 1324.5245850216597, 44, 30825.299433231354, 'high_ctr_domain_blacklist', 'high_ctr_domain_blacklist', 765.4502576775849, 4, 195955.26596546173, 'root', 'default_wg', 222.921171923168, 75, 3043.6170673243205, 'elastic_consumer', '', 0, 80, 0, 'ml_zeppelin', '', 0, 220, 0, 'root', '', 0, 8, 0, 'elastic_consumer', 'elastic_consumer', 0, 30, 0, 'high_ctr_domain_blacklist', '', 0, 4, 0, 'db_backup_restore', '', 0, 26, 0
r
Based on the data you provided, we have identified the "smoking guns" for your high I/O wait and multi-second EBS latencies. 1. The Culprit Analysis Here is the breakdown of your audit results (assuming the format: 
User
, 
Resource Group
, 
Total Scan GB
, 
Query Count
, 
Avg Scan MB
): User | Total Scan (GB) | Query Count | ***Avg Scan per Query (MB)*** 
high_ctr_domain_blacklist
 | 765 GB | 4 | 195,955 MB (~191 GB) 
ml_zeppelin
 | 1,324 GB | 44 | 30,825 MB (~30 GB) 
root
(default_wg) | 222 GB | 75 | 3,043 MB (~3 GB) Why this is killing your disks: * 
high_ctr_domain_blacklist
is the primary offender. This user ran only 4 queries, but each query scanned nearly 200 GB of data. On a gp3 volume, reading 200GB for a single query will saturate the throughput for several minutes, causing every other process (including compaction and ingestion) to queue behind it. This is why your read latency is 2–6 seconds. * 
ml_zeppelin
is the "Death by a Thousand Cuts." While its average scan is smaller (~30GB), it is running 10x more frequently. Collectively, it has scanned over 1.3 TB. 2. Immediate Action Plan A. Fix the 
high_ctr_domain_blacklist
SQL A 200GB average scan suggests this user is likely doing: 1. *`SELECT ` on a massive table without a 
WHERE
clause. 2. The "Offset Pagination" you mentioned. If they are at 
OFFSET 1,000,000
, StarRocks is scanning 200GB of data just to throw it away and return the last 100 rows. 3. Missing Partition Pruning: They might be querying a partitioned table but not including the partition key in the 
WHERE
clause. Action: Find the exact SQL for this user:
Copy code
sql
SELECT queryId, querySql, scanBytes, execTime 
FROM starrocks_audit_db__.starrocks_audit_tbl__ 
WHERE user = 'high_ctr_domain_blacklist' 
ORDER BY scanBytes DESC LIMIT 5;
B. Tighten the Resource Group (More than just CPU) Since you are on a Shared-nothing cluster, CPU limits alone won't stop these users from saturating the disks. You should add a 
scan_thread_num
 limit to their resource group to throttle their I/O concurrency.
Copy code
sql
ALTER RESOURCE GROUP high_ctr_domain_blacklist_group WITH (
    "cpu_core_limit" = "4",
    "concurrency_limit" = "2", -- Only allow 2 of these monster queries at once
    "scan_thread_num" = "4"     -- Heavily limit the number of I/O threads they can spawn
);
C. Tune EBS / Storage * Check gp3 Throughput: If your 4 volumes are currently at the default 125 MB/s, your total throughput is only 500 MB/s. At that speed, one 200GB scan takes 400 seconds of pure disk airtime. * Recommendation: Increase the provisioned Throughput (not just IOPS) of your gp3 volumes to 500 MB/s or 1000 MB/s. This is often the cheapest way to "unstuck" a StarRocks cluster on AWS. Summary Your 
iowait
isn't a "system" problem; it's an application logic problem. The user 
high_ctr_domain_blacklist
is treating your analytical database like a sequential file reader. Fix those 4 queries, and your ingestion health and 
iowait
will likely return to normal levels immediately.
m
@Rocky what is the default scan_thread_num for resource groups? potentially unbounded to number of vCPUs?
r
To clarify, there is no explicit parameter named 
scan_thread_num
 in the StarRocks Resource Group definition. I may have conflated that name with other internal settings in my previous response. The actual mechanism StarRocks uses to control scan threads is more integrated. Here is how it works by default and how it relates to your vCPUs: 1. The Defaults are Tied to vCPUs By default, the number of scan threads is indeed bound to the number of vCPUs on your BE node. * Global Scan Pool (
pipeline_scan_thread_pool_thread_num
): For the Pipeline execution engine (v3.x+), the global scan thread pool size defaults to the number of CPU cores (or sometimes 
cores * 1.5
in certain minor versions). * External Scan Pool (
pipeline_connector_scan_thread_num_per_cpu
): For data lake/external scans, the default is 8 threads per CPU core. * Legacy Storage Engine (
scanner_thread_pool_thread_num
): The default is 48 (fixed, not always tied to cores). 2. "Exclusive" vs. "Shared" Resource Group Behavior How these threads are allocated depends on how you configured your resource group: * Shared Resource Groups (
cpu_weight
> 0): These groups share the global 
pip_scan_com
thread pool. They are "unbounded" in the sense that they can submit as many tasks as they want to the global queue, though they are eventually throttled by their overall 
cpu_weight
. * Exclusive Resource Groups (
exclusive_cpu_cores
> 0): When you define an exclusive group, StarRocks creates a dedicated scan thread pool for it (visible in 
information_schema.be_threads
as 
pip_scan_<group_id>
). * The size of this dedicated pool is generally proportional to the number of 
exclusive_cpu_cores
 you assigned. * If you assigned 4 cores, that group will have a much smaller dedicated pool than a 32-core machine’s default, which effectively caps its I/O submission rate. 3. How to Actually Limit the "Problematic User" Since 
scan_thread_num
isn't a valid property, you should use these existing parameters to protect your disks: 1. Lower 
concurrency_limit
: Set this to 
1
or 
2
for that user. This ensures that even if they have a "bad" query, they can't run five of them at once and kill the disk. 2. Use 
big_query_scan_rows_limit
: If you know a "good" pagination query should only ever scan 1 million rows, set this limit to 10 million. If the user’s 
OFFSET
causes a scan of 100 million rows, StarRocks will automatically kill the query. 3. Use Session Variable 
scan_parallelism
(v3.2+): You can set this globally or for a specific user to limit the number of parallel scan instances a single query can spawn.
Copy code
sql
    SET PROPERTY FOR 'user_name' 'scan_parallelism' = '2';
Summary If you set a resource group to 
exclusive_cpu_cores = 4
on a 32-core machine, StarRocks will naturally restrict that user's scan parallelism to roughly 1/8th of the system's total capacity. If you used 
cpu_weight
, they are only limited during CPU contention, but they can still "burst" and saturate your EBS I/O if the disks are otherwise idle. References • administration/management/BE_configuration.md • sql-reference/System_variable.md
m
@Rocky When I tested changing cluster topology from 12 i4i.8xlarge to 16 i4i.16xl machines, I forgot to mount the 3rd and 4th NVMe drives. Does this explain why my compaction was cooked.
r
Yes, forgetting to mount and configure those extra drives is almost certainly why your compaction is "cooked." You essentially upgraded your CPU and Memory but halved your intended I/O capacity relative to the machine's potential. Here is the technical breakdown of why this happened: 1. The Drive Discrepancy On AWS i4i instances, the local NVMe storage is partitioned into physical 3.75 TB modules. * i4i.8xlarge: Has 2 NVMe drives (Total 7.5 TB). * i4i.16xlarge: Has 4 NVMe drives (Total 15.0 TB). By only mounting 2 drives on the 16xl nodes, you are only accessing 5,600 MB/s of sequential read throughput instead of the 11,200 MB/s the instance is capable of. 2. Tablet Distribution Bottleneck StarRocks distributes tablets across the paths defined in your 
storage_root_path
. * If your 
be.conf
only listed 2 paths (or you only mounted 2 drives to those paths), StarRocks crammed 100% of the data and I/O operations onto 50% of the available hardware. * Compaction is an I/O-heavy background process that reads multiple rowsets and writes a new merged version. By missing two drives, each of your remaining drives is doing double the work (more seeks, more concurrent writes), leading to extreme disk contention and high 
iowait
. 3. CPU vs. I/O Imbalance The i4i.16xl has 64 vCPUs. StarRocks defaults its compaction threads (
compact_threads
) based on CPU core count (usually 1/4 of cores). * On a 16xl, you likely have 16 compaction threads running. * If those 16 threads are all hammering only 2 NVMe drives instead of 4, the drives will hit their IOPS/Throughput ceiling almost immediately. * This creates a "convoy effect" where user queries and ingestion tasks get stuck behind the massive I/O queue created by compaction. How to Fix It 1. Mount the missing drives: Format and mount the 3rd and 4th NVMe drives. 2. Update 
be.conf
: Ensure 
storage_root_path
includes all 4 mount points: 
storage_root_path=/data1/storage;/data2/storage;/data3/storage;/data4/storage
3. Restart BEs: StarRocks will recognize the new paths. _ _Note:* It won't automatically move old data to the new drives. New data will naturally flow there, but for old data, you may need to wait for compaction to eventually rewrite it into the new paths or manually trigger a rebalance if you have the overhead. Summary: You effectively created a massive I/O funnel. Your 16xl machines have the "engine" (CPU) of a racecar but the "fuel line" (I/O) of a sedan. Mounting those drives will restore the balance and let compaction breathe. References • administration/management/BE_configuration.md • administration/management/compaction.md • administration/management/monitoring/metrics.md • knowledge/admin_and_operation/shared_data_compaction.md References • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md
m
@Rocky This was for my shared data cluster hosting a primary key table receiving high throughput inserts and partial updates. Isn't compaction more of an issue of S3 IOPS too? Also, does starrocks v4.1 additional configs help me if I have no hot tablets? if my tablet distribution is very even? lake_compaction_max_parallel_default help or not? Because I thought v4.1 features were mostly targeted towards hot tablet issues and large tablets. But all my tablets are between 2-3GB (in a 128 bucket primary key table)
r
In a shared-data (cloud-native) cluster, compaction performance is a balance between S3 throughput and local I/O (NVMe cache). For a Primary Key (PK) table with high-throughput updates, your missing NVMe drives are likely a much bigger bottleneck than S3 IOPS. Here is why your situation is unique and how the v4.1 features apply to you: 1. Is it S3 IOPS or Local I/O? In StarRocks shared-data mode, compaction doesn't happen "on S3." It happens on your BE/CN nodes. * The Process: The BE downloads uncompacted rowsets from S3 to your local NVMe drives (Data Cache), merges them, and then uploads the result back to S3. * The Bottleneck: If you are missing 2 out of 4 NVMe drives on an 
i4i.16xl
, you have effectively halved your local I/O bandwidth (from ~11 GB/s to ~5.5 GB/s). * Primary Key Impact: PK tables are the most I/O-intensive for compaction because they don't just merge data; they must also check the Primary Key Index (which is often cached on those same NVMe drives) and handle delete vectors or partial updates. * Verdict: Your S3 IOPS are likely fine; your BE nodes are "I/O bound" locally because 64 vCPUs are trying to push merge operations through a "half-sized" pipe. 2. Does 
lake_compaction_max_parallel_default
help small tablets? The short answer is not significantly for 2-3GB tablets. * The Feature: This config (introduced/refined in v4.1) allows a single tablet to be compacted using multiple threads in parallel (Internal Parallelism). This was designed specifically for "Large Tablets" (StarRocks is moving toward a 100GB-per-tablet target in v4.1). * Your Case: Since your tablets are already small (2-3GB), a single thread can usually chew through one tablet quickly enough. Splitting a 2GB tablet into parallel sub-tasks adds overhead without much gain. * The Better Config: For your 128-bucket (tablet) distribution, you care more about Global Parallelism (how many tablets the node compacts at once) rather than internal parallelism. * 
lake_compaction_max_tasks
: (FE config) Ensure this is set to 
-1
(adaptive) or a high enough number to utilize your 16 nodes. * 
compact_threads
: (CN/BE config) On 16xl machines (64 cores), this should be set to 16 (the 25% rule). If this is set to 16 but you only have 2 drives, those 16 threads will fight for I/O and cause the high 
iowait
you are seeing. 3. Why v4.1 still matters for you (even without hot tablets) Even if you don't have "hot" tablets, v4.1 and recent 3.x updates include several improvements for your specific high-throughput PK workload: * Load Spill (v3.5+ / v4.x): This allows ingestion to "spill" to disk when memory is tight, which prevents OOMs during high-throughput loads but puts even more pressure on your NVMe drives. If you enable this, mounting those missing drives becomes mandatory. * Adaptive Scheduling: v4.1 is smarter about choosing which tablets to compact based on the "Compaction Score" across the whole cluster, helping keep your 128 buckets healthy even if the load is slightly uneven. * Metadata Optimization: Shared-data PK tables in v4.1 have improved handling of metadata (TXN logs), which reduces the number of small S3 API calls. Summary Recommendation 1. Mount the drives first. This is 90% of your problem. Without the full I/O bandwidth of the 
i4i.16xl
, no software config will fix the compaction backlog. 2. Ignore 
lake_compaction_max_parallel
for now. Your 2-3GB tablet size is the "sweet spot" for standard compaction. 3. Check 
max_cumulative_compaction_num_singleton_deltas
: In high-throughput scenarios, set this to 100 (default is often higher). This makes each compaction task smaller and faster, which prevents your NVMe drives from being locked in long, massive merge cycles. References • administration/management/FE_configuration.md
m
@Rocky Would 6 i4i.16xl or 12 i4i.8xl work better for high throughput ingestion and query performance?
r
For high-throughput ingestion and query performance in a StarRocks shared-data cluster, 12 x i4i.8xlarge is generally superior to 6 x i4i.16xlarge, even though the total vCPUs, RAM, and NVMe storage are identical. In an MPP (Massively Parallel Processing) architecture like StarRocks, a "wider" cluster (more nodes) typically outperforms a "taller" cluster (larger nodes) for the following reasons: 1. Ingestion Throughput (The "Wider" Advantage) * Parallel Write Handlers: StarRocks distributes ingestion (Stream Load, Routine Load) across all available CN/BE nodes. With 12 nodes, you have double the number of concurrent endpoints to receive and parse incoming data streams. * Reduced IO Contention: For Primary Key (PK) tables, ingestion involves checking the PK index and writing to the WAL. Having 12 distinct OS kernels and 12 distinct network queues managing these writes reduces the likelihood of "internal lock contention" compared to hammering a single large node with twice the thread count. * Flush Parallelism: Each node has its own memory budget and flush thread pool. 12 nodes can flush smaller memtables to the local NVMe cache in parallel more effectively than 6 large nodes, which helps prevent "backpressure" during high-throughput bursts. 2. Query Performance & Concurrency * Finer Scan Granularity: StarRocks splits queries into fragments and morsels. With 12 nodes, you can distribute a single query across more physical workers, potentially reducing the latency of large scans by increasing the degree of parallel I/O to the NVMe cache. * Higher Query QPS: For high-concurrency small queries (the "dashboard" use case), having more nodes allows the FE to distribute the load more thinly, reducing CPU and memory "spikes" on any individual node. * Cache Utilization: While the total cache size is the same, 12 nodes provide more distributed access points to that cache. If your 128 tablets are evenly distributed, 12 nodes mean roughly 10-11 tablets per node, whereas 6 nodes would handle ~21 tablets per node. The lower tablet-per-node density often leads to more stable performance. 3. Compaction Efficiency * Distributed Load: Compaction is the most resource-intensive background task for PK tables. In shared-data mode, the FE schedules compaction. 12 nodes provide 12 independent compaction thread pools. * I/O Throughput: As you discovered in your previous test, missing disks "cooks" compaction. Even with all disks mounted, a 16xl machine has a massive amount of CPU (64 cores) pushing through a shared network/EBS pipe. 12 nodes provide more "lanes" for S3 traffic and local I/O compared to 6 nodes. 4. Blast Radius & Reliability * Availability: If an 
i4i.16xlarge
node fails or undergoes AWS maintenance, you lose 17% of your total cluster capacity instantly. If an 
i4i.8xlarge
fails, you only lose 8%. * Recovery: In shared-data mode, tablets are not strictly "rebalanced" but the metadata must be updated. A smaller node recovering or joining is generally faster and less disruptive to the rest of the cluster. When to choose the 6 x i4i.16xlarge instead? The only scenario where the larger nodes win is if you have extremely "heavy" individual queries that require more than 256GB of memory for a single execution fragment (e.g., a massive, multi-terabyte join that cannot be effectively shuffled or spilled to disk). However, for most StarRocks workloads, the MPP engine's ability to shuffle data makes the 12-node 8xl configuration more efficient. Recommendation: Go with 12 x i4i.8xlarge. It provides better concurrency, more stable ingestion, and a smaller failure domain for the same cost. References • knowledge/usage/load/shared_data_batch_ingestion.md • deployment/plan_cluster.md
m
@Rocky But what if I was encountering bandwidth exceeded issues with my i4i.8xlarge cluster?
r
If you were encountering "Bandwidth Exceeded" errors on your 
i4i.8xlarge
cluster, then the shift to 
i4i.16xlarge
was the right move—and it explains why your initial configuration (where you missed the NVMe drives) felt so broken. In an AWS environment, the jump from an 8xlarge to a 16xlarge is one of the most critical "cliffs" for networking and storage throughput. Here is why your experience changed: 1. The "Burstable" vs. "Baseline" Trap * i4i.8xlarge: AWS rates this at "Up to 12.5 Gbps" network and 10 Gbps EBS. The "Up to" means it is a burstable limit. Once you exhaust your network I/O credits (common during high-throughput ingestion or heavy compaction), your speed can drop significantly, triggering "Bandwidth Exceeded" or timing out. * i4i.16xlarge: This is where you cross into 25 Gbps baseline networking and 20 Gbps EBS bandwidth. This is dedicated/non-burstable performance. If your ingestion rate or the background compaction (which involves massive S3 uploads/downloads) was pushing past that 12.5 Gbps envelope on individual nodes, your cluster would "choke," leading to the issues you saw. 2. Network vs. Local I/O (The "Perfect Storm") When you moved to the 
16xlarge
but forgot to mount 2 of the 4 NVMe drives, you created a massive bottleneck: 1. The Good: You doubled your network pipe to S3 (25 Gbps). StarRocks started pulling data from S3 for compaction and ingestion much faster. 2. The Bad: You effectively halved the local storage bandwidth that is supposed to "catch" that data. 3. The Result: The 64 vCPUs and the 25 Gbps network pipe were dumping data into a local storage subsystem that was physically missing half its capacity. This caused your "cooked" compaction—the drives were likely pegged at 100% utilization while the CPU and Network sat waiting (iowait). 3. Does v4.1 
lake_compaction_max_parallel_default
help here? In your specific case with 2-3GB tablets: * No, not really. That parameter (Internal Parallelism) is designed to let one massive tablet (e.g., 50GB+) use multiple threads. * Because you have 128 buckets (tablets) and only 16 nodes, each node is already handling about 8 tablets. * StarRocks will naturally use its Global Parallelism (
compact_threads
) to work on those 8 tablets simultaneously. On a 16xl node, you have plenty of cores to run 8-16 compaction tasks in parallel. * The real fix for you is I/O width, not thread parallelism. Increasing threads on a node that is missing its physical drives will only make the "Bandwidth Exceeded" (now local I/O bandwidth) worse. Technical Recommendation If you stick with the 
16xlarge
nodes to maintain that 25 Gbps network baseline: 1. Mount all 4 drives. This is non-negotiable for 
i4i
instances to get the advertised performance. 2. Set 
compact_threads = 16
 in 
be.conf
. This matches the high vCPU count of the 16xl and takes advantage of the 25Gbps network. 3. Set 
lake_compaction_max_tasks = -1
 (FE config) to allow the cluster to dynamically handle the compaction load across your nodes. 4. Monitor `iowait`: If you still see high iowait after mounting all drives, only then should you look at 
lake_compaction_max_parallel_default
or reducing the ingestion frequency. Summary: Your 
8xlarge
cluster was likely hitting a Network/S3 Bandwidth wall. Your 
16xlarge
cluster hit a Local Disk Bandwidth wall because of the unmounted drives. Fixing the drives will finally "uncork" the full potential of those 16xl machines. References • faq/shared_data_faq.md • knowledge/usage/load/shared_data_batch_ingestion.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky iowait wasn't the issue though. It was just compaction not keeping up
r
In a shared-data cluster where your tablets are consistently small (2-3GB) and distributed evenly, the "headline" features of v4.1 like 
lake_compaction_max_parallel_default
 are likely not what will solve your problem. Here is the breakdown of why compaction is falling behind and how the v4.1 configs apply to your specific architecture: 1. Does 
lake_compaction_max_parallel_default
help you? Probably not. * The Intent: This feature (Internal/Intra-tablet Parallelism) was designed for StarRocks' new "Large Tablet" strategy (targeting 100GB tablets). It allows a single massive tablet to be split into multiple sub-tasks so it doesn't become a single-threaded bottleneck. * Your Case: For a 2-3GB tablet, a single compaction thread is extremely efficient. Splitting a 2GB tablet into multiple parallel sub-tasks adds metadata coordination overhead that will likely yield zero or even negative performance gains. * The Verdict: If you have 128 buckets across 16 nodes, you have ~8 tablets per node. You should focus on "Global Parallelism" (doing those 8 tablets at once) rather than "Internal Parallelism" (doing 1 tablet with 3 threads). 2. Why compaction isn't keeping up (without high 
iowait
) If 
iowait
is low but compaction is still falling behind, the bottleneck is usually Thread Starvation or S3 Throughput, not the physical disk. * The 
compact_threads
limit: By default, CN nodes often have 
compact_threads
set to 4. On an 
i4i.16xlarge
(64 vCPUs), 4 threads are almost certainly your bottleneck. Those 4 threads are completing tasks as fast as they can, but they can't clear the queue of 128 tablets fast enough. * Fix: Increase 
compact_threads
to 16 (25% of your 64 cores). This allows the node to work on all 8 of its tablets simultaneously without fighting for CPU. * S3 Network Latency: In shared-data mode, the "wait" time often happens during S3 
GET
and 
PUT
operations. This shows up as CPU idle time (not 
iowait
), because the CPU is waiting for the network card, not a local block device. * v4.1 Help: v4.1 includes optimizations to reduce S3 API calls (File Bundling and Metadata thinning), which reduces the "waiting on S3" overhead. 3. v4.1 features that actually help your scenario Even if you don't use the parallel tablet compaction, v4.1 (and late 3.x) has features that benefit high-throughput PK tables: * Size-Tiered Compaction for PK Tables: (
enable_pk_size_tiered_compaction_strategy
) This is now the default. It significantly reduces "write amplification" by only merging rowsets of similar sizes. This means you do less total I/O to S3 for the same amount of data ingested. * Parallel PK Index Lookups: v4.1 parallelizes the Primary Key index lookup and generation phase. For PK tables, the "Index Check" is often the most CPU-heavy part of the commit/compaction cycle. This will speed up the "processing" time of each compaction task. * Automatic Tablet Splitting: If one of your 128 buckets eventually grows to 10GB+ while others stay at 2GB (skew), v4.1 can automatically split it to keep your cluster balanced without you needing to re-bucket the whole table. Summary Recommendations 1. Mount all NVMe drives: (As discussed before) This ensures you have the local "runway" to handle 16+ parallel compaction tasks. 2. Tune `compact_threads`: Set this to 16 on your 
i4i.16xl
nodes. This is the single most likely reason your compaction is "slow" without being "heavy" on the disk. 3. Tune `max_cumulative_compaction_num_singleton_deltas`: Set this to 100. This makes each compaction task "bite-sized." Smaller tasks finish faster and release their threads back to the pool sooner, which prevents the compaction score from spiraling. 4. Ignore `lake_compaction_max_parallel_default`: Keep it at the default or 
0
for your 2GB tablets. It won't help you until your tablets reach at least 10GB-20GB. References
m
@Rocky Compact threads is already 16 on my i4i.16xl nodes. Since my iowait was only 0.2-0.3% cpu, will mounting the remaining 2 NVMe still help compaction? I'm trying to diagnose what caused compaction issues when i downscaled from 12 i4i.8xl nodes
r
Even with low 
iowait
(0.2-0.3%), mounting the remaining 2 NVMe drives will likely help, but it is not the primary reason your compaction fell behind during the downscale. The 0.3% 
iowait
confirms that your current 2 disks are not physically saturated. However, the downscale from 12 nodes to 6 nodes created two massive bottlenecks that have nothing to do with disk latency. 1. The "Concurrency Math" (The Real Culprit) In StarRocks shared-data mode, the maximum number of concurrent compaction tasks is determined by the FE configuration 
lake_compaction_max_tasks
. * Default Value: 
-1
(which calculates as 
Number of CN Nodes * 16
). * Your 12-node cluster: could run 192 tasks simultaneously. Since you only have 128 tablets, the cluster could literally compact every single tablet in your table at the exact same time. * Your 6-node cluster: can only run 96 tasks simultaneously. * The Result: You suddenly have 32 tablets (25% of your table) that are forced to wait for a free thread. This creates a "version backlog" where the compaction score starts to climb because the FE cannot schedule enough work to keep up with the ingestion rate. 2. The "Tablet-to-Thread" Ratio * At 12 Nodes: Each node handled ~10-11 tablets. With 
compact_threads = 16
, every node had more threads than it had tablets. Compaction was essentially "instant." * At 6 Nodes: Each node now handles ~21.3 tablets. With 
compact_threads = 16
, each node has a local queue. Some tablets are now waiting behind others. * Why 
iowait
is low: The nodes aren't "struggling" to write; they are simply not allowed to work on the remaining tablets until the first 16 are done. Your CPU is "idle" (waiting for more tasks from the FE) or busy with the 16 tasks it has, but it's not waiting on the disk. 3. How the missing 2 NVMes do affect you If the disks aren't the bottleneck for latency, they are still a bottleneck for Cache Capacity: * Shared-data Compaction uses the Cache: Compaction needs to read rowsets. If those rowsets are in the local NVMe cache, compaction is fast. If they have been evicted (because you only have half the cache space you're supposed to have), compaction must fetch them from S3. * Idle CPU vs. IOWait: Fetching from S3 is a network operation. If your node is waiting for S3, the CPU will show as Idle, not 
iowait
. * Conclusion: By only having 2 disks, you have a smaller "hot data" window. Compaction is likely spending more time waiting for S3 downloads than it would if it had the full 4-disk cache capacity. Recommendations to Fix the 6-Node Cluster If you want to stay at 6 nodes, you need to "stretch" their capacity to match what the 12 nodes were doing: 1. Mount the 2 NVMe drives: This doubles your cache size and ensures that compaction rarely has to go to S3 for data. This reduces the duration of each task. 2. Manually increase `lake_compaction_max_tasks`:
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "160");
(Set this to a value higher than your 128 tablets to ensure the FE never throttles the cluster). 3. Increase 
compact_threads
on CNs: On an 
i4i.16xl
(64 cores), you can safely push 
compact_threads
to 24 or 32 if you aren't at 100% CPU usage. This will eliminate the "local queue" on the nodes so they can process all ~21 tablets at once. 4. Verify S3 Bandwidth: Check your CloudWatch metrics for 
NetworkIn
. If your 6 nodes are hitting the 25Gbps limit collectively while the 12 nodes were only hitting 12Gbps each, you've hit a network throughput ceiling. Summary: Your compaction is "stuck in traffic" because you reduced the number of lanes (nodes) and the speed limit (max tasks). Mounting the disks will make the cars faster, but you also need to open more lanes (threads) to clear the traffic. References
m
@Rocky Is there a way I can check concurrent compaction task? I want to see whether compaction parallelism dropped when i changed cluster topology. I need grafana metric so I can check historical data
r
To check concurrent compaction tasks and verify if parallelism dropped after your topology change, you should look at the following metrics in your Grafana/Prometheus setup. 1. Cluster-Wide Compaction Concurrency Since you are using Primary Key tables in shared-data mode, the "Update Compaction" (PK compaction) is what you need to track. In Grafana, use this PromQL to see the total number of tasks running across all nodes: * Metric: 
sum(starrocks_be_running_update_compaction_task_num)
* Historical View: If you see this value drop from ~128 (your old 12-node cluster could handle all 128 tablets at once) to exactly 96 (the new 6-node cluster limit), it confirms that your FE-level throttling was the bottleneck. 2. Node-Level Thread Utilization To see if individual nodes are maxing out their local 
compact_threads
(16): * Metric: 
starrocks_be_running_update_compaction_task_num
(Grouped by 
instance
) * What to look for: If the line for each node is a "flat line" at 16, it means the node has a local queue and is completely saturated. 3. Key Metrics in the "Shared-Data" Grafana Dashboard If you are using the official StarRocks Grafana templates, look for the "Shared-data" or "Lake Storage" dashboard. Specifically: * Lake Compaction Tasks: Often listed as 
FE Lake Compaction Running Tasks
. This is the FE's view of how many tasks it has dispatched. * Compaction Score: Look for 
Max Tablet Compaction Score
. If this started trending upward precisely when you dropped from 12 to 6 nodes, it confirms the throughput drop. 4. Comparison Table for your Diagnostics Metric Name | What it tells you 
starrocks_be_running_update_compaction_task_num
| Actual PK compaction tasks running on CNs right now. 
starrocks_be_running_cumulative_compaction_task_num
| Standard cumulative compaction tasks (less relevant for PK tables). 
starrocks_fe_lake_compaction_pending_tasks
| How many tablets are "waiting in line" at the FE because the cluster limit (
lake_compaction_max_tasks
) is reached. 
starrocks_be_compaction_task_cost_time_ms_avg
| Whether each individual task started taking longer (due to the missing NVMe cache). Summary for your Diagnosis When you downscaled, two things likely happened simultaneously: 1. FE Throttling: Your cluster-wide capacity dropped from 192 tasks to 96 tasks (because 
lake_compaction_max_tasks
defaults to 
nodes * 16
). You can check this by seeing if 
sum(starrocks_be_running_update_compaction_task_num)
is capped at 96. 2. Increased Task Duration: Because you missed 2 NVMe drives, the local cache hit rate likely dropped. Each compaction task now spends more time "waiting" for data from S3, making each thread occupied for longer. Check 
starrocks_be_compaction_task_cost_time_ms_avg
for this. Recommendation: To restore the old parallelism on just 6 nodes, set 
ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "160");
and increase 
compact_threads
to 24 or 32 on your CN nodes. References • administration/management/monitoring/metrics.md • administration/management/compaction.md • sql-reference/information_schema/be_compactions.md • administration/management/FE_configuration.md References • administration/management/FE_configuration.md • administration/management/compaction.md • administration/management/monitoring/metrics.md
m
@Rocky base_compaction_num_threads_per_disk = 1 Does this config affect shared data cluster?
r
No, 
base_compaction_num_threads_per_disk
does not affect a shared-data (compute-storage separation) cluster. In a shared-data cluster, the compaction architecture is entirely different from the traditional shared-nothing (local storage) model: 1. Why it's ignored * Shared-Nothing (OLAP tables): Compaction threads are tied to physical disks because each disk manages its own set of tablets. The total threads = 
num_disks
* 
base_compaction_num_threads_per_disk
. * Shared-Data (Lake tables): Your tablets live in S3/Object Storage. The local NVMe drives on your 
i4i.16xl
nodes are used only as a cache, not as persistent storage roots. Because compaction is reading from and writing back to S3, StarRocks uses a unified thread pool for the entire node rather than a per-disk thread pool. 2. The configuration that actually matters For your CN nodes, the parameter that controls the number of concurrent compaction tasks is: 
compact_threads
 (Default: 4) If you see that your node is only running a few compaction tasks despite having 64 cores, you should increase this value. * Recommendation: Set this to 16 (25% of your 64 vCPUs) or even 24 if you have heavy ingestion and your CPU usage allows it. * How to change it dynamically:
Copy code
sql
    UPDATE information_schema.be_configs SET VALUE = 16 WHERE name = "compact_threads";
3. Other relevant Shared-Data parameters If you are troubleshooting why compaction isn't keeping up, these are the only parameters you should focus on: * 
lake_compaction_max_tasks
(FE Config): Controls the cluster-wide limit. Default is 
-1
(Nodes * 16). If this is set too low, the FE won't even send tasks to your CNs. * 
compact_thread_pool_queue_size
(CN Config): The size of the task queue on the node. * 
max_cumulative_compaction_num_singleton_deltas
(CN Config): Recommended to be 100. This makes each task smaller and faster, which helps the thread pool clear the backlog more efficiently. Summary: You can ignore all 
_per_disk
compaction settings on your CN nodes. Focus entirely on 
compact_threads
. References • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/BE_configuration.md References • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/BE_configuration.md • administration/management/compaction.md
m
@Rocky What is a comprehensive list of be_configs I should update to adjust for my 16xl machines compared to default values
r
For an i4i.16xl node (64 vCPUs, 512GB RAM), the default StarRocks configurations are often too conservative. Since you are in a shared-data (compute-storage separation) environment with local NVMe drives for caching, you should adjust these parameters to fully utilize the high CPU count, massive RAM, and fast local I/O. 1. Compaction Tuning (Critical for your current issue) On a 64-core machine, the default 4 threads will cause a massive backlog. * 
compact_threads = 16
: Set this to 25% of your core count. This allows the node to process more tablets in parallel. * 
max_cumulative_compaction_num_singleton_deltas = 100
: (Default is 1000). Reducing this makes each compaction "job" smaller and faster, preventing threads from being "stuck" on giant tasks for too long. * 
lake_compaction_stream_buffer_size_bytes = 4194304
: (4MB, Default 1MB). Increases the buffer size for reading data from S3 during compaction, reducing the number of S3 requests. 2. Data Cache & NVMe Utilization Since you have 2x 3.75TB NVMe drives, ensure StarRocks is using them aggressively. * 
starlet_star_cache_disk_size_percent = 90
: (Default 80). If these drives are dedicated to StarRocks, you can push this higher. * 
disk_safe_level = 90
: (Default 80). Prevents the cache from being evicted too early. * 
block_cache_enable_buffer = true
: Enables a block buffer to optimize Data Cache efficiency for high-frequency reads. 3. CPU & Query Parallelism StarRocks usually auto-detects core counts, but manual overrides ensure the pipeline engine isn't idling. * 
pipeline_exec_thread_pool_thread_num = 64
: Usually defaults to the core count. Ensure it is at least 64. * 
scan_thread_pool_thread_num = 64
: For high-concurrency scans from the NVMe cache. * 
lake_flush_thread_num_per_store = 0
: (Default 0). At 0, StarRocks uses 
CPU Cores * 2
. For your node, this is 128 threads, which is excellent for handling high-volume ingestion. 4. Memory Management With 512GB of RAM, you can afford larger buffers to reduce disk/S3 spill. * 
load_process_max_memory_limit_percent = 50
: (Default 30). If you do heavy ingestion, increasing this allows the MemTables to grow larger before flushing, creating fewer/larger segments that are easier to compact. * 
mem_limit = 90%
: Ensure this is set to at least 90% if the node is dedicated to StarRocks. 5. Network (S3 Throughput) * 
download_buffer_size = 8388608
: (8MB, Default 4MB). Helps maximize the 25Gbps network bandwidth of the 
i4i.16xl
when downloading snapshot files or segments from S3. ────────── How to apply these: Most of these can be updated dynamically without a restart:
Copy code
sql
-- Apply to a specific CN/BE
UPDATE information_schema.be_configs 
SET VALUE = "16" 
WHERE name = "compact_threads";

-- FE Config (Run this to fix the cluster-wide limit)
ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "160");
Note: For static configs (like 
starlet_star_cache_disk_size_percent
), you must update the 
be.conf
file and restart the process. References • administration/management/BE_configuration.md • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/memory_management.md References • knowledge/admin_and_operation/shared_data_compaction.md • deployment/plan_cluster.md • deployment/shared_data/feature-support-shared-data.md
m
@Rocky MySQL [requests]> SELECT t.be_id AS cn_id, COUNT(*) AS tablets, SUM(t.num_rowset) AS total_rowsets, MAX(t.num_rowset) AS max_rowsets_per_tablet FROM information_schema.be_tablets t JOIN information_schema.tables_config c ON t.table_id = c.table_id WHERE c.table_name = 'requests' GROUP BY t.be_id ORDER BY max_rowsets_per_tablet DESC; +--------+---------+---------------+------------------------+ | cn_id | tablets | total_rowsets | max_rowsets_per_tablet | +--------+---------+---------------+------------------------+ | 215623 | 1404 | 4752 | 35 | | 215602 | 1408 | 4816 | 28 | | 215613 | 1403 | 4843 | 23 | | 215620 | 1404 | 4806 | 21 | | 215607 | 1406 | 4765 | 20 | | 232880 | 1423 | 4862 | 18 | +--------+---------+---------------+------------------------+ |
Copy code
|     QueryPeakMemoryUsage: ?, QueryAllocatedMemoryUsage: 311.307 GB                                                                                |
|     Top Most Time-consuming Nodes:                                                                                                                |
|         1. OLAP_SCAN (id=0) : 2s886ms (98.36%)                                                                                               |
|         2. TOP_N (id=1) [ROW_NUMBER, TOP-N]: 45.593ms (1.55%)                                                                                     |
|         3. MERGE_EXCHANGE (id=2) [GATHER]: 2.329ms (0.08%)                                                                                        |
|         4. RESULT_SINK: 48.186us (0.00%)                                                                                                          |
|         5. AGGREGATION (id=3) [finalize, update]: 41.144us (0.00%)                                                                                |
|     Top Most Memory-consuming Nodes:                                                                                                              |
|     NonDefaultVariables:                                                                                                                          |
|         enable_adaptive_sink_dop: false -> true                                                                                                   |
|         enable_async_profile: true -> false                                                                                                       |
|         enable_profile: false -> true                                                                                                             |
| Fragment 0                                                                                                                                        |
| │   BackendNum: 1                                                                                                                                 |
| │   InstancePeakMemoryUsage: 231.797 KB, InstanceAllocatedMemoryUsage: 250.898 KB                                                                 |
| │   PrepareTime: ?                                                                                                                                |
| └──RESULT_SINK                                                                                                                                    |
|    │   TotalTime: 48.186us (0.00%) [CPUTime: 48.186us]                                                                                            |
|    │   OutputRows: 1                                                                                                                              |
|    │   SinkType: MYSQL_PROTOCAL                                                                                                                   |
|    └──AGGREGATION (id=3) [finalize, update]                                                                                                       |
|       │   Estimates: [row: 1, cpu: 8.00, memory: 8.00, network: 0.00, cost: 77122736548.00]                                                       |
|       │   TotalTime: 41.144us (0.00%) [CPUTime: 41.144us]                                                                                         |
|       │   OutputRows: 1                                                                                                                           |
|       │   PeakMemory: ?, AllocatedMemory: ?                                                                                                       |
|       │   AggExprs: [max(1: received)]                                                                                                            |
|       └──MERGE_EXCHANGE (id=2) [GATHER]                                                                                                           |
|              Estimates: [row: 1, cpu: 8.00, memory: 8.00, network: 8.00, cost: 77122736528.00]                                                    |
|              TotalTime: 2.329ms (0.08%) [CPUTime: 194.431us, NetworkTime: 2.134ms]                                                                |
|              OutputRows: 1                                                                                                                        |
|              PeakMemory: ?, AllocatedMemory: ?                                                                                                    |
|                                                                                                                                                       |
| Fragment 1                                                                                                                                        |
| │   BackendNum: 6                                                                                                                                 |
| │   InstancePeakMemoryUsage: 107.532 MB, InstanceAllocatedMemoryUsage: 311.306 GB                                                                 |
| │   PrepareTime: ?                                                                                                                                |
| └──DATA_STREAM_SINK (id=2)                                                                                                                        |
|    │   PartitionType: UNPARTITIONED                                                                                                               |
|    └──TOP_N (id=1) [ROW_NUMBER, TOP-N]                                                                                                            |
|       │   Estimates: [row: 1, cpu: 30849094592.00, memory: 8.00, network: 30849094592.00, cost: 77122736496.00]                                   |
|       │   TotalTime: 45.593ms (1.55%) [CPUTime: 45.593ms]                                                                                         |
|       │   OutputRows: 6                                                                                                                           |
|       │   PeakMemory: ?, AllocatedMemory: ?                                                                                                       |
|       │   OrderByExprs: [<slot 1> 1: received]                                                                                                    |
|       └──OLAP_SCAN (id=0)                                                                                                                    |
|              Estimates: [row: 3856136824, cpu: 30849094592.00, memory: 0.00, network: 0.00, cost: 15424547296.00]                            |
|              TotalTime: 2s886ms (98.36%) [CPUTime: 19.745ms, ScanTime: 2s866ms]                                                              |
|              OutputRows: 1.906B (1906150973)                                                                                                 |
|              RuntimeFilter: 1.906B (1906150973) -> 1.906B (1906150973) (0.00%)                                                               |
|              Table: : requests                                                                                                               |
|              Detail Timers: [ScanTime = IOTaskExecTime + IOTaskWaitTime]                                                                     |
|                  IOTaskExecTime: 1s580ms [min=1.060ms, max=2s865ms]                                                                          |
|                      CreateSegmentIter: 1s204ms [min=40ns, max=2s576ms]                                                                      |
|                      IOStatistics:                                                                                                           |
|                          IOTimeRemote: 560.121us [min=0ns, max=1s630ms]                                                                      |
|                          IOTimeTotal: 1.005ms [min=0ns, max=1s633ms]                                                                         |
|                      IOTime: 1.005ms [min=0ns, max=1s633ms]                                                                                  |
|                      SegmentInit: 340.891ms [min=0ns, max=1s425ms]                                                                           |
|                      SegmentRead: 7.044ms [min=0ns, max=1s677ms]                                                                             |
|                          BlockSeek: 2.962ms [min=0ns, max=1s651ms]                                                                           |
|                  IOTaskWaitTime: 513.056us [min=6.322us, max=3.875ms]                                                                        |
|                                                                                                                                                       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
70 rows in set (3.266 sec)

MySQL [requests]> select max(received) from request
Why such simple query take so long. Table order key is 
received
r
The query 
SELECT MAX(received) FROM requests
is taking ~3.2 seconds because it is performing a full column scan of 1.9 billion rows and checking the delete vector for every single row. Here is a breakdown of why this is happening despite 
received
being an "order key": 1. The "Primary Key Table" Limitation You mentioned in the previous turn that you are using Primary Key (PK) tables. In the PK model: * Delete Vectors: StarRocks must determine if a row is "visible" by checking a delete vector. Because a row with the "maximum" value might have been deleted or updated in a more recent rowset, the engine cannot simply look at the segment's 
max
metadata (Zone Map). * Full Scan: Consequently, StarRocks is forced to scan every row of the 
received
column to verify its visibility before calculating the maximum. Your profile shows 
OutputRows: 1.906B
, confirming that nearly 2 billion rows were read and passed up from the scan layer. 2. High Metadata Overhead (
CreateSegmentIter
) Your profile shows that 1.2 seconds (37%) of the time was spent in 
CreateSegmentIter
. * This is directly related to your compaction issue. * With 35 rowsets per tablet and ~8,400 tablets total, the engine has to open and initialize over 290,000 rowset iterators to execute this query. * Even if the scan itself were fast, the "bookkeeping" of managing 35 overlapping versions of data for every tablet is creating a massive bottleneck. 3. Why the "Order Key" isn't helping (yet) In a Duplicate Key table, StarRocks can optimize 
MAX(sort_key)
by only reading the last few blocks of the last segment. However, in a Primary Key table: * The 
ORDER BY
key primarily helps with range filters (e.g., 
WHERE received > '...'
). * It does not currently allow for a "metadata-only" 
MAX()
because of the need to process deletes/updates. Recommendations to fix this: 1. Fix Compaction Parallelism (Immediate) As discussed, your compaction backlog is making the "metadata" part of the query slow. * Check if 
max_rowsets_per_tablet
drops after you increased 
compact_threads
. * Once it drops to <10, the 
CreateSegmentIter
time in your profile should shrink from 1.2s to <100ms. 2. Use a Sync Materialized View (The "Index" approach) If you need this 
MAX()
query to be sub-millisecond, create a single-value Materialized View. StarRocks will maintain the max value incrementally:
Copy code
sql
CREATE MATERIALIZED VIEW mv_max_received AS 
SELECT MAX(received) FROM requests;
3. Verify Sort Key Column Order Ensure 
received
is the first column in your 
ORDER BY
clause. While it won't stop the scan in a PK table, it ensures the data is physically clustered by time, which makes the scan significantly more I/O efficient (as seen in your profile, where 1.9B rows were scanned in only 2.8s—roughly 680 million rows per second). Summary: The 3-second delay is a combination of PK delete-vector overhead (forcing a full scan) and compaction backlog (forcing the engine to open 35x more files than necessary). Improving compaction will help, but for instant results on 2 billion rows, a Materialized View is the correct architectural solution. References • knowledge/usage/best-practice.md • best_practices/table_clustering.md
m
@Rocky PRIMARY KEY(
received
, 
auction_id
, 
imp_key
) COMMENT "OLAP" PARTITION BY date_trunc('hour', received) DISTRIBUTED BY HASH(
auction_id
) BUCKETS 128 ORDER BY(
received
, 
auction_id
) PROPERTIES ( "compression" = "ZSTD", "datacache.enable" = "true", "datacache.partition_duration" = "25 hours", "enable_async_write_back" = "false", "enable_persistent_index" = "true", "file_bundling" = "true", "partition_live_number" = "72", "persistent_index_type" = "CLOUD_NATIVE", "replication_num" = "1", "storage_volume" = "core_services_volume" ); | This is my table definition. I have the exact same table in my shared nothing cluster and it only takes ~400ms for the exact same query. What is going on? Do I have to re-create the table when changing cluster topology?
r
The performance difference you are seeing (3.2s vs. 400ms) is not due to a "bad" table definition, but rather a difference in how Shared-Data (Lake) nodes handle metadata and primary key indexing compared to Shared-Nothing (Local) nodes. You do not need to re-create the table, but you should understand the two architectural bottlenecks currently slowing you down: 1. The "Metadata Tax" of 35 Rowsets In your shared-nothing cluster, compaction is usually more aggressive and has zero network overhead for metadata. In your shared-data cluster, you have 35 rowsets per tablet. * The Cost: To find the 
MAX(received)
, StarRocks must open every single rowset to check for "Delete Vectors" (to see if the highest value was updated or deleted). * The Profile: Your 
CreateSegmentIter
takes 1.2 seconds. This is the time spent by the CN node fetching metadata for those 290,000+ rowset/tablet combinations from the LakeService (FE) and S3. * Shared-Nothing Advantage: On a local disk BE, opening a local file descriptor is nearly instant. In Shared-Data, this involves object storage metadata lookups or cache-checks that are significantly slower when the rowset count is high. 2. 
CLOUD_NATIVE
vs. 
LOCAL
Persistent Index Your table uses 
persistent_index_type = "CLOUD_NATIVE"
. * Shared-Nothing: Always uses 
LOCAL
persistent indexes (stored on local SSD). They are extremely fast because the index is physically right next to the data. * Shared-Data: 
CLOUD_NATIVE
stores the index in S3 and caches it on your NVMe. If the index isn't fully warmed up, or if the CN has to frequently sync index pages from S3 to verify deletes for those 35 rowsets, latency spikes. * Why it matters for MAX: Since you are using a Primary Key table, the engine must use the index to build a visibility map (DelVector). If the index metadata is "cold" or fragmented across 35 rowsets, the query waits. 3. Bucket Over-fragmentation You have 
DISTRIBUTED BY HASH(auction_id) BUCKETS 128
and you partition by hour. * The Math: 128 buckets * 24 hours = 3,072 tablets per day. * The Problem: If each tablet is small (e.g., <100MB), the ratio of "metadata overhead" to "actual data" becomes very high. In Shared-Data, this is a performance killer because every tablet requires an S3 metadata handshake. * Recommendation: For an 
i4i.16xl
, you want larger tablets (roughly 1GB–10GB each). Unless you are ingesting terabytes per hour, 128 buckets per hour is likely too many. You might see better performance with 16–32 buckets. How to bridge the gap without re-creating: 1. Aggressive Compaction (Mandatory): Your shared-nothing cluster likely has fewer rowsets. Once you increase 
compact_threads
on the CNs and the 
max_rowsets_per_tablet
drops from 35 down to 5 or less, your 
CreateSegmentIter
time will likely drop from 1.2s to under 100ms. 2. Warm the Metadata Cache: Ensure the CNs have enough memory for metadata. * Check BE config: 
lake_metadata_cache_limit
(Default is usually 10% of mem). If you have millions of tablets, increase this. 3. Check the "Fast Max" logic: StarRocks can optimize 
MAX(sort_key)
, but it disables this if the 
DelVector
(delete vector) is too complex. Reducing the rowset count via compaction is the only way to re-enable this optimization path. Summary: You are seeing the "Lakehouse overhead." Shared-data clusters are slightly slower than shared-nothing for metadata-heavy operations unless the data is well-compacted. Fix the compaction backlog first, and the 3.2s query will likely drop close to the 400ms mark. References • knowledge/usage/best-practice.md • deployment/shared_data/feature-support-shared-data.md
m
@Rocky How can I monitor whether my compaction is keeping up with ingestion in shared data cluster primary key table?
r
In a StarRocks shared-data cluster, the most reliable way to monitor compaction health is through the Compaction Score. This score directly represents the "backlog" of work and tells you if ingestion is outpacing the background compaction process. 1. Key Metric: Compaction Score (CS) For shared-data (Lake) tables, the score roughly corresponds to the number of rowsets that need to be merged. * Healthy (< 10): Compaction is caught up. * Warning (> 100): The system will start throttling (slowing down) your ingestion commits to prevent metadata explosion. * Critical (> 2000): The system will reject new ingestion tasks entirely to protect the cluster. 2. SQL Monitoring (Fastest way) You can query the 
information_schema
to find exactly which tables or partitions are lagging:
Copy code
sql
-- Find the partitions with the highest compaction backlog
SELECT 
    DB_NAME, 
    TABLE_NAME, 
    PARTITION_NAME, 
    MAX_CS as Max_Compaction_Score,
    AVG_CS as Avg_Compaction_Score,
    VISIBLE_VERSION 
FROM information_schema.partitions_meta 
WHERE TABLE_NAME = 'requests'
ORDER BY Max_Compaction_Score DESC;
_Note: 
MAX_CS
is the highest score across all tablets in that partition. Since you saw 35 rowsets earlier, your score is likely at least 35, which is getting close to the throttling threshold._ 3. Detailed Tablet Status If you want to see if specific nodes (CNs) are struggling to keep up with their assigned tablets:
Copy code
sql
SELECT 
    BE_ID, 
    COUNT(*) as total_tablets,
    AVG(NUM_ROWSET) as avg_rowsets,
    MAX(NUM_ROWSET) as max_rowsets 
FROM information_schema.be_tablets
GROUP BY BE_ID;
4. Direct FE Inspection For real-time debugging of a specific table, use the internal 
proc
tool:
Copy code
sql
-- Replace 'your_db' and 'requests' with your actual names
SHOW PROC '/DBS/your_db/requests/partitions';
This returns a table including 
AvgCS
, 
P50CS
, and 
MaxCS
. If 
MaxCS
is consistently rising while you are ingesting data, your compaction is falling behind. 5. Prometheus / Grafana Metrics If you use Prometheus, monitor these specific metrics to set up alerts: * 
starrocks_fe_tablet_max_compaction_score
: The highest score in the entire cluster (alert if > 80). * 
be_cumulative_compaction_rowsets_per_second
: Shows the throughput of compaction. If this is high but the score is still rising, you need more 
compact_threads
. * 
lake_compaction_slowdown_count
: (FE metric) Increments every time a commit is delayed due to high compaction scores. If this is > 0, your users will feel ingestion latency. Summary for your 16xl nodes: With 64 cores, you should be able to keep the Max_CS below 20 even under heavy load. If you see it climbing toward 100, it means your 
compact_threads
or 
lake_compaction_max_tasks
are still too low for too low for your ingestion volume. References • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md • sql-reference/information_schema/be_tablets.md • administration/management/monitoring/metrics.md References • administration/management/monitoring/metrics.md • administration/management/compaction.md
m
@Rocky I attached a screen shot of fe tablet max compaction score. It is zig zag pattern where it spikes high and back down to low value between 20 and 30. But the spikes are getting taller and taller. How do i remediate this?
r
The "zig-zag" pattern you are seeing is the signature of a race between ingestion and compaction. * The Downward Slope: Compaction is running and successfully merging rowsets. * The Upward Slope: New data is being ingested, creating new rowsets faster than the previous compaction cycle finished. * Taller Spikes: This is the critical warning. It means you are accumulating Compaction Debt. Each cycle is failing to clear the "residual" work from the previous cycle, and the backlog is growing. To remediate this on your 
i4i.16xl
(64-core) nodes, you need to increase the "velocity" of compaction so the downward slope is steeper and starts sooner. 1. Increase Worker Concurrency (CN Side) Your nodes have 64 cores, but the default 
compact_threads
is likely only 4. You are under-utilizing your hardware. * Action: Increase the threads to 25% of your core count (16 threads).
Copy code
sql
-- Run this on each CN node (or via your config management)
UPDATE information_schema.be_configs SET VALUE = 16 WHERE name = "compact_threads";
2. Make Compaction Cycles "Faster & Smaller" By default, StarRocks tries to merge up to 1000 files at once. In a high-throughput Primary Key table, this makes each compaction task "heavy" and slow, allowing ingestion to pile up in the meantime. * Action: Reduce the max files per task to 100. This makes tasks finish faster and commit their results more frequently, smoothing out the spikes.
Copy code
sql
UPDATE information_schema.be_configs SET VALUE = 100 WHERE name = "max_cumulative_compaction_num_singleton_deltas";
3. Tune FE Scheduling (FE Side) In shared-data clusters, the FE manages the schedule. If the spikes are getting taller, the FE may not be triggering the next "round" fast enough after a success. * Action: Reduce the wait time between successful compaction runs on the same partition.
Copy code
sql
ADMIN SET FRONTEND CONFIG ("lake_compaction_interval_ms_on_success" = "2000"); -- Default is 10000 (10s)
* Action: Ensure the cluster-wide task limit isn't capping you. If you have many partitions, the default (Nodes * 16) might be too low.
Copy code
sql
ADMIN SET FRONTEND CONFIG ("lake_compaction_max_tasks" = "128"); -- Adjust based on total CN count
4. Check for "Stuck" Tasks If the spikes never go back down to a baseline of ~5-10, a task might be failing. * Action: Run this query to see if any compaction tasks are consistently failing or timing out:
Copy code
sql
SELECT * FROM information_schema.be_cloud_native_compactions 
WHERE state = 'FAILED' 
ORDER BY finish_time DESC LIMIT 10;
_If you see "Timeout" or "Memory Exceed" errors, you may need to increase 
compaction_memory_limit_per_thread
._ 5. Ingestion-Side Batching If you are using Routine Load or frequent Stream Loads, check your commit frequency. * If you are committing every 1–2 seconds, you are creating a "metadata storm." * Try to increase your batch interval (e.g., to 10–15 seconds) so that each ingestion creates one large rowset instead of 10 tiny ones. This is the most effective way to lower the "Compaction Score" baseline. Summary: Your 64-core nodes have plenty of power. By increasing 
compact_threads
to 16 and reducing the 
interval_ms_on_success
, you should see the zig-zag spikes stabilize and eventually trend downward slopes become much more aggressive. References • best_practices/primarykey_table.md • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md
m
@Rocky What's the difference between lake_publish_tablet_version_latency_99 and sum(rate(starrocks_fe_txn_publish_execute_latency_ms_sum{starrocks_cluster="$cluster"}[$interval])) / sum(rate(starrocks_fe_txn_publish_execute_latency_ms_count{starrocks_cluster="$cluster"}[$interval])) One is ~10s and the other can go up to 3 hours.
r
The discrepancy between these two metrics points to a "Long-Tail" problem in your transaction publishing. While individual tablets are responding quickly, your transactions as a whole are being held hostage by a small number of slow tablets or coordination issues. 1. The Definitions * 
lake_publish_tablet_version_latency_99
(10s): This is a BE/CN-side metric. It measures the time it takes for a single tablet to process a "publish version" request. A 10s value at the 99th percentile means that 99% of individual tablet updates are completing reasonably fast. * 
starrocks_fe_txn_publish_execute_latency_ms
(3 hours): This is an FE-side metric. It measures the time from when the FE picks up a committed transaction to publish it, until the entire transaction finishes. _ _Note: Your formula calculates the average latency of transactions that finished during that interval.* 2. Why the 3-hour Gap? The most common reason for a transaction taking 3 hours while tablets take 10s is Quorum/Completeness failure. A transaction in StarRocks is not marked "Visible" until the FE receives a success message from the tablets. In a Shared-Data (Lake) cluster, the FE waits for the CNs to acknowledge they have updated the metadata and persistent index. * The "Stuck Tablet" Scenario: If your transaction involves 1,000 tablets and 999 finish in 10 seconds, but one single tablet hangs (perhaps due to a specific CN node being overloaded or an S3/object storage timeout), the FE metric 
txn_publish_execute_latency
will continue to climb until that last tablet finishes or the transaction finally times out. * Persistent Index & Compaction Score: Since you have a Primary Key table with a high compaction score (~35 rowsets), the "Publish" phase is where the CN must update the Persistent Index. If one CN is struggling with memory or disk I/O to load the index for its tablets, it will delay the entire transaction. * Serialization and Batching: In StarRocks 3.x shared-data mode, 
lake_enable_batch_publish_version
is 
true
by default. This batches multiple transactions together to improve throughput. If one transaction in a large batch is stuck, the "Execute" latency for all transactions in that batch will appear inflated in the FE metrics. 3. How to Remediate To find the "smoking gun," you need to look for the tablets that are falling into the 1% tail: 1. Check for "Stuck" Transactions: Run this to see which transactions are currently in the 
COMMITTED
state but not yet `VISIBLE`:
Copy code
sql
    SHOW PROC '/transactions';
Look for transactions with a very old 
CommitTime
. 2. Identify the Slow Tablets: If you find a transaction ID from the step above that has been running for hours:
Copy code
sql
    -- Replace <txn_id> with the ID found above
    SHOW TERMINATE TABLET <txn_id>;
    -- Or more commonly:
    SHOW TRANSACTION <txn_id>;
This will often show you which BE/CN nodes have not yet responded. 3. FE Configuration Adjustments: If the FE is simply overwhelmed with the volume of small publish tasks, increase the concurrency: * `lake_publish_version_max_threads`: Increase this (e.g., to 1024) if your 
publish_schedule_latency
is also high. * `lake_publish_version_timeout_ms`: Check if this is set too high. If a tablet is truly stuck, you want it to fail and retry rather than wait for 3 hours. 4. Confirm the Formula: If you are using 
sum(rate(..._sum)) / sum(rate(..._count))
, a single transaction that took 24 hours to finish and finally completed in your 1-minute window will mathematically drive the "average" up to several hours, even if all other transactions that minute took 1 second. Check the 
quantile="0.99"
 version of the metric instead of the average to see if the problem is systemic or just a few "zombie" transactions.
Summary: Your CNs are healthy (10s tablet latency), but your transactions are waiting for "the last mile." Investigate the 
COMMITTED
transactions to see which specific nodes or tablets are failing to report back to report back to the FE. References • administration/management/monitoring/metrics.md • administration/management/FE_configuration.md • faq/shared_data_faq.md • administration/management/compaction.md References • administration/management/monitoring/metrics.md • faq/shared_data_faq.md • knowledge/admin_and_operation/shared_data_compaction.md
2 Views
Open in Slack
PreviousNext
Powered by