Enabling Parallel Query

TaurusDB supports parallel query. You can enable or disable parallel query for an instance on the console or using hints.

Prerequisites

It is recommended that the DB engine version of your TaurusDB instance be 2.0.45.230900 or later.

Constraints

Both the primary node and read replicas support parallel query. Parallel query consumes a lot of compute resources (such as vCPUs and memory). To ensure instance stability, if the kernel version of your TaurusDB instance is 2.0.42.230600 or later, parallel query is disabled for the primary node by default. To enable it, submit a service ticket.
If you use OFFSET or LIMIT without adding an ORDER BY condition, the order of the result set returned for parallel queries may be different from that for non-parallel queries. In this case, you need to add an ORDER BY primary key or unique key to ensure that the result set is correct.

System Parameters and Status Variables

System Parameters

**Table 1** System parameters
Parameter	Level	Description
force_parallel_execute	Global, Session	Controls whether to enable parallel query. If this parameter is set to ON, query SQL statements are executed in parallel as much as possible. If this parameter is set to OFF, parallel query tuning is disabled. If this parameter is set to AUTO (supported in kernel 2.0.60.241200 or later), parallel query tuning is only used in specific scenarios, which are controlled by the options in pq_support_features_switch. Value range: ON, OFF, and AUTO Default value: OFF
pq_master_enable	Global	Controls whether to enable parallel query on the primary node. This parameter must be used together with force_parallel_execute. If this parameter is set to ON and force_parallel_execute is set to ON or AUTO, parallel query is applied on the primary node. If this parameter is set to OFF, parallel query is not applied on the primary node. Read replicas are not affected by this parameter. Value range: ON and OFF Default value: ON
parallel_max_threads	Global	Specifies the maximum number of active threads allowed for parallel execution. If the number of active threads in the current system exceeds the value of this parameter, parallel execution cannot be enabled for new queries. Value range: 0 to 4294967295 Default value: 64
parallel_default_dop	Global, Session	Specifies the default parallelism degree for parallel execution. If the parallelism degree is not specified in query statements, this parameter value is used. Value range: 0 to 1024 Default value: 4
parallel_cost_threshold	Global, Session	Specifies the cost threshold for enabling parallel execution. If the parallel execution cost of query statements exceeds the value of this parameter, parallel execution is enabled. Value range: 0 to 4294967295 Default value: 1000
parallel_queue_timeout	Global, Session	Specifies the waiting time of the parallel execution. If the waiting time exceeds the value of this parameter, new queries will be executed in single-thread mode. Value range: 0 to 4294967295 Default value: 0
parallel_memory_limit	Global	Specifies the maximum available memory for parallel execution. If the amount of memory used for parallel execution exceeds the value of this parameter, new queries will not be executed in parallel mode. Value range: 0 to 4294967295 Default value: 104857600
parallel_setup_cost	Global, Session	Specifies the cost of launching parallel worker processes. It is used to estimate the total cost of parallel execution. Value range: 0 to 1000000000 Default value: 1000
parallel_tuple_cost	Global, Session	Specifies the cost of transferring one tuple from a parallel worker process to another process. It is used to estimate the total cost of parallel execution. Value range: 0 to 1000000 Default value: 1.5

Status Variables

**Table 2** Status variables
Variable	Level	Description
PQ_threads_running	Global	Specifies the total number of concurrent threads that are running.
PQ_memory_used	Global	Specifies the total memory used for parallel execution.
PQ_threads_refused	Global	Specifies the total number of queries that fail to be executed in parallel due to the limit on the total number of threads.
PQ_memory_refused	Global	Specifies the total number of queries that fail to be executed in parallel due to the limit on the total memory.

Enabling Parallel Query

You can enable or disable parallel query by configuring system parameters on the console or using hints.

You can enable or disable parallel query and adjust the parallelism degree by configuring system parameters on the Parameters page of the console.

Log in to the management console.
Click in the upper left corner and select a region and project.
Click in the upper left corner of the page and choose Databases > TaurusDB.
On the Instances page, click the instance name.
In the navigation pane, choose Parameters.

Alternatively, click in the upper right corner of the Basic Information page and choose Modify Parameters.

Configure system parameters to enable parallel query. The parameters listed in Table 3 can be dynamically modified without the need to reboot the instance.

**Table 3** System parameters for parallel query
Parameter	Description
force_parallel_execute	Controls whether to forcibly enable parallel query.
pq_master_enable	Controls whether to enable parallel query on the primary node.
parallel_default_dop	Specifies the number of concurrent threads.
parallel_cost_threshold	Specifies the cost threshold for enabling parallel query. The default value is 1000. You are advised not to set this parameter to a small value because parallel query is not suitable for simple queries.

You can also adjust the parallelism degree and modify the cost threshold for enabling parallel query in a session. For example, you can set the parallelism degree to 8 and the cost threshold for enabling parallel query to 100 as follows:

SET parallel_default_dop=8;
SET parallel_cost_threshold=100;

Hints can be used to control whether a single statement is executed in parallel. If parallel execution is disabled by default, uses hints to enable parallel execution for specific SQL statements. You can also use hints to disable parallel execution for specified SQL statements.

SELECT is followed by PQ (Hints). Otherwise, the hints do not take effect. parallel_default_dop indicates the parallelism degree of a parallel query and its value ranges from 1 to min(parallel_max_threads, 1024). When the parallel_default_dop value exceeds the normal range, parallel query does not take effect.

Enabling parallel execution
- Use the default parameter settings.
```
SELECT /*+ PQ() */ … FROM …
```
- Use the default parameter settings and specify the parallelism degree to 8.
```
SELECT /*+ PQ(8) */ … FROM …
```
- Use the default parameter settings and specify the parallel-executed table to t1.
```
SELECT /*+ PQ(t1) */ … FROM …
```
- Use the default parameter settings and specify the parallel-executed table to t1 and the parallelism degree to 8.
```
SELECT /*+ PQ(t1 8) */ … FROM …
```
Disabling parallel execution
When parallel query is enabled, use the NO_PQ hint to disable parallel execution of a single SQL statement.
NO_PQ (Hints) takes precedence over PQ (Hints). If a SQL statement contains NO_PQ (Hints), the SQL statement will not be executed concurrently even if PQ (Hints) is configured.
```
SELECT /*+ NO_PQ */ … FROM …
```

Checking the Statuses of Query Statements Executed in Parallel

Run the following SQL statement to display the statuses of query statements executed in parallel, as shown in Figure 1.

show status like "%PQ%"

Figure 1 Status
Click to enlarge

Use EXPLAIN to display the parallel execution plans of the query statements, as shown in Figure 2.

Figure 2 Parallel execution plan
Click to enlarge

Compared with a traditional execution plan, a parallel execution plan has one more row of records. In the first row of the query results, the parallel-executed tables and parallelism degree are displayed.

Parent Topic: Parallel Query

Previous topic: Supported and Unsupported Scenarios

Next topic: Testing Parallel Query Performance