CREATE RESOURCE POOL

Function

CREATE RESOURCE POOL creates a resource pool and specifies the Cgroup for the resource pool. The system resources (CPU, memory, I/O, and storage resources) of a database are limited. When multiple types of services (such as data loading, batch analysis, and real-time query) are running at the same time, they may compete for resources and hinder operations. As a result, the throughput decreases and the overall performance deteriorates. Allocating system resources correctly prevents efficiency loss due to poor resource use. DWS offers a resource management feature that lets you create separate resource pools for different needs. These pools keep resources independent from one another.

This syntax is for reference only. You are advised to create a resource pool on the console. For details about resource pools, see "DWS Resource Load Management" in the User Guide.

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='priority' | nodegroup='nodegroup_name' | is_foreign = boolean | except_rule='except_rule' | weight=bandwidth_weight | enable_concurrency_scaling=boolean}[, ... ])];

Parameter Description

1

CREATE RESOURCE POOL pool1;

1

CREATE RESOURCE POOL pool2 WITH (CONTROL_GROUP="High");

1

CREATE RESOURCE POOL pool3 WITH (ACTIVE_STATEMENTS=5, MEM_PERCENT=50, weight=5);

1

CREATE RESOURCE POOL pool4 WITH (MAX_DOP=10);

Examples

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

Create a default resource pool, and associate it with the Medium Timeshare Workload Cgroup under DefaultClass.

1

CREATE RESOURCE POOL pool1;

Create a resource pool, and associate it with the High Timeshare Workload Cgroup under DefaultClass.

1

CREATE RESOURCE POOL pool2 WITH (CONTROL_GROUP="High");

Create pool_p and set the maximum number of complex statements that can be concurrently executed to 5, the memory usage to 50%, and the network weight to 5.

1

CREATE RESOURCE POOL pool_p WITH (ACTIVE_STATEMENTS=5, MEM_PERCENT=50, weight=5);

Query the configuration parameters of the resource pool in the PG_RESOURCE_POOL system catalog.

1

SELECT * FROM PG_RESOURCE_POOL WHERE respool_name = 'pool_p';

1
2

CREATE EXCEPT RULE except_rule4 WITH (elapsedtime=10);
CREATE resource pool resource_pool_a4 WITH (except_rule='except_rule4');

Create a resource pool and enable the elastic concurrent expansion function of the resource pool.

1

CREATE RESOURCE POOL pool6 WITH (enable_concurrency_scaling=true);

Example: Create an exception rule to terminate a job when the job runs for more than 10 seconds.

In DWS resource load management, exception rules take effect only after being bound to a resource pool, and then the user who executes a job is bound to a specified resource pool. If an exception rule is triggered when a user executes a job, the job will be terminated. Perform the following operations to simulate the termination process.

1. Create a test table and import data to the table.
   1. Connect to the database as the system administrator and run the following SQL statements to create temporary table src to provide data sources for subsequent INSERT operations:

      1 2	CREATE SCHEMA test; CREATE TABLE test.src AS SELECT 1;

   2. Run the following SQL statements to create table test.test:

      1 2	DROP TABLE IF EXISTS test.test; CREATE TABLE test.test(a int, b numeric(15,2)) WITH(orientation=column);

   3. Run the SQL statements to insert 20 million rows of data into the two tables. It takes about half a minute. Please wait.

      1	INSERT INTO test.test SELECT generate_series(1,20000000) % 1000,generate_series(1,20000000) FROM test.src;

2. Create exception rule set except_rule4 and set elapsedtime to 10 seconds.

   1	CREATE EXCEPT RULE except_rule4 WITH (elapsedtime=10);

3. Create resource pool resource_pool_a4 and bind the exception rule to it.

   1	CREATE resource pool resource_pool_a4 WITH (except_rule='except_rule4');

4. Create a job user and bind the user to resource_pool_a4. The password is configured as required.

   1	CREATE USER user4 RESOURCE POOL 'resource_pool_a4' PASSWORD 'password';

5. Grant the permission to access table test.test to user4.

   1 2	GRANT USAGE ON SCHEMA test TO user4; GRANT SELECT ON TABLE test.test TO user4;

6. Connect to the database as user4 and run the following SQL statement. Once the statement is executed for more than 10 seconds, it is terminated.

   1	SELECT * FROM test.test;

   

7. Check exception rule definitions in the PG_EXCEPT_RULE system catalog.

   1	SELECT * FROM PG_EXCEPT_RULE WHERE name = 'except_rule4';

   

Helpful Links

ALTER RESOURCE POOL, DROP RESOURCE POOL, CREATE EXCEPT RULE