What is Auto DOP?
The OceanBase AI Database optimizer supports two strategies for determining the degree of parallelism (DOP): AUTO and MANUAL. Auto DOP is enabled when the AUTO strategy is selected.
Why Auto DOP?
You can manually specify the DOP in MANUAL mode. One way is to use system variables to enable parallel execution for all queries in the current session and specify the DOP, but this may introduce unnecessary parallel execution overhead for queries in the session that do not require parallel acceleration or a high DOP, leading to performance degradation. Another way is to use hints to specify the DOP for specific queries, but this requires individual consideration for each business query, which is not feasible in scenarios with a large number of business queries.
To address the inconvenience and limitations of manually specifying the DOP, the optimizer in AUTO mode can automatically determine whether to enable parallel execution and decide on an appropriate DOP based on the actual query conditions.
The Auto DOP feature of the OceanBase AI Database query optimizer evaluates the time required to execute a query during plan generation, automatically determining whether to enable parallelism and selecting a suitable DOP for the current query, thereby avoiding the performance degradation caused by manual DOP specification.
How to enable Auto DOP?
The OceanBase AI Database optimizer allows configuration of the Auto DOP selection strategy at the GLOBAL and SESSION levels using the system variable parallel_degree_policy, and also allows configuration at the query level using a higher-priority hint. The configuration methods 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 ...;
Relationship between Auto DOP and other parallelism enabling methods
For more information about the methods for enabling parallel execution and their priorities, see Parallel execution enabling methods and their priority.
After Auto DOP is enabled, the optimizer can enable parallelism for operations such as SELECT, DELETE, UPDATE, and INSERT. For data maintenance operations like DELETE, UPDATE, and INSERT, PDML is automatically used after parallelism is enabled, so there is no need to configure _enable_parallel_dml to turn on PDML.
How to determine if Auto DOP needs to be enabled?
To enable the Auto DOP feature, you must assess the system's business workload type and configure the relevant system variables appropriately. Otherwise, enabling it may have the opposite effect and pose a significant risk to the system.
Auto DOP evaluates only based on query execution cost, deciding whether to enable parallel processing and calculating the DOP with the goal of reducing response time (RT). If the workload contains slow queries that are not of concern to users, these queries may be executed in parallel and achieve a high degree of concurrency after the feature is enabled. The parallel execution of these queries will consume system parallel resources, interfering with other queries that require normal concurrent processing and optimized response time. If these queries consume too many resources, the system CPU load will rise sharply, severely affecting normal business queries. In this case, you need to assess the business workload and adopt appropriate resource isolation strategies. Refer to the introduction to resource isolation in Resource isolation overview before deciding whether to use the Auto DOP feature.
Furthermore, Auto DOP can only optimize for the current query response time (RT) target and does not consider scenarios where there are a large number of queries that need concurrent processing in the system. Therefore, when using the Auto DOP feature in an actual business system, you need to adjust the relevant system variables accordingly based on your actual optimization goals. Refer to Scenario 1: Extreme query performance optimization and Scenario 2: System performance optimization described below in Application scenarios.
How to intervene in DOP selection behavior under the Auto DOP strategy?
Under the Auto DOP strategy, the values of two related system variables, parallel_degree_limit and parallel_min_scan_time_threshold, affect the obtained DOP value and need to be adjusted as required under different deployment resources and business workload conditions.
Set the parallel_min_scan_time_threshold variable
The system variable parallel_min_scan_time_threshold is used to calculate the degree of parallelism in the Auto DOP strategy. It represents the reference execution time for enabling parallelism on base table scans. When the estimated execution time for a base table scan exceeds this set value, parallelism is enabled for the base table scan, and an appropriate degree of parallelism is calculated using this set value. The variable's default value is 1000, in milliseconds, with a minimum value of 10.
Decreasing the value of parallel_min_scan_time_threshold relaxes the conditions for enabling parallel scanning, allowing base table scans with shorter estimated execution times to also utilize parallel processing. This enables more efficient parallel scanning for tables with relatively fixed data volumes.
Considering that enabling Auto DOP may result in a large number of queries being executed with high degrees of parallelism, thereby consuming a significant amount of system resources, this parameter uses a relatively cautious default setting. Therefore, when enabling Auto DOP, you should adjust this parameter appropriately based on the system's actual workload to ensure that query efficiency is improved without affecting system stability. For example, adjusting parallel_min_scan_time_threshold to 20 or 10 can increase the degree of parallelism for some single-table scan queries to 4 or 6, thereby optimizing query performance.
A configuration example is as follows:
/* Set the maximum DOP for the GLOBAL-level Auto DOP strategy */
SET GLOBAL parallel_degree_limit = 64;
/* Set the maximum DOP for the SESSION-level Auto DOP strategy */
SET SESSION parallel_degree_limit = 64;
SET parallel_degree_limit = 64;
/* Set the DOP for the GLOBAL-level Auto DOP strategy */
SET GLOBAL parallel_min_scan_time_threshold = 100;
/* Set the DOP for the SESSION-level Auto DOP strategy */
SET SESSION parallel_min_scan_time_threshold = 100;
SET parallel_min_scan_time_threshold = 100;
An application example is as follows:
explain select /*+parallel(auto)*/ * from t1;
-- Set the parameter to 20. With a final DOP of 4, the estimated cost for scanning the base table after parallelism is enabled 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
-- When the parameter is set to 10 and the final DOP is 6, the estimated cost for scanning the base table after parallel execution is enabled is 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 variable
The parallel_degree_limit system variable specifies the maximum DOP allowed when Auto DOP is enabled. The default value is 0, which specifies not to limit the automatically obtained DOP. When this value is used, the optimizer limits the maximum DOP based on the number of CPU cores of the tenant and the value of the parallel_servers_target variable.
After you enable Auto DOP for your system, the system load can be high if a large number of queries require high DOPs. In this case, you can set the parallel_degree_limit variable to a value less than the number of CPU cores to limit the maximum DOP allowed.
For example, when the parallel_min_scan_time_threshold variable is set to 10 and the parallel_degree_limit variable is set to 4, the maximum DOP allowed for a query is still 4 even though the DOP calculated based on the value 10 of parallel_min_scan_time_threshold is 6.
Application scenarios of Auto DOP
The auto DOP feature allows the optimizer to proactively enable parallel processing for query operations to improve execution efficiency. However, the scale of parallelism is constrained by specific system variables. Therefore, it is necessary to fine-tune these parameters in different usage scenarios to optimize performance.
Scenario 1: Extreme query performance optimization
When the database load is low, meaning there is no significant query pressure, if you want to utilize all parallel resources available to the tenant for optimal query performance, it is recommended to set the reference execution time threshold for Auto DOP to the lowest and not to 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 no maximum DOP limit
ALTER SYSTEM SET parallel_min_scan_time_threshold = 10; -- 10 milliseconds
This configuration enables the optimizer to use parallel processing at a sufficiently low execution time threshold and to use the largest possible DOP to accelerate queries.
Scenario 2: Comprehensive system performance optimization
When the database has a heavy concurrent query load, a large number of queries executed at high DOPs can consume excessive parallel resources, leading to issues such as query queuing. To avoid excessive consumption of system resources by queries with high DOPs, you can adjust the variables as follows:
- Decrease the value of the
parallel_degree_limitvariable to a value less than the number of CPU cores to limit the maximum DOP allowed. - Increase the value of the
parallel_min_scan_time_thresholdvariable to make it harder to trigger parallel execution and reduce the utilization of parallel execution resources.
For example, to limit the maximum DOP to 32 and enable parallel processing only for queries with an estimated execution time exceeding 20 milliseconds, the parameter configuration is as follows:
ALTER SYSTEM SET parallel_degree_policy = AUTO;
ALTER SYSTEM SET parallel_degree_limit = 32;
ALTER SYSTEM SET parallel_min_scan_time_threshold = 20; -- 20 milliseconds
The above configuration helps balance query performance and resource allocation at the system level, optimizing overall database response and stability.