Product Documentation

Appearance

Creating Resource Pools ​

The main purpose of a resource pool is to allocate memory to queries and other database operations. When a query is queued for execution, it lands in a resource pool based on criteria defined in WLM rules, and that resource pool executes the query if it can.

Note: If a resource pool cannot execute the query within a given time limit or with the memory available to the pool, the system (or an administrator) may move the query into another pool for execution.

A resource pool belongs to a specific profile. Every profile must have the following resource pools:

  • A system pool, which is created by default; you cannot remove or modify this pool. This pool is allocated 11.8GB of memory and 171.8GB of temporary space. Maximum and minimum concurrency is set to 3/2.
  • A user-defined pool marked as the default pool

You can add any number of user-defined pools to a profile, as long as you mark one of them as the default pool.

A single query occupies an execution slot. You set the minimum and maximum concurrency within a resource pool to some number of slots. You can set up "flex pools" that can expand or contract based on the values that you set. For example, a pool can have values of 12/3, where 12 is the maximum concurrency and 3 is the minimum. For details, see Examples of Flex Pools.

Resource pools also define several other thresholds:

  • Memory use and temporary space; see Allocating Query Memory.
  • Queue size: how many queries can be waiting for execution in a given pool before action is taken
  • Maximum wait limit: how long a query can wait in a queue before action is taken
  • Maximum row limit: how many rows a query can process before action is taken
  • Maximum execution limit: how many seconds a query may run before being cancelled

To create a resource pool:

  • Go to Manage > Workloads > Create Resource Pool in the SMC. Create Resource Pool is an action that you can select below each user-defined profile.
  • Alternatively, use the SQL command CREATE WLM RESOURCE POOL.

To see a list of resource pools that are currently in the system, go to Manage > Workloads in the SMC, query the sys.wlm_pending_pool view, or use the ybsql \dwrp command, as shown here:

premdb=# \dwrp
                                                                 List of WLM resource pools
   Name     | Max Concurrency | Min Concurrency | Queue size | Wait limit | Row limit | Exec time limit |   Profile   | Requested memory |  Spill   | System? 
-------------+-----------------+-----------------+------------+------------+-----------+-----------------+-------------+------------------+----------+---------
 admin       |               2 |                 |       1000 |            |           |                 | default     | 4096             | 5.0      | yes
 large       |               4 |                 |       1000 |            |           |                 | default     |                  | 55.0     | yes
 long        |               1 |                 |       1000 |            |           |                 | flex        | 22%              | 22%      | yes
 maintenance |               1 |                 |        100 |            |           |                 | maintenance | 100%             |          | yes
 mix         |              12 |               3 |       1000 |            |           |                 | flex        |                  |          | yes
 small       |               8 |                 |       1000 |            |           |                 | default     | 8192             | 20.0     | yes
 system      |               3 |               2 |        100 |            |           |                 |             | 11264            | 163840MB | yes
 yb100: long |               4 |               2 |       1000 |            |           |                 | yb100       | 22%              | 22%      | no
 yb100: mix  |              20 |               3 |       1000 |            |           |                 | yb100       |                  |          | no
(9 rows)

In This Section

Parent topic:How WLM Works