How WLM Works

This section explains how WLM works and walks through the setup process for profiles, resource pools, and rules.

Profiles and Configurations

At any given time, a single WLM configuration is active on the system (and applies to all databases that you create on a single appliance). This active state is based on a specific WLM profile. A profile describes one or more workloads that the system needs to manage and a set of operating conditions, as defined by associated resource pools and rules.

Rules and resource pools are the main mechanisms for defining the conditions under which concurrent queries are executed and scheduled. WLM rules trigger work to go to different pools and consume resources appropriate to those pools.

When you want to modify your WLM strategy, you can switch to a different profile and change the active state. For example, you may have a WLM configuration that is intended to satisfy requirements for "business hours" and a configuration that meets the requirements for a "maintenance window." You can also activate changes by adding or changing rules and pools in the current profile.

For more details, see Creating Profiles.

Resource Pools

Every profile is associated with a number of resource pools, which define attributes for work that gets assigned to those pools. These attributes define maximum memory allocation, minimum and maximum concurrency levels, queue length, and so on. To reduce the need to maintain and switch between too many different profiles, "flex pools" have adjustable concurrency limits, rather than fixed boundaries on the number of queries that can run at any given time. These pools support flexible and dynamic sharing of system resources.

All profiles inherit the following fixed system pools:
  • system:large
  • system:medium
  • system:small
You cannot edit or remove system pools. The presence of these pools guarantees that background system operations and SMC operations are not starved of resources.

Every profile requires another pool that is marked as the default pool; a system pool cannot be the default pool. You can add any number of other pools to the profile based on your requirements.

For more details, see Creating Resource Pools.

Rules

Work is assigned to resource pools based on rules. These rules qualify work for execution in specific pools, based on various criteria and actions triggered by those criteria. For every query that comes in, all of the rules associated with the active configuration are evaluated. For example, the user that runs a particular query, the database in which it runs, or the SQL content of the query itself might determine which pool the query runs in and impose limits on its memory use or priority.

For more details, see Creating Rules.

Summary of WLM Setup Steps

To set up a simple WLM configuration and test it, follow these steps:
  1. Create a profile.
  2. Create one or more resource pools.
  3. Set one pool as the default pool.
  4. Create some rules.
  5. Activate the profile.
  6. Run some queries to test the application of rules.
  7. Monitor rule processing and resource pool selection.

For a specific example of these steps, see A Simple WLM Example.