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 bymax_instance_num. It is not a fixed fraction of CPU cores in every warehouse. Check the effective value withSHOW 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 scenario | Typical characteristics | Recommended parallelism | Rationale |
|---|---|---|---|
| Single-table simple operation | Single-table point query, WHERE scanning a small amount of data, LIMIT returning a small amount of data, materialized view hit | 1 | Extra worker tasks may add overhead when the scan or result size is small |
| Two-table JOIN / aggregation | Large data volume, CPU-intensive, CPU not saturated | 16 | Fully leverages the parallel capability of the Pipeline execution engine; do not increase without limit (for example, 48 can add scheduling overhead) |
| High-concurrency stress test | Multiple queries themselves can already saturate the CPU | 1 | Excessive parallelism only adds thread scheduling and framework scheduling overhead |
| Complex query | Bottleneck is hard to identify in one shot | Default value | Adjust 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 method | Scope | When it takes effect | Recommended scenario |
|---|---|---|---|
| SQL HINT | Single SQL statement | Immediate | Fine-grained tuning of a single SQL (safest) |
| Session | Current session | Immediate | Tuning a group of related queries |
| Global | Current session and default for future sessions | Other existing sessions retain their values | An 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):
| Metric | Before | After |
|---|---|---|
| Query duration | 28 seconds | 19 seconds |
| CPU utilization | 60% | 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
| Symptom | Possible cause | Investigation suggestion |
|---|---|---|
| Query becomes slower after raising parallelism | Thread scheduling overhead exceeds parallelism gains | Check operator time costs via Profile; step back using the 4-2-1 approach |
| CPU utilization is saturated but the query becomes slower | Parallelism is too high, causing context switches | Lower the parallelism; for high-concurrency scenarios, set it to 1 |
| Global adjustment does not take effect | Existing connections have not applied the new configuration | Reconnect the client or set the variable in its session |
| Slow scans despite more pipeline tasks | Scan concurrency is limited by table model, scan mode, or scanner sizing | Inspect 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:
- Start from CPU utilization: Compare Query Profile with CPU metrics to check whether more parallel execution could help.
- Prefer SQL-level adjustment: A single-SQL HINT adjustment is the safest. Avoid aggressive global changes.
- Scenario-based decisions: Refer to the Parallelism starting points to choose an appropriate value.
- Step-down trials: For complex queries, adjust gradually using the 4-2-1 step-down approach, observing query performance and machine load.