The parameters (variables or configuration items) of parallel queries (PX) in OceanBase Database determine the speed of parallel queries. They mainly include parameters related to parallelism and EXCHANGE.
Parallelism variables
The variables related to parallelism mainly determine the number of workers for each query. The following table describes these variables in detail.
Variable name |
Description |
Value range |
Default value |
Configuration recommendation |
|---|---|---|---|---|
| parallel_servers_target | Controls whether a query is queued before it is executed. If the number of workers required for the query exceeds the value of this variable, the query is queued. Otherwise, the query is executed. | [0, 1800] | 10 (calculated based on the number of CPU cores) | This variable determines whether to continue or queue a query when there are not enough workers to handle it during parallel query execution. |
| _force_parallel_query_dop | Specifies the default parallelism for queries in a session. If no PARALLEL hint is specified, the parallelism of the query is controlled by this variable. | [1, +∞] | 1 | This variable is recommended for scenarios where you need to run a batch of parallel queries in the same session without adding a hint to each query. |
| _force_parallel_dml_dop | Specifies the default parallelism for DML queries in a session. If no PARALLEL hint is specified, the parallelism of the DML query is controlled by this variable. | [1,+∞] | 1 | This variable is recommended for scenarios where you need to run a batch of parallel DML queries in the same session without adding a hint to each query. |
View the values of parallel variables
You can use the SHOW VARIABLES command to view the values of parallel variables in OceanBase Database. However, please note that some variables are hidden and not recommended for modification.
Session variables
The session variables _force_parallel_query_dop and _force_parallel_dml_dop are hidden variables. You can query them as follows:
-- Query from the DBA_OB_SYS_VARIABLES table
SELECT * FROM oceanbase.DBA_OB_SYS_VARIABLES WHERE name LIKE '%_force_parallel%';
-- Query system variables
SELECT @@_force_parallel_query_dop;
Although these queries can return the values of hidden variables, it is not recommended to modify them.
SYS tenant query
If you are the sys tenant, you can also query these parallel variables as follows:
SELECT tenant_id, name, value
FROM oceanbase.CDB_OB_SYS_VARIABLES
WHERE name LIKE '%_force_parallel%';
EXCHANGE (Shuffle) configuration item
The EXCHANGE (Shuffle) configuration item is mainly used to control memory management and data redistribution strategies during data transmission between distributed function operators (DFOs). OceanBase Database encapsulates this data transmission process in the data transfer layer (DTL) module.
Configuration item name |
Description |
Value range |
Default value |
Configuration recommendation |
|---|---|---|---|---|
| dtl_buffer_size | Controls the size of the buffer used for sending data between EXCHANGE operators (Transmit and Receive). Data is sent only when it reaches the buffer size limit, reducing the cost of transmitting each row. |
[4k, 2M] | 64K | In PX scenarios, the size of this configuration item affects the data transmission between EXCHANGE operators. Generally, it is not recommended to modify this configuration item. However, if you want to reduce the number of data transmissions, you can try modifying it. |
You can use the SHOW PARAMETERS command to view the value of a configuration item, as shown in the following example:
obclient> SHOW PARAMETERS LIKE '%dtl%';
Expected output:
+-------+----------+----------------+----------+-----------------+-----------+-------+---------------+----------+---------+---------+-------------------+---------------+-----------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level | default_value | isdefault |
+-------+----------+----------------+----------+-----------------+-----------+-------+---------------+----------+---------+---------+-------------------+---------------+-----------+
| zone1 | observer | 172.xx.xxx.xxx | 2882 | dtl_buffer_size | CAPACITY | 64K | to be removed | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE | 64K | 1 |
+-------+----------+----------------+----------+-----------------+-----------+-------+---------------+----------+---------+---------+-------------------+---------------+-----------+
1 row in set
Other parallelism-related parameters
Parameter name |
Description |
Value range |
Default value |
Configuration recommendation |
|---|---|---|---|---|
| _enable_px_batch_rescan | Controls whether to use BATCH RESCAN during the execution of a distributed PX RESCAN plan generated by NLJ. This can improve performance. | True or False | True | By default, this parameter is set to True, which can improve performance but consumes more memory. |
| _bloom_filter_enabled | Controls whether to enable BLOOM FILTER in HASH JOIN scenarios. | True or False | True | In parallelism scenarios with a degree of parallelism greater than 1, this parameter is enabled by default. If the join conditions in HASH JOIN do not provide good filtering, enabling BLOOM FILTER will add additional overhead. In such scenarios, consider disabling this feature. |