What are the control parameters for DOP? - PolarDB - Alibaba Cloud Documentation Center

PolarDB supports multiple policies to control the degree of parallelism (DOP) globally or for a single query and ensure that a reasonable DOP value is selected when resource loads are appropriate.

Parameters for parallel query queuing policies

max_parallel_workers specifies the maximum number of parallel workers within a single node. If the number of parallel workers exceeds the max_parallel_workers value, new parallel queries enters the queue and waits. The queue scheduling use the first-in, first-out (FIFO) policy.

queuing_parallel_degree_limit specifies the maximum DOP allowed in the queue. If the total number of parallel workers in the queue exceeds the queuing_parallel_degree_limit value, the queue is considered full and sequential execution is used for subsequent queries.

pq_max_queuing_time specifies the maximum waiting time in the queue. If the waiting time in the queue exceeds the specified value, the queue is immediately removed and sequential execution is used.

Parameters

Parameters	Level	Description
max_parallel_workers	Global	The maximum number of parallel workers. If the number of parallel workers exceeds the specified value, new parallel queries enters the queue and waits. Valid values: 1 to 10000. The default value is twice the number of CPU cores.
queuing_parallel_degree_limit	Global	The maximum sum of DOP values for parallel queries that can enter the queue at a time. If the specified value is exceeded, parallel queries are prohibited and sequential execution is used. Valid values: 0 to 10000. Default value: 64.
pq_max_queuing_time	Global,Session	The maximum waiting time in the queue. If the waiting time in the queue exceeds the specified value, sequential execution is used. Unit: millisecond. Valid values: 0 to 18446744073709551615. Default value: 200.

Statistical variables:

Variable	Level	Description
PQ_refused_over_total_workers	Global and session	The number of queries that are not performed in parallel due to the limit on the total number of workers.
PQ_refused_over_max_queuing_time	Global and session	The number of queries for which elastic parallel query fails due to queuing timeout.
Total_running_parallel_workers	Global	The number of parallel workers that are running.

Adaptive DOP settings

PolarDB supports adaptive DOP settings based on query costs and current resource loads, including DOP for single-node elastic parallel query and scheduling policies for multi-node elastic parallel query. The following table describes the parameters.

Parameter

Level

Description

parallel_degree_policy

Global

The DOP policy for an individual query. Valid values:

TYPICAL: PolarDB sets the DOP to the same value as max_parallel_degree, regardless of database loads such as CPU utilization.
AUTO: PolarDB enables or disables the elastic parallel query feature based on database loads such as CPU utilization, and selects the DOP based on the query cost.
REPLICA_AUTO (default): Only read-only nodes determine whether to enable the elastic parallel query feature based on database loads such as CPU utilization, and select the DOP based on the query cost. The primary node does not perform elastic parallel query.

parallel_workers_policy

session

The elastic parallel query policy. Valid values:

LOCAL: single-node elastic parallel query.
AUTO: The elastic parallel query feature is enabled. Task scheduling is performed based on the real-time loads of the nodes in the cluster. If the computing resources of a node are insufficient, idle resources of other nodes in the cluster can be used for scheduling. Multi-node elastic parallel query is enabled when the query cost exceeds the specified threshold.
MULTI_NODES: forces multi-node elastic parallel query. The maximum DOP increases with the number of nodes. This value is suitable for analytic queries on large amounts of data.

Limits on system resources

If the parallel_degree_policy parameter is set to AUTO, PolarDB enables or disables parallel queries based on the CPU usage, memory usage, or the input/output operations per second (IOPS) value of the database. PolarDB also sets the DOP based on the costs of parallel queries.

Parameter	Level	Value	Description
loose_auto_dop_cpu_pct_hwm	Global	Valid values: 0 to 100. Default value: 70.	The threshold of the CPU usage. If the CPU usage exceeds the threshold, PolarDB disables parallel queries.
loose_auto_dop_mem_pct_hwm		Valid values: 0 to 100. Default value: 90.	The threshold of the memory usage. If the memory usage exceeds the threshold, PolarDB disables parallel queries.
loose_auto_dop_iops_pct_hwm		Value values: 0 to 100. Default value: 80.	The threshold of the IOPS usage. If the IOPS usage exceeds the threshold, PolarDB disables parallel queries.
loose_auto_dop_low_degree_cost		Value value: 0 to 18446744073709551615. Default value: 500000.	The policy that is used to automatically set the DOP. After you enable parallel queries, PolarDB sets the DOP based on the following rules: If the optimizer estimates that the costs of serial queries are lower than the value of this parameter, the DOP is set to 2. If the optimizer estimates that the costs of serial queries are greater than or equal to the value of this parameter, the DOP is set to the same value as max_parallel_degree. Note This parameter is used to set the DOP only after PolarDB enables parallel queries. It is not used to enable or disable parallel queries.