• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Resource Load Management
  5. Resource Load Management Framework Overview
  6. Resource Pools

Resource Pools

Context

The core element of resource load management in 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.

Resource Pools

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 set the priority and 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 DWS, or modify or remove a resource pool using corresponding DDL statements.

Association Tasks

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

8GB

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 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 dbadmin or a user with database administrator permissions, you can run SQL statements to create and manage resource pools. For details, 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 is associated with the default Cgroup, which is the Medium timeshare Cgroup under the DefaultClass Cgroup.
    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.
    CREATE RESOURCE POOL respool2 WITH (control_group='Rush');

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

    CREATE RESOURCE POOL
    NOTE:
    • 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.
    CREATE RESOURCE POOL respool3 WITH (active_statements=5);

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

    CREATE RESOURCE POOL
    NOTE:
    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.
    CREATE RESOURCE POOL respool4 WITH (MEM_PERCENT=20);

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

    CREATE RESOURCE POOL
    NOTE:

    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
      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
      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
      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.
    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 number of concurrent tasks is less than the sum of local number of concurrent tasks, the number of tasks concurrently executed is less than the global number of concurrent tasks.

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:
    CREATE RESOURCE POOL pool1 WITH (ACTIVE_STATEMENTS=5);
  • To change the number after creating a resource pool:
    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

    NOTE:

    1. The points are used when query_mem is not used during task execution.

    2. After the total number of points of the resource pool is used up, the queuing operation is triggered in (First In, First Out Queuing) FIFO mode.

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 memory_limit, max_dop, and cpu_affinity attributes of the resource pool.
  • View the resource pool bound with a user.
    SELECT rolrespool FROM PG_AUTHID WHERE rolname = 'rolename';
      rolrespool  
    --------------
     default_pool
    (1 row)
  • View all the resource pool information of the current cluster.
    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.
SELECT * FROM gs_control_group_info('respool_name');