http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> If smaller "task_consume_second" or...
# questions-and-troubleshooting
c
@Rocky If smaller "task_consume_second" or load size parameters create more versions then I guess that will compromise near-term performance. What is the best way to manage compaction to mitigate that?
r
Small 
task_consume_second
or frequent small loads lead to a high number of rowsets (versions). If compaction can't keep up, query performance drops due to the overhead of merging versions at read-time, eventually leading to ingestion slowdowns or "too many versions" errors. To manage this, you can tune compaction to be more aggressive or increase its resource allocation. 1. Increase Compaction Resources If you have high-frequency writes, you must provide more CPU threads to compaction tasks so they can keep pace with the incoming data. * Shared-Data (Compute-Storage Separation): * 
compact_threads
(CN config): Increase this to allow more concurrent compaction tasks. The recommended value is 25% of the CN's CPU cores. * 
lake_compaction_max_tasks
(FE config): Controls the total concurrent tasks in the cluster. Default is 
-1
(adaptive: CN nodes × 16). * Shared-Nothing (Local Storage): * 
cumulative_compaction_num_threads_per_disk
(BE config): Increase the threads dedicated to merging new versions. * 
base_compaction_num_threads_per_disk
(BE config): Increase the threads for merging cumulative rowsets into the base data. * Primary Key Tables: * 
update_compaction_num_threads_per_disk
(BE config): Specifically for PK table version management. 2. Make Compaction Tasks Faster By default, StarRocks tries to merge many files at once. In high-frequency scenarios, it is often better to perform smaller, faster merges to keep the version count from spiking. * 
max_cumulative_compaction_num_singleton_deltas
(BE/CN config): Reduce this from the default (often 1000) to 100. This ensures individual compaction tasks finish quickly and release resources back to the pool sooner. * 
min_cumulative_compaction_num_singleton_deltas
(BE/CN config): Reducing this (e.g., to 2 or 3) will trigger cumulative compaction more aggressively after very few versions are created, preventing a large backlog. 3. Adjust Ingestion Thresholds (Mitigation) If you cannot increase resources and must tolerate more versions temporarily, you can prevent ingestion from failing by raising the "safety" limits. Note: This mitigates the "load failure" but does not solve the "query performance" issue. * Shared-Data (FE config): * `lake_ingest_slowdown_threshold`: Default 100. Increase this to delay the point where StarRocks throttles your ingestion. * `lake_compaction_score_upper_bound`: Default 2000. Increase this to prevent ingestion from stopping entirely. * Shared-Nothing (BE config): * `tablet_max_versions`: Default 1000. Increase this to allow more versions before the load fails. Summary Recommendation To maintain performance with small `task_consume_second`: 1. Set 
compact_threads
to 25% of CPU cores. 2. Set 
max_cumulative_compaction_num_singleton_deltas
to 100 to speed up task turnaround. 3. Monitor the Compaction Score (found in 
show proc '/compactions'
). If it stays consistently high (e.g., > 100), you either need more resources or must increase your 
task_consume_second
to create larger, fewer batches. References • administration/management/BE_configuration.md • best_practices/primarykey_table.md • administration/management/monitoring/metrics.md
c
@Rocky what percentage of CPUs should 
cumulative_compaction_num_threads_per_disk
be? What about 
base_compaction_num_threads_per_disk
?
r
The official documentation does not provide a strict "percentage of CPU" formula for the per-disk parameters in shared-nothing (local storage) clusters. Instead, it provides specific recommended ranges and a general resource allocation guideline used in the shared-data architecture. 1. Shared-Data Architecture (The 25% Rule) In compute-storage separation (shared-data), where compaction is managed via a global thread pool, the recommendation is explicit: * 
compact_threads
: Set this to 25% of the BE/CN CPU core count. 2. Shared-Nothing Architecture (Per-Disk Tuning) In shared-nothing clusters, threads are allocated per disk. For high-frequency ingestion scenarios where you need to merge versions faster, StarRocks recommends the following starting points: Parameter | Recommended (for high frequency) | Default 
cumulative_compaction_num_threads_per_disk
| 4 | 1 
base_compaction_num_threads_per_disk
| 2 | 1 
update_compaction_num_threads_per_disk
| 4 (for Primary Key tables) | 1 3. How to Calculate the Impact To prevent compaction from overwhelming your CPUs, calculate the total possible compaction threads across your BE: $$\text{Total Threads} = (\text{Cumulative} + \text{Base} + \text{Update}) \times \text{Number of Disks}$$ Example: If you have a BE with 16 cores and 4 disks: * Using the defaults (1 thread each): $ (1+1) \times 4 = 8$ threads. (50% of cores, which is high). * Using the high-frequency recommendation (4 cumulative, 2 base): $(4+2) \times 4 = 24$ threads. This would exceed your CPU capacity (150% of cores) and cause significant query interference. Guideline: If you have many disks relative to your CPU cores, keep the per-disk numbers low (1 or 2). If you have few disks (e.g., a single large NVMe) and many cores, you should increase these numbers significantly to ensure compaction isn't bottlenecked by a single thread. Summary Recommendation * Target: Aim for the total number of compaction threads across all disks to be roughly 25% to 33% of your total CPU cores. * Monitor: Watch the Compaction Score (
show proc '/compactions'
). * Score > 100: Compaction is falling behind; increase threads or decrease 
max_cumulative_compaction_num_singleton_deltas
to 100. * Score < 10: Compaction is healthy; you can reduce threads to save CPU for queries. References • administration/management/BE_configuration.md • administration/management/compaction.md • faq/loading/Loading_faq.md References • administration/management/compaction.md • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/BE_configuration.md
c
@Rocky 
show proc '/compactions'
seems to always return nothing. What does that mean?
r
The reason 
SHOW PROC '/compactions'
returns nothing depends on your cluster architecture. This command is primarily designed for Shared-data (Compute-Storage Separation) clusters. 1. If you are on Shared-data (Cloud Native) 
SHOW PROC '/compactions'
shows active or recently completed tasks. * Empty Result: This means there are currently no compaction tasks running or queued. It is actually a good sign—it suggests the system is caught up with its work. * To check the "health" (backlog): Instead of looking for active tasks, look at the Compaction Score. * Run: 
SHOW PROC '/dbs/<db_name>/<table_name>/partitions';
* Look at the MaxCS (Max Compaction Score) column. * MaxCS < 10: Healthy/Caught up. * MaxCS > 100: Compaction is falling behind. 2. If you are on Shared-nothing (Local Storage) In a standard shared-nothing cluster, 
SHOW PROC '/compactions'
is often not supported or remains empty because compaction is handled locally and autonomously by each BE. The FE does not maintain a global list of "compaction tasks" in the same way. To monitor compaction health in Shared-nothing: * Check Metrics (Recommended): Use Prometheus/Grafana and look for the metric 
starrocks_be_tablet_cumulative_compaction_score
or 
starrocks_be_max_tablet_compaction_score
. * Check via FE: You can see an overview of BE health, including some version-related info, with 
SHOW PROC '/backends';
. * Check a Specific Tablet: 1. Get a tablet ID: 
SHOW TABLETS FROM <table_name> LIMIT 1;
2. Check that tablet's status: 
SHOW PROC '/statistic/_check_tablet/<tablet_id>';
(This will show the version count and compaction status for that specific unit of data). Summary: How to see the "Score" If you want to know if your 
task_consume_second
settings are causing a problem, do not rely on the process list. Instead, query the metadata views:
Copy code
sql
-- This works for both architectures in newer versions (v3.1+)
SELECT 
    DB_NAME, 
    TABLE_NAME, 
    MAX_CS 
