StarRocks on Paimon: Morning Fast, Daytime Slow - Root Cause & Layered Optimization

Introduction

In lakehouse architectures where StarRocks queries Paimon external tables, a common production pain point emerges: the same SQL returns in seconds before 8 AM but slows by multiples during daytime peak hours. SQL logic and data volume remain stable, yet performance shows clear diurnal periodicity.

The essence is that while SQL logic is unchanged, the underlying physical file state of the lake table changes cyclically between day and night. Query performance fluctuation stems from the compounding effects of lake table physical file state, compute resource contention, and storage link pressure.

1. Typical Phenomenon and Essence

Phenomenon Characteristics

In production, the same query on the same Paimon external table executes quickly (seconds) before 8 AM, starts slowing after 10 AM, and may bloat by multiples to tens of times during afternoon peaks. Restarting the SQL or switching FE nodes has no effect; the issue reproduces stably every daytime and auto-recovers next dawn.

Essence Judgment

The core essence: SQL logic has not changed, but the underlying physical file morphology changes cyclically between day and night. Query performance volatility originates from the叠加 effect of lake table physical file state, compute resource contention, and storage link pressure.

2. Five-Layer Root Cause Deep Dive

Root causes can be decomposed into five layers by impact probability and weight, with chain amplification effects.

Layer 1: Core Root Cause — Paimon Lake Table Small Files and DV Fragment Accumulation

This is the primary driver of diurnal performance difference, directly determining physical scan overhead.

Off-peak state: CDC write pressure drops or pauses at dawn; compaction merge tasks complete, bucket data files merge, Delete Vector (DV) fragments are cleaned. Queries open only a few large files; metadata parsing and I/O overhead are minimal; performance is optimal.

Peak state: Daytime Flink CDC continuously writes to Paimon primary key tables; each checkpoint generates new data files and DV fragments. When write rate exceeds compaction merge rate, small file count per bucket grows linearly.

Performance impact path: StarRocks querying Paimon external tables must open each Parquet file, parse metadata, load DVs for filtering. More files mean higher file-open overhead, metadata parsing overhead, storage I/O count, causing CPU and I/O overhead to rise linearly and query latency to spike.

Layer 2: Secondary Root Cause — StarRocks Compute Layer Resource Contention

Daytime peak sees concurrent report queries, ad-hoc analysis, materialized view refreshes, data loads contending for CN node CPU, memory, I/O. Per-query compute quota drops, elongating scan, filter, aggregation stages. Off-peak exclusive resource performance naturally diverges from peak contention performance.

Layer 3: Link Root Cause — Underlying Storage System QPS Pressure

Paimon writes, compaction merges, and external table queries all hit underlying storage (HDFS / object storage).

Massive small files at peak amplify list and open metadata request counts, easily saturating storage gateway QPS limits, raising I/O response latency.

Storage-side latency back-propagates to query side, lengthening overall file scan duration, forming a vicious cycle: more fragments → more requests → slower storage → even slower queries .

Layer 4: Amplification Factor — Limited Predicate Pushdown Capability

Partition pruning filters irrelevant partitions, but massive fragment files inside partitions cannot skip open and metadata parsing via predicate pushdown. Every small file must complete open, footer parse, statistics read before judging skipability. More fragments mean higher invalid overhead ratio, severely diluting predicate pushdown gains.

Layer 5: Aggravation Factor — Cache Hit Rate Decline

Off-peak query-formed Parquet data cache and metadata cache get continuously evicted by newly generated daytime files. Cache hit rate drops, forcing massive requests through to underlying storage, exacerbating storage link pressure and stripping queries of local cache acceleration, further amplifying peak performance decay.

Key Conclusion: Five layers form a chain amplification effect. Lake table fragments are the source, triggering increased storage I/O, leading to longer query times, higher resource consumption, ultimately triggering compute contention and overall performance avalanche. Governance must start at the source; compute-side tuning alone is passive defense and cannot cure the root cause.

