Help Center> GaussDB> Centralized_3.x> SQL Reference> SQL Syntax> ALTER RESOURCE POOL

Updated on 2024-05-07 GMT+08:00

View PDF

ALTER RESOURCE POOL

Description

Modifies the Cgroup of a resource pool.

Precautions

Only a user with the ALTER permission on the current database can perform this operation.

Syntax

    
         ALTER RESOURCE POOL pool_name
    WITH ({MEM_PERCENT= pct | CONTROL_GROUP="group_name" | ACTIVE_STATEMENTS=stmt | MAX_DOP = dop | MEMORY_LIMIT='memory_size' | io_limits=io_limits | io_priority='io_priority'}[, ... ]);

Parameters

pool_name
Specifies the name of a resource pool.

The resource pool must already exist.

Value range: a string. It must comply with the naming convention.
group_name
Specifies the name of a Cgroup.
- You can use either double quotation marks ("") or single quotation marks ('') in the syntax when setting the name of a Cgroup.
- The value of group_name is case-sensitive.
- If an administrator specifies a Workload Cgroup under Class, for example, control_group set to class1:workload1, the resource pool will be associated with the workload1 Cgroup under class1. The level of Workload can also be specified. For example, control_group is set to class1:workload1:1.
- If a database user specifies the Timeshare string (Rush, High, Medium, or Low) in the syntax, for example, control_group is set to High, the resource pool will be associated with the High Timeshare Cgroup under DefaultClass.
Value range: an existing Cgroup.
stmt
Specifies the maximum number of statements that can be concurrently executed in a resource pool.

Value range: numeric data ranging from –1 to 2147483647. –1 indicates no restriction and 0 indicates that no statement can be executed.
dop
Specifies the maximum statement concurrency degree for a resource pool, equivalent to the number of threads that can be created for executing a statement.

Value range: numeric data ranging from 1 to 2147483647
memory_size
Specifies the maximum memory size of a resource pool.

Value range: a string of 1 KB to 2047 GB case-sensitive characters.
mem_percent
Specifies the proportion of available resource pool memory to the total memory or group user memory.

In multi-tenant scenarios, mem_percent of group users or service users ranges from 1 to 100. The default value is 20.

In common scenarios, mem_percent of common users is an integer ranging from 0 to 100. The default value is 0.

When both of mem_percent and memory_limit are specified, only mem_percent takes effect.
io_limits
Specifies the upper limit of IOPS in a resource pool.

It is counted by 10 thousands per second.

Value range: numeric data ranging from 0 to 2147483647
io_priority
Specifies the I/O priority for jobs that consume many I/O resources. It takes effect when the I/O usage reaches 90%.

There are three priorities: Low, Medium, and High. If you do not want to control I/O resources, set this parameter to None, which is the default value.

Value range: enumerated type. The options are None, Low, Medium, and High.

The settings of io_limits and io_priority are valid only for complex jobs, such as batch import (using INSERT INTO SELECT, COPY FROM, or CREATE TABLE AS), complex queries involving over 500 MB data on each DN, and VACUUM FULL.
max_workers
Concurrency in a table during data redistribution. This column is used only for scaling.
max_connections
Maximum number of connections that can be used by a resource pool.

The total maximum number of connections in all resource pools cannot exceed the maximum number of connections specified by max_connections of the entire GaussDB process.

Examples

The following example assumes that the user has created the class1 Cgroup and three Workload Cgroups under class1: Low, wg1, and wg2.

   
        -- Create a resource pool.
gaussdb=# CREATE RESOURCE POOL pool1 WITH (CONTROL_GROUP="Medium");

-- Specify the High Timeshare Workload Cgroup under the DefaultClass Cgroup.
gaussdb=# ALTER RESOURCE POOL pool1 WITH (CONTROL_GROUP="High");

-- Specify the Low Timeshare Workload Cgroup under the class1 Cgroup.
gaussdb=# ALTER RESOURCE POOL pool1 WITH (CONTROL_GROUP="class1:Low");

-- Specify the wg1 Workload Cgroup under the class1 Cgroup.
gaussdb=# ALTER RESOURCE POOL pool1 WITH (CONTROL_GROUP="class1:wg1");

-- Specify the wg2 Workload Cgroup under the class1 Cgroup.
gaussdb=# ALTER RESOURCE POOL pool1 WITH (CONTROL_GROUP="class1:wg2:3"); 

-- Delete the resource pool pool1.
gaussdb=# DROP RESOURCE POOL pool1;