Product DocumentationAppearance
Creating WLM 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 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 system pool, which is created by default and cannot be removed or modified, executes work on the default cluster, the running cluster, or the system cluster (if defined). In other words, this pool only exists in the profile that is currently activated on the current cluster. When you activate a profile, you will see the system pool listed and the total resources of the cluster shared among the other pools plus the system pool. In order to avoid wasting resources, the system pool is only included on one cluster.
Because different instances and clusters have different hardware instance types, your mileage will vary with respect to resource allocation when a profile is activated for a cluster. Dividing a non-fixed amount of memory for a pool among a given number of slots will have different results on clusters backed by different instance types.
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:
- In Yellowbrick Manager, go to Workload Management, select a profile, and click +Resource Pool. Then fill out the details in the Create Resource Pool screen. For example:
- Alternatively, use the SQL command CREATE WLM RESOURCE POOL.
To see a list of resource pools that are currently defined, go to Workload Management and select a profile. You can also 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 | 1 | | 2000 | | | | default | 4096 | 5.0 | yes
instance_large_mix | 30 | 15 | 2000 | | | | instance_large | | | yes
instance_small_mix | 3 | 2 | 2000 | | | | instance_small | | | yes
large | 4 | | 2000 | | | | default | | 55.0 | yes
long | 1 | | 2000 | | | | flex | 20% | 20% | yes
maintenance | 10 | | 100 | | | | maintenance | 100% | | yes
mix | 8 | 4 | 2000 | | | | flex | | | yes
small | 8 | | 2000 | | | | default | 8192 | 20.0 | yes
system | 3 | 2 | 100 | | | | | 13312 | 98304MB | yes
(9 rows)See also Creating WLM Profiles.
In This Section
Parent topic:How WLM Works