High Concurrency

High-Concurrency Best Practices

Investigation and tuning guide for high-QPS workloads: data modeling, primary-key optimization, query cache, pipeline parallelism, connection pooling, and emergency load disabling.

Five root causes account for most high-concurrency performance problems:

• Cause A — Connection pool exhausted (too many connections, no client pool)
• Cause B — FE planning CPU saturation (complex queries, no plan cache)
• Cause C — pipeline_dop too high for short queries (scheduling overhead)
• Cause D — Session-level timeout override causing memory volatility
• Cause E — Query cache not effective (low hit ratio, wrong workload type)

Metric Taxonomy — Read This First

Before tuning, establish a baseline measurement of the current state.

FE connection metrics

How to retrieve:

bashCopy code
# Check qe_max_connection setting
grep "qe_max_connection" fe/conf/fe.conf
# Default: 1024

# Count active connections via SQL
# (run on FE that is not itself overloaded)
SELECT COUNT(*) FROM information_schema.processlist;

# Or check current queries
# SHOW PROC '/current_queries';

Query cache metrics

How to retrieve:

bashCopy code
# Per-BE cache hit ratio (Prometheus metric)
curl -s "http://<be_host>:<be_http_port>/metrics" | grep query_cache_hit_ratio

# Detailed cache stats per BE
curl -s "http://<be_host>:<be_http_port>/api/query_cache/stat"

Pipeline parallelism metrics

How to retrieve:

bashCopy code
# Check current global pipeline_dop
mysql -e "SHOW VARIABLES LIKE 'pipeline_dop';"

# Check per-session overrides in audit log
grep "pipeline_dop" fe/log/fe.audit.log | tail -20

Audit log metrics

How to retrieve:

bashCopy code
# QueryTime distribution (last hour)
grep "$(date +'%Y-%m-%d %H')" fe/log/fe.audit.log \
  | awk -F'QueryTime=' '{print $2}' | cut -d'|' -f1 \
  | sort -n | awk 'BEGIN{c=0} {a[c++]=$1} END{
      print "P50:", a[int(c*0.50)], "P99:", a[int(c*0.99)], "Max:", a[c-1]
    }'

# MemCostBytes outliers
grep "$(date +'%Y-%m-%d')" fe/log/fe.audit.log \
  | awk -F'MemCostBytes=' '{if(NF>1) print $2}' | cut -d'|' -f1 \
  | sort -rn | head -10

# Find queries with session-level query_timeout override
grep "query_timeout" fe/log/fe.log | grep "set.*session" | tail -20

Key rules for interpretation:

1. If pipeline_dop is auto (0) on a system with many vCPUs, short queries spawn too many threads per query — the scheduling overhead exceeds query execution time. Always set pipeline_dop = 1 for high-QPS OLTP workloads.
2. Query cache hit ratio below 10% means the workload is not cache-friendly — common with high-cardinality GROUP BY or pre-shuffle aggregation. Do not allocate more cache capacity; fix the workload pattern.
3. Connection exhaustion (qe_max_connection) is a hard limit — new clients receive immediate rejection. This is different from query queue (which waits). Check both.
4. MemCostBytes outliers in audit log that do not appear in the query latency P99 are likely queries with session-level timeout overrides that run for hours without being killed.

High-Concurrency Tuning Reference

Phase 1 — Identify Bottleneck Type

Step 1.1 — Determine where queries are stuck

sqlCopy code
-- Check query states
SHOW PROC '/current_queries';
-- Key columns: QueryState (Planning/Running/etc.), ElapsedTime, MemUsageBytes

-- If many in Planning state → Cause B (FE planning CPU)
-- If many in Running state with short QueryTime expected → Cause C (pipeline_dop)
-- If connection errors before reaching query state → Cause A (connection pool)

bashCopy code
# Check FE CPU
top -b -n 1 | grep java
# If FE java process near 100% CPU → Cause B

