Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Resource Pools

Updated on 2022-08-16 GMT+08:00

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.

NOTICE:
  • 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
    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.
    1
    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.
    1
    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
      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

    NOTE:
    • 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

NOTICE:
  • 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

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback