Configuration parameters - PolarDB - Alibaba Cloud Documentation Center

This topic describes the parameters and variables of the elastic parallel query feature.

Parallel query parameters

Note

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.

Parameter	Level	Description
max_parallel_degree	Global and session	The maximum DOP for an individual query. This parameter specifies the maximum number of workers that are used to run queries in parallel. Valid values: 0 to 1024. Default value: 0. This value specifies that the elastic parallel query feature is disabled. Note The PolarDB optimizer can use different elastic parallel query execution plans to run the main query and the subqueries in parallel. If the optimizer uses the same plan, the maximum number of workers cannot exceed the value of max_parallel_degree. The total number of workers used for the entire query is the sum of the workers used by the main query and the subqueries. The elastic parallel query setting in the console and the max_parallel_degree parameter take effect based on the following rules: If you enable the elastic parallel query feature in the PolarDB console and configure the max_parallel_degree parameter, the settings in the console take effect. We recommend that you enable the elastic parallel query feature in the PolarDB console. If you do not enable the elastic parallel query feature in the PolarDB console but set the max_parallel_degree parameter to a value greater than 0, the elastic parallel query feature is automatically enabled.
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. Note The value of max_parallel_workers in a serverless cluster is adjusted based on the scaling of node specifications. For more information about the queuing policies of parallel queries, see Parameters that define the queuing policies of parallel queries.
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, the additional query is not granted resources for parallel queries and sequential execution is used. Valid values: 0 to 10000. Default value: 64.
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. Valid values: 0 to 18446744073709551615. Default value: 200. Unit: millisecond.
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. Note For more information about degree of parallelism configuration policies, see DOP policies.
records_threshold_for_parallelism	Session	If the number of scanned rows exceeds the value of records_threshold_for_parallelism, the optimizer enables the elastic parallel query feature. Valid values: 0 to 18446744073709551615. Default value: 10000. Note If the majority of your queries are simple and involve only a small amount of data, we recommend that you set the threshold to at least 2000.
cost_threshold_for_parallelism	Session	If the cost of sequential queries exceeds the value of cost_threshold_for_parallelism, the optimizer enables the elastic parallel query feature. Valid values: 0 to 18446744073709551615. Default value: 50000.
records_threshold_for_mpp	session	If the number of scanned rows of a table involved in a query statement exceeds this threshold, the optimizer considers using multi-node elastic parallel query. Valid values: 0 to 18446744073709551615. Default value: 0. The value 0indicates N times the records_threshold_for_parallelism value. The N value is the number of nodes in the current cluster.
cost_threshold_for_mpp	session	If the sequential execution cost of a query statement exceeds this threshold, the optimizer considers using multi-node elastic parallel query. Valid values: 0 to 18446744073709551615. Default value: 0. The value 0 indicates N times the cost_threshold_for_parallelism value. The N value is the number of nodes in the current cluster.

Parallel query variables

Variable	Level	Description
Parallel_workers_created	Global and session	The number of parallel workers that have been created since the start of the session.
Gather_records	Global and session	The total number of records that are gathered.
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 that are not performed in parallel due to the timeout of parallel query queues.
Total_running_parallel_workers	Global	The number of parallel workers that are running.