FROM information_schema.partitions_meta 
ORDER BY MAX_CS DESC 
LIMIT 10;
If 
MAX_CS
is consistently high (>100), then your compaction is struggling to keep up with the version creation rate. References • administration/management/compaction.md • administration/management/monitoring/metrics.md • administration/management/BE_configuration.md • knowledge/admin_and_operation/shared_data_compaction.md References • administration/management/compaction.md
c
@Rocky how can I check the number of tablet versions on shared nothing architecture?
r
In a shared-nothing architecture, you can check the number of tablet versions using either a SQL view (easiest for analysis) or administrative commands (best for deep-diving into one tablet). 1. Using SQL (Recommended) Starting from StarRocks v3.1, you can query the 
information_schema.be_tablets
view. This is the most efficient way to find tablets with the highest version counts across your entire cluster. Find the Top 10 tablets with the most versions:
Copy code
sql
SELECT 
    TABLE_ID, 
    PARTITION_ID, 
    TABLET_ID, 
    NUM_VERSION, 
    BE_ID 
FROM information_schema.be_tablets 
ORDER BY NUM_VERSION DESC 
LIMIT 10;
2. Using 
SHOW TABLET
(For a specific table) If you want to see the version counts for all tablets within a specific table, use the 
SHOW TABLET
command.
Copy code
sql
SHOW TABLETS FROM your_table_name;
* Column to look for: 
VersionCount
. * This will list every tablet, its backend (BE), and the current number of rowset versions it contains. 3. Checking Partition-level health If you want to see which partitions are struggling with compaction (which is usually caused by having too many versions), query the 
partitions_meta
view:
Copy code
sql
SELECT 
    DB_NAME, 
    TABLE_NAME, 
    PARTITION_NAME, 
    MAX_CS -- This is the Max Compaction Score
