This topic describes how to use the elastic parallel query (ePQ) feature.
Usage notes
Both read-only nodes and the primary node support the elastic parallel query feature. The elastic parallel query feature is disabled on the primary node by default.
Enable elastic parallel query
In the Cluster Endpoint section on the Overview page of the PolarDB console, click Modify. On the Configure Nodes page, set the DOP and the parallel engine. For more information, see the Parallel Query parameter described in Configure PolarProxy.
We recommend that you use the following parallel query settings:
Multi-node elastic parallel query supports adaptive task scheduling based on costs and real-time cluster loads. We recommend that you enable multi-node elastic parallel query for better acceleration.
Increase the value of the max_parallel_degree parameter in small increments. We recommend that you do not use a value that exceeds a quarter of the total number of CPU cores. For example, after you set the max_parallel_degree parameter to 2, you can check the CPU utilization the next day. If the CPU utilization is low, increase the value of the max_parallel_degree parameter. If the CPU utilization is high, do not increase the value.
DOP specifies the maximum number of worker threads that can run simultaneously in a single compute node for a single query. If you select multi-node elastic parallel query, the maximum number of worker threads that can run simultaneously in a single query is the product of the DOP and the number of nodes.
When you enable the elastic parallel query feature, set the innodb_adaptive_hash_index parameter to OFF. If the innodb_adaptive_hash_index parameter is set to ON, the performance of parallel query is degraded.
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.
If you enable the elastic parallel query feature in the PolarDB console and configure the max_parallel_degree parameter, the settings displayed in the PolarDB console shall prevail. We recommend that you enable the elastic parallel query feature in the PolarDB console.
Disable parallel queries
In the Cluster Endpoint section on the Overview page of the PolarDB console, click Modify. On the Configure Nodes page, you can disable the elastic parallel query feature. For more information, see Configure PolarProxy.
After you disable the elastic parallel query feature in the PolarDB console, you must confirm that the max_parallel_degree is set to 0 to ensure that the elastic parallel query feature is disabled.
Use hints
You can optimize an individual SQL statement by using hints. For example, if the elastic parallel query feature is disabled by the system, you can use hints to accelerate a slow SQL query that is frequently executed. For more information, see Parallel hints.
Use thresholds to specify whether the optimizer can enable elastic parallel query
PolarDB provides two thresholds to specify whether the optimizer can enable the elastic parallel query feature. If an SQL statement meets one of the following conditions, the optimizer enables the elastic parallel query feature.
records_threshold_for_parallelism
If the number of scanned rows exceeds the value of records_threshold_for_parallelism, the optimizer enables the elastic parallel query feature. The default value is 10000. 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.
NoteThe number of scanned rows is estimated based on the statistics of tables. This number can be different from the actual number of rows.
cost_threshold_for_parallelism
If the cost of sequential queries exceeds the value of cost_threshold_for_parallelism, the optimizer enables the elastic parallel query feature. The default value is 50000.
Use thresholds to specify whether to enable multi-node elastic parallel query
PolarDB provides two thresholds to specify whether to enable multi-node elastic parallel query. If an SQL statement meets one of the following conditions, multi-node elastic parallel query can be used.
records_threshold_for_mpp
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 and schedules parallel tasks to multiple nodes for computing. The default value is N times the records_threshold_for_parallelism value. The N value is the number of nodes involved in the current cluster endpoint.
cost_threshold_for_mpp
If the sequential execution cost of a query statement exceeds this threshold, the optimizer considers using multi-node elastic parallel query. The default value is N times the cost_threshold_for_parallelism value. The N value is the number of nodes involved in the current cluster endpoint.