Updated on 2022-07-29 GMT+08:00

Resource Pools

Context

The core element of resource load management in GaussDB(DWS) is the resource pool, which provides attributes to control memory, I/O, and CPU resources at the bottom layer and to manage and allocate resources based on the priority-based task scheduling mechanism, managing resource load for user services.

Resource pool-oriented load management includes concurrence management and priority-based task scheduling.

Overview

The resource pool is a resource configuration mechanism for allocating host resources. You can specify resources and concurrency queue attributes for a resource pool, and then configure the available resources for a job by binding it to the resource pool.

The database administrator binds the resource pool to its corresponding Cgroup based on different service types.

Resource Pool-Oriented Load Management

Figure 1 illustrates how resource loads are managed.

Figure 1 Load management

A resource pool allocates resources through its binding Cgroup. The task priority depends on the resource volume allocated to the binding resource pool. Generally, the more resources a resource pool has, the higher priority a binding task possesses, as the task will have more resources for execution.

In this figure:

  • The priority of task A is higher than that of task C and task D, because the resource pool that task A is bound to has the largest volume of resources.
  • Task A and Task B possess the same priority and share system resources allocated to resource pool 1 in equal during competition because the two are in the same resource pool.

To adjust the priority of a task, you can simply change its binding resource pool.

Initialization Process

Figure 2 and Table 1 illustrates the details of the operation process in resource load management.

Figure 2 Load management workflow
Table 1 Load management tasks

Task

Description

Resource Pools

Create a resource pool using the DDL statements provided in GaussDB(DWS), or modify or remove a resource pool using corresponding DDL statements.

Associated Jobs

Associate a user to a resource pool to bind the tasks to a specified resource pool before executing the task.

Executing the task

-

default_pool

After the resource load management function is enabled, default_pool is automatically created by the system. A session or user not associated with a resource pool is associated with default_pool. By default, default_pool is bound with the DefaultClass:Medium Cgroup and does not limit the number of concurrent services.

For detailed default_pool attributes, see Table 2.

Table 2 default_pool attributes

Attribute

Value

Description

respool_name

default_pool

Specifies the name of the resource pool.

mem_percent

100

Specifies the maximum memory usage.

cpu_affinity

-1

Specifies the CPU affinity (reserved).

control_group

DefaultClass:Medium

Specifies the Cgroup associated with the resource pool.

active_statements

-1

Specifies the maximum number of concurrent queries allowed by the resource pool. The value -1 indicates that the number of concurrent queries is not limited.

max_dop

1

Concurrency level of execution operators after the SMP is enabled (reserved).

memory_limit

8 GB

Specifies the upper memory usage limit (reserved).

parentid

0

Specifies the OID of the parent resource pool.

io_limits

0

Specifies the upper limit of I/Os per second. It is counted by ones in a column-store table and by ten thousands in a row-store table. 0 indicates that the upper limit of I/Os per second is not controlled.

io_priority

None

I/O priority set for jobs that consume many I/O resources. It takes effect when I/O usage reaches 90%. Its values Low, Medium, and High correspond to 25%, 50%, and 80% of the IOPS used by each job when it was triggered, respectively. None indicates the priority is not controlled.

  • default_pool cannot be modified in GaussDB(DWS).
  • For the services associated with default_pool, the number of concurrent queries is limited by the max_active_statements parameter. max_active_statements specifies the maximum number of queries that can be performed on a CN.

Procedure

Creating a resource pool

After resource load management is enabled, the default resource pool is insufficient to address the resource load management requirements of services. Therefore, new resource pools must be used to reallocate system resources to address the requirements.

