StarRocks Production Incident: Continuous Report Refresh Triggers Cluster Crash & Recovery

Background: Last-Day Ambush

Scenario: A StarRocks enterprise edition storage-compute separation cluster served as the core storage for a real-time data warehouse, powering a "reconciliation mobile dashboard." On the last day of the period, a large number of users accessed the dashboard simultaneously and continuously refreshed the reports.

Symptoms:

StarRocks CN node CPU usage spiked to 80%+ (Figure 1).

Some query response times exceeded 30 seconds, causing dashboard load failures.

Cluster user connections surged past 200 (far above the usual ~50).

Query p99 latency degraded from under 5 seconds to over 10 seconds.

Emergency Response: Critical Actions to Stop the Bleeding

1. Step 1: Throttle Slow Queries, Cut Off the "Resource Black Hole"

Many queries ran longer than 300 seconds (default timeout), occupying CPU and memory for extended periods and becoming "resource black holes."

Action: Reduced the StarRocks global query timeout from 300s to 10s:

-- Set global query timeout
SET GLOBAL query_timeout = 10;

Effect: Inefficient slow queries were forcibly terminated.

2. Step 2: Scale Out CN Nodes, Bring Resources Online in 5 Minutes

The storage-compute separation cluster's CN (Compute Node) layer is independent compute resources. Daily configuration was 15 CN nodes, already at load limit.

Action: Emergency scaled out 15 additional CN nodes via Kubernetes to the compute layer of the storage-compute separation cluster.

Effect: New CN nodes initialized and joined the cluster within 5 minutes. Query concurrency capacity doubled, CN CPU usage dropped from 85% to 45%, and query response times recovered to under 5 seconds.

Root Cause Analysis: From Phenomenon to Essence

1. Direct Cause: User "Irrational Behavior"

The business side did not communicate the "reconciliation day" access peak in advance. Massive users continuously refreshed reports, driving query request volume to 4x the daily norm.

Some report SQL lacked optimization (e.g., full table scans, large table joins), with single-query CPU consumption 10x that of normal queries.

2. Fundamental Cause: Dual Impact of Resource Elasticity and Architecture Choice

Storage-compute separation value not pre-leveraged: Although storage-compute separation was adopted, CN node auto-scaling was not pre-configured.

Hidden risks of storage-compute integration (comparison): If a storage-compute integrated cluster had been used, scaling out would require replica rebalancing, making new resources unavailable quickly.

Missing query governance: Multi-Warehouse capabilities were not fully utilized for resource isolation.

Lessons Learned: From Pitfalls to Long-Term Assurance

1. Storage-Compute Separation Cluster: Core Architecture Choice for Production

During this emergency, the storage-compute separation architecture was the key to completing CN scale-out in 5 minutes. Its core advantages versus storage-compute integration are clear:

(1) Core Benefits of Storage-Compute Separation: Rapid Elasticity and Resource Decoupling

Compute layer scale-out ready in seconds: CN nodes are independent compute resources. After scale-out, no data replica operations are involved; new nodes can serve queries within 5 minutes.

Storage layer remains stable and undisturbed: CN storage nodes are decoupled from compute nodes; compute layer scale-out/in does not affect data replica distribution or stability.

More optimal resource cost: Compute and storage layers can be scaled independently on demand, avoiding waste caused by the "resource bundling" of storage-compute integration.

(2) Fatal Drawbacks of Storage-Compute Integration: Low Scale-Out Efficiency and Stability Risks

Replica rebalancing required after scale-out: In storage-compute integrated clusters, BE nodes handle both storage and compute. After adding BE nodes, the cluster automatically triggers "replica rebalancing" (migrating data shards to new nodes).

High stability risk: During replica rebalancing, data migration consumes massive disk I/O and network bandwidth, potentially increasing latency or causing failures for existing queries.

Long resource availability cycle: New BE nodes typically need 30+ minutes from startup to completing replica rebalancing and truly serving business requests (depending on data volume).

2. Establish a "Business Access Reporting Mechanism": Align Expectations in Advance

The core gap in this production issue was the "information asymmetry between business and technology" — business didn't realize their operations would impact the cluster, and technology couldn't prepare resources ahead of time. A standardized business access reporting process is needed:

(1) Define "Scenarios Requiring Reporting"

Require business to submit reports 1 day in advance for:

Fixed periodic scenarios: reconciliation days, monthly report exports, weekly operational analyses.

Ad-hoc scenarios: major promotions, concentrated user access after new feature launches, data backfill needs.

High resource consumption scenarios: full table data exports, cross-table complex join queries.

(2) Key Information Required in Reports

Business-submitted reports must include:

Time window: Specific dates and time ranges (precise to the hour).

Access volume: Estimated concurrent users, query QPS (referencing daily data).

Query characteristics: Core SQL statements (including report/dashboard query logic).

Business priority: Whether the access is for core business (e.g., reconciliation is high priority, requiring stability guarantees).

(3) Technical Side Response Actions

Upon receiving a report, the technical side must complete within 1 hour:

Resource assessment: Based on storage-compute separation architecture, pre-scale dedicated Warehouse CN nodes (reserve 30% redundancy).

SQL pre-review: Run Explain analysis on core queries, optimize slow queries (e.g., create temporary materialized views).

Policy adjustment: Temporarily adjust the Warehouse's resource quotas and query timeout settings.

(4) Establish a "Two-Way Sync Group"

For core businesses, create a dedicated "business + technology" communication group. Sync cluster status and business access situations 1 hour before, during, and after the reporting window to avoid information lag.

3. Fully Leverage Enterprise Edition Multi-Warehouse + Storage-Compute Separation: Resource Isolation and Elasticity

StarRocks Enterprise Edition's Multi-Warehouse combined with storage-compute separation is the golden combination for "stability + efficiency" in large-scale production:

(1) Split Dedicated Warehouses by Business Type (Independent Compute Layer)

Core Real-Time Business Warehouse: Bound to high-spec CN nodes, ensuring low latency for real-time writes and queries.

Report Analysis Warehouse: Bound to elastic CN node groups, auto-scaling during peaks and scaling down during troughs.

Offline Data Export Warehouse: Bound to low-priority CN nodes, avoiding occupation of core resources.

(2) Configure CN Node Auto-Scaling (Storage-Compute Separation Exclusive)

Based on Kubernetes + StarRocks storage-compute separation cluster, configure HPA for each Warehouse's CN nodes:

Trigger condition: CN node CPU usage > 60% sustained for 5 minutes.

Scale-out upper limit: 3x daily instance count.

Scale-in condition: CPU usage < 30% sustained for 10 minutes.

Advantage: New CN nodes become available within 5 minutes, no need to wait for replica rebalancing.

4. Implement "Full Query Lifecycle Governance": From Ingestion to Execution

(1) Query Ingestion Layer: Warehouse + Resource Group Dual Isolation

Each business's users are bound to a dedicated Warehouse by default:

-- Bind report user to report_wh
GRANT USAGE ON WAREHOUSE report_wh TO USER 'report_user'@'%';

(2) Query Execution Layer: Set Query Timeout per User

Configure session query timeouts for different users:

-- Report user session timeout set to 10s
ALTER USER report_user SET PROPERTIES ('session.query_timeout' = '10');
-- Core business user session timeout set to 30s
ALTER USER realtime_user SET PROPERTIES ('session.query_timeout' = '30');

(3) Query Optimization Layer: Pre-Review

Establish SQL pre-review mechanism: New report SQL submitted by business must pass StarRocks Explain analysis to avoid full table scans, large table Cartesian products, and other inefficient operations.

For high-frequency report SQL, pre-create materialized views / pre-aggregated tables :

-- Pre-aggregated materialized view for reconciliation report
CREATE MATERIALIZED VIEW mv_report_reconciliation
AS SELECT 
    dt, merchant_id, SUM(amount) AS total_amount
FROM fact_trade
GROUP BY dt, merchant_id
REFRESH ASYNC EVERY 10 MINUTE;

5. Core Safeguards to Prevent StarRocks Cluster Crashes

Beyond the above strategies, a "crash protection net" must be built across three dimensions: architecture selection, data modeling, and operational mechanisms.

(1) Architecture Level: Storage-Compute Separation Is Mandatory for Large-Scale Scenarios

Abandon storage-compute integrated architecture: Avoid replica rebalancing risks during scale-out; choose storage-compute separation to achieve "compute elasticity, storage stability."

Isolate businesses by Warehouse: Each Warehouse belongs to a different business, preventing a single Warehouse failure from making the business unavailable.

(2) Data Model Level: Reduce Query Compute Cost

Adopt star/snowflake schema: Split large tables into fact tables + dimension tables to reduce cross-table join computation.

Properly configure partitioning and bucketing: Partition by time (e.g., daily), bucket by high-frequency query fields (e.g., merchant_id) to avoid full table scans.

-- Fact table partitioned by day, bucketed by merchant_id
CREATE TABLE fact_trade (
    dt DATE,
    merchant_id BIGINT,
    amount DECIMAL(18,2)
)
PARTITION BY RANGE (dt) (
    PARTITION p20251231 VALUES [('2025-12-31'), ('2026-01-01'))
)
DISTRIBUTED BY HASH(merchant_id) BUCKETS 64;

Avoid large field storage: Move JSON, TEXT, and other large fields to separate tables or mount via external tables to reduce unnecessary data reads during queries.

(3) Operational Mechanism Level: Detect Risks Early

Configure "Cluster Health Patrol": Daily automated patrol of the following metrics, triggering alerts on anomalies:

CN/BE node survival count.

Disk usage (single disk > 80% triggers storage layer scale-out).

Each Warehouse's resource usage (exceeding threshold triggers compute layer scale-out).

Regular stress testing: Monthly simulate 2x daily peak query requests to verify storage-compute separation cluster's compute elasticity.

Summary: StarRocks Storage-Compute Separation + Multi-Warehouse Stability Loop

The core conclusion from this experience is: In large-scale production scenarios, storage-compute separation is the "architectural foundation" of StarRocks stability, and Multi-Warehouse is the "core tool" for resource governance :

Storage-compute separation solves the "elasticity efficiency problem": Compute layer scale-out ready in 5 minutes, avoiding replica rebalancing risks of storage-compute integration.

Multi-Warehouse solves the "resource isolation problem": Business queries do not interfere with each other; core business priority is guaranteed.

Business reporting solves the "information gap problem": Prepare resources in advance to avoid sudden peak impacts.

Query governance solves the "inefficient request problem": End-to-end control from ingestion to execution reduces cluster load.

This battle clarified: Architecture selection is the prerequisite for stability, tool capability is the guarantee for efficiency — choose the right storage-compute separation, use multi-cluster well, to truly achieve StarRocks production-grade stability.