• Data Warehouse Service

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

CREATE RESOURCE POOL

Function

CREATE RESOURCE POOL creates a resource pool and specifies the Cgroup of the resource pool.

Precautions

As long the current user has CREATE permission, the user can create a resource pool.

Syntax

CREATE 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'| nodegroup="nodegroupname"}[, ... ])];

Parameter Description

  • pool_name

    Specifies the name of the resource pool.

    The name of the resource pool cannot be same as that 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: A string. It must comply with the rule in the description, which specifies the created 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 threads that can be created for executing a statement.

    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.

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

    In a non-multi-tenant scenario, mem_percent of common users ranges 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

    Specifies the priority of complex jobs in the resource pool

    (Low, Medium, or High). The priority will decrease by one level when the I/O usage exceeds 90%. 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.
  • 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 default resource pool, and the control group is "Medium" Timeshare Workload under "DefaultClass":
CREATE RESOURCE POOL pool1;

-- Create a resource pool, and specify "High" Timeshare Workload under "DefaultClass" as its control group:
CREATE RESOURCE POOL pool2 WITH (CONTROL_GROUP="High");

-- Delete the resource pools:
DROP RESOURCE POOL pool1;
DROP RESOURCE POOL pool2;