starrocks/starrocks-debug-skills/tablet

Tablet Governance

Investigation guide for tablet health, balance, data skew, bucket optimization, and version-count / compaction-lag issues.

Five root causes account for the vast majority of cases:

• Cause A — data skew from low-cardinality bucket key
• Cause B — too many versions (compaction lag)
• Cause C — error state replicas (VERSION_ERROR, SCHEMA_ERROR)
• Cause D — too many tablets (over-sharding from fine partitions or excess buckets)
• Cause E — disk balance retry loop (metadata state inconsistency)

Metric Taxonomy — Read This First

Before using any metrics, understand the two-layer observability structure:

SQL-based observability

`SHOW TABLET FROM <table>` — per-tablet summary for a table:

`SHOW TABLET <tablet_id>` — single tablet detail:

`information_schema.be_tablets` — per-replica metrics from BE (most detailed):

How to retrieve metrics

Option 1 — SQL (primary method)

-- Per-table tablet distribution and health
SHOW TABLET FROM <table_name>;
 
-- Single tablet detail + detailcmd for replica-level VersionCount
SHOW TABLET <tablet_id>;
-- Run the detailcmd from output:
SHOW PROC '/dbs/<db_id>/<table_id>/<partition_id>';
 
-- Tablet replica distribution across BEs
ADMIN SHOW REPLICA DISTRIBUTION FROM <table_name>;
 
-- Deep tablet state per BE (most detailed)
SELECT be_id, tablet_id, num_rowset, num_version, num_segment,
       ROUND(data_size / 1024 / 1024, 1) AS data_size_mb,
       num_row, state
FROM information_schema.be_tablets
WHERE table_id = <table_id>
ORDER BY num_rowset DESC LIMIT 20;
 
-- Cluster-wide unhealthy tablets
SHOW PROC "/statistic";
SHOW PROC "/statistic/<db_id>";
 
-- Find tablets with high rowset count (compaction lag indicator)
SELECT be_id, tablet_id, num_rowset, num_segment,
       ROUND(data_size / 1024 / 1024, 1) AS data_size_mb
FROM information_schema.be_tablets
WHERE num_rowset > 100
ORDER BY num_rowset DESC LIMIT 20;
 
-- Find data skew: tablets with extreme size variance
SELECT be_id, tablet_id,
       ROUND(data_size / 1024 / 1024, 1) AS data_size_mb,
       num_row
FROM information_schema.be_tablets
WHERE table_id = <table_id>
ORDER BY data_size DESC LIMIT 20;
 
-- Count tablets per BE (distribution balance check)
SELECT be_id, COUNT(*) AS tablet_count,
       SUM(data_size) / 1024 / 1024 / 1024 AS total_gb
FROM information_schema.be_tablets
GROUP BY be_id
ORDER BY tablet_count DESC;

Option 2 — curl BE HTTP port (compaction and rowset state)

# Compaction status for a specific tablet
curl "http://<be_ip>:8040/api/compaction/show?tablet_id=<tablet_id>"
 
# All compaction metrics on a BE
curl -s "http://<be_ip>:8040/metrics" | grep -E "compaction_score|num_rowset"

Option 3 — BE HTTP tablet detail

# Full tablet metadata from BE
curl "http://<be_ip>:8040/api/meta/header?tablet_id=<tablet_id>"

Default HTTP ports: BE = 8040, FE = 8030.

Key rules for interpretation:

1. VersionCount > 500 continuously (from SHOW TABLET) indicates compaction cannot keep up with write rate — this is a compaction problem (see skill 12-compaction), not a tablet governance problem.
2. DataSize variance > 10x across tablets of the same table strongly indicates data skew from a low-cardinality or hot-value bucket key.
3. num_rowset in be_tablets equals VersionCount for non-PK tables; both come from _rs_metas.size(). For PK tables, num_rowset = active rowsets in latest version (usually smaller).
4. LstFailedVersion > 0 and LstFailedVersion != LstSuccessVersion on a replica means that replica has a version gap — it needs repair clone.
5. Total tablet count per cluster should be monitored: > 500,000 tablets creates FE metadata overhead and BE file descriptor pressure.

Tablet Health Reference

Phase 1 — Assess Tablet Health

Step 1.1 — Cluster-wide quick scan

-- Check for unhealthy tablets across all databases
SHOW PROC "/statistic";

• UnhealthyTabletNum = 0 everywhere → proceed to performance-focused steps
• UnhealthyTabletNum > 0 → note which DB IDs are affected, go to Step 1.2

Step 1.2 — BE-level tablet scan

-- Find tablets with high rowset count (compaction lag signal)
SELECT be_id, COUNT(*) AS tablet_count,
       MAX(num_rowset) AS max_rowsets,
       SUM(CASE WHEN num_rowset > 100 THEN 1 ELSE 0 END) AS tablets_over_100_rowsets,
       SUM(CASE WHEN num_rowset > 500 THEN 1 ELSE 0 END) AS tablets_over_500_rowsets
FROM information_schema.be_tablets
GROUP BY be_id
ORDER BY max_rowsets DESC;
 
