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

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.

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

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

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)