high-concurrency

name	high-concurrency
description	Use when optimizing for high-QPS (thousands of queries/sec) workloads or diagnosing throughput plateaus and latency spikes under load. Covers pipeline_dop=1 tuning, connection pooling (HikariCP/Druid), PreparedStatement plan caching, query cache hit-rate analysis, PK model point-query optimization (short-circuit scan, persistent index), and detecting session-level timeout overrides that cause memory volatility.
version	2.0.0
category	high-concurrency
keywords	["high QPS","connection pool","PreparedStatement","query cache","pipeline_dop","primary key","short circuit"]
tools	["find"]
related_cases	["case-015-memory-volatility"]

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

Metric / Observable	Meaning
Connection count approaching qe_max_connection	Connection layer bottleneck
SHOW PROC '/current_queries' count	Active concurrent queries right now
FE log Reach limit of connections	Connection limit hit — new clients rejected

How to retrieve:

# 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

Metric	Meaning
starrocks_be_query_cache_hit_ratio	Cache hit ratio (0–1); <0.1 = cache not effective
query_cache_capacity (BE config)	Total cache size allocated per BE

How to retrieve:

# 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

Metric / Observable	Meaning
pipeline_dop current session value	Degree of parallelism per query fragment
SHOW PROC '/current_queries' → many queries in RUNNING	Queries competing for pipeline threads

How to retrieve:

# 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

Metric	Meaning
QueryTime distribution	Latency profile — P50/P99 separation indicates outliers
MemCostBytes per query	Memory per query; outliers indicate unbounded queries
ScanRows per query	Data volume per query; large values indicate full scans

How to retrieve:

# 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

Scenario	Key signal	Recommended action
QPS plateau below expected throughput	pipeline_dop > 1; short query profile shows scheduler overhead	SET GLOBAL pipeline_dop = 1
Connection exhausted; clients rejected	qe_max_connection reached; FE log Reach limit of connections	Increase qe_max_connection; enforce client-side connection pooling
Cache hit ratio < 10%	starrocks_be_query_cache_hit_ratio < 0.1	Analyze workload — fix high-cardinality GROUP BY; consider PreparedStatement for plan reuse
Memory volatility; BEs OOM	MemCostBytes outliers in audit; long-running queries; session-level overrides	Kill offending queries; add resource group timeout governance
FE CPU near 100%; queries stuck in Planning	SHOW PROC '/current_queries' shows large count in Planning state	Enable PreparedStatement (useServerPrepStmts=true); scale FE; stagger dashboard refreshes
Connection storm on cold start	No connection pool configured	Deploy HikariCP or Druid; set pool min/max to expected concurrency

---

Phase 1 — Identify Bottleneck Type

Step 1.1 — Determine where queries are stuck

-- 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)

# 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:

Pattern	Points toward
Clients get too many connections error	Cause A — connection pool exhausted
SHOW PROC '/current_queries' shows many in Planning; FE CPU high	Cause B — FE planning CPU saturation
QPS plateaus; short queries slow; pipeline_dop > 1	Cause C — pipeline_dop overhead
BE memory drops sharply; OOM restarts; outlier queries in audit	Cause D — session-level timeout override
Cache hit ratio < 10% despite high QPS	Cause E — query cache not effective

---

Phase 2 — Measure Current State

Step 2.1 — Baseline connection utilization

-- 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

# 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

# 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:

-- 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");

# 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

-- 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:

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

-- 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");

# 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:

-- 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 ...;

-- 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:

# 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

-- 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:

-- 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

# 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

-- 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)

# 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

Parameter	Default	Description	Dynamic
qe_max_connection	1024	Max simultaneous MySQL connections per FE	No (fe.conf restart)
max_user_connections	100	Per-user connection limit	Yes (ALTER USER SET PROPERTIES)
pipeline_dop	0 (auto)	Pipeline parallelism per fragment; set to 1 for high-QPS OLTP	Yes (SET GLOBAL)
enable_query_cache	false	Enable per-tablet query result caching	Yes (SET GLOBAL)
query_cache_capacity	536870912 (512MB)	Cache size per BE node	No (be.conf restart)
query_cache_entry_max_bytes	4194304 (4MB)	Max size of a single cache entry	No (be.conf restart)
query_cache_entry_max_rows	409600	Max rows in a single cache entry	No (be.conf restart)
enable_query_queue	false	Enable query queuing to absorb bursts	Yes (FE ADMIN SET)
query_queue_max_queued_queries	1024	Max queries waiting in queue	Yes (FE ADMIN SET)
enable_short_circuit	false	Short-circuit scan for PK point queries	Yes (SET GLOBAL)
enable_persistent_index	false	Persistent PK index on SSD (PK tables)	No (table property at creation)
statistic_collect_concurrency	3	Concurrent statistics collection jobs	Yes (FE ADMIN SET)
disable_load_job	false	Emergency: stop all import jobs	Yes (FE ADMIN SET)

---

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

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

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

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