# Check connection limit
grep "qe_max_connection" fe/conf/fe.conf
mysql -e "SELECT COUNT(*) FROM information_schema.processlist;"
# If count near qe_max_connection → Cause A

Bottleneck signal → root cause:

Phase 2 — Measure Current State

Step 2.1 — Baseline connection utilization

sqlCopy code
-- Active query count
SELECT COUNT(*) FROM information_schema.processlist;

-- Query state distribution
SELECT STATE, COUNT(*) FROM information_schema.processlist GROUP BY STATE;

Step 2.2 — Baseline cache effectiveness

bashCopy code
# Check cache hit ratio on each BE
for be in <be1> <be2> <be3>; do
    echo "=== $be ==="
    curl -s "http://$be:8040/api/query_cache/stat" | python3 -m json.tool 2>/dev/null | grep -E "hit|miss|ratio"
done

Step 2.3 — Baseline audit log latency

bashCopy code
# P99 QueryTime for the last hour
grep "$(date +'%Y-%m-%d %H')" fe/log/fe.audit.log \
  | awk -F'QueryTime=' 'NF>1{print $2}' | cut -d'|' -f1 \
  | sort -n > /tmp/qtimes.txt
wc -l /tmp/qtimes.txt
# P99 line number = 0.99 * total_count

Phase 3 — Take Action by Cause

Cause A — Connection Pool Exhausted

Confirm all match:

• FE log shows Reach limit of connections or clients receive too many connections
• SELECT COUNT(*) FROM information_schema.processlist is near or at qe_max_connection
• Client applications do not use connection pooling (verified by connection creation patterns in audit)

Mechanism: FE enforces qe_max_connection as a hard limit on simultaneous MySQL protocol connections. Without client-side connection pooling, each request opens and closes a TCP connection, which both exhausts the FE limit and adds round-trip overhead. Under bursty load (dashboard refreshes, batch job starts), the FE connection queue fills instantly.

Actions:

sqlCopy code
-- Step A-1: Increase connection limit (FE config, requires restart)
-- Edit fe/conf/fe.conf: qe_max_connection = 4096

-- Step A-2: Increase per-user connection limit
ALTER USER 'bi_user' SET PROPERTIES ('max_user_connections' = '500');

-- Step A-3: Enable query queue to absorb bursts
ADMIN SET FRONTEND CONFIG ("enable_query_queue" = "true");
ADMIN SET FRONTEND CONFIG ("query_queue_max_queued_queries" = "1000");

Copy code
# Step A-4: Client-side — mandatory connection pooling
# HikariCP example:
# hikari.minimumIdle=10
# hikari.maximumPoolSize=50
# hikari.connectionTimeout=30000

# Step A-5: JDBC URL for PreparedStatement plan caching
# jdbc:mysql://<fe_ip>:9030/<db>?useServerPrepStmts=true

sqlCopy code
-- Step A-6: FE load balancing — deploy 3+ FEs behind TCP load balancer
-- Nginx: stream proxy to port 9030 across all FE hosts

→ Go to Phase 4: Verify Recovery

Cause B — FE Planning CPU Saturation

Confirm all match:

• SHOW PROC '/current_queries' shows large number in Planning state
• top on FE host shows java process consuming near 100% CPU
• Queries are complex (many joins, views, or large partition counts)

Mechanism: FE planner runs CBO (cost-based optimizer), partition pruning, and statistics lookups for every query. With high concurrent QPS of non-trivial queries, planner threads compete for CPU. PreparedStatement plan caching avoids re-planning identical query shapes, which is the primary mitigation.

Actions:

bashCopy code
# Step B-1: Enable PreparedStatement plan caching via JDBC
# Add to JDBC URL:
# useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=256

sqlCopy code
-- Step B-2: Reduce planning cost for complex views
-- Replace complex views with materialized views for frequent query patterns

