• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. SQL Syntax
  6. ALTER RESOURCE POOL

ALTER RESOURCE POOL

Function

ALTER RESOURCE POOL changes the control group of a resource pool.

Precautions

Users having the ALTER permission can modify resource pools.

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

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 compliant with the naming convention.

  • group_name

    Specifies the name of a control group.

    NOTE:

    The syntax can use either double quotation marks ("") or single quotation mark ('') when you set the name of a control group.

    Value range: An existing control group.

  • stmt

    The maximum number of concurrently executed resource pool statements.

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

  • dop

    The maximum number of resource pool statements that can be concurrently executed.

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

  • memory_size

    The maximum storage of 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 of a common user is an integer ranging from 0 to 100. The default value is 0.

  • io_limits

    Specifies the upper limit of I/Os per second in the resource pool.

    The I/Os are counted by ones for column storage and by thousands for row storage.

  • io_priority

    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. If you do not want to control the priority, use the default value None.

NOTE:
  • The value of group_name is case-sensitive.
  • If group_name is not specified, the default value "Medium" is used, which specifies the "Medium" Timeshare Cgroup of DefaultClass Cgroup. For details, see Resource Pools.
  • If the database user specifies the string of Timeshare (it is one of "Rush", "High", "Medium", or "Low"), for example, set control_group to High, the resource pool is specified to the Timeshare control group whose control_group is High under DefaultClass.
  • When both mem_percent and memory_limit are specified, only mem_percent takes effect.
  • The settings of io_limits and io_priority are valid only for complex jobs, such as batch import using INSER INTO SELECT, COPY FROM, or CREATE TABLE AS; complex queries involving over 500 MB data on each DN; and VACUUM FULL.

Example

-- Create a resource pool:
CREATE RESOURCE POOL pool1;

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

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