Import Troubleshooting

Investigation guide for import slowness, timeouts, RPC failures, publish timeouts, Primary Key model tuning, and load profile analysis.

Six root causes account for the vast majority of cases:

• Cause A — Write slow: thread pool bottleneck (async_delta_writer / memtable_flush saturated)
• Cause B — Write slow: BRPC / network issue (connection backlog, packet loss)
• Cause C — Write slow: PK index rebuild during clone / decommission
• Cause D — Publish timeout: compaction lag on PK table
• Cause E — Read slow: source-side bottleneck (Kafka partition count, file IO, HTTP client)
• Cause F — RPC Failed: statistics collection conflict saturating BRPC

Metric Taxonomy — Read This First

Before using any metrics, understand the write pipeline and where each metric fits:

Thread pool metrics (BE-side, per-node)

BRPC metrics (BE-side, per interface)

Key interfaces: tablet_writer_open, tablet_writer_add_chunks, tablet_writer_add_segment

Import state metrics (FE-side, SQL)

Key fields from information_schema.loads:

How to retrieve metrics

Option 1 — BRPC metrics endpoint (per-BE)

# View all import-related thread pool metrics
curl -s "http://<be_ip>:8060/vars" | grep -E "async_delta_writer|memtable_flush|segment_replicate|segment_flush"

# View BRPC thread utilization
curl -s "http://<be_ip>:8060/vars" | grep -E "brpc_worker|bthread|tablet_writer"

# Loop all BEs
for be in 10.0.0.1 10.0.0.2 10.0.0.3; do
  echo "=== $be ==="
  curl -s "http://$be:8060/vars" | grep -E "async_delta_writer_pending|async_delta_writer_execute|memtable_flush_io"
done

Option 2 — SQL (import state)

-- All running and recent import tasks
SELECT ID, LABEL, STATE, TYPE, PROGRESS, SCAN_BYTES, SINK_ROWS,
       LOAD_START_TIME, LOAD_FINISH_TIME, ERROR_MSG
FROM information_schema.loads
WHERE STATE NOT IN ('FINISHED', 'CANCELLED')
ORDER BY CREATE_TIME DESC;

-- Historical import throughput by minute
SELECT date_trunc('minute', load_finish_time) AS t,
       count(*) AS tpm, sum(SCAN_BYTES) AS scan_bytes, sum(sink_rows) AS sink_rows
FROM _statistics_.loads_history
GROUP BY t ORDER BY t DESC LIMIT 10;

Option 3 — FE transaction log (write vs publish duration)

# Find write vs publish duration by label or txn_id
grep "<label_or_txn_id>" fe.log | grep "finishTransaction"
# Log format: "write cost: 243ms ... publish total cost: 154ms"
# write cost >> publish cost → slow write phase (Cause A/B/C)
# publish cost >> write cost → slow publish phase (Cause D)

Option 4 — BE auto-diagnostic profile (v3.4+)

# Reached timeout profile (v3.4+: auto-logged on timeout)
grep "profile=" be.WARNING
# Example: tablet writer add chunk timeout. txn_id=1691, cost=16728ms, timeout=16500ms, profile=xxx

# Stack trace (v3.5+)
grep "diagnose stack trace, id:" be.INFO
# Extract the id, then:
grep "DIAGNOSE <id> -" be.INFO > stack_trace.log

Key rules for interpretation:

1. Start with the FE transaction log (finishTransaction) to split write cost vs publish cost — this tells you which phase to focus on.
2. If async_delta_writer.pending is high, the pool is too small — increase number_tablet_writer_threads. If execute is high with pending low, the bottleneck is within the task (check wait_flush, wait_replica, pk_preload).
3. If BRPC used ≈ total and Brpc Processing Requests is near-zero during a timeout, the BRPC layer itself is the bottleneck — not the storage layer.
4. PK tables have an additional pk_preload sub-phase in async_delta_writer.execute — this alone can cause minutes-long write stalls during clone or decommission operations.

Import Slow Signal Reference

Terminology

Phase 1 — Confirm Import Is Behind

Step 1.1 — Check import state and timestamps

-- Find slow / stuck imports
SELECT ID, LABEL, STATE, TYPE,
       TIMESTAMPDIFF(SECOND, LOAD_START_TIME, NOW()) AS elapsed_sec,
       PROGRESS, SCAN_BYTES, SINK_ROWS, ERROR_MSG
FROM information_schema.loads
WHERE STATE IN ('LOADING', 'PREPARED', 'COMMITED')
ORDER BY elapsed_sec DESC;