-- Step B-3: Reduce statistics collection concurrency during peak hours
ADMIN SET FRONTEND CONFIG ("statistic_collect_concurrency" = "1");
-- Or disable temporarily:
ADMIN SET FRONTEND CONFIG ("enable_statistic_collect" = "false");

bashCopy code
# Step B-4: Scale FE horizontally
# Add another FE behind the load balancer for additional planner capacity
# Add 3+ FEs; use Nginx/HAProxy TCP load balancer on port 9030

→ Go to Phase 4: Verify Recovery

Cause C — High pipeline_dop for Short Queries

Confirm all match:

• SHOW VARIABLES LIKE 'pipeline_dop' shows 0 (auto) or >1
• Queries are short (sub-second expected) but actual latency is much higher
• System QPS is lower than expected given available CPU headroom

Mechanism: When pipeline_dop = 0 (auto), StarRocks sets parallelism to vCPUs/2. For a 32-vCPU BE, each query spawns 16 pipeline threads. With 100 concurrent short queries, the OS scheduler must context-switch among 1600 threads — the overhead exceeds actual computation time. Setting pipeline_dop = 1 reduces each query to one thread, allowing the OS to schedule them efficiently.

Actions:

sqlCopy code
-- Step C-1: Set pipeline_dop = 1 globally (critical for high-QPS OLTP)
SET GLOBAL pipeline_dop = 1;

-- Step C-2: For mixed workloads (some OLAP, some OLTP)
-- Use resource groups with per-group pipeline_dop override
-- Or use query hints for OLAP queries:
SELECT /*+ SET_VAR(pipeline_dop=4) */ * FROM large_table ...;

sqlCopy code
-- Step C-3: Enable short-circuit read for PK point queries
SET GLOBAL enable_short_circuit = true;
-- Verify: EXPLAIN shows "Short Circuit Scan: true" for PK lookup queries

-- Step C-4: Confirm version compatibility
-- pipeline_dop = 1 effective from v2.3+
-- v2.4+: parallel_fragment_exec_instance_num forced to 1; pipeline_dop = 0 auto-sets to vCPUs/2

→ Go to Phase 4: Verify Recovery

Cause D — Session-Level Timeout Override Causing Memory Volatility

Confirm all match:

• BE memory drops sharply or BEs OOM-restart
• MemCostBytes outliers in audit log — queries using GB of memory
• FE log shows query_timeout set at session level by specific clients
• Global query_timeout policy appears correct but individual queries run for hours

Mechanism: A client sets query_timeout = 30000 (30000 seconds = 8+ hours) at session level, overriding the global query_timeout. Queries from this client run unbounded, accumulating memory across BEs. When multiple such queries coexist, cumulative memory exceeds BE capacity, triggering OOM kills. The global policy appears correct in SHOW VARIABLES but session-level overrides are invisible unless explicitly searched.

Actions:

bashCopy code
# Step D-1: Find session-level timeout overrides in FE log
grep "query_timeout" fe/log/fe.log | grep -v "global" | tail -20

# Step D-2: Find long-running queries in audit log
awk -F'QueryTime=' 'NF>1{qt=$2+0; if(qt>300000) print}' fe/log/fe.audit.log | tail -20
# QueryTime > 300000ms = >5 min

sqlCopy code
-- Step D-3: Identify and kill the offending queries
SHOW PROC '/current_queries';
-- Filter: ElapsedTime > expected threshold
KILL QUERY <query_id>;

-- Step D-4: Enforce timeout governance via resource groups
CREATE RESOURCE GROUP rg_governed
TO (user='offending_user')
PROPERTIES (
    "cpu_core_limit" = "4",
    "mem_limit" = "20%",
    "big_query_cpu_second_limit" = "300",
    "big_query_mem_limit" = "5368709120"  -- 5GB
);

-- Step D-5: Add SQL blacklist for queries without WHERE (optional)
ADMIN SET FRONTEND CONFIG ("enable_sql_blacklist" = "true");
ADD SQLBLACKLIST "select .* from [^w]*;";

