The speed of a parallel query (PX) is determined by its parameters (variables or configuration parameters), which mainly include the parallelism and EXCHANGE parameters.
Parallelism variables
The parallelism variables mainly determine the number of workers for each query.
Variable name |
Description |
Value range |
Default value |
Configuration recommendation |
|---|---|---|---|---|
| parallel_servers_target | Before a query is queued, it checks whether the required parallelism and the total number of workers exceed this value. If they do, the query is queued. Otherwise, it continues to execute. | [0, 9223372036854775807] | 0 (It is calculated based on the number of CPU cores. The actual value may vary.) | This variable mainly controls whether to continue or queue a parallel query when there are not enough workers to process it in a PX scenario. |
You can use the SHOW VARIABLES statement to view the values 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
The EXCHANGE (Shuffle) parameters mainly control the memory management and data redistribution strategy during data transmission between distributed function operators (DFOs). In other words, they control the memory during data shuffling. OceanBase Database encapsulates data transmission into a module called the Data Transfer Layer (DTL).
Parameter name |
Description |
Value range |
Default value |
Configuration recommendation |
|---|---|---|---|---|
| dtl_buffer_size | The size of the buffer used to send data between EXCHANGE operators (Transmit and Receive). Data is sent only when it reaches this buffer size, reducing the cost of transmitting each row. |
[4k, 2M] | 64K | In PX scenarios, the data sent between EXCHANGE operators depends on the size of this parameter. Generally, you do not need to modify this parameter. If you want to reduce the number of data sends, you can try modifying it. However, it is generally not recommended to change this value. |
You can use the SHOW PARAMETERS statement to view the values of these parameters, as shown in the following example:
obclient> SHOW PARAMETERS LIKE '%dtl%'\G
*************************** 1. row ***************************
ZONE: zone1
SVR_TYPE: observer
SVR_IP: 10.xxx.xxx.xxx
SVR_PORT: 2882
NAME: dtl_buffer_size
DATA_TYPE: NULL
VALUE: 64K
INFO: to be removed
SECTION: OBSERVER
SCOPE: CLUSTER
SOURCE: DEFAULT
EDIT_LEVEL: DYNAMIC_EFFECTIVE
1 row in set