Step 1.2 — Determine which phase is slow

# Split write vs publish duration
grep "<label_or_txn_id>" fe.log | grep "finishTransaction"
# "write cost: Xms ... publish total cost: Yms"

Step 1.3 — Scan thread pool health

# Quick health check on all pools
curl -s "http://<be_ip>:8060/vars" \
  | grep -E "(async_delta_writer|memtable_flush|segment_replicate_sync)\.(pending|execute|io)"

If any pending metric is non-zero and sustained → pool is saturated → proceed to Phase 2.

Phase 2 — Identify Slow Phase (Read vs Write vs Publish)

Step 2.1 — Enable and read load profile

-- Session variable (Broker Load / INSERT INTO)
SET enable_profile = true;
-- Auto-enable for long imports (>60s)
SET big_query_profile_threshold = 60s;

-- Table property (Stream Load / Routine Load)
ALTER TABLE <table_name> SET ("enable_load_profile" = "true");

-- View profiles
SHOW PROFILELIST;
ANALYZE PROFILE FROM '<profile_id>';

Step 2.2 — Profile interpretation

Step 2.3 — Route to cause

Phase 3 — Take Action by Cause

Cause A — Write Slow: Thread Pool Bottleneck

Confirm all match:

• async_delta_writer.pending non-zero and sustained, OR memtable_flush.io ≈ execute
• LoadChannel.WaitFlushTime or WaitWriterTime high in profile
• Cluster CPU and network are not saturated (ruling out Cause B)

Mechanism: Import pipeline writes through async_delta_writer → memtable_flush → disk. When the writer pool or flush pool is saturated, new tablet write tasks queue. The BRPC callback on the coordinator side waits past its deadline. If the disk (HDD) is the IO bottleneck, adding threads makes it worse — reduce concurrency instead.

# Check pool saturation
curl -s "http://<be_ip>:8060/vars" | grep "async_delta_writer"
# async_delta_writer_pending: NNN  ← non-zero = pool too small
# async_delta_writer_execute: NNN  ← high with low pending = within-task bottleneck

curl -s "http://<be_ip>:8060/vars" | grep "memtable_flush"
# memtable_flush_io: NNN  ← if ≈ execute, disk is the bottleneck

-- v3.2+: increase writer threads (all BEs at once)
UPDATE information_schema.be_configs SET value = 32
WHERE name = 'number_tablet_writer_threads';  -- default 16

-- Increase flush threads per disk (if IO util < 70%)
UPDATE information_schema.be_configs SET value = 4
WHERE name = 'flush_thread_num_per_store';  -- default 2 per disk

-- If HDD and IO > 90%: REDUCE threads to improve per-task throughput
UPDATE information_schema.be_configs SET value = 8
WHERE name = 'number_tablet_writer_threads';

# v2.5+ per-BE
curl -XPOST "http://<be_ip>:8040/api/update_config?number_tablet_writer_threads=32"
curl -XPOST "http://<be_ip>:8040/api/update_config?flush_thread_num_per_store=4"

Also check RocksDB (shared-nothing) if txn_commit sub-metric is high:

# Check rocksdb write latency
grep "Stalling writes" be/meta/LOG | tail -10
# If stalling: increase rocksdb write buffer

→ Go to Phase 4 to verify recovery

Cause B — Write Slow: BRPC / Network Issue

Confirm all match:

• BRPC used ≈ total threads
• Brpc Processing Requests counter is near 0 during timeout window (server not processing)
• netstat -na | grep 8060 shows many connections in CLOSE_WAIT or TIME_WAIT

Mechanism: BRPC connections between coordinator BE and executor BEs become congested or enter a broken state. New RPC calls queue behind stuck connections. The coordinator sees timeout without the executor ever processing the request. This is different from Cause A where the executor receives the call but processes it slowly.

# Check BRPC thread saturation
curl -s "http://<be_ip>:8060/vars" | grep -E "bthread_count|brpc_worker"

# Check interface-level latency
curl -s "http://<be_ip>:8060/vars" | grep -E "tablet_writer_(open|add_chunks|add_segment)"
# latency-99 high → server-side slow; latency-avg normal but P99 high → spiky

# Check TCP connection health
netstat -na | grep 8060 | awk '{print $6}' | sort | uniq -c
# Many CLOSE_WAIT or TIME_WAIT → stale connections

-- Increase RPC timeout
ADMIN SET FRONTEND CONFIG("brpc_send_plan_fragment_timeout_ms" = "180000");