FROM information_schema.partitions_meta 
ORDER BY MAX_CS DESC 
LIMIT 10;
What is a "Healthy" Version Count? * < 100 versions: Very healthy. Performance will be optimal. * 100 - 500 versions: Normal for high-frequency ingestion, but compaction should be actively working to bring this down. *
1,000 versions:
 High. You may start seeing query performance degradation. * Default Limit: By default, StarRocks will trigger an error ("too many versions") if a tablet exceeds 1,000 versions (configured by 
tablet_max_versions
in the BE config). If you see versions climbing toward 1,000, you should consider increasing your 
task_consume_second
or increasing compaction threads as discussed previously. References • sql-reference/information_schema/be_tablets.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md • administration/management/resource_management/Replica.md • knowledge/admin_and_operation/shared_data_compaction.md • administration/management/compaction.md References • sql-reference/information_schema/be_tablets.md • administration/management/monitoring/metrics.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_TABLET.md • knowledge/admin_and_operation/shared_data_compaction.md
c
@Rocky how can I find the logical partition (hour in my case) for a partition_id?
r
To find the logical partition name (e.g., your hourly label) for a specific 
partition_id
, you can use the following methods: Method 1: Using 
information_schema.partitions_meta
(Recommended) Starting from StarRocks v3.1.9 and v3.2.4, you can query this system table directly. It is the most efficient way to map a 
partition_id
to its name and human-readable range.
Copy code
sql
SELECT 
    DB_NAME, 
    TABLE_NAME, 
    PARTITION_NAME, 
    PARTITION_VALUE -- This often shows the logical range/hour
FROM information_schema.partitions_meta 
WHERE PARTITION_ID = <your_partition_id>;
Method 2: Using 
SHOW PARTITIONS
 If you are on an older version or prefer standard SQL commands, you can list the partitions for a specific table.
Copy code
sql
SHOW PARTITIONS FROM <your_table_name>;
* PartitionId: The ID you have. * PartitionName: The logical name (e.g., 
p2023102710
). * Range: The actual time bounds (e.g., 
[('2023-10-27 10:00:00'), ('2023-10-27 11:00:00'))
). Method 3: Using 
SHOW PROC
 If you don't know which table the ID belongs to, you can use the 
PROC
interface. Note that this requires navigating through the database and table IDs first: 1. Find the DB and Table: If you only have a 
tablet_id
, run 
SHOW TABLET <tablet_id>
first to get the 
DbName
and 
TableName
. 2. Run the Proc command:
Copy code
sql
    -- Replace the names with your actual DB and Table
    SHOW PROC '/dbs/<db_name>/<table_name>/partitions';
