Skip to main content
VeloDB Cloud 26.x is based on Apache Doris 4.x. See Version Mapping.

Parallelism Tuning

Queries can execute in parallel across backend (BE) nodes in the selected cluster and across worker tasks within each node. The execution plan determines how work is distributed.

One-sentence definition: parallel_pipeline_task_num controls the number of worker tasks used to execute a single Fragment within a single BE.

Check whether parallelism is the bottleneck​

Before you start tuning, confirm the following:

  • Have you compared Query Profile with CPU metrics to check whether the query is CPU-bound?
  • What compute capacity and resource usage does the selected cluster have?
  • What type of query is it: point query, JOIN/aggregation, stress test, or complex query?
  • Are you using the Duplicate or Unique Key Merge-On-Write table model?
  • Do you plan to adjust at the SQL, session, or global level?

Note:

The engine default is 0, which selects parallelism automatically from the pipeline executor capacity in the selected cluster, capped by max_instance_num. It is not a fixed fraction of CPU cores in every warehouse. Check the effective value with SHOW VARIABLES LIKE 'parallel_pipeline_task_num'; before tuning.

Parallelism tuning principles​

The purpose of parallel_pipeline_task_num is to fully utilize multi-core resources and reduce query latency. However, multi-core parallelism introduces data Shuffle operators and multi-thread synchronization logic, so excessive parallelism can lead to wasted resources.

Parallelism starting points​

The values below are starting points for experiments on a 16-core backend, not recommendations for every cluster. Compare latency and resource use under representative concurrency.

Query scenarioTypical characteristicsRecommended parallelismRationale
Single-table simple operationSingle-table point query, WHERE scanning a small amount of data, LIMIT returning a small amount of data, materialized view hit1Extra worker tasks may add overhead when the scan or result size is small
Two-table JOIN / aggregationLarge data volume, CPU-intensive, CPU not saturated16Fully leverages the parallel capability of the Pipeline execution engine; do not increase without limit (for example, 48 can add scheduling overhead)
High-concurrency stress testMultiple queries themselves can already saturate the CPU1Excessive parallelism only adds thread scheduling and framework scheduling overhead
Complex queryBottleneck is hard to identify in one shotDefault valueAdjust flexibly based on Profile and machine load; try the 4-2-1 step-down approach

Prefer testing a single query with a SQL hint before changing a session or global default.


Set query parallelism​

The query engine supports manually specifying query parallelism at three granularities: SQL, session, and global.

Set parallelism for one query​

  • Purpose: Affects only a single SQL statement, providing flexible and precise control.
  • Command: Use a SQL hint.
  • Description: Suitable for fine-grained tuning of specific SQL statements without affecting other queries.

The following queries require existing TPCH-style nation and lineitem tables with the referenced columns.

SELECT /*+SET_VAR(parallel_pipeline_task_num=8)*/ *
FROM nation, lineitem
WHERE lineitem.l_suppkey = nation.n_nationkey;

SELECT /*+SET_VAR(parallel_pipeline_task_num=8,runtime_filter_mode=global)*/ *
FROM nation, lineitem
WHERE lineitem.l_suppkey = nation.n_nationkey;

Set session parallelism​

  • Purpose: Affects all queries in the current session.
  • Command: Set via session variables.
  • Description: All SQL statements within the session use this parallelism, including single-row queries, which may degrade performance for some small queries.
SET parallel_pipeline_task_num = 8;

Set a global default​

  • Purpose: Changes the warehouse connection default for future sessions and the current session.
  • Command: Use SET GLOBAL.
  • Description: Typically used for global CPU utilization tuning. A global setting requires the relevant SQL privilege. It affects the current connection and newly created connections. Reconnect existing clients, or set the session variable on those connections.
SET GLOBAL parallel_pipeline_task_num = 8;

Compare tuning scopes​

Tuning methodScopeWhen it takes effectRecommended scenario
SQL HINTSingle SQL statementImmediateFine-grained tuning of a single SQL (safest)
SessionCurrent sessionImmediateTuning a group of related queries
GlobalCurrent session and default for future sessionsOther existing sessions retain their valuesAn administrator has verified a suitable warehouse default

Data sharding and parallelism​

The execution engine can split a tablet scan into parallel scanners, so scan concurrency is not always equal to the number of tablets. Parallel scanning depends on the table model, scan mode, row ranges, and scanner sizing. The scan must allow parallel scanning and avoid the serial or pushed-down aggregation path. The engine also checks whether storage merging can be skipped or preaggregation is enabled. Table model alone does not determine scanner count.

Increasing parallel_pipeline_task_num changes pipeline task parallelism. It does not guarantee the same increase in scan concurrency. Check the scan operators and task counts in Query Profile.

Tuning examples​

Example 1: Lower parallelism under CPU pressure​

Reduce contention between concurrent queries

Issue: CPU usage on the production cluster is too high, affecting the performance of low-latency queries.

Root cause analysis: A query with many worker tasks can compete with other queries for CPU. In production scenarios where resources are tight, this can affect overall stability.

Solution: Lower the parallelism from the automatic value 0 to 4.

SET GLOBAL parallel_pipeline_task_num = 4;

Upstream observation: The Apache Doris example reports CPU usage dropping to 60% of the original peak. The source does not specify the full hardware, engine build, or workload configuration. This is an illustration of the tradeoff, not a VeloDB Cloud benchmark.