After connecting to the database as user or a user with database administrator permissions, you can run SQL statements to create and manage resource pools. For details about the syntax, see CREATE RESOURCE POOL, ALTER RESOURCE POOL, and DROP RESOURCE POOL.

  • Create a resource pool associated with the default Cgroup. If you do not specify an associated Cgroup when creating a resource pool, the resource pool will be associated with the default Cgroup, which is the Timeshare Cgroup Medium under the DefaultClass Cgroup.
    1
    CREATE RESOURCE POOL respool1;
    

    If the following information is displayed, the resource pool has been created.

    CREATE RESOURCE POOL
  • Create a resource pool associated with the Rush timeshare Cgroup.
    1
    CREATE RESOURCE POOL respool2 WITH (control_group='Rush');
    

    If the following information is displayed, the resource pool has been created.

    CREATE RESOURCE POOL
    • The value of control_group is case-sensitive and must be contained in quotation marks (').
    • Only strings indicating Timeshare Cgroups (Rush, High, Medium, or Low) can be specified. For example, set control_group to High. The resource pool is specified to the Timeshare Cgroup whose control_group is High under DefaultClass.
  • Create a resource pool that limits the number of concurrent queries. When creating a resource pool, you can set active_statements to specify the maximum number of concurrent tasks associated with the resource pool. If the limit is exceeded, a task queue is implemented to control the number of concurrent tasks.
    1
    CREATE RESOURCE POOL respool3 WITH (active_statements=5);
    

    If the following information is displayed, the resource pool has been created.

    CREATE RESOURCE POOL
    1. The default value of active_statements is 10, indicating that there are a maximum of 10 concurrent tasks on a resource pool.
    2. The value range of active_statements is from -1 to INT_MAX, where -1 indicates that the number of concurrent tasks is not limited.
  • Create a resource pool that limits available memory. While creating a resource pool, you can set MEM_PERCENT to specify the maximum memory available to the resource pool. MEM_PERCENT is an integer ranging from 0 to 100.
    1
    CREATE RESOURCE POOL respool4 WITH (MEM_PERCENT=20);
    

    If the following information is displayed, the resource pool has been created.

    CREATE RESOURCE POOL

    Set the size of the memory used by the resource pool to 20% of the available memory size.

Managing resource pools

After creating resource pools, an administrator often needs to manage resource pools as required, such as adjusting resource pool configurations or deleting obsolete resource pools.

Resource pool management includes:

  • Modifying resource pool attributes
    • Modifying Cgroups associated with resource pools
      1
      ALTER RESOURCE POOL respool1 WITH (control_group="Rush");
      

      If the following information is displayed, the Cgroup has been modified.

      ALTER RESOURCE POOL
    • Modifying the number of concurrent tasks of resource pools
      1
      ALTER RESOURCE POOL respool1 WITH (ACTIVE_STATEMENTS=15);
      

      If the following information is displayed, the number of concurrent tasks has been modified.

      ALTER RESOURCE POOL
    • Modifying the upper limit on the available memory of resource pools
      1
      ALTER RESOURCE POOL respool1 WITH (MEM_PERCENT=20);
      

      If the following information is displayed, the upper limit of the available resource pool memory has been modified.

      ALTER RESOURCE POOL

    For more usage, see ALTER RESOURCE POOL.

  • DROP RESOURCE POOL deletes a resource pool.
    1
    2
    DROP RESOURCE POOL respool1;
    DROP RESOURCE POOL
    

Managing Local Concurrent Tasks

The local number of concurrent tasks indicates the maximum number of tasks that can be concurrently executed on one resource pool. The number is specified by ACTIVE_STATEMENTS.

Generally, the global number of concurrent tasks must be greater than the sum of local number of concurrent tasks. If the global concurrency is less than the sum of local concurrency, the actual number of concurrent jobs will not exceed the configured global concurrency.

You can specify the number of concurrent tasks of a resource pool either when creating a resource pool or change the number after creating the resource pool.

  • To specify the number when creating a resource pool:
    1
    CREATE RESOURCE POOL pool1 WITH (ACTIVE_STATEMENTS=5);
    
  • To change the number after creating a resource pool:
    1
    ALTER RESOURCE POOL pool1 WITH (ACTIVE_STATEMENTS=3);
    

    The resource pool uses the method of calculating the concurrent points to calculate the number of concurrent tasks, and the formula is:

    Number of points used during task execution: active_points = (query_mem/respool_mem) x active_statements x 100

    Total number of points of a resource pool: total_points = active_statements x 100

    Unit points: 100

    • The points are used when query_mem is not used during task execution.
    • After all the points of a resource pool are used up, the jobs will enter a first in first out (FIFO) queue.
    • MAX_DOP limits the number of concurrent fast lanes. The value ranges from 1 to INT_MAX. The default value is -1, indicating that the number of concurrent fast lanes is not limited.
    • The value range of ACTIVE_STATEMENTS is from -1 to INT_MAX. The default value is 10, which is recommended. The values 0 and -1 mean that slow lane concurrency is not subject to the value of ACTIVE_STATEMENTS.
    • The value of MEM_PERCENT ranges from 0 to 100. When the value is set to 0, the slow lane concurrency is not subject to the value of MEM_PERCENT.
    • The slow lane concurrency is restricted by ACTIVE_STATEMENTS and MEM_PERCENT. The point control mode is used. The total number of points is determined by ACTIVE_STATEMENTS.
      • When ACTIVE_STATEMENTS is set to -1 or 0, the value of total_points is 90.
      • Job points are classified into memory points and concurrent points. The number of memory points is determined by the estimated memory of a job. The number of concurrent points is fixed at 100. The number of memory points and that of concurrent points are controlled separately. After the limit is exceeded, the slow lane query triggers queuing, and the queue complies with the FIFO rule.
    • Generally, the global concurrency must be greater than or equal to the sum of local concurrency. If the global concurrency is less than the sum of local concurrency, the actual number of concurrent jobs will not exceed the configured global concurrency.
    • In non-adaptive-load scenarios, a complex statement is a statement whose execution cost is greater than or equal to the value of the GUC parameter parctl_min_cost. Among common statements, the DDL statements and SET statements, which come with low execution costs, are not restricted to local concurrency.
    • In adaptive load scenarios, a complex statement is a statement whose estimated memory usage is greater than a specified threshold (32 MB currently).
    • If enable_transaction_parctl is set to off, stored procedures and transactions are not restricted by local concurrency control.
    • If enable_transaction_parctl is set to on, transaction_pending_time is set to a value greater than 0, and the number of transaction block statements and stored procedure statements waiting in a queue exceeds the specified value, the statements will be forcibly executed regardless of the current resource status.
    • Statements triggered by internal logics, such as the internal logics of system views and the execution logics of gs_clean and CM tools, are not restricted to local concurrency.
    • You can manage memory and concurrency separately or jointly. Under joint management and control, jobs can be executed only when both the memory and concurrency meet the delivery conditions.

Viewing the Current Resource Pool

  • Do not use insert, update, delete, or truncate in the system catalog pg_resource_pool for resource load management. Otherwise, the contents of pg_resource_pool queried through different CNs may be inconsistent.
  • Do not modify the cpu_affinity attribute of a resource pool.
  • View the resource pool associated with a user.
    1
    2
    3
    4
    5
    SELECT rolrespool FROM PG_AUTHID WHERE rolname = 'rolename';
      rolrespool  
    --------------
     default_pool
    (1 row)
    
  • View all the resource pool information of the current cluster.
    1
    SELECT * FROM PG_RESOURCE_POOL;
    
     respool_name | mem_percent | cpu_affinity |    control_group    | active_statements | max_dop | memory_limit | parentid | io_limits | io_priority 
    --------------+-------------+--------------+---------------------+-------------------+---------+--------------+----------+-----------+-------------
     default_pool |         100 |           -1 | DefaultClass:Medium |                -1 |       1 | 8GB          |        0 |         0 | None
    (1 row)

Viewing the Cgroup Information Associated with a Resource Pool

View the Cgroup information associated with a resource pool. In the following example, resource_pool_a1 is the name of the resource pool.
1
SELECT * FROM gs_control_group_info('resource_pool_a1');
        name         |  class  |  workload   | type  | gid | shares | limits | rate | cpucores
---------------------+---------+-------------+-------+-----+--------+--------+------+----------
 class_a:workload_a1 | class_a | workload_a1 | DEFWD |  87 |     30 |      0 |    0 | 0-3
(1 row)
Table 3 gs_control_group_info attributes

Attribute

Value

Description

name

class_a:workload_a1

Class name and workload name

class

class_a

Class Cgroup name

workload

workload_a1

Workload Cgroup name

type

DEFWD

Cgroup type (Top, CLASS, BAKWD, DEFWD, and TSWD)

gid

87

Cgroup ID

shares

30

Percentage of CPU resources to those on the parent node

limits

0

Percentage of CPU cores to those on the parent node

rate

0

Allocation ratio in Timeshare

cpucores

0-3

CPU core count