Summary of common naming patterns If your table uses automatic partitioning by hour, the 
PartitionName
usually follows a pattern like: * 
p2023102710
(representing October 27, 2023, at 10:00 AM). * If you manually named them, they will appear exactly as you defined them in the 
ADD PARTITION
statement. References • knowledge/admin_and_operation/shared_data_compaction.md • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • sql-reference/information_schema/partitions.md References • sql-reference/sql-statements/table_bucket_part_index/SHOW_PARTITIONS.md • sql-reference/information_schema/partitions.md • knowledge/admin_and_operation/shared_data_compaction.md
c
@Rocky I noticed that 
information_schema.column_statistics
has the most tablet versions. I I make compaction more aggressive will I be "wasting" cpu on that table rather than my business tables? In may case, when I set 
task_consume_second
to 2 minutes I have version numbers less than 20, pushing that to 20 seconds moves it to high double digits. But it will be still lower than column_statistics. If I am aiming for super low versions like I had with long consumptions I think I will have to much compaction amplification
r
The high version count on 
information_schema.column_statistics
(which is a view for the internal 
_statistics_.column_statistics
table) is a very common observation. Here is why this is happening and why you should be careful about over-tuning for "super low" version counts. 1. Why 
column_statistics
has the most versions This table stores metadata for the Cost-Based Optimizer (CBO). * Frequent Updates: StarRocks periodically collects statistics (every 5 minutes by default via 
statistic_collect_interval_sec
) and also collects stats after the first load of any table. * Global Target: Since almost every load in your cluster eventually triggers a stats update, this single system table receives a constant stream of small 
INSERT
operations. * Result: It naturally accumulates versions faster than almost any business table. It is effectively the "audit log" of your data's health, making it very "chatty." 2. Routine Load and 
task_consume_second
 The 
task_consume_second
parameter you adjusted controls how long a Routine Load task stays open before committing. * 2 minutes: Fewer commits $\rightarrow$ larger data batches $\rightarrow$ fewer versions created per hour. * 20 seconds: 6x more commits $\rightarrow$ 6x more rowsets created per hour. * The Impact: By moving to 20 seconds, you are significantly increasing the ingestion frequency. This forces the compaction engine to work much harder to keep up. 3. Is it a "Waste" of CPU? In a sense, yes. Aiming for "super low" versions (like < 20) on a system table like 
column_statistics
is often a waste of resources due to Compaction Amplification: * Compaction Amplification: If you compact rowsets too aggressively, you end up re-reading and re-writing the same data multiple times to keep the file count low. This consumes significant Disk IO and CPU. * Diminishing Returns: The performance difference for a query reading 20 versions vs. 80 versions is negligible. The StarRocks "Unhealthy" threshold (MaxCS) is typically 100. * Recommendation: If your 
column_statistics
table is staying in the "high double digits" (e.g., 50–90 versions) with your 20-second tasks, it is perfectly healthy. You should only worry if it starts consistently exceeding 500–1,000 versions, which can trigger "too many versions" errors and slow down the CBO. Summary Advice * Don't chase "0" or "<20": High-frequency ingestion (20s) naturally results in higher steady-state version counts. This is the intended trade-off for lower data latency. * Monitor MaxCS, not just Version Count: Use 
SELECT TABLE_NAME, MAX_CS FROM information_schema.partitions_meta ORDER BY MAX_CS DESC LIMIT 10;
. As long as 
MAX_CS
is staying below 100, the system is keeping up. * Accept System Table Noise: 
column_statistics
will always be your "noisiest" table. Unless it is causing BE stability issues or OOMs, it is best to let the default compaction background threads handle it. References • using_starrocks/Cost_based_optimizer.md • sql-reference/sql-statements/data-ingestion/routine-load/CREATE_ROUTINE_LOAD.md • administration/management/compaction.md • administration/management/BE_configuration.md References • sql-reference/information_schema/be_compactions.md • using_starrocks/Cost_based_optimizer.md • administration/management/compaction.md
Open in Slack
PreviousNext
Powered by