Help Center > > Developer Guide> SQL Reference> SQL Syntax> CREATE RESOURCE POOL

CREATE RESOURCE POOL

Updated at:Mar 13, 2020 GMT+08:00

Function

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

Precautions

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

Syntax

1
2
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" | is_foreign=boolean }[, ... ])];

Parameter Description

  • pool_name

    Specifies the name of a resource pool.

    The name of a resource pool cannot be same as that of an existing resource pool.

    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 mark ('') in the syntax when setting the name of a Cgroup.
    • The value of group_name is case-sensitive.
    • If group_name is not specified, the string "Medium" will be used by default in the syntax, indicating the Medium Timeshare Cgroup under DefaultClass.
    • 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, if control_group is set to High, the resource pool will be associated with the High Timeshare Cgroup under DefaultClass.
    • In multi-tenant scenarios, the Cgroup associated with a group resource pool is a Class Cgroup, and that associated with a service resource pool is a Workload Cgroup. Additionally, switching Cgroups between different resource pools is not allowed.

    Value range: a string. It must comply with the rule in the description, specifying 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 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.

    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 ranges 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, use the default value None.

    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

This example assumes that Cgroups have been created by users in advance.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
-- Create a default resource pool, and associate it with the Medium Timeshare Cgroup under Workload under DefaultClass:
CREATE RESOURCE POOL pool1;

-- Create a resource pool, and associate it with the High Timeshare Cgroup under Workload under DefaultClass:
CREATE RESOURCE POOL pool2 WITH (CONTROL_GROUP="High");

-- Create a resource pool, and associate it with the Low Timeshare Cgroup under Workload under class1:
CREATE RESOURCE POOL pool3 WITH (CONTROL_GROUP="class1:Low");

-- Create a resource pool, and associate it with the wg1 Workload Cgroup under class1:
CREATE RESOURCE POOL pool4 WITH (CONTROL_GROUP="class1:wg1");

-- Create a resource pool, and associate it with the wg2 Workload Cgroup under class1:
CREATE RESOURCE POOL pool5 WITH (CONTROL_GROUP="class1:wg2:3");

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

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel