Use when compaction score is sustained above 100, imports are failing with 'too many versions', or query MERGE time is elevated. Covers three root causes: import rate exceeding cumulative compaction throughput (Cause A), batch DELETE creating delete-predicate version explosion (Cause B), and large tablet where base compaction is not self-triggering (Cause C). Shared-nothing deployments only — for shared-data see shared-data/SKILL.md.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
The command stays on one line. Scroll horizontally to inspect it before copying.
Prefer a local copy? Download the files currently available to SkillsMP.
File Explorer
3 files
Showing SKILL.md
SKILL.md
Source instructions · Read-only preview
name
compaction
description
Use when compaction score is sustained above 100, imports are failing with 'too many versions', or query MERGE time is elevated. Covers three root causes: import rate exceeding cumulative compaction throughput (Cause A), batch DELETE creating delete-predicate version explosion (Cause B), and large tablet where base compaction is not self-triggering (Cause C). Shared-nothing deployments only — for shared-data see shared-data/SKILL.md.
Highest cumulative compaction score among all tablets on this BE, updated each scheduler round
starrocks_be_tablet_base_max_compaction_score
Highest base compaction score on this BE, updated each scheduler round
starrocks_be_tablet_update_max_compaction_score
Highest PK (update) compaction score on this BE. Not forwarded to FE.
FE-level metric (aggregated, cross-node)
Metric name
Meaning
starrocks_fe_max_tablet_compaction_score
Max of max(cumulative_score, base_score) across all BEs. ⚠️ Does NOT include update (PK) compaction score — update scores are never forwarded from BE to FE.
How to retrieve metrics
Option 1 — curl the BE HTTP port (immediate, no Prometheus needed)
# Pull all compaction-related metrics from one BE
curl -s "http://<be_ip>:<be_http_port>/metrics" | grep -E "compaction_score|compaction_num"# Targeted: cumulative, base, update scores on one BE
curl -s "http://<be_ip>:<be_http_port>/metrics" \
| grep -E "tablet_cumulative_max_compaction_score|tablet_base_max_compaction_score|tablet_update_max_compaction_score"
be 10.0.0.1 10.0.0.2 10.0.0.3;
curl -s \
| grep -E
# Loop across all BEs (replace IPs and port as needed)
-- FE-level: cluster-wide worst case (non-PK only)
starrocks_fe_max_tablet_compaction_score
-- Per-BE breakdown: which node is the hotspot
starrocks_fe_tablet_max_compaction_score
-- BE-level: cumulative score per node (Cause A signal)
starrocks_be_tablet_cumulative_max_compaction_score
-- BE-level: base score per node (Cause B / C signal)
starrocks_be_tablet_base_max_compaction_score
-- BE-level: PK update score — NOT in FE metric, must query BE directly
starrocks_be_tablet_update_max_compaction_score
Option 4 — SQL (when metrics endpoint is unavailable)
-- There is no SHOW PROC path for compaction scores in shared-nothing mode.-- (SHOW PROC '/compactions' exists but is shared-data only — it reads lake CompactionMgr history.)-- Infer per-BE pressure from be_tablets instead:SELECT be_id,
COUNT(*) AS tablet_count,
MAX(num_rowset) AS max_rowset,
SUM(CASEWHEN num_rowset >100THEN1ELSE0END) AS tablets_over_100_rowsets,
SUM(CASEWHEN num_rowset >500THEN1ELSE0END) AS tablets_over_500_rowsets
FROM information_schema.be_tablets
GROUPBY be_id
ORDERBY max_rowset DESC;
Default HTTP ports: BE = 8040, FE = 8030. Verify with SHOW BACKENDS or be.conf / fe.conf.
Key rules for interpretation:
starrocks_fe_max_tablet_compaction_score is the cluster-wide worst case for non-PK tables. It hides which BE and which type is the problem — always drill into BE-level metrics next.
PK table blind spot: If the cluster is primarily PK tables, starrocks_fe_max_tablet_compaction_score can show normal while starrocks_be_tablet_update_max_compaction_score is critically high. Always check BE-level update score separately for PK tables.
BE metrics are refreshed each time the compaction scheduler runs a round. If the scheduler is stuck, BE metrics go stale.
Update (PK) compaction scores use a completely different formula and can legitimately be in the thousands — compare version count, not raw score, to judge severity.
In shared-data mode the FE metric reflects cloud-native compaction state, unrelated to BE-level rowset scoring.
Score Value Reference
Action threshold: investigate when score stays above 100 continuously. A momentary spike is normal; only treat it as a problem if it remains above 100 for 5+ minutes of sustained observation.
Base score elevated; num_rowset high but data_size_mb tiny per rowset
DELETE version explosion
Phase 3 Cause B
Score suddenly doubles
rowset_count > 90% of tablet_max_versions — urgency mode
Act immediately to avoid write block
Update (PK) score
Score
Status
Action
< 100
Healthy
None
Sustained > 100
Delete accumulation or multi-segment rowsets building up
Check version count and delete ratio
1000+
Normal range for heavily written PK tables
Do not judge severity by score — use VersionCount (active rowsets from SHOW TABLET) < 1000 as the health bar instead
-1
Tablet skipped (compaction in progress, error state, or interval too short)
Check whether tablet is in error state
Phase 1 — Confirm Compaction Is Behind
Step 1.1 — Read metrics and identify type
Check in order:
1. starrocks_fe_max_tablet_compaction_score > 100 → drill into BE metrics
2. starrocks_be_tablet_cumulative_max_compaction_score → rising? → Cause A signal
3. starrocks_be_tablet_base_max_compaction_score → high? → Cause B or C signal
4. starrocks_be_tablet_update_max_compaction_score → check version count, not raw score
Score pattern → root cause signal:
Pattern
Points toward
FE metric high; only cumulative BE metric rising
Cause A — imports outpacing cumulative compaction
FE metric high; cumulative and base both elevated; import rate is moderate
Cause B — DELETE-driven version explosion
FE metric high; base score high on one BE; cumulative normal
Cause C — large tablet not self-triggering base compaction
FE metric oscillating; cumulative spikes then drops
Normal: compaction is keeping up but bursty; verify scan MERGE time
FE metric low or normal; PK table import/query degrading
⚠️ PK blind spot: update score not in FE metric — check starrocks_be_tablet_update_max_compaction_score directly at each BE
Two FE-side metrics — they are different:
starrocks_fe_max_tablet_compaction_score — single aggregated value, the max() across all BEs. Use for alerting.
starrocks_fe_tablet_max_compaction_score{backend="<host>:<port>"} — per-BE breakdown gauge, one time series per backend. Use in Grafana to see which BE is the hotspot.
If metrics are unavailable → proceed to Step 1.2.
Step 1.2 — Scan problem tablets via information_schema.be_tablets
Use num_rowset from be_tablets to find candidate tablets. To distinguish Pattern B (DELETE explosion) from A/C, check fe.audit.log for DELETE bursts on the suspect table — that is more direct than any derived metric.
-- Step 1: find tablets with high rowset countSELECT
bt.be_id,
bt.tablet_id,
bt.num_rowset,
bt.num_segment,
ROUND(bt.data_size /1024/1024, 1) AS data_size_mb,
tc.table_name,
tc.table_schema
FROM information_schema.be_tablets bt
LEFTJOIN information_schema.tables_config tc ON bt.table_id = tc.table_id
ORDERBY bt.num_rowset DESC
LIMIT 20;
-- Step 2: for each suspicious tablet, get VersionCountSHOW TABLET <tablet_id>;
-- Key field: VersionCount — the actual version count tracked by the system-- Run the detailcmd from the output to see VersionCount across all replicas
Match to one of these patterns:
Pattern A — Cumulative compaction lagging (→ Cause A):
Each import creates one rowset; cumulative compaction merges batches. Import rate > compaction throughput = rowsets pile up.
Pattern B — DELETE version explosion (→ Cause B):
num_rowset HIGH (e.g., 8500) — each DELETE creates one delete-predicate rowset
data_size_mb TINY relative to num_rowset (e.g., 2 MB total for 8500 rowsets ≈ 0.00024 MB each)
Each DELETE on a non-PK table creates a delete-predicate rowset — a real entry in _rs_metas, so num_rowset rises with every DELETE. VersionCount (from SHOW TABLET) equals num_rowset for non-PK tablets (both come from _rs_metas.size()). Only base compaction can merge delete-predicate rowsets. Check the data-per-rowset ratio: < 0.01 MB/rowset strongly indicates DELETE explosion rather than import pile-up.
Pattern C — Large tablet, base not triggering (→ Cause C):
num_rowset elevated, data_size_mb in GB range
data_size_mb / num_rowset is large (normal data rowsets, not delete-predicate rowsets)
Base compaction ratio trigger not met; only runs on 24h periodic timer.
Pattern: healthy:
num_rowset < 20 and VersionCount < 50 for all tablets
Step 1.3 — Fallback: Read be.INFO log
# What is the scheduler selecting right now?
grep "Found the best tablet to compact" be.INFO | tail -50
# Log format:# Found the best tablet to compact. compaction_type=cumulative tablet_id=132561 highest_score=59# Found the best tablet to compact. compaction_type=base tablet_id=238743 highest_score=34# Found the best tablet to compact. compaction_type=update tablet_id=864228 highest_score=19## compaction_type=cumulative repeatedly → Cause A# compaction_type=base on same tablet repeatedly → Cause B or C# compaction_type=update with score > 1000 → check version count, not score# Rank tablets by score
grep "Found the best tablet to compact" be.INFO \
| grep -oE "tablet_id=[0-9]+ highest_score=[0-9]+" \
| sort -t= -k4 -rn \
| head -20
# Find tablets already hitting the version error
grep "too many versions" be.INFO \
| grep -oE "tablet_id: [0-9]+" \
| sort | uniq -c | sort -rn | head -10
# Find tablets with score > 200 (significant backlog)
grep -E "highest_score=[2-9][0-9]{2,}" be.INFO | tail -30
# Size-tiered policy: find urgency-mode tablets (rowset_count > 90% of tablet_max_versions)
grep "reached_max_versions=true" be.INFO | tail -20
# Find tablets skipped repeatedly (compaction pool under pressure)
grep "skip tablet" be.INFO | grep -oE "tablet:[0-9]+" | sort | uniq -c | sort -rn | head -10
# Find compaction tasks being submitted (VLOG level 2 required)
grep "submit task to compaction pool" be.INFO | tail -20
# Format: task_id:<id>, tablet_id:<id>, compaction_type:<type>, compaction_score:<score>
Phase 2 — Locate and Characterize the Problem Tablet
Step 2.1 — Get table and partition context
-- Run on Leader FESHOW TABLET <tablet_id>;
-- Key fields: DbName, TableName, PartitionName, State, VersionCount, detailcmd-- Run the detailcmd shown in above output, e.g.:SHOW PROC '/dbs/10089/10092/10094';
-- Check: VersionCount across all 3 replicas (should be similar)-- Note the CompactionStatus URL and open in browser:-- last_base_compaction_time (Cause C signal if > 24h)-- last_cumulative_compaction_time-- cumulative_layer_point (boundary between base and cumulative)-- Determine table type (critical for Cause B vs PK compaction path)SELECT table_name, table_type, engine
FROM information_schema.tables
WHERE table_schema ='<db_name>'AND table_name ='<table_name>';
-- table_type = 'PRIMARY KEY' → PK path (update compaction, no delete-predicate versions)-- Otherwise → non-PK path (cumulative + base compaction)
Step 2.2 — Confirm root cause
-- For non-PK tables: num_rowset == num_version == VersionCount (all from _rs_metas.size())-- For PK tables: num_version = all edit versions ever; num_rowset = active rowsets in latest versionSELECT tablet_id, num_rowset, num_version, num_segment,
ROUND(data_size /1024/1024, 1) AS data_size_mb
FROM information_schema.be_tablets
WHERE tablet_id =<tablet_id>;
Cause A — Import rate exceeds cumulative compaction throughput
Confirm all match:
num_rowset > 50, data_size_mb proportional to expected row count
starrocks_be_tablet_cumulative_max_compaction_score is the dominant rising metric
Log shows compaction_type=cumulative for this tablet
Mechanism: Each import creates one rowset. Cumulative compaction merges batches of
min_cumulative_compaction_num_singleton_deltas to max_cumulative_compaction_num_singleton_deltas rowsets.
When import throughput > compaction throughput, num_rowset rises continuously.
At query time, MERGE over all rowsets is in-memory — MERGE time rises linearly with num_rowset.
Queries slow 5–20× before any error occurs.
Sub-pattern A1 (import-dominant): disk IO < 70% → add compaction threads or batch imports larger.
Sub-pattern A2 (pool-saturated): disk IO > 90%, threads running but num_rowset still climbing
→ reduce max_compaction_concurrency to improve per-task IO throughput, then throttle imports.
→ Go to Phase 3, Cause A
Cause B — Batch DELETE statements creating version explosion
Table type is non-PK (for non-PK: num_rowset == num_version == VersionCount; PK uses delete vectors)
fe.audit.log shows DELETE burst on this table
# Count DELETE statements on this table (last 24h)
grep -i "delete from <table_name>" fe.audit.log | grep "$(date +%Y-%m-%d)" | wc -l
# If count > 500 in a short window → Cause B confirmed# Find the time window of the burst
grep -i "delete from <table_name>" fe.audit.log \
| awk '{print $1, $2}' | cut -c1-16 \
| sort | uniq -c | sort -rn | head -20
# Example: 850 2025-05-25 02: → 850 DELETEs in one minute at 2AM
Mechanism: For non-PK tables, each DELETE WHERE id = 1 — even single-row — creates a
delete-predicate rowset stored in _rs_metas. This increments both num_rowset and num_version
equally (they always equal _rs_metas.size() for non-PK tablets). Cumulative compaction ignores
delete-predicate rowsets entirely; only base compaction can apply and merge them. Base compaction
is 5–10× more IO-intensive than cumulative (full segment rewrite). With 8000+ delete rowsets, base
compaction runs continuously and saturates disk IO. Once num_rowset > tablet_max_versions
(default 1000), new imports block with "too many versions".
→ Go to Phase 3, Cause B
Cause C — Large tablet, base compaction not self-triggering
Confirm all match:
num_rowset elevated, data_size_mb in GB range
data_size_mb in GB range (normal data rowsets, not delete-predicate rowsets)
CompactionStatus URL shows last_base_compaction_time > 24h ago
Mechanism: Base compaction triggers when cumulative layer size > base_cumulative_delta_ratio
(default 0.3) × base layer size. For large, slowly-written tablets the cumulative layer stays
below 30% of the multi-GB base for a long time. Base only runs on the 24h periodic timer.
The next time it fires, it processes a very large segment, causing a sudden IO spike.
→ Go to Phase 3, Cause C
Phase 3 — Take Action by Cause
Cause A — Import too frequent / Compaction threads insufficient
Immediate: Manual compaction
# Non-PK table: cumulative compaction on the problem tablet
curl -XPOST "http://<be_ip>:<be_http_port>/api/compact?compaction_type=cumulative&tablet_id=<tablet_id>"# Non-PK table: base compaction
curl -XPOST "http://<be_ip>:<be_http_port>/api/compact?compaction_type=base&tablet_id=<tablet_id>"# PK table: update compaction
curl -XPOST "http://<be_ip>:<be_http_port>/api/compact?compaction_type=update&tablet_id=<tablet_id>"
Or ADMIN EXECUTE for coarser granularity (v2.5.6+):
-- Step 1: Get backend_idSHOW BACKENDS;
-- By partition (most common)
ADMIN EXECUTEON<backend_id>'
StorageEngine.submit_manual_compaction_task_for_partition(<partition_id>, 0)
';
-- By table
ADMIN EXECUTEON<backend_id>'
StorageEngine.submit_manual_compaction_task_for_table(<table_id>, 0)
';
-- By tablet
ADMIN EXECUTEON<backend_id>'
StorageEngine.submit_manual_compaction_task_for_tablet(<tablet_id>, 0)
';
-- Check status
ADMIN EXECUTEON<backend_id>'
System.print(StorageEngine.get_manual_compaction_status())
';
Increase compaction throughput dynamically (no restart):
-- v3.2+: apply to all BEs at once via SQL-- Sub-pattern A1 (disk IO < 70%): raise concurrencyUPDATE information_schema.be_configs SETvalue=8WHERE name ='max_compaction_concurrency';
UPDATE information_schema.be_configs SETvalue=2WHERE name ='cumulative_compaction_check_interval_seconds';
-- PK table: increase update compaction threads (can only increase, not decrease at runtime)UPDATE information_schema.be_configs SETvalue=2WHERE name ='update_compaction_num_threads_per_disk';
-- Sub-pattern A2 (disk IO > 90%): REDUCE concurrency to improve per-task throughputUPDATE information_schema.be_configs SETvalue=4WHERE name ='max_compaction_concurrency';
For Flink: increase sink.buffer-flush.interval-ms (default 300000 ms) and
sink.buffer-flush.maxrows (default 500000) to batch more rows per flush.
Cause B — Batch DELETE statements (version explosion)
Step B-1: Stop the DELETE workload
# Identify source of DELETE statements
grep -i "delete from <table_name>" fe.audit.log \
| awk -F'|''{print $4}' | sort | uniq -c | sort -rn
# Output: user / IP issuing the DELETEs → kill at application level# Confirm DELETE traffic has stopped
grep -i "delete from <table_name>" fe.audit.log | tail -5
Step B-2: Assess recovery path
-- For non-PK: num_rowset == VersionCount (both from _rs_metas.size()); use num_rowset as the recovery gaugeSELECT tablet_id, num_rowset,
ROUND(data_size /1024/1024, 1) AS data_size_mb,
FROM information_schema.be_tablets
WHERE tablet_id =<tablet_id>;
-- num_rowset < 5000 → base compaction can recover in hours → Option B-2b-- num_rowset > 10000 → DROP TABLE is faster → Option B-2a
Option B-2a — Fast recovery: DROP TABLE FORCE
-- 1. Back up if neededINSERT INTO<backup_table>SELECT*FROM<table_name>;
-- 2. Force drop — bypasses recycle bin, triggers immediate metadata cleanupDROPTABLE<table_name> FORCE;
-- 3. Recreate and re-import
Score drops immediately; cluster recovers within minutes.
Option B-2b — Wait with tuned compaction
-- Shorten base compaction check intervalUPDATE information_schema.be_configs SETvalue=10WHERE name ='base_compaction_check_interval_seconds'; -- default 60-- Increase base compaction threadsUPDATE information_schema.be_configs SETvalue=3WHERE name ='base_compaction_num_threads_per_disk'; -- default 1, static but can be set dynamically on v3.2+-- Monitor: num_rowset should decrease by hundreds per interval as base compaction merges delete rowsetsSELECT tablet_id, num_rowset,
ROUND(data_size /1024/1024, 1) AS data_size_mb,
FROM information_schema.be_tablets
WHERE tablet_id =<tablet_id>;
-- Run every 5 minutes; if num_rowset not decreasing after 30min → check for base compaction errors
# Watch base compaction progress in log
grep "base.*tablet_id=<tablet_id>\|compact done.*<tablet_id>" be.INFO | tail -20
# If not progressing, check for errors
grep "base compaction.*error\|base compaction.*fail" be.INFO | tail -20
-- Instead of: DELETE FROM t WHERE id = 1; (repeated 10000x)-- Use: DELETE FROM t WHERE id IN (1, 2, 3, ...) LIMIT 10000;-- Or: ALTER TABLE t DROP PARTITION <old_partition>; -- zero-copy, no compaction-- Or: Migrate to Primary Key model (DELETEs use delete vectors, not predicate versions)
Cause C — Large tablet, base compaction not triggering
# Force base compaction on the tablet immediately
curl -XPOST "http://<be_ip>:<be_http_port>/api/compact?compaction_type=base&tablet_id=<tablet_id>"
-- Lower the size-ratio trigger so base compaction fires soonerUPDATE information_schema.be_configs SETvalue=0.1WHERE name ='base_cumulative_delta_ratio'; -- default 0.3; lower = trigger sooner-- Shorten the periodic base compaction intervalUPDATE information_schema.be_configs SETvalue=43200-- 12h; default 86400 (24h)WHERE name ='base_compaction_interval_seconds_since_last_operation';
Phase 4 — Verify Recovery
-- num_rowset should be declining; for non-PK tablets num_rowset == VersionCountSELECT tablet_id, num_rowset, num_segment,
ROUND(data_size /1024/1024, 1) AS data_size_mb
FROM information_schema.be_tablets
WHERE tablet_id =<tablet_id>;
-- Confirm no new import failures from "too many versions"SELECT LABEL, STATE, ERROR_MSG
FROM information_schema.loads
WHERE STATE ='FAILED'ORDERBY LOAD_FINISH_TIME DESC
LIMIT 10;
-- v3.2+: apply to all BEs at onceUPDATE information_schema.be_configs SETvalue=<value>WHERE name ='<param>';
SELECT*FROM information_schema.be_configs WHERE name ='<param>'; -- verify
# v2.5+: apply per BE
curl -XPOST "http://<be_ip>:<be_http_port>/api/update_config?<param>=<value>"
curl "http://<be_ip>:<be_http_port>/varz" | grep <param> # verify
Static parameters (*_num_threads_per_disk for non-PK) must be in be.conf and require restart.
Common Issues Quick Reference
Symptom
Most Likely Cause
First Check
too many versions error on import
Any compaction lag
be_tablets.num_rowset vs tablet_max_versions (1000)
case-014-scan-skew — MERGE phase bottleneck from rowset accumulation (Cause A pattern)
Cross-Skill Guides
guides/cascade-cluster-degradation.md — Full cascade: compaction backlog → import slow → BE OOM → FE deadlock. Read this when multiple symptoms appear simultaneously across skills.
