Help Center> Data Warehouse Service> SQL Syntax Reference> DDL Syntax> ALTER RESOURCE POOL
View PDF

ALTER RESOURCE POOL

Function

ALTER RESOURCE POOL changes the Cgroup of a resource pool.

Precautions

Users having the ALTER permission can modify resource pools.

Syntax

1
2
 
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'}[, ... ]);

Parameter Description

  • pool_name

    Specifies the name of the resource pool.

    The name of the resource pool is the name of an existing resource pool.

    Value range: a string. It must comply with the naming convention rule.

  • group_name

    Specifies the name of a Cgroup.

    • You can use either double quotation marks ("") or single quotation mark ('') in the syntax when setting the name of a Cgroup.
    • The value of group_name is case-sensitive.
    • When group_name is not specified, the default value "Medium" is used. It is the "Medium" Timeshare Cgroup

      of the DefaultClass Cgroup.

    • If a database user specifies the Timeshare string (Rush, High, Medium, or Low) in the syntax, for example, if control_group is set to High, the resource pool will be associated with the High Timeshare Cgroup under DefaultClass.

    Value range: an existing control group.

  • stmt

    Specifies the maximum number of statements that can be concurrently executed in a resource pool.

    Value range: Numeric data ranging from -1 to INT_MAX.

  • dop

    This is a reserved parameter.

    Value range: Numeric data ranging from -1 to INT_MAX.

  • memory_size

    Specifies the maximum storage for a resource pool.

    Value range: a string, from 1KB to 2047GB.

  • mem_percent

    Specifies the proportion of available resource pool memory to the total memory or group user memory.

    The value of mem_percent for a common user 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.

    The IOPS is counted by ones for column storage and by 10 thousands for row storage.

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

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.

Examples

The example assumes that the user has created the class1 Cgroup and three Workload Cgroups under class1: Low, wg1, and wg2. For details, see Priority Control.

1
2
3
4
5
6
7
8
 
-- Create a resource pool:
CREATE RESOURCE POOL pool1;

-- Specify "High" Timeshare Workload under "DefaultClass" as the Cgroup for a resource pool:
ALTER RESOURCE POOL pool1 WITH (CONTROL_GROUP="High");

-- Remove resource pool 1:
DROP RESOURCE POOL pool1;

Helpful Links

CREATE RESOURCE POOL, DROP RESOURCE POOL

Parent topic: DDL Syntax