Schedule compute nodes and control DOP - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to schedule compute nodes and ensure a desired degree of parallelism (DOP) by using the Elastic Parallel Query (ePQ) feature.

Prerequisites

The feature is supported on a PolarDB for PostgreSQL cluster that meets the following requirements:

PostgreSQL 11 with a minor engine version of 1.1.20 or later
PostgreSQL 14 with a minor engine version of 14.6.6.0 or later

Note

You can execute one of the following statements to query the minor engine version of your PolarDB for PostgreSQL cluster:

PostgreSQL 11
```
show polar_version;
```
PostgreSQL 14
```
select version();
```

Overview

ePQ allows you to implement fine-grained control on compute nodes in PolarDB for PostgreSQL clusters. This improves the utilization of computing resources. You can use idle computing resources for parallel queries to maximize resource utilization and prevent other workloads from being affected. ePQ has the following features:

ePQ allows you to dynamically schedule the compute nodes with idle resources to perform parallel queries in a PolarDB for PostgreSQL cluster.
ePQ allows you to dynamically adjust the DOP of parallel queries on each compute node in a PolarDB for PostgreSQL cluster. This way, parallel queries do not affect other processes that run on the same compute node.

Schedule compute nodes

Parameters

Parameter	Description
polar_px_nodes	The compute nodes that are used to perform parallel queries. By default, this parameter is left empty, which specifies that all read-only nodes are used. Note You can execute the following statement to query the compute nodes that are used to perform parallel queries: `SHOW polar_px_nodes;` If the loads of some read-only nodes are high, you can modify the `polar_px_nodes` parameter to specify the read-only nodes with lower loads to perform parallel queries. The `polar_px_nodes` parameter specifies a list of compute nodes. Separate node names with commas (,).
polar_px_use_primary	Specifies whether the primary node is used to perform parallel queries. Valid values: ON OFF Note This parameter applies only to a PolarDB for PostgreSQL cluster whose version is PostgreSQL 14. You can execute the following statement to specify that the primary node is used to perform parallel queries: `SET polar_px_use_primary TO ON;`
polar_px_use_master	Specifies whether the master node is used to perform parallel queries. Valid values: ON OFF Note This parameter applies only to a PolarDB for PostgreSQL cluster whose version is PostgreSQL 11. You can execute the following statement to specify that the master node is used to perform parallel queries: `SET polar_px_use_master TO ON;`

Examples

Execute the following statement to install the polar_monitor extension, which is used to return the names of compute nodes:
```
CREATE EXTENSION IF NOT EXISTS polar_monitor;
```
Query the names of all compute nodes in your cluster by using the topology view that is provided by the polar_monitor extension.
```
SELECT name,slot_name,type FROM polar_cluster_info;
```
The following sample code provides an example of the query results:
```
 name  | slot_name |  type
-------+-----------+---------
 node0 |           | Primary
 node1 | standby1  | Standby
 node2 | replica1  | Replica
 node3 | replica2  | Replica
(4 rows)
```
Note
The following types of compute nodes are returned:
- Primary: a primary node.
- Replica: a read-only node.
- Standby: a standby node.
Execute the following statement to use read-only nodes node2 and node3 with lower loads to perform parallel queries:
```
SET polar_px_nodes = 'node2,node3';
SHOW polar_px_nodes;
```
The following sample code provides an example of the results:
```
 polar_px_nodes
----------------
 node2,node3
(1 row)
```

Control DOP

Parameters

Parameter

Description

polar_px_dop_per_node

The number of segments on each compute node for parallel queries in the current session. Each segment starts a process for each slice that is to be executed. Default value: 3. To perform best practices, we recommend that you set this parameter to half the number of CPU cores of a compute node.

Note

If the CPU loads of compute nodes are high, you can set this parameter to a smaller value. Make sure that the CPU utilization of each compute node is lower than 80%.
If the query performance is unsatisfactory, you can set this parameter to a greater value. Make sure that the CPU utilization of each compute node is lower than 80%. Otherwise, other background processes may be slowed down.

Examples

Execute the following statement to create a table in your cluster:
```
CREATE TABLE test(id INT);
```
If the cluster has two read-only nodes and the polar_px_nodes parameter is left empty, both the read-only nodes are used to perform parallel queries. If the polar_px_dop_per_node parameter is set to 3, each compute node contains three segments. The following sample code provides an example of the execution plan:
```
SHOW polar_px_nodes;
 polar_px_nodes
----------------

(1 row)

SHOW polar_px_dop_per_node;
 polar_px_dop_per_node
-----------------------
 3
(1 row)

EXPLAIN SELECT * FROM test;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 PX Coordinator 6:1  (slice1; segments: 6)  (cost=0.00..431.00 rows=1 width=4)
   ->  Partial Seq Scan on test  (cost=0.00..431.00 rows=1 width=4)
 Optimizer: PolarDB PX Optimizer
(3 rows)
```
In this example, each of the six segments on two read-only nodes starts a process to execute slice1, which is the only slice of the execution plan. Therefore, six processes are started to perform a query in parallel.

If you set the polar_px_dop_per_node parameter to 4 and then perform another query, a total of eight segments on two read-only nodes are used. Each of the eight segments starts a process to execute the only slice slice1 of the execution plan. Therefore, eight processes are started to perform the query in parallel.

SET polar_px_dop_per_node TO 4;

EXPLAIN SELECT * FROM test;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 PX Coordinator 8:1  (slice1; segments: 8)  (cost=0.00..431.00 rows=1 width=4)
   ->  Partial Seq Scan on test  (cost=0.00..431.00 rows=1 width=4)
 Optimizer: PolarDB PX Optimizer
(3 rows)

Set the polar_px_use_primary parameter to ON, which specifies that the primary node is used to perform parallel queries. In this example, four segments on the primary node are also used. Therefore, 12 processes are started to perform the query in parallel.

SET polar_px_use_primary TO ON;

EXPLAIN SELECT * FROM test;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 PX Coordinator 12:1  (slice1; segments: 12)  (cost=0.00..431.00 rows=1 width=4)
   ->  Partial Seq Scan on test  (cost=0.00..431.00 rows=1 width=4)
 Optimizer: PolarDB PX Optimizer
(3 rows)