The parameters (variables or configuration items) of parallel query (PX) determine its speed, mainly including the degree of parallelism (DOP) and EXCHANGE related parameters.
DOP variables
DOP variables determine the number of parallel workers in each query.
Variable name |
Description |
Value range |
Default value |
Recommended configuration |
|---|---|---|---|---|
| parallel_servers_target | Before a query is queued, the system checks whether the required DOP and the total number of required workers exceed the limit set for this parameter. If yes, the system queues the query. Otherwise, the system proceeds to the query execution. | [0, 9223372036854775807] | 0. The value is obtained based on the number of CPUs, and the actual value must be used. |
This parameter controls whether to proceed to the parallel execution of a query or queue it if the number of available workers is insufficient. |
You can use SHOW VARIABLES to view the value of these variables, as shown in the following example:
obclient> SHOW VARIABLES LIKE '%paral%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| parallel_servers_target | 4 |
+-------------------------+-------+
1 rows in set
EXCHANGE (shuffle) parameters
EXCHANGE (shuffle) parameters are mainly used to control memory management and data redistribution strategies during data transfer between Data Flow Operators (DFOs), which is the memory control when data is shuffled. OceanBase AI Database encapsulates its data transfer service into a module called Data Transfer Layer (DTL).
Parameter |
Description |
Value range |
Default value |
Recommended configuration |
|---|---|---|---|---|
| dtl_buffer_size | Specifies the size of the buffer for each data transmission operation between EXCHANGE operators, namely, the Transmit and Receive operators. Data is sent only when the value of this parameter reaches the upper limit. This reduces the cost of transmitting each row. |
[4K, 2M] | 64K | This parameter controls the data transmission between EXCHANGE operators. We recommend that you do not modify this parameter unless you want to reduce the frequency of data transmission. |
You can use SHOW PARAMETERS to view the value of these parameters, as shown in the following example:
obclient> SHOW PARAMETERS LIKE '%dtl%'\G
*************************** 1. row ***************************
zone: zone1
svr_type: observer
svr_ip: 172.xx.xxx.xxx
svr_port: 2882
name: dtl_buffer_size
data_type: CAPACITY
value: 64K
info: to be removed
section: OBSERVER
scope: CLUSTER
source: DEFAULT
edit_level: DYNAMIC_EFFECTIVE
1 row in set