What is Auto DOP?
OceanBase Database’s optimizer supports two strategies for obtaining the degree of parallelism (DOP): AUTO and MANUAL, where AUTO refers to Auto DOP.
Why Auto DOP?
You can manually specify the DOP using the MANUAL mode. One way to do this is by using system variables to enable parallel execution for all queries in the current session and specify the DOP. However, this may introduce additional parallel execution overhead for queries that do not need parallel acceleration or do not require a high DOP, potentially leading to performance degradation. Another way is to use hints to specify the DOP for specific queries. However, in this case, you need to look into each business query separately, which is not feasible for scenarios with a large number of business queries.
To avoid the inconvenience and limitations of manually specifying the DOP, the optimizer in the AUTO mode can automatically determine whether to enable parallel execution and the suitable DOP based on the actual query conditions.
The Auto DOP feature of OceanBase Database’s optimizer evaluates the execution time of a query to determine whether to enable parallel execution and the suitable DOP for the query. This feature avoids the performance degradation caused by manually specifying the DOP.
How to enable Auto DOP?
OceanBase Database’s optimizer allows you to enable and disable Auto DOP at the global and session levels using the parallel_degree_policy system variable. You can also use hints with higher priorities to enable and disable Auto DOP at the query level. The syntaxes are as follows:
/* Enable Auto DOP at the global level. */
SET global parallel_degree_policy = AUTO;
/* Enable Auto DOP at the session level. */
SET session parallel_degree_policy = AUTO;
SET parallel_degree_policy = AUTO;
/* Disable Auto DOP at the global level. */
SET global parallel_degree_policy = MANUAL;
/* Disable Auto DOP at the session level. */
SET session parallel_degree_policy = MANUAL;
SET parallel_degree_policy = MANUAL;
/* Use a hint to enable Auto DOP at the query level. */
SELECT /*+PARALLEL(AUTO)*/ * FROM ...;
/* Use a hint to disable Auto DOP at the query level. */
SELECT /*+PARALLEL(MANUAL)*/ * FROM ...;
SELECT /*+PARALLEL(8)*/ * FROM ...;
Auto DOP and other parallel execution modes
For the various parallel execution modes, see Parallel execution enabling methods and their priority.
After you enable Auto DOP, the optimizer can enable parallel execution for SELECT, DELETE, UPDATE, and INSERT statements. For DELETE, UPDATE, and INSERT data maintenance statements, parallel execution is automatically enabled and PDML is automatically used. You do not need to configure the _enable_parallel_dml parameter to enable PDML.
How to determine whether to enable Auto DOP?
To enable Auto DOP, you must evaluate the type of system business load and reasonably configure related system variables. Otherwise, enabling Auto DOP may lead to negative effects and pose a significant risk to the system.
Auto DOP evaluates whether to enable parallel processing and calculate DOP based on the query execution cost to minimize the response time (RT). If the business load contains slow queries that are not concerned by users, these queries may be executed in parallel after Auto DOP is enabled and obtain a higher concurrency degree. The parallel execution of these queries occupies system parallel resources and interferes with other queries that require normal concurrency processing and optimized response times. If these queries occupy too many resources, the system CPU load will surge, severely affecting normal business queries. In this case, you must evaluate the business load and use an appropriate resource isolation strategy to handle it. For details on resource isolation, refer to the Resource isolation overview documentation. Then, decide whether to use the Auto DOP feature.
Moreover, Auto DOP is geared towards optimizing the RT for the current query, and it does not account for scenarios where numerous queries require concurrent processing within the system. To effectively utilize Auto DOP in your business system, tailor its implementation based on your specific optimization goals by adjusting relevant system variables, as described in the subsequent sections on Applicable scenarios of Auto DOP.
How to intervene in the DOP selection behavior under the Auto DOP strategy
In the Auto DOP strategy, the values of two related system variables, parallel_degree_limit and parallel_min_scan_time_threshold, affect the size of the DOP. You need to adjust them based on the deployment resources and business loads.
Set the parallel_min_scan_time_threshold parameter
The system variable parallel_min_scan_time_threshold is used in the Auto DOP strategy to calculate the parallelism. It represents the reference execution time for enabling parallelism in base table scans. When the estimated execution time for a base table scan exceeds this value, parallelism will be enabled for the base table scan, and this value will be used to calculate an appropriate degree of parallelism. The default value of the variable is 1000 milliseconds, with a minimum value of 10 milliseconds.
If you decrease the value of parallel_min_scan_time_threshold, you can relax the conditions for enabling parallel scanning, which allows base table scans with shorter estimated execution times to use parallel processing and enables more efficient parallel scanning for tables with relatively fixed data volumes.
However, enabling Auto DOP may cause a large number of queries to be executed at high parallelism levels, which may occupy a large amount of system resources. Therefore, when you enable Auto DOP, you should adjust this parameter based on the actual workload of your system to ensure that query efficiency is improved without affecting system stability. For example, you can adjust parallel_min_scan_time_threshold to 20 or 10 to increase the parallelism level of some single-table scan queries to 4 or 6, which optimizes query performance.
The following example shows how to set the parallel_min_scan_time_threshold parameter:
/* Set the maximum DOP for the Auto DOP strategy at the global level. */
SET GLOBAL parallel_degree_limit = 64;
/* Set the maximum DOP for the Auto DOP strategy at the session level. */
SET SESSION parallel_degree_limit = 64;
SET parallel_degree_limit = 64;
/* Set the threshold for enabling parallel execution for the Auto DOP strategy at the global level. */
SET GLOBAL parallel_min_scan_time_threshold = 100;
/* Set the threshold for enabling parallel execution for the Auto DOP strategy at the session level. */
SET SESSION parallel_min_scan_time_threshold = 100;
SET parallel_min_scan_time_threshold = 100;
Here is an example:
explain select /*+parallel(auto)*/ * from t1;
-- Set the parameter to 20, and the final DOP is 4. After enabling parallel execution, the estimated cost of scanning the base table is 12 ms.
set parallel_min_scan_time_threshold = 20;
=========================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
---------------------------------------------------------
|0 |PX COORDINATOR | |512000 |619089 |
|1 |└─EXCHANGE OUT DISTR |:EX10000|512000 |230501 |
|2 | └─PX BLOCK ITERATOR| |512000 |12189 |
|3 | └─TABLE FULL SCAN|t1 |512000 |12189 |
=========================================================
Outputs & filters:
-------------------------------------
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=256
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=256
dop=4
-- Set the parameter to 10, and the final DOP is 6. After enabling parallel execution, the base table scan cost evaluates to 8 ms.
set parallel_min_scan_time_threshold = 10;
=========================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
---------------------------------------------------------
|0 |PX COORDINATOR | |512000 |542256 |
|1 |└─EXCHANGE OUT DISTR |:EX10000|512000 |153667 |
|2 | └─PX BLOCK ITERATOR| |512000 |8126 |
|3 | └─TABLE FULL SCAN|t1 |512000 |8126 |
=========================================================
Outputs & filters:
-------------------------------------
0 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=256
1 - output([INTERNAL_FUNCTION(t1.c1, t1.c2, t1.c3, t1.c4)]), filter(nil), rowset=256
dop=6
Set the parallel_degree_limit parameter
The parallel_degree_limit system variable specifies the maximum parallel degree that can be used by Auto DOP. When this parameter is set to 0, no limit is imposed on the parallel degree automatically obtained. This is also the default value. By default, the optimizer limits the maximum parallel degree based on the CPU of the tenant and the parallel_servers_target system variable.
After Auto DOP is enabled on the system, if a large number of queries with a high parallel degree are executed, causing high system load, you can set the parallel_degree_limit parameter to further limit the maximum parallel degree. In this case, you can set parallel_degree_limit to a value less than a certain CPU value.
In the example mentioned in the parallel_min_scan_time_threshold section, even when parallel_degree_limit is set to 4 and parallel_min_scan_time_threshold is set to 10, the query can only use a maximum parallel degree of 4.
Applicable scenarios of Auto DOP
Auto DOP allows the optimizer to enable parallel execution for query operations to improve execution efficiency. However, the scale of parallelism is restricted by specific system variables, so fine-tuning these parameters is necessary to optimize performance in different scenarios.
Scenario 1: Extreme optimization of query performance
If you want to use all available parallel resources for optimal query performance when the database load is low and there are no other significant query loads, set the reference execution time threshold of Auto DOP to the lowest value and do not limit the maximum DOP. The corresponding parameter settings are as follows:
ALTER SYSTEM SET parallel_degree_policy = AUTO;
ALTER SYSTEM SET parallel_degree_limit = 0; -- indicates that no maximum DOP is limited
ALTER SYSTEM SET parallel_min_time_threshold = 10; -- 10 milliseconds
This configuration enables the optimizer to enable parallel execution at a sufficiently low execution time threshold and use the maximum DOP to accelerate queries.
Scenario 2: Optimization of system performance
When a large number of concurrent queries are executed against a database, a large number of queries with higher DOP may cause insufficient parallel resources in the system, resulting in query queuing. To avoid the system resource consumption caused by a large number of queries with high DOP, you can adjust the parameters in the query mechanism performance optimization:
- By setting the
parallel_degree_limitparameter to a value less than the number of CPU cores in the system, you can limit the maximum DOP used by Auto DOP. - By increasing the
parallel_min_time_thresholdparameter, you can reduce the sensitivity of parallel execution and reduce the utilization rate of parallel resources.
For example, to limit the maximum DOP to 32 and enable parallel processing only for queries with an evaluation execution time of more than 20 milliseconds, you can set the parameters as follows:
ALTER SYSTEM SET parallel_degree_policy = AUTO;
ALTER SYSTEM SET parallel_degree_limit = 32;
ALTER SYSTEM SET parallel_min_time_threshold = 20; -- 20 milliseconds
This configuration helps balance query performance and resource allocation at the system level and optimizes overall database responsiveness and stability.