# tcpdump for network-layer analysis (capture on coordinator BE)
tcpdump -i any -n port 8060 -w /tmp/brpc_$(date +%s).pcap

If BRPC is stuck (not recovering): restart the affected BE. Review brpc_connection_pool_size in FE config.

→ Go to Phase 4 to verify recovery

Cause C — Write Slow: PK Index Rebuild

Confirm all match:

• async_delta_writer.pk_preload is high (minutes range)
• Table is a Primary Key model table
• Cluster has active clone or node decommission tasks in progress

Mechanism: During import, the PK model's async_delta_writer must preload (rebuild) the PK index for each tablet before writing. If the tablet is being cloned or decommissioned, the index is not in the local cache and must be rebuilt from scratch — this can take minutes per tablet and blocks the entire write pipeline for that tablet.

# Confirm pk_preload is the bottleneck
curl -s "http://<be_ip>:8060/vars" | grep "pk_preload"

# Check if clone tasks are running
grep "clone tablet" be.INFO | tail -20
grep "migration" fe.log | tail -20

-- Skip PK index preload during import (safe: index is rebuilt on next compaction)
UPDATE information_schema.be_configs SET value = "true"
WHERE name = 'skip_pk_preload';

-- Or per-BE
-- curl -XPOST "http://<be_ip>:8040/api/update_config?skip_pk_preload=true"

-- Enable persistent index to keep PK index on disk (avoids rebuild)
ALTER TABLE <pk_table_name> SET ("enable_persistent_index" = "true");

→ Go to Phase 4 to verify recovery

Cause D — Publish Timeout: Compaction Lag

Confirm all match:

• FE finishTransaction log shows publish total cost >> write cost
• starrocks_be_publish_version_queue_count (shared-nothing) or lake_publish_tablet_version_queuing_count (shared-data) elevated
• Table is a Primary Key model table with high upsert rate

Mechanism: PK table publish uses a synchronous apply path (enable_sync_publish): the transaction cannot commit until all replicas have applied the new rowset — including replaying delete vectors against all accumulated rowsets. When compaction lags behind upserts, accumulated rowsets multiply apply time. Each publish blocks waiting for apply to complete. Subsequent imports queue behind pending publishes.

# Monitor publish queue depth
curl -s "http://<be_ip>:8040/metrics" | grep "publish_version_queue_count"

# Check apply latency in BE log
grep "apply_rowset_commit finish" be.INFO | tail -20
# Format: apply_rowset_commit finish. tablet=<id> cost=<ms>ms
# Cost > 5000ms per commit → severe backlog

