-- 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;

# 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"

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

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

-- 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;

-- 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;

-- 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

-- 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>;

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;

# 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';

-- Routine Load: batch more rows per interval
ALTER ROUTINE LOAD FOR <job_name>
PROPERTIES (
  "max_batch_interval" = "60",
  "desired_concurrent_number" = "3"
);

-- Check for version gaps across replicas of a specific tablet
SHOW TABLET <tablet_id>;
-- Run the detailcmd from output
-- Compare Version and LstFailedVersion across replicas

-- Mark bad replica to trigger re-replication from healthy replica
ADMIN SET REPLICA STATUS PROPERTIES(
  "tablet_id" = "<tablet_id>",
  "backend_id" = "<bad_be_id>",
  "status" = "bad"
);

ADMIN REPAIR TABLE <table_name>;
ADMIN REPAIR TABLE <table_name> PARTITION (<partition_name>);

-- After repair clone completes, LstFailedVersion should equal LstSuccessVersion
SHOW TABLET <tablet_id>;

-- Total tablet count in cluster
SELECT COUNT(*) AS total_tablets FROM information_schema.be_tablets;
 
-- Tablets per BE
SELECT be_id, COUNT(*) AS tablet_count
FROM information_schema.be_tablets
GROUP BY be_id ORDER BY tablet_count DESC;
 
-- Find tables with most tablets
SELECT table_id, COUNT(DISTINCT tablet_id) AS tablet_count
FROM information_schema.be_tablets
GROUP BY table_id
ORDER BY tablet_count DESC LIMIT 20;

# Find BE PID
ps aux | grep starrocks_be
 
# Check current FD usage
ls /proc/<be_pid>/fd | wc -l
 
# Check FD limit
cat /proc/<be_pid>/limits | grep "open files"

# In BE startup script or systemd unit
ulimit -n 1000000
 
# Or in /etc/security/limits.conf
*  soft  nofile  1000000
*  hard  nofile  1000000

-- Merge small partitions (use dynamic partitioning at coarser granularity)
-- Example: change daily partitions to monthly
ALTER TABLE <table_name>
SET ("dynamic_partition.time_unit" = "MONTH");
 
-- Reduce bucket count on over-sharded tables
-- Target: ~1GB per tablet (raw data)
CREATE TABLE <table_name>_new
DISTRIBUTED BY HASH(<col>) BUCKETS <lower_n>
AS SELECT * FROM <table_name>;
 
-- Drop unused test/backup tables
DROP TABLE <old_table_name>;
 
-- Implement partition TTL to auto-expire old data
ALTER TABLE <table_name>
SET ("dynamic_partition.history_partition_num" = "90");

-- Check for same tablet repeatedly failing in balance history
SHOW PROC "/cluster_balance/history_tablets";
-- Look for same TabletId appearing many times with FAILED status

# Rolling restart: restart BEs one at a time
# Wait for each to rejoin before restarting the next
./bin/stop_be.sh && sleep 5 && ./bin/start_be.sh

-- Confirm balance tasks completing without repeated failures
SHOW PROC "/cluster_balance/history_tablets";
SHOW PROC "/statistic";

-- Primary signal: all tablets healthy
SHOW PROC "/statistic";
-- UnhealthyTabletNum = 0, ErrorStateTabletNum = 0
 
-- Confirm skew resolved (Cause A)
ADMIN SHOW REPLICA DISTRIBUTION FROM <table_name>;
-- TabletCount should be balanced; RowCount distribution should be even
 
-- Confirm compaction recovering (Cause B)
SELECT be_id, MAX(num_rowset) AS max_rowsets,
       SUM(CASE WHEN num_rowset > 100 THEN 1 ELSE 0 END) AS tablets_over_100
FROM information_schema.be_tablets
WHERE table_id = <table_id>
GROUP BY be_id;
-- num_rowset should be decreasing
 
-- Confirm replica health (Cause C)
SHOW TABLET <tablet_id>;
-- LstFailedVersion should equal LstSuccessVersion across all replicas
 
-- Confirm tablet count reduced (Cause D)
SELECT COUNT(*) AS total_tablets FROM information_schema.be_tablets;

Table created with low-cardinality distribution key
  ↓ observable: SHOW TABLET FROM <table> shows DataSize variance > 10x across tablets
All data for dominant key values routes to one or few tablets
  ↓ observable: information_schema.partitions shows uneven DATA_LENGTH; SHOW PROC '/cluster_balance/tablet_checker' may show oversized tablets
Query scan assigns one instance to the large tablet; parallelism nominally full
  ↓ observable: Profile per-instance Active time in OLAP_SCAN_NODE shows one instance orders of magnitude slower
Query bottlenecked on single slow scan instance; others idle-wait
  ↓ observable: max instance active time >> avg; total query time equals slowest instance

Inverted index build tasks accumulate due to high write rate or slow index worker
  ↓ observable: BE log inverted index pending task queue depth increasing; index build lag metric rising
Tablet cannot compact rowsets with pending index tasks; version count rises
  ↓ observable: SELECT num_rowset FROM information_schema.be_tablets shows affected tablets with high counts; BE log compaction skipped due to pending index
Compaction falls behind; many unmerged rowsets accumulate
  ↓ observable: Grafana compaction score rising; BE log compaction score warnings
Scan must MERGE many rowsets at query time
  ↓ observable: Profile MERGE time dominant in OLAP_SCAN_NODE; inverted index scan time elevated

Table/partition proliferation results in too many tablets per BE
  ↓ observable: SHOW PROC '/cluster_balance/tablet_checker' shows high tablet count per BE; BE log tablet count warning
Each tablet holds open file descriptors for segment, bloom filter, and index files
  ↓ observable: ls /proc/<be_pid>/fd | wc -l approaches ulimit -n value
BE reaches OS fd limit; cannot open new segment files for writes or reads
  ↓ observable: BE log "Too many open files"; ulimit -n shows limit lower than actual FD usage
New segment writes fail; load jobs return IO error from affected BE
  ↓ observable: Load job error: file open failed; TabletCount in Profile very high