starrocks/starrocks-debug-skills/resource-isolation

# All resource group metrics from FE
curl -s "http://<fe_host>:8030/metrics?type=json" \
  | python3 -m json.tool \
  | grep -A 5 "resource_group"
 
# Or raw metrics
curl -s "http://<fe_host>:8030/metrics" | grep resource_group

# BE resource group metrics
curl -s "http://<be_host>:8040/metrics" | grep resource_group
 
# Loop across all BEs
for be in <be1> <be2> <be3>; do
    echo "=== $be ==="
    curl -s "http://$be:8040/metrics" | grep resource_group
done

# Check queue configuration
mysql -e "ADMIN SHOW FRONTEND CONFIG LIKE 'query_queue%';"
 
# Check queue depth in FE metrics
curl -s "http://<fe_host>:8030/metrics" | grep query_queue
 
# Check if queries are being queued or rejected
grep -E "queue is full|admitted to queue|query rejected" fe/log/fe.log | tail -20

# Queries assigned to default_wg (unclassified)
grep "resource_group=default_wg" fe/log/fe.audit.log | wc -l
 
# CPU cost per user (for cpu_core_limit tuning)
awk -F'|' '{
    for(i=1;i<=NF;i++) {
        if($i ~ /^User=/) user=substr($i,6)
        if($i ~ /^cpuCostNs=/) cpu=substr($i,12)+0
    }
    print user, cpu
}' fe/log/fe.audit.log | awk '{a[$1]+=$2} END{for(u in a) print a[u]/1e9, u}' | sort -rn | head -10
 
# Memory outliers
grep "memCostBytes" fe/log/fe.audit.log \
  | awk -F'memCostBytes=' '{print $2}' | cut -d'|' -f1 | sort -rn | head -10

-- Are queries being classified into resource groups?
-- Check a running query's resource group:
EXPLAIN VERBOSE <SQL>;
-- Look for: ResourceGroup: <group_name>
 
-- Set resource group for current session (for testing):
SET resource_group = '<group_name>';

# What fraction of queries go to default_wg (unclassified)?
grep "resource_group=default_wg" fe/log/fe.audit.log | wc -l
grep "resource_group=" fe/log/fe.audit.log | wc -l
# High ratio → Cause A

# Memory limit exceeded errors
grep -E "Memory limit exceeded|MemTracker exceed" be/log/be.log | tail -10
# → Cause B
 
# Queue full rejections
grep -E "queue is full|query rejected" fe/log/fe.log | tail -10
# → Cause C
 
# Unbounded big queries (no circuit breaker fired)
grep "$(date +'%Y-%m-%d')" fe/log/fe.audit.log \
  | awk -F'QueryTime=' 'NF>1{qt=$2+0; if(qt>600000) print}' | tail -10
# QueryTime > 600s → Cause D

# Statistics-related BRPC errors
grep -E "rpc.*statistic|statistic.*rpc|brpc.*fail" fe/log/fe.log | tail -10
# → Cause E

SHOW RESOURCE GROUPS ALL;
-- Key fields: cpu_core_limit, mem_limit, concurrency_limit
-- big_query_cpu_second_limit, big_query_scan_rows_limit, big_query_mem_limit

-- CPU usage per user (last 30 days) — for cpu_core_limit tuning
SELECT user, SUM(cpuCostNs)/1e9 AS cpu_sec
FROM starrocks_audit_db__.starrocks_audit_tbl__
WHERE state IN ('EOF','OK')
  AND timestamp >= now() - INTERVAL 30 DAY
GROUP BY user
ORDER BY cpu_sec DESC;
 
-- Memory P99 per user — for big_query_mem_limit tuning
SELECT user, percentile_approx(memCostBytes, 0.99) AS mem_p99
FROM starrocks_audit_db__.starrocks_audit_tbl__
WHERE state IN ('EOF','OK')
GROUP BY user
ORDER BY mem_p99 DESC;
 
-- Query time P99 per user — for big_query_cpu_second_limit tuning
SELECT user, percentile_approx(queryTime, 0.99)/1000 AS time_p99_sec
FROM starrocks_audit_db__.starrocks_audit_tbl__
WHERE state IN ('EOF','OK')
GROUP BY user
ORDER BY time_p99_sec DESC;

-- Step A-1: Create resource groups for each user type
CREATE RESOURCE GROUP rg_bi
TO (user='bi_user', role='bi_role')
PROPERTIES (
    "cpu_core_limit" = "10",
    "mem_limit" = "30%",
    "concurrency_limit" = "10",
    "big_query_cpu_second_limit" = "600",
    "big_query_scan_rows_limit" = "1000000000",
    "big_query_mem_limit" = "10737418240"
);
 
CREATE RESOURCE GROUP rg_etl
TO (user='etl_user')
PROPERTIES (
    "cpu_core_limit" = "16",
    "mem_limit" = "40%",
    "concurrency_limit" = "5"
);
 
-- Step A-2: Create a catch-all group to prevent default_wg fallback
CREATE RESOURCE GROUP rg_default_catch
TO ()  -- matches all unmatched queries
PROPERTIES (
    "cpu_core_limit" = "4",
    "mem_limit" = "20%",
    "concurrency_limit" = "20"
);
 
-- Step A-3: Verify classification is working
-- Run a query and check EXPLAIN VERBOSE for ResourceGroup field
EXPLAIN VERBOSE SELECT COUNT(*) FROM <table>;

# Step B-1: Identify current mem_limit vs actual P99 usage
# (Use audit log query from Phase 2 Step 2.2)
 
# Step B-2: Configure dedicated spill directory on separate disk
# In be.conf:
# spill_local_storage_dir = /data/spill/starrocks
mkdir -p /data/spill/starrocks
echo "spill_local_storage_dir = /data/spill/starrocks" >> be/conf/be.conf
# Requires BE restart

-- Step B-3: Increase mem_limit based on P99 usage (set to 1.5x P99)
ALTER RESOURCE GROUP rg_bi SET PROPERTIES (
    "mem_limit" = "45%"
);
 
-- Step B-4: Set big_query_mem_limit as circuit breaker (2x P99)
ALTER RESOURCE GROUP rg_bi SET PROPERTIES (
    "big_query_mem_limit" = "21474836480"  -- 20GB
);