-- Check for severely skewed tablets across all tables
SELECT table_id, be_id,
       MAX(data_size) / 1024 / 1024 AS max_tablet_mb,
       MIN(data_size) / 1024 / 1024 AS min_tablet_mb,
       MAX(data_size) / NULLIF(MIN(data_size), 0) AS size_ratio
FROM information_schema.be_tablets
GROUP BY table_id, be_id
HAVING size_ratio > 10
ORDER BY size_ratio DESC LIMIT 10;

Step 1.3 — Identify issue type

Phase 2 — Identify Problem Tablets

Step 2.1 — Drill into suspect table

-- Tablet distribution for the table
SHOW TABLET FROM <table_name>;
 
-- Replica distribution across BEs
ADMIN SHOW REPLICA DISTRIBUTION FROM <table_name>;
 
-- Detailed per-replica metrics
SELECT be_id, tablet_id, num_rowset, num_version,
       ROUND(data_size / 1024 / 1024, 1) AS data_size_mb,
       num_row, state
FROM information_schema.be_tablets
WHERE table_id = <table_id>
ORDER BY data_size DESC LIMIT 30;

Step 2.2 — Confirm root cause

Cause A — Data skew

Confirm all match:

• DataSize varies > 10x between tablets of the same table
• num_row also varies proportionally (not a compaction artifact)
• Distribution key has low cardinality or a dominant hot value

→ Go to Phase 3, Cause A

Cause B — Too many versions

Confirm all match:

• num_rowset > 100 in be_tablets for multiple tablets
• VersionCount > 500 from SHOW TABLET <tablet_id>
• Compaction score rising (check skill 12-compaction for compaction-specific diagnosis)

→ Go to Phase 3, Cause B

Cause C — Error state replicas

Confirm all match:

• ErrorStateTabletNum > 0 in SHOW PROC "/statistic"
• SHOW TABLET FROM <table> shows LstFailedVersion > 0 and differs from LstSuccessVersion
• Replica State is not NORMAL

→ Go to Phase 3, Cause C

Cause D — Too many tablets

Confirm all match:

• Total tablet count > 500,000 or tablet count per BE > 50,000
• Many small partitions (< 100MB each) or excess bucket count relative to data size
• BE log shows FD warnings or ulimit -n approaching limit

→ Go to Phase 3, Cause D

Cause E — Disk balance retry loop

Confirm all match:

• SHOW PROC "/cluster_balance/history_tablets" shows same tablet repeatedly failing
• ErrMsg references metadata state or _shutdown_tablets
• BE IO elevated on specific disk without progress

→ Go to Phase 3, Cause E

Phase 3 — Take Action by Cause

Cause A — Data skew (low-cardinality bucket key)

Step A-1: Confirm skew pattern

-- Check approximate value distribution in the bucket key column
SELECT <bucket_key_col>, COUNT(*) AS cnt
FROM <table_name>
GROUP BY <bucket_key_col>
ORDER BY cnt DESC LIMIT 20;
-- If top value holds >> 1/bucket_count of total rows → bucket key has hot values

Step A-2: Identify correct bucket key

The bucket key must have high cardinality and uniform distribution. Good candidates:

• User ID, order ID, device ID (UUID-like)
• Composite keys that spread data evenly

Avoid: status, region, date, boolean columns.

Step A-3: Recreate table with better bucket key

-- Create new table with better bucket key
CREATE TABLE <table_name>_new
DISTRIBUTED BY HASH(<high_cardinality_col>) BUCKETS <n>
AS SELECT * FROM <table_name>;
 
-- Swap
ALTER TABLE <table_name> RENAME <table_name>_old;
ALTER TABLE <table_name>_new RENAME <table_name>;

Step A-4: Use StarRocksBuckets CLI for analysis

The standalone StarRocksBuckets utility can analyze existing distributions and generate ALTER TABLE DDL for optimal bucket count and key. Run against the affected table before deciding on the new schema.

Cause B — Too many versions (compaction lag)

Step B-1: Identify affected tablets

SELECT be_id, tablet_id, num_rowset,
       ROUND(data_size / 1024 / 1024, 1) AS data_size_mb
FROM information_schema.be_tablets
WHERE num_rowset > 100
ORDER BY num_rowset DESC LIMIT 20;

Step B-2: Confirm compaction type and take action

This is a compaction problem. Use skill 12-compaction for the full diagnosis.

Quick remediation:

# Manual compaction trigger (cumulative)
curl -XPOST "http://<be_ip>:8040/api/compact?compaction_type=cumulative&tablet_id=<tablet_id>"
 
# Manual compaction trigger (base — for delete explosion)
curl -XPOST "http://<be_ip>:8040/api/compact?compaction_type=base&tablet_id=<tablet_id>"

-- Increase compaction throughput (v3.2+)
UPDATE information_schema.be_configs SET value = 8
WHERE name = 'max_compaction_concurrency';
 
UPDATE information_schema.be_configs SET value = 2
WHERE name = 'cumulative_compaction_check_interval_seconds';

Step B-3: Reduce import frequency if needed