-- Increase publish worker threads
-- (be.conf: transaction_publish_version_worker_count, default = #CPU cores)
UPDATE information_schema.be_configs SET value = 16
WHERE name = 'transaction_publish_version_worker_count';

-- Check PK compaction score (see compaction skill for full diagnosis)
-- High update compaction score → run manual compaction
-- curl -XPOST "http://<be_ip>:8040/api/compact?compaction_type=update&tablet_id=<tablet_id>"

Also check for clone tasks interfering with publish:

grep "clone.*tablet" be.INFO | tail -20

→ Go to Phase 4 to verify recovery

Cause E — Read Slow: Source Side Bottleneck

Confirm all match:

• Profile shows CONNECTOR_SCAN / FileScanNode time >> OLAP_TABLE_SINK time
• OR Routine Load task count equals Kafka partition count and lag grows monotonically

Mechanism: The read phase (source → StarRocks) is the bottleneck. For Routine Load, parallelism is capped by Kafka partition count — adding StarRocks resources provides no relief. For Broker Load, many small files cause excessive open/seek overhead. For Stream Load with large JSON batches, deserialization is the CPU bottleneck.

-- Routine Load: increase batch size and consumption window
ALTER ROUTINE LOAD FOR <job_name>
PROPERTIES (
  "max_routine_load_batch_size" = "209715200",   -- 200MB (default 100MB)
  "routine_load_task_consume_second" = "30"       -- default 15s
);

-- Check Routine Load parallelism vs Kafka partition count
SHOW ROUTINE LOAD FOR <job_name>\G
-- TaskRunning == Kafka partition count → cannot increase parallelism without repartitioning

For Flink connector: check Flink CPU utilization first — it is often the bottleneck, not StarRocks.

• sink.buffer-flush.max-bytes
• sink.buffer-flush.max-rows
• sink.buffer-flush.interval-ms
• checkpoint-interval

→ Go to Phase 4 to verify recovery

Cause F — RPC Failed: Statistics Collection Conflict

Confirm all match:

• Import ERROR_MSG contains RPC Failed
• Failure pattern is intermittent, correlated with scheduled statistics collection windows
• FE log shows many concurrent ANALYZE tasks in information_schema.task_runs

Mechanism: Automatic ANALYZE triggers large-scale statistics scans across many tables. These scans saturate the BRPC send queue on BEs. Incoming plan fragment delivery for import tasks fails because the BRPC worker queue is full. FE retries exhaust or time out and the import transaction aborts.

# Check brpc latency during the failure window
curl -s "http://<be_ip>:8060/vars" | grep exec_

# Check TCP connection state on BRPC port
netstat -na | grep 8060 | head -30

-- Immediate mitigation: disable automatic statistics collection
ADMIN SET FRONTEND CONFIG("enable_collect_full_statistic" = "false");
ADMIN SET FRONTEND CONFIG("enable_statistic_collect" = "false");
ADMIN SET FRONTEND CONFIG("enable_statistic_collect_on_first_load" = "false");

-- Increase RPC timeout to tolerate BRPC congestion
ADMIN SET FRONTEND CONFIG("brpc_send_plan_fragment_timeout_ms" = "180000");

-- Long-term: tune statistics collection schedule
-- Increase interval and reduce concurrency
ADMIN SET FRONTEND CONFIG("statistic_collect_interval_sec" = "1200");
ADMIN SET FRONTEND CONFIG("statistic_collect_concurrency" = "1");

→ Go to Phase 4 to verify recovery

Phase 4 — Verify Recovery

-- Confirm import tasks are completing successfully
SELECT STATE, COUNT(*) AS cnt, AVG(TIMESTAMPDIFF(SECOND, LOAD_START_TIME, LOAD_FINISH_TIME)) AS avg_sec
FROM information_schema.loads
WHERE LOAD_FINISH_TIME > DATE_SUB(NOW(), INTERVAL 10 MINUTE)
GROUP BY STATE;
-- FINISHED should dominate; CANCELLED/FAILED should be 0

-- Confirm no new import failures
SELECT LABEL, STATE, ERROR_MSG
FROM information_schema.loads
WHERE STATE = 'CANCELLED'
ORDER BY LOAD_FINISH_TIME DESC
LIMIT 10;

-- Confirm publish queue is draining (PK tables)
-- Monitor over 2-3 minutes: count should decrease
SELECT COUNT(*) AS pending_publish
FROM information_schema.loads
WHERE STATE = 'COMMITED';

# Confirm thread pool metrics are back to normal
curl -s "http://<be_ip>:8060/vars" | grep "async_delta_writer_pending"
# Should be 0 or near-0

curl -s "http://<be_ip>:8060/vars" | grep "memtable_flush_io"
# Should be proportional to actual import load (not 100%)

# Confirm no new timeout errors in BE log
grep "Reached timeout\|RPC Failed" be.WARNING | tail -10
# Should be empty or timestamps should be in the past

Grafana: starrocks_be_publish_version_queue_count should return to near-0. Import throughput (sink_rows per minute) should return to baseline.

Thread Pool Configuration Reference

Shared-Nothing Write Pipeline

Shared-Data Write Pipeline

Applying Dynamic Parameters

-- v3.2+: apply to all BEs at once
UPDATE information_schema.be_configs SET value = <value> WHERE name = '<param>';
SELECT * FROM information_schema.be_configs WHERE name = '<param>';  -- verify

# v2.5+: apply per BE
curl -XPOST "http://<be_ip>:<be_http_port>/api/update_config?<param>=<value>"
curl "http://<be_ip>:<be_http_port>/varz" | grep <param>  # verify

Static parameters must be set in be.conf and require restart.

Import Observation Reference

Show Commands

information_schema.loads — Full Schema

Common Issues Quick Reference

Related Cases

• case-001-broker-load-backlog — task pool saturation root cause
• case-002-rpc-failed-statistics — statistics collection starving RPC
• case-007-memory-tracking-leak — slow imports caused by memory tracker leak
• case-009-stream-load-stuck — tablet meta cache bug

Causal Chains

Chain 1: async_delta_writer Pool Saturation → Write Timeout

High-concurrency imports saturate async_delta_writer thread pool
  ↓ observable: BE pool metrics show active ≈ total with queue > 0 for async_delta_writer
New delta write tasks queue; flush cannot proceed
  ↓ observable: BE log shows wait_flush duration growing per tablet write
BRPC callback exceeds deadline waiting for write acknowledgement
  ↓ observable: BE log "Reached timeout" on tablet write RPC; import task STATE stuck in LOADING
FE times out waiting for tablet commit

Import job fails with Reached timeout; data not committed.

Trigger conditions: write_buffer_size too small causing excessive flush frequency; import concurrency exceeds thread pool capacity.

Break point: Increase number_tablet_writer_threads; raise write_buffer_size; reduce import concurrency at source.

Chain 2: Statistics Collection Saturates BRPC → RPC Failed

Automatic ANALYZE triggers large-scale statistics scan across many tables
  ↓ observable: FE log shows many concurrent ANALYZE tasks in information_schema.task_runs
BRPC send queue on BE fills; plan fragment delivery fails
  ↓ observable: BE log "plan fragment send fail"; FE log "RPC Failed" sending fragments to BE
Import fragment cannot reach BE; FE retries exhaust or time out
  ↓ observable: information_schema.loads shows STATE = FAILED with error containing "RPC Failed"
Import transactions abort

Import jobs fail intermittently during scheduled statistics collection windows.

Trigger conditions: Statistics collection scheduled during peak import hours; brpc_worker_threads under-provisioned.

Break point: Separate statistics schedule from peak import windows; reduce statistic_collect_concurrency; disable via ADMIN SET FRONTEND CONFIG("enable_collect_full_statistic" = "false").

Chain 3: PK Table Compaction Lag → Rowset Accumulation → Publish Timeout

High-frequency upserts on PK table; compaction cannot keep pace
  ↓ observable: starrocks_be_publish_version_queue_count elevated and growing; num_rowset per tablet rising
Apply thread must replay many delete vectors against accumulated rowsets
  ↓ observable: BE log shows pk_preload latency increasing; wait_replica extends per commit
Publish version step blocks waiting for apply to complete
  ↓ observable: FE finishTransaction log shows publish cost >> write cost
Transaction publish deadline exceeded

Import fails with publish timeout; subsequent imports on the same table also stall.

Trigger conditions: PK table with high upsert rate; pk_index_cache_capacity too small; max_pk_compaction_threads not scaled.

Break point: Increase max_pk_compaction_threads; enlarge pk_index_cache_capacity; monitor starrocks_be_publish_version_queue_count as leading indicator.

Chain 4: Memory Tracker Leak → Import Throttle → Progress Frozen

Long-running BE process accumulates unreleased memory in tracker
  ↓ observable: SHOW PROC '/current_queries' shows cumulative memUsageBytes not decreasing after query completion
Memory tracker reports usage near mem_limit; import admission control activates
  ↓ observable: BE log shows import tasks throttled or rejected with "memory exceed"
Import tasks queue but cannot start; progress metric frozen
  ↓ observable: information_schema.loads shows STATE = LOADING with PROGRESS barely advancing over minutes
Import times out or is manually cancelled

Import appears to start successfully but makes no progress; restarting BE temporarily resolves until leak accumulates again.

Trigger conditions: Memory tracker reference-count bug in a specific code path; BE running for days without restart.

Break point: Audit SHOW PROC '/current_queries' memory; rolling restart affected BE; patch tracker release on error exit paths.

Chain 5: Routine Load Kafka Partition Bottleneck → Lag Grows

Routine Load task count capped by Kafka partition count
  ↓ observable: SHOW ROUTINE LOAD shows TaskRunning == Kafka partition count; cannot increase parallelism
Incoming message rate exceeds drain rate; lag grows
  ↓ observable: Kafka consumer group lag metric grows monotonically; PROGRESS in information_schema.loads stalls
Unconsumed messages accumulate in Kafka topic
  ↓ observable: kafka-consumer-groups.sh --describe shows LAG increasing per partition
Routine Load lag grows unboundedly; data freshness SLA breached

Downstream queries see stale data; eventually Kafka retention boundary reached and messages dropped.

Trigger conditions: Topic created with too few partitions; data volume grew after initial sizing.

Break point: Increase Kafka topic partition count; raise desired_concurrent_number in Routine Load job after repartitioning.

Cross-Skill Guides

• guides/cascade-import-rpc-failed.md — Statistics collection → BRPC starvation → import/query RPC failure. Read this when imports fail with "RPC Failed" and ANALYZE tasks are running concurrently.
• guides/cascade-cluster-degradation.md — Full cluster degradation cascade ending in BE OOM and FE deadlock; import is a key stage in the chain.

Resources

• Loading Troubleshooting
• Query Profile Structure