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.
The MySQL configuration file compatibility prefix loose_ is added to all cluster parameters in the PolarDB console. If you want to modify the parameters in the PolarDB console, make sure to modify the ones that contain the loose_ prefix.
Parameters that define the queuing policies of parallel queries
Parameter | Description |
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 query is immediately removed from the queue and uses sequential execution instead. |
Details of the DOP parameters
Parameter | 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.
Note The value of max_parallel_workers in a serverless cluster is adjusted based on the scaling of node specifications. |
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.
|
pq_max_queuing_time | Global and session | The maximum waiting time in the queue. If the waiting time in the queue exceeds the specified value, sequential execution is used.
|
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:
|
DOP control based on system resource usage
If parallel_degree_policy is set to AUTO or REPLICA_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. When parallel queries are enabled, you can set the DOP based on the system resource usage of parallel queries.
Parameter | Level | Description |
auto_dop_cpu_pct_hwm | Global | The threshold of the CPU usage. If the CPU usage exceeds the threshold, PolarDB disables parallel queries.
|
auto_dop_mem_pct_hwm | The threshold of the memory usage. If the memory usage exceeds the threshold, PolarDB disables parallel queries.
| |
auto_dop_iops_pct_hwm | The threshold of the IOPS usage. If the IOPS usage exceeds the threshold, PolarDB disables parallel queries.
|