Product Documentation

Appearance

CREATE WLM RESOURCE POOL ​

Create a workload management (WLM) resource pool.

CREATE WLM RESOURCE POOL name (PROFILE name [ options ])

Only superusers can create, alter, and drop WLM resource pools.

POOL name ​

Use any valid SQL identifier as the pool name. Names longer than 63 characters are allowed but truncated. See also SQL Identifiers.

PROFILE name ​

A single set of parentheses is required around the profile name and the subsequent list of options (if any). The resource pool may belong to an existing profile or a profile that you have not yet created. For example: (profile myprofile)

You cannot attach a user-defined resource pool to the system profiles (default and maintenance).

options ​

Express each option as a key-value pair and separate the options with commas. You can list options in any order. If you do not specify any options, the result is a resource pool with default values. The profile name and subsequent options must be enclosed in parentheses.

max_concurrency

Maximum number of concurrent queries that can run in this pool. The default is 1. max_concurrency must be less than or equal to 100. If max_concurrency is greater than min_concurrency, the pool is a flex pool. See Examples of Flex Pools.

min_concurrency

Minimum number of concurrent queries that can run in this pool. The default is 1. min_concurrency must be less than or equal to max_concurrency.

slots (deprecated)

For backward compatibility, the number of concurrent queries that can run in this pool. The default is 1. This option cannot be null and must be set to a number between 1 and 100.

queue_size

Number of queries that can wait in the queue for this pool. Set this option to a number greater than 0. The default is 10. This option cannot be null.

maximum_wait_limit

Maximum number of seconds that a query can wait in this pool. This option can be set to a number greater than 0 or null.

maximum_row_limit

Maximum number of rows that a query can return when executed in this pool. This option can be set to a number greater than or equal to 0 or null.

maximum_exec_time

Maximum duration (number of seconds) for a query that executes in this pool. This option can be set to a number greater than or equal to 0 or null.

max_spill_pct

Maximum temporary space available for spilling. Enter a percentage from 1 to 100. Other valid values are null and 0. A value of 0 disables spilling for this resource pool. A value of null, which is the default, is equivalent to Any in the SMC, which means split any unallocated temporary space with other queries running under pools set to Any. (Requested memory per slot must be at least 1GB for pools with spilling enabled and 512MB for pools with spilling disabled.)

requested_memory

Memory restricted for this pool, expressed either in MB or as a percentage, using single quotes. For example, '5000', '1000MB', and '10%' are valid values (stored as 5000, 1000, and 10%, respectively). You can also specify null (no restriction on memory).

The requested memory for the pool is divided equally among the concurrent query slots. Requested memory per slot must be at least 1GB for pools with spilling enabled and 512MB for pools with spilling disabled.

If more memory is requested than is available per worker, the memory will be scaled down. However, the system pools will always get the memory allocated to them. See Allocating Query Memory.

next_memory_queue

Name of the next resource pool to use, if memory is exceeded for this pool. A null value is allowed (no next pool).

next_exec_time_limit_queue

Name of the next resource pool to use, if the execution time limit is exceeded for this pool. A null value is allowed (no next pool).

Query the sys.wlm_pending_pool table to see the resource pools in the system. Alternatively, use the ybsql \dwrp command to return a list of resource pools.

Examples ​

Create a resource pool with a named profile but otherwise default settings:

premdb=# create wlm resource pool test_pool1 (profile testing);
CREATE WLM RESOURCE POOL
premdb=# select * from sys.wlm_pending_pool where profile_name='testing';
-[ RECORD 1 ]--------------+-----------
name                       | test_pool1
profile_name               | testing
memory_requested           | remainder
memory_per_query_bytes     | 8417968128
temp_space_requested       | remainder
temp_space_per_query_bytes | 3999268864
max_concurrency            | 1
min_concurrency            | 1
queue_size                 | 100

Create another resource pool in the same profile but specify maximum/minimum concurrency as 12/3:

premdb=# create wlm resource pool test_pool_12_and_3 (max_concurrency 12, min_concurrency 3, profile testing);
CREATE WLM RESOURCE POOL

Create a resource pool with spilling disabled, a queue size of 2, maximum/minimum concurrency of 2/1, and a requested memory size of 512MB:

premdb=# create wlm resource pool mb512_nospill_2_and_1 (max_concurrency 2, min_concurrency 1, queue_size 2, requested_memory '512MB', max_spill_pct 0, profile testing);
CREATE WLM RESOURCE POOL

Attempt to create a resource pool with spilling enabled (by default), 2 slots, and a requested memory size of 512MB. The system returns an expected error:

premdb=# create wlm resource pool mb512_spill_2_and_1 (max_concurrency 2, min_concurrency 1, queue_size 2, requested_memory '512MB', profile testing);
ERROR:  requested_memory must be >= 1024MB per query

Parent topic:SQL Commands