OceanBase AI Database provides a series of parameters to control the initialization and tuning of parallel execution (PX). When starting OceanBase, default parallel execution control parameters can be calculated based on the tenant's number of CPUs and the tenant parameter px_workers_per_cpu_quota. You can also choose not to use the default values, manually specify parameter values at startup, and adjust them later based on your actual scenario. The parallel execution feature is enabled by default.
This topic describes how to control parallel execution parameters from the following aspects:
- Default parallel execution parameters
- Parameter tuning for parallel execution
- Parallel control based on real-time load
Default parallel execution parameters
Parallel execution parameters control behaviors such as the number of parallel threads and parallel execution queuing. The details are summarized in the following table:
Parameter name |
Default value |
Level |
Description |
|---|---|---|---|
px_workers_per_cpu_quota |
10 | Tenant-level parameter | The number of parallel execution threads that can be allocated to each CPU. Value range: [1, 20]. |
parallel_servers_target |
MIN_CPU × px_workers_per_cpu_quota |
Tenant-level variable | Indicates the number of parallel execution threads a tenant can request on each node. |
parallel_degree_policy |
MANUAL | Tenant-level/session-level variable | The automatic DOP switch. When set to AUTO, automatic DOP is enabled, and the optimizer automatically calculates the query's degree of parallelism based on statistics. When set to MANUAL, the degree of parallelism is controlled by hints, TABLE PARALLEL attributes, session-level DOP, and other factors. |
To lower the barrier for using parallel execution, OceanBase AI Database has minimized the number of parallel execution parameters, allowing out-of-the-box use with default settings. In special scenarios, you can tune applications by modifying the default parameters.
px_workers_per_cpu_quota
This parameter specifies the number of parallel execution threads that can be allocated per CPU. When the tenant's MIN_CPU is set to N, if the parallel load is evenly distributed, each node can allocate N × px_workers_per_cpu_quota threads. If the data accessed by the parallel load is unevenly distributed, some nodes may temporarily allocate more than N × px_workers_per_cpu_quota threads. After the load ends, these excess threads are automatically reclaimed.
The impact of px_workers_per_cpu_quota on the default value of parallel_servers_target occurs only during tenant creation. After the tenant is created, modifying the value of px_workers_per_cpu_quota does not change the value of parallel_servers_target.
Generally, there is no need to change the default value of px_workers_per_cpu_quota. If resource isolation is not enabled and parallel execution occupies all CPU resources, you can try reducing the value of the px_workers_per_cpu_quota parameter to alleviate this issue.
parallel_servers_target
This parameter specifies the number of parallel execution thread resources a tenant can request per node. When resources are exhausted, parallel execution requests must queue. For more information about queuing, see Concurrency control and queuing.
Very low CPU utilization for parallel execution may be due to a too-small parallel_servers_target value, which causes the SQL DOP to be downgraded and the actual number of allocated threads to be less than expected.
Note
MIN_CPU represents the tenant's min_cpu value, which is typically specified during tenant creation.
After setting the parallel_servers_target value, reconnect and execute the following command to view the latest value:
SHOW VARIABLES LIKE 'parallel_servers_target';
From an operations perspective, to avoid frequent adjustments, you can set a maximum value for parallel_servers_target. Theoretically, parallel_servers_target can be set to infinity, but this leads to inefficiency: all queries start executing without queuing, competing for CPU time slices, disk I/O, and network I/O.
From a throughput perspective, this issue is not severe. However, from a single SQL latency perspective, this competition severely affects latency. Considering both CPU and I/O utilization, you can generally set parallel_servers_target to MIN_CPU × 10, based on the tenant's unit MIN_CPU. In a few I/O-intensive scenarios where CPUs might not be fully utilized, you can set parallel_servers_target to MIN_CPU × 20.
parallel_degree_policy
This parameter is the automatic DOP enable switch. When set to AUTO, automatic DOP is enabled, and the optimizer automatically calculates the query's degree of parallelism based on statistics. When set to MANUAL, the degree of parallelism is controlled based on hints, TABLE PARALLEL attributes, and session-level DOP.
If you are not familiar with the rules for setting the degree of parallelism, you can set parallel_degree_policy to AUTO to allow the optimizer to automatically select the degree of parallelism. For information about the calculation rules for automatic degree of parallelism, see Set the degree of parallelism for parallel execution.
Parameter tuning for parallel execution
ob_sql_work_area_percentage
A tenant-level variable that limits the maximum memory available to the SQL module. It is a percentage value representing the ratio of the memory used by the SQL module to the total tenant memory. The default value is 5, which means it occupies 5% of the tenant memory. When the SQL module uses more memory than the limit, memory spilling to disk is triggered.
You can search for WORK_AREA in the observer.log file to view the actual memory usage of the SQL work area. For example:
[MEMORY] tenant_id=1001 ctx_id=WORK_AREA hold=2,097,152 used=0 limit=157,286,400
In read-heavy workloads where data spilling to disk occurs due to limited SQL module memory, you can appropriately increase the value of ob_sql_work_area_percentage.
workarea_size_policy
OceanBase AI Database implements global adaptive memory management. When workarea_size_policy is set to AUTO, the execution framework allocates memory to each operator (such as HashJoin, GroupBy, and Sort) using the optimal strategy, enabling an adaptive data spilling-to-disk policy.
Parameter tuning for parallel DML
If transaction requirements do not exist, it is recommended to use INSERT INTO SELECT with direct load when importing data into a table to insert data into the new table all at once. This method can both shorten import time and avoid insufficient memory caused by too fast writing.
Parallel control based on real-time workload
The system supports using the number of PX tasks running on a node in real time as a criterion to determine whether sufficient resources are available to execute parallel queries, and introduces the following parameters:
Parameter |
Default value |
Level |
Note |
|---|---|---|---|
px_target_workers_per_cpu |
8 | Tenant level | It is used to control the expected ratio of parallel execution threads to the number of tenant CPUs. |
px_target_low_watermark |
60 | Tenant level | It specifies the low-watermark percentage for setting the number of parallel threads. When this threshold is exceeded, downgrading control is initiated for the degree of parallelism (DOP) of newly scheduled DFO tasks to prevent CPU resource exhaustion. |
px_target_high_watermark |
80 | Tenant level | The high-watermark percentage threshold for controlling the number of parallel threads. The closer the real-time load is to this threshold, the greater the degree of parallelism is degraded. When the load exceeds this threshold, the degree of parallelism drops to 1. |
Notice
The following condition must be met for these three parameters: px_target_low_watermark <= px_target_high_watermark <= px_target_workers_per_cpu.
Considerations
Parallel control based on real-time workload is enabled by default for newly created tenants. In this case, the parallel control based on parallel_servers_target no longer takes effect.