Note:

A global setting affects the current and future connections. Reconnect other clients or set the session variable explicitly on their existing connections.

Example 2: Increase parallelism for a CPU-bound query​

Compare join time and CPU utilization

Issue: A compute-intensive query takes 28 seconds to execute, with CPU utilization at only 60%.

The SQL requires existing tables t1(key, value) and t2(key, value). Row counts and measurements below are historical upstream observations.

Sample SQL:

SELECT
sum(if(t2.value IS NULL, 0, 1)) AS exist_value,
sum(if(t2.value IS NULL, 1, 0)) AS no_exist_value
FROM t1
LEFT JOIN t2 ON t1.key = t2.key;

Key Profile metrics:

The upstream excerpt below reports a value = value join predicate, whereas the sample SQL joins on key. It is retained as an illustration of operator timing and is not a matching profile capture of that SQL.

HASH_JOIN_OPERATOR (id=3 , nereids_id=448):
- PlanInfo
- join op: LEFT OUTER JOIN(BROADCAST)[]
- equal join conjunct: (value = value)
- cardinality=2,462,330,332
- vec output tuple id: 5
- output tuple id: 5
- vIntermediate tuple ids: 4
- hash output slot ids: 16
- projections: value
- project output tuple id: 5
- BlocksProduced: sum 360.099K (360099), avg 45.012K (45012), max 45.014K (45014), min 45.011K (45011)
- CloseTime: avg 8.44us, max 13.327us, min 5.574us
- ExecTime: avg 26sec153ms, max 26sec261ms, min 26sec33ms
- InitTime: avg 7.122us, max 13.395us, min 4.541us
- MemoryUsage: sum , avg , max , min
- PeakMemoryUsage: sum 1.16 MB, avg 148.00 KB, max 148.00 KB, min 148.00 KB
- ProbeKeyArena: sum 1.16 MB, avg 148.00 KB, max 148.00 KB, min 148.00 KB
- OpenTime: avg 2.967us, max 4.120us, min 1.562us
- ProbeRows: sum 1.462330332B (1462330332), avg 182.791291M (182791291), max 182.811875M (182811875), min 182.782658M (182782658)
- ProjectionTime: avg 165.392ms, max 169.762ms, min 161.727ms
- RowsProduced: sum 1.462330332B (1462330332), avg 182.791291M (182791291), max 182.811875M (182811875), min 182.782658M (182782658)

Root cause analysis:

  • The dominant time cost (ExecTime: avg 26sec153ms) is concentrated in the Join operator.
  • The total volume of data processed (ProbeRows: 1.462 billion) is huge, which is a typical CPU-intensive workload.
  • Monitoring shows CPU utilization at only 60%, indicating room for acceleration.

Solution: Increase the parallelism.

SET parallel_pipeline_task_num = 16;

Upstream result comparison (Apache Doris example, not reproduced in VeloDB Cloud):

MetricBeforeAfter
Query duration28 seconds19 seconds
CPU utilization60%90%

FAQ​

Q1: What is the default parallelism? The engine default is 0. The query engine chooses a value from the selected cluster's pipeline executor capacity and caps it with max_instance_num.

Q2: Is a higher parallelism always better? No. Excessive parallelism brings thread scheduling and framework scheduling overhead, which can actually reduce performance. For example, setting it to 48 on a 16-core backend can add overhead.

Q3: Why does SET GLOBAL not take effect on existing connections? SET GLOBAL changes the current session and the default for future sessions. Reconnect other clients, or set the variable explicitly in those sessions.

Q4: Do all table models support concurrent reads within a shard? Scanner parallelism depends on the selected scan path, including storage merging, preaggregation, and serial-read decisions. Pipeline task count and scanner count are separate. Inspect both in Query Profile.

Q5: How do I tell whether a query is CPU-bound? Use Query Profile to identify operators with high ExecTime, and compare that with cluster CPU utilization. Low CPU utilization can also result from I/O or dependency waits. Consider increasing parallelism only when the query has available parallel work and CPU capacity.


Troubleshooting​

SymptomPossible causeInvestigation suggestion
Query becomes slower after raising parallelismThread scheduling overhead exceeds parallelism gainsCheck operator time costs via Profile; step back using the 4-2-1 approach
CPU utilization is saturated but the query becomes slowerParallelism is too high, causing context switchesLower the parallelism; for high-concurrency scenarios, set it to 1
Global adjustment does not take effectExisting connections have not applied the new configurationReconnect the client or set the variable in its session
Slow scans despite more pipeline tasksScan concurrency is limited by table model, scan mode, or scanner sizingInspect the scan profile and table model before increasing task parallelism again

Summary​

Typically, you do not need to intervene in query parallelism. If adjustment is needed, follow these principles:

  1. Start from CPU utilization: Compare Query Profile with CPU metrics to check whether more parallel execution could help.
  2. Prefer SQL-level adjustment: A single-SQL HINT adjustment is the safest. Avoid aggressive global changes.
  3. Scenario-based decisions: Refer to the Parallelism starting points to choose an appropriate value.
  4. Step-down trials: For complex queries, adjust gradually using the 4-2-1 step-down approach, observing query performance and machine load.