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.
El comando permanece en una sola línea. Desplázate horizontalmente para revisarlo antes de copiarlo.
¿Prefieres una copia local? Descarga los archivos que SkillsMP tiene disponibles ahora.
Explorador de archivos
3 archivos
Mostrando SKILL.md
SKILL.md
Instrucciones de origen · Vista previa de solo lectura
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.
