PolarDB for MySQL exposes two groups of configuration items for the elastic parallel query feature: cluster parameters you set in the PolarDB console and session-level status variables you query at runtime.
Parallel query parameters
All cluster parameters in the PolarDB console use the loose_ prefix for MySQL configuration file compatibility. When modifying parameters in the PolarDB console, select the parameters that include the loose_ prefix.
The following parameters control how elastic parallel query behaves. They cover the degree of parallelism (DOP) for individual queries, the total worker budget, queuing behavior, and the row-count and cost thresholds that trigger parallel execution.
| Parameter | Scope | Description |
|---|---|---|
loose_max_parallel_degree |
Global, session | The maximum DOP for a single query. Sets the upper limit on the number of parallel workers for that query. Valid values: 0–1024. Default: 0 (elastic parallel query disabled). |
loose_max_parallel_workers |
Global | The maximum number of parallel workers across all queries. Queries exceeding this limit are queued. Valid values: 1–10,000. Default: twice the number of CPU cores. |
loose_queuing_parallel_degree_limit |
Global | The maximum sum of DOP values allowed in the queue at any given time. Queries that exceed this limit fall back to sequential execution. Valid values: 0–10,000. Default: 64. |
loose_pq_max_queuing_time |
Global, session | The maximum time a query can wait in the queue. Queries that exceed this limit fall back to sequential execution. Valid values: 0–18,446,744,073,709,551,615. Default: 200 (milliseconds). |
loose_parallel_degree_policy |
Global | The DOP selection policy for individual queries. Valid values: TYPICAL, AUTO, REPLICA_AUTO (default). For details, see DOP policies. |
loose_records_threshold_for_parallelism |
Session | The minimum number of rows a query must scan before the optimizer considers parallel execution. Valid values: 0–18,446,744,073,709,551,615. Default: 10000. |
loose_cost_threshold_for_parallelism |
Session | The minimum sequential execution cost before the optimizer considers parallel execution. Valid values: 0–18,446,744,073,709,551,615. Default: 50000. |
loose_records_threshold_for_mpp |
Session | The minimum number of scanned rows of a table involved in a query statement before the optimizer considers multi-node elastic parallel query. Valid values: 0–18,446,744,073,709,551,615. Default: 0 (N × loose_records_threshold_for_parallelism, where N is the number of nodes in the cluster). |
loose_cost_threshold_for_mpp |
Session | The minimum sequential execution cost before the optimizer considers multi-node elastic parallel query. Valid values: 0–18,446,744,073,709,551,615. Default: 0 (N × loose_cost_threshold_for_parallelism, where N is the number of nodes in the cluster). |
Parameter notes
`loose_max_parallel_degree`
The PolarDB optimizer can run the main query and subqueries using separate parallel execution plans. When separate plans are used, the total worker count is the sum of workers for the main query and all subqueries — not bounded by loose_max_parallel_degree per subquery. When the same plan is used, the total worker count cannot exceed loose_max_parallel_degree.
The console setting and loose_max_parallel_degree interact as follows:
-
If elastic parallel query is enabled in the PolarDB console and
loose_max_parallel_degreeis also set, the console setting takes effect. Enable elastic parallel query in the PolarDB console for most scenarios. -
If elastic parallel query is not enabled in the PolarDB console but
loose_max_parallel_degreeis set to a value greater than0, the feature is automatically enabled.
`loose_max_parallel_workers`
In a serverless cluster, the value of loose_max_parallel_workers is automatically adjusted as node specifications scale. For queuing policy details, see Parameters that define the queuing policies of parallel queries.
`loose_parallel_degree_policy`
| Value | Behavior |
|---|---|
TYPICAL |
DOP is always set to loose_max_parallel_degree, regardless of database load or CPU utilization. |
AUTO |
The optimizer enables or disables elastic parallel query based on database load (including CPU utilization) and selects the DOP based on query cost. |
REPLICA_AUTO (default) |
Read-only nodes determine whether to enable elastic parallel query based on database load and select the DOP based on query cost. The primary node does not perform elastic parallel query. |
`loose_records_threshold_for_parallelism`
If most of your queries are simple and involve small amounts of data, set this threshold to at least 2000 to avoid triggering parallel execution unnecessarily.
Recommended settings
Use the following guidance as a starting point for your cluster configuration:
| Goal | Recommendation |
|---|---|
| Enable parallel query | Set loose_max_parallel_degree to a value greater than 0, or enable the feature in the PolarDB console. |
| Limit parallel worker consumption | Reduce loose_max_parallel_workers from its default (twice the number of CPU cores) if parallel queries are consuming too many resources. Adjust based on observed Total_running_parallel_workers. |
| Avoid unnecessary parallelism on OLTP workloads | Raise loose_records_threshold_for_parallelism to 2000 or higher if most queries are simple and touch small data sets. |
| Restrict parallel query to read-only nodes | Use loose_parallel_degree_policy = REPLICA_AUTO (the default) to keep the primary node free from parallel query overhead. |
| Tune multi-node parallel query | Leave loose_records_threshold_for_mpp and loose_cost_threshold_for_mpp at their default value of 0 to inherit the single-node thresholds scaled by the node count. Raise them only if multi-node parallelism triggers too aggressively. |
Parallel query variables
Use these status variables to monitor elastic parallel query at runtime and to diagnose why parallel execution may not be triggered for certain queries. Query them with SHOW GLOBAL STATUS or SHOW SESSION STATUS.
| Variable | Scope | Description |
|---|---|---|
Parallel_workers_created |
Global, session | The total number of parallel workers created since the session started. |
Gather_records |
Global, session | The total number of records gathered by parallel workers. |
PQ_refused_over_total_workers |
Global, session | The number of queries that fell back to sequential execution because the total parallel worker limit (loose_max_parallel_workers) was reached. |
PQ_refused_over_max_queuing_time |
Global, session | The number of queries that fell back to sequential execution because the queue wait time exceeded loose_pq_max_queuing_time. |
Total_running_parallel_workers |
Global | The number of parallel workers currently running. |
If PQ_refused_over_total_workers or PQ_refused_over_max_queuing_time is increasing, parallel queries are being throttled due to resource contention. Increase loose_max_parallel_workers or loose_pq_max_queuing_time to reduce fallback to sequential execution.