3. Standardized Troubleshooting Methodology

Core troubleshooting logic is controlled variable comparison : capture morning/evening run characteristics of the same SQL for horizontal comparison, first locate bottleneck layer, then drill down layer by layer, avoiding aimless parameter tuning.

Step 1: Compare Query Profiles to Determine Bottleneck Direction

Most efficient first step, locks bottleneck attribution in 5 minutes. Focus on three core metrics:

Step 2: Check Paimon Runtime State to Verify Fragment Degree

Monitor two metric categories:

File count metrics: Data file count and DV file count per bucket over time; confirm daytime continuous rise and peak-to-valley gap.

Compaction metrics: Compaction task backlog queue, execution rate, single task duration; judge if merge capacity lags write rate.

Step 3: Check StarRocks Resource Levels to Verify Compute Contention

Observe CN node CPU utilization, memory usage, disk I/O load at peak; also watch query queue count, large query memory footprint, resource group wait time to confirm resource saturation and contention.

Step 4: Check Underlying Storage Metrics to Verify Link Pressure

View HDFS / object storage total request QPS, average response latency; focus on list and get metadata request proportion and latency; confirm if storage gateway is bottleneck.

Step 5: Check Cache Hit Rate to Verify Amplification Effect

Compare morning/evening Paimon metadata cache and Parquet data cache hit rate changes; confirm if cache invalidation is an aggravation factor.

Troubleshooting Principle: Do Profile comparison first to set direction, then verify layer by layer. Forbid blind parameter tuning before root cause location to avoid introducing more uncertainty.

4. Layered Governance Optimization System

Following priority source governance → compute mitigation → link assurance → business fallback , build a four-layer governance system forming a complete closed loop from root to protection.

L1 Source Layer: Paimon Lake Table Fragment Governance (Highest Priority)

Reduce fragments at data write and merge source — the fundamental solution.

Independent Compaction Resource Pool: Isolate compaction tasks from real-time write tasks; deploy independent TaskManagers or dedicated resource pools for compaction, preventing daytime write peaks from starving compaction and causing fragment pileup. Ensuring merge rate keeps pace with write rate is core to controlling daytime file count.

Upstream Write Side Frequency Reduction: Reduce small file generation rate at source:

Reasonably increase Flink checkpoint interval from seconds to 2–5 minutes to reduce file generation frequency.

Enable sink-side batching config to increase per-batch write volume.

Set bucket count reasonably per business throughput to avoid bucket over-proliferation amplifying file count.

Partition-Level Differentiated Governance:

For high-frequency update hot partitions, optimize compaction trigger thresholds to increase merge frequency.

For historical cold partitions, periodically run Full Compaction to thoroughly clean stale DVs and redundant files.

Reduce unnecessary updates/deletes at business layer to lower DV fragment generation rate.

L2 Compute Layer: StarRocks Side Performance Optimization (High Priority)

Given lake table fragments cannot be fully eliminated, reduce query dependence on lake table scans via compute-side optimization.

Multi-Level Cache System Construction: Enable and optimize Paimon external table related caches:

Metadata cache: increase capacity, extend TTL to reduce repeated file listing and metadata requests.

Data cache: expand Parquet data cache space, improve hot data hit ratio, reduce storage-penetrating requests.

Async Materialized View Fallback: For high-frequency stable query scenarios, build async materialized views to precompute Paimon external table data into StarRocks internal tables. Queries hit internal tables directly, completely bypassing lake table scans, small file parsing, storage access — delivering order-of-magnitude performance gains; the most thorough solution for slow external table queries.

Compute Resource Isolation: Use Resource Groups or independent Warehouses to isolate core reports, materialized view refreshes from ad-hoc queries, preventing low-priority queries from crowding core business resources, ensuring peak-time core query performance stability.