→ Go to Phase 4: Verify Recovery

Cause E — Query Cache Not Effective

Confirm all match:

• starrocks_be_query_cache_hit_ratio < 0.1 (less than 10% hit rate)
• enable_query_cache = true is set but QPS / latency have not improved
• Workload uses high-cardinality GROUP BY or involves Shuffle before aggregation

Mechanism: Query cache stores intermediate aggregation results per tablet. It only works when the aggregation can be partitioned by tablet without a prior shuffle. High-cardinality GROUP BY (e.g., GROUP BY user_id with 100M distinct users) produces too many unique cache keys to achieve meaningful reuse. Similarly, if a query requires data from multiple tablets for the same aggregation result, results cannot be cached per-tablet.

Actions:

sqlCopy code
-- Step E-1: Verify cache is enabled and configured
SHOW VARIABLES LIKE 'enable_query_cache';
-- Check BE config:
-- query_cache_capacity (default 512MB per BE)
-- query_cache_entry_max_bytes
-- query_cache_entry_max_rows

-- Step E-2: Analyze EXPLAIN for cache eligibility
EXPLAIN SELECT ... FROM tbl GROUP BY low_cardinality_col;
-- Look for: QueryCache: Enabled in EXPLAIN output
-- If not present → workload is not cache-eligible

-- Step E-3: For cache-ineligible workloads — use PreparedStatement instead
-- PreparedStatement caches execution plans, reducing FE planning overhead
-- jdbc:mysql://<fe_ip>:9030/<db>?useServerPrepStmts=true

-- Step E-4: Optimize data model for cache eligibility
-- Use low-cardinality GROUP BY columns (e.g., date, region, category)
-- Move high-cardinality filters to WHERE clause rather than GROUP BY
-- Use materialized views for frequently repeated aggregation patterns

bashCopy code
# Step E-5: Tune cache size if hit rate is >30% but eviction rate is high
# Check eviction stats:
curl -s "http://<be_host>:8040/api/query_cache/stat" | python3 -m json.tool

# Increase cache size in be.conf (requires restart):
# query_cache_capacity = 1073741824  # 1GB

→ Go to Phase 4: Verify Recovery

Phase 4 — Verify Recovery

sqlCopy code
-- Verify connection utilization is below limit
SELECT COUNT(*) FROM information_schema.processlist;
-- Should be well below qe_max_connection

-- Verify query state distribution is healthy
SELECT STATE, COUNT(*) FROM information_schema.processlist GROUP BY STATE;
-- Should show no accumulation in Planning state

-- Verify pipeline_dop setting took effect
SHOW VARIABLES LIKE 'pipeline_dop';
-- Should show the configured value (e.g., 1)

bashCopy code
# Verify cache hit ratio improved (Cause E)
curl -s "http://<be_host>:8040/api/query_cache/stat" | python3 -m json.tool | grep ratio

# Verify no long-running outlier queries (Cause D)
grep "$(date +'%Y-%m-%d %H')" fe/log/fe.audit.log \
  | awk -F'QueryTime=' 'NF>1{qt=$2+0; if(qt>300000) print}' | wc -l
# Should be 0 or very low

# Verify FE CPU is no longer saturated (Cause B)
top -b -n 1 | grep java
# FE java process CPU% should be well below 100%

QPS validation: Run a load test with your expected concurrency. Compare:

• P99 latency before vs. after
• Max QPS before plateau vs. after
• Connection error rate (should be 0)

High-Concurrency Key Config Parameters

Data Modeling for High QPS

Partition + Sort Key + Bucket Key synergy:

• Partition key: always use a time column; ensures partition pruning via WHERE event_time >= ....
• Sort key: put highest-frequency equality filter columns first (e.g., user_id).
• Bucket key: use the query's ID column as bucket key for bucket pruning (WHERE order_id = 12345 scans only one tablet).

