Schedule compute nodes and control DOP

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

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

Examples

1. 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;

2. 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.

3. 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

Examples

1. Execute the following statement to create a table in your cluster:

   CREATE TABLE test(id INT);

2. 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.

3. 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)

4. 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)