All Products
Search
Document Center

Parameter tuning for a parallel query

Last Updated: Jun 18, 2021

In OceanBase Database, the performance of a parallel query is determined by the values of the parallel execution parameters, including those related to the degree of parallelism and EXCHANGE.

Parameters about the degree of parallelism

These parameters determine the number of parallel workers in each query.

Parameter

Description

Value range

Default Value

Recommended configuration

parallel_max_servers

Specifies the maximum number of parallel execution threads for each server. The sum of all PX workers can not exceed the value of this parameter.

[0, 1800]

10. The value is obtained based on the number of CPUs, and the actual value must be used.

This parameter controls the total number of PX workers in parallel execution. We recommend that you set it to a value that is a multiple of the number of the CPU cores available.

parallel_servers_target

Before a query is put into a queue, the system checks whether the sum of workers exceeds the required degree of parallelism of the query. If yes, the query is put into a queue. Otherwise, the query execution continues.

[0, 1800]

10. The value is obtained based on the number of CPUs, and the actual value must be used.

This parameter specifies whether to proceed to the parallel execution of a query or to put it in a queue if the number of available workers is insufficient to process it.

The parallel_max_servers parameter specifies the maximum degree of parallelism. The parallel_servers_target parameter specifies whether to put a query into a queue during the parallel execution of the query. These two parameters work coordinately. If you only use the parallel_max_servers parameter to set the maximum degree of parallelism, all workers are activated in the case of excessive queries, leading to the CPU overload and the decrease of the query performance. If the resources such as CPU cores are limited, you can use the parallel_servers_target parameter to control whether to put the query into a queue and improve the throughput of parallel execution.

You can run the SHOW VARIABLES command to view the values of these parameters, as shown in the following example:

obclient>SHOW VARIABLES LIKE '%paral%';

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| ob_max_parallel_degree  | 32    |
| ob_stmt_parallel_degree | 1     |
| parallel_max_servers    | 5     |
| parallel_servers_target | 4     |
+-------------------------+-------+
4 rows in set (0.00 sec)
Notice

You do not need to configure the ob_stmt_parallel_degree parameter, because it is invalid for the PX framework.

EXCHANGE (Shuffle) parameter

The EXCHANGE (Shuffle) parameter controls the parameters during data transfer between Data Flow Objects (DFOs). In other words, it controls the memory usage during data shuffle. OceanBase Database encapsulates a 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 data transmission between EXCHANGE operators every time. Data is sent only when the value of this parameter reaches the upper limit, to reduce the cost of transferring each row.

[0, 1800]

10. The value is obtained based on the number of CPUs, and the actual value must be used.

This parameter controls the data transmission between EXCHANGE operators. We recommend that you do not modify this parameter unless you want to reduce the number of times of data transmission.

You can run the SHOW PARAMETERS command to view the value of the parameter, as shown in the following example:

obclient>SHOW PARAMETERS LIKE '%dtl%';

+-------+----------+----------------+----------+-----------------+-----------+-------+---------------+----------+---------+---------+-------------------+
| zone  | svr_type | svr_ip         | svr_port | name            | data_type | value | info          | section  | scope   | source  | edit_level        |
+-------+----------+----------------+----------+-----------------+-----------+-------+---------------+----------+---------+---------+-------------------+
| zone1 | observer | 100.81.152.114 |    36500 | dtl_buffer_size | NULL      | 64K   | to be removed | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+----------------+----------+-----------------+-----------+-------+---------------+----------+---------+---------+-------------------+
1 row in set (0.01 sec)