Primary Key Model Optimizations:

• Persistent primary key index (v3.1+): "enable_persistent_index" = "true" — moves index from memory to SSD.
• Hybrid row-column storage: "storage_type" = "column_with_row" — adds row store for single-row point queries.
• Short-circuit read: SET enable_short_circuit = true — bypasses execution engine for PK point queries. Verify with EXPLAIN: look for Short Circuit Scan: true.

Causal Chains

Chain 1: QPS Spike → Connection Pool Exhausted → Latency P99 Collapses

Copy code
Client-side QPS spikes (batch job start, traffic surge)
  ↓ observable: FE metric connection_total rising rapidly toward qe_max_connection threshold
FE connection pool reaches qe_max_connection limit
  ↓ observable: FE log "Reach limit of connections" or "too many connections" rejection messages
New connection requests queued or immediately rejected by FE
  ↓ observable: client-side connection errors; SHOW PROC '/current_queries' count at or near max
Queued connections accumulate; per-connection wait time rises
  ↓ observable: client P99 latency metric rises steeply; application connection timeout logs appear

Clients receive connection refused or timeout; throughput collapses despite FE/BE having available CPU headroom.

Trigger conditions: qe_max_connection too low; connection pooling disabled or undersized on client; sudden batch workload launch without connection ramping.

Break point: Increase qe_max_connection in fe.conf; enforce client-side connection pooling; use query queuing (enable_query_queue=true) to absorb bursts.

Chain 2: Concurrent Small Queries → FE CPU Saturated → Planning Backlog

Copy code
High volume of small queries arrives concurrently (BI dashboard refresh)
  ↓ observable: SHOW PROC '/current_queries' shows large number of queries in Planning state
FE planner threads (CBO, partition pruning, stats lookup) compete for CPU
  ↓ observable: top on FE host shows Java process consuming near 100% CPU across all cores
FE plan generation latency increases
  ↓ observable: FE log shows increased time between query receipt and "Begin to execute" line
Query throughput drops; planning backlog grows
  ↓ observable: SHOW PROC '/current_queries' count continuously growing; arrival rate exceeds completion rate

Overall throughput drops; latency rises even for trivially simple queries; FE becomes the bottleneck.

Trigger conditions: FE host under-powered relative to concurrent query load; statistics collection running concurrently with peak traffic; complex views increase per-query planning cost.

Break point: Scale FE to higher-core host; enable query plan caching for repeated patterns; stagger BI dashboard refresh schedules.

Chain 3: Large Runtime Filter Broadcast → Network Burst → Packet Loss → Query Retry

Copy code
Large join query triggers runtime filter (RF) generation at build side
  ↓ observable: EXPLAIN shows RUNTIME FILTER broadcast to all probe-side BEs; RF size estimate is large
All BE nodes simultaneously receive large RF payload
  ↓ observable: network monitoring shows synchronized burst of inbound traffic on all BE hosts at join build completion
Burst exceeds switch buffer capacity; packets dropped
  ↓ observable: netstat -s | grep retransmit shows spike in TCP retransmits on BE hosts during query
BE nodes detect RF delivery timeout; RF is disabled or fragment retries
  ↓ observable: BE log "runtime filter timeout" or "skip runtime filter due to delivery failure"; execution time increases
Retried fragments generate additional traffic, amplifying the burst
  ↓ observable: SHOW PROC '/current_queries' shows elevated execution time and retry count for same query

Query takes significantly longer; cluster-wide synchronized network bursts coincide with large join queries.

Trigger conditions: Many BEs (>20) with a single large RF; network switch insufficient buffer for synchronized multicast; RF size threshold not tuned.

Break point: Tune runtime_filter_max_in_num and runtime_filter_size_limit to cap RF payload; enable RF pipeline mode to stagger delivery.

Related Cases

• case-015-memory-volatility — session-level timeout override case study

Resources

• Query cache documentation
• Primary key model documentation