With the rapid growth of data volumes, optimizing query performance and resource utilization has become a key objective in database management. The degree of parallelism (DOP) is a critical metric for evaluating parallel execution capabilities and can significantly improve query response times and system throughput.
Parallel queries improve SQL query efficiency by executing tasks simultaneously across multiple processing units. The core principle is to optimize query plans and fully utilize the system's computing resources and I/O capabilities, thereby accelerating the execution of individual queries.
Characteristics of parallel queries
Differences between OLTP and OLAP
Different types of SQL queries exhibit distinct characteristics in parallel processing:
- OLTP (Online Transaction Processing): Typically involves a large number of short transactions, with small data volumes per query, and has limited demand for parallelism. It relies more on fast transaction response.
- OLAP (Online Analytical Processing): Typically processes massive datasets for large-scale analysis and depends heavily on parallel queries to improve query throughput and efficiency.
Query decomposition and operator parallelism
In parallel queries, a query is decomposed into multiple operators and processed in parallel at different stages. These stages include:
- Data reading: Scans data in parallel.
- Computation and execution: Multiple processing units participate in computation simultaneously, accelerating complex operations such as aggregation and sorting.
- Result output: Optimizes data merging and return.
Strategies for optimizing parallel queries
Generate distributed query plans
Distributed execution is a crucial component of parallel queries. In OceanBase AI Database, the generation of distributed query plans determines query execution efficiency. By analyzing data distribution, operator dependencies, and resource usage, the system can construct efficient execution plans.
For more information, see Distributed plan generation.
Adjust the DOP
Properly adjusting the DOP based on query complexity and data volume is key to optimizing query performance:
- Low DOP: Fails to fully utilize system resources, leading to prolonged query times.
- High DOP: May cause resource contention, thereby reducing system throughput.
By monitoring query execution plans and analyzing execution time and resource usage, you can precisely adjust the DOP to achieve the optimal balance.
How do you adjust the degree of parallelism?
To accelerate queries and meet business requirements, parallel execution is often used to optimize query performance. But how much parallelism is appropriate? In the optimizer, the amount of parallel resources is described using the degree of parallelism (DOP). In practical scenarios, whether to enable parallelism and how to set the DOP are typically determined based on query execution characteristics and business needs, guided by experience.
OceanBase AI Database provides both manual and automatic methods for setting the DOP.
Manually specify the optimizer's degree of parallelism
You can manually set the degree of parallelism (DOP). For more information, see Set the degree of parallelism for parallel execution.
Automatic optimizer-driven DOP selection
Auto DOP
When you manually specify the degree of parallelism (DOP), you can enable parallel execution for all queries in the current session by using a system variable and specify the DOP. However, this approach can introduce unnecessary parallel execution overhead for queries that do not require parallel acceleration or a high DOP, potentially leading to performance degradation. Alternatively, you can specify the DOP for individual queries using hints, but this requires evaluating each query separately, which becomes impractical in environments with a large number of queries.
To overcome the inconvenience and limitations of manually specifying the DOP, the query optimizer provides the Auto DOP feature. This feature evaluates the estimated execution time of a query during query plan generation and automatically determines whether to enable parallelism and the appropriate DOP. By doing so, it avoids the performance degradation that can arise from manually setting the DOP.
Notice
- After enabling Auto DOP, you need to adjust the settings of two related parameters,
parallel_min_scan_time_thresholdandparallel_degree_limit. Otherwise, Auto DOP may have adverse effects on the AP system. - Auto DOP decides whether to enable parallelism and calculates the DOP based solely on the estimated query execution cost, aiming to reduce response time (RT). If there are slow queries in the workload that users do not prioritize, resource isolation measures must be implemented. Otherwise, after enabling Auto DOP, applying the DOP determined by Auto DOP to all queries may negatively impact the system.
Enable Auto DOP
The optimizer's DOP selection strategy currently has two modes: AUTO and MANUAL. In AUTO mode, the optimizer can automatically determine whether to enable parallelism and the appropriate DOP based on the actual query conditions. In MANUAL mode, parallelism can only be enabled through manual intervention.
The optimizer's DOP selection strategy can be configured at the global or session level using the system variable parallel_degree_policy, or at the query level using a higher-priority hint.
Notice
After you modify a system variable at the global level, the modification takes effect only on new connections, and existing connections must be closed.
-- 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;
Hints take precedence over system variables. You can use hints to enable or disable Auto DOP at the query level.
-- Use a hint to enable Auto DOP at the query level.
select /*+parallel(auto)*/ * from t1;
-- Use a hint to disable Auto DOP at the query level.
select /*+parallel(manual)*/ * from t1;
select /*+parallel(8)*/ * from t1;
Related parameter settings
When Auto DOP calculates the DOP for a query, the final DOP is influenced by the following two system variables. Under different deployment resources and business workloads, you can also adjust these two variables to influence Auto DOP behavior.
Notice
After you modify a system variable at the global level, the modification takes effect only on new connections, and existing connections must be closed.
parallel_min_scan_time_threshold: Specifies the threshold for the estimated scan cost, in milliseconds (ms), for enabling parallelism on base tables. This variable affects whether parallelism is enabled for a query and the DOP used. The default value is 1000, and the minimum value is 10.
Application scenarios:
Adjust this variable to control the number of parallel queries and their DOPs in the AP system. After enabling Auto DOP, if some of the queries you care about are not executed in parallel or use a DOP lower than the upper limit, you can decrease the value of parallel_min_scan_time_threshold. After the adjustment, more queries will be executed in parallel, and queries that already use parallelism may use a higher DOP. In the opposite case, you can increase the value of this variable.
The default value of this variable is relatively conservative to prevent a large number of queries from running with high DOPs and consuming excessive system resources after Auto DOP is enabled. Generally, when enabling Auto DOP, you should evaluate the system workload and decrease the value to an appropriate level.
Example:
-- Set the evaluation cost threshold for enabling parallelism on base table scans in the current session to 10 ms.
set parallel_min_scan_time_threshold = 10;
-- Set the evaluation cost threshold for enabling parallelism on base table scans globally to 10 ms.
set global parallel_min_scan_time_threshold = 10;
explain select /*+parallel(auto)*/ * from t1;
-- Set the parameter to 20. The final DOP is 4, and the estimated cost of 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
-- Set the parameter to 10. The final DOP is 6, and the estimated cost of scanning the base table after parallelism 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
parallel_degree_limit: Specifies the maximum DOP allowed after Auto DOP is enabled. The default value is 0. In this case, the optimizer limits the maximum DOP based on the number of CPU cores of the tenant and the value of the system variable parallel_servers_target.
Application scenarios:
After enabling Auto DOP in an AP system, if a large number of queries require a high DOP, causing excessive system load, you can set parallel_degree_limit to a value smaller than the number of CPU cores to limit the maximum DOP allowed.
If only one AP query is being executed in the system, keep the default value of parallel_degree_limit so that the maximum DOP allowed for the query is equal to the number of CPU cores. If you need a higher DOP under Auto DOP, you can set parallel_degree_limit to a value larger than the number of CPU cores to remove the limitation imposed by the CPU count. This adjustment is supported only for extreme performance optimization of a single AP query and is not allowed in production environments.
Example:
-- Set the maximum DOP allowed by Auto DOP for the current session to 8.
set parallel_degree_limit = 8;
-- Set the maximum DOP allowed by Auto DOP globally to 8.
set global parallel_degree_limit = 8;
For more information about Auto DOP, see Auto DOP.
Parallel execution
Parallel execution divides a large task into smaller tasks and launches multiple threads or processes to process them in parallel. This uses more CPU and I/O resources and thereby shortens the response time of operations.
Parallel execution includes parallel query, parallel DDL, and parallel DML.
There are two ways to enable parallel execution:
- Enable parallel execution by manually specifying the degree of parallelism (DOP) or using Auto DOP.
- For partitioned tables with more than one partition, parallel query is automatically enabled.
For more information, see Execution of parallel queries.