Query Concurrency Control: Set resource thresholds and concurrency limits for non-partition-pruned full-scan large queries to prevent single large SQL from exhausting cluster resources and triggering cascading performance degradation.

L3 Link Layer: Underlying Storage Capability Assurance (Medium Priority)

Enhance storage gateway concurrent processing capacity; evaluate and scale QPS specs per business peak.

Optimize storage-side metadata access performance; reduce list request response latency.

Prioritize reducing storage request volume at source via L1 fragment governance, then supplement capacity via scaling; avoid blind scaling causing cost waste.

L4 Business Layer: Usage Specification Fallback (Basic Assurance)

Enforce Partition Pruning: Add validation at query entry; intercept or alert queries without partition conditions to avoid meaningless full table scans.

Off-Peak Scheduling for Large Queries: Schedule batch reports, data exports, offline training to dawn off-peak windows, avoiding daytime business peaks.

Query Tiered Governance: Classify queries by business importance; prioritize resource and priority guarantees for core business.

Governance Priority Mnemonic: Govern lake first, tune compute second, protect link, fallback business. Skipping source governance and only optimizing downstream is forever passive defense, never fundamentally solving periodic fluctuation.

5. Summary

StarRocks + Paimon architecture diurnal query performance fluctuation is a structural issue under the lakehouse "real-time write + external table query" mode, resulting from lake table fragments, compute contention, storage pressure, and cache invalidation stacking.

Core Troubleshooting Logic

Enter via same-SQL morning/evening Profile comparison; quickly locate bottleneck layer through scan file count difference: large file count gap → prioritize lake table governance; similar file count → prioritize compute resource governance.

Core Governance Logic

Advance by layered progression principle.

Lakehouse performance governance is not single-point tuning but a full-link system engineering. Only by forming a complete governance closed loop from data write, lake table storage, compute query to business usage can consistent peak/off-peak performance and stable business experience be achieved.

High-Frequency Q&A

Q1: Compaction Configured, Why Still Many Small Files at Daytime?

Most common production issue. Core logic: Compaction only merges existing stock files, cannot stop new writes continuously generating files. Compaction cleans historical fragments, but daytime CDC real-time writes keep producing new data files and DV fragments. When write rate exceeds merge rate, total file count still rises continuously.

Solution requires upstream-downstream coordination:

Upstream write side: Increase checkpoint interval, enable sink batching, set bucket count reasonably to reduce small file generation rate at source.

Lake table merge side: Independent compaction resources to ensure daytime merge capacity keeps up with write speed; adjust compaction trigger thresholds if needed to increase merge aggressiveness.

Query side fallback: Build async materialized views; high-frequency queries read StarRocks internal tables directly, so lake table fragments don't affect query performance — a root-and-branch fallback.

Q2: How Large Is Performance Gap Between Materialized View and Direct External Table Query?

Peak typically shows 1–2 orders of magnitude difference. Materialized view essence: precompute query results into StarRocks local internal tables; data optimized via sorting, indexing, compression; no small file overhead, no DV parsing overhead, no remote storage access overhead; performance matches ordinary OLAP internal tables. Direct peak-time Paimon external table query must open massive fragment files, parse DVs, cross-network access storage; multi-layer overhead stacks causing significant latency bloat.

Materialized view cost: extra storage cost and refresh latency; suits high-frequency, stable, second-to-minute latency tolerant queries; low-frequency ad-hoc queries better fit "direct external table + cache optimization".

Q3: Does Independent Compaction Task Significantly Increase Resource Cost?

Incurs some extra resource overhead but ROI is extremely high. If compaction shares resources with writes, daytime write peaks starve compaction, causing fragment pileup and eventual query performance avalanche — business impact and remediation cost far exceed independent compaction resource investment.

Practice Advice: Compaction doesn't need extremely high resource specs; core is guaranteeing its resources aren't preempted by write tasks. Start with independent slots or independent TaskManagers, adjust gradually per bucket count and write throughput to find cost-performance balance.