Introduction to WLM ​

Workload management (WLM) refers to the process of allocating system resources in an optimal way so that database operations can be done efficiently and with respect to some order of priority. The goal is to meet service-level agreements (SLAs) between users and database administrators. These SLAs typically define performance requirements for both queries and other database operations, such as bulk loads and inserts.

A workload is a set of queries or other database requests that is to some extent a known quantity. For example, if a group of users run ad hoc queries against the same set of tables every day, that set of queries, though somewhat arbitrary, may be thought of as a known and expected workload. A resource-intensive report that is run by one user at the same time every morning may also be anticipated as a separate (and probably high-priority) workload. A third example is system operations, such as bulk loads and backups, which may occur during a "maintenance window," when end users have minimal access to the system.

Workloads may be defined across many different dimensions: in terms of when they are run, the application or user that runs them, the type of work, their expected duration, whether they are resource-intensive, and so on. These variables are typical for MPP database systems, which are rarely used for one type of query or by one type of user. Some familiar use cases that workload management can address are as follows:

• Runaway queries: ability to identify and stop long-running queries that, for example, select all of the rows from a very large table (whether issued naively, by mistake, or at a "bad time")
• Short-query bias: the ability to give priority to queries that run very fast (subsecond speed) and prevent them from being queued behind longer-running queries for which an instant response is neither expected nor required
• Ad hoc queries: the ability to place "browsing" or "discovery" queries at a lower priority in the queue than more critical queries that are needed to run the business
• Time-sensitive queries: the ability to apply different rules at different times of the day or week. For example, weekly business roll-ups have the highest priority until they are done. All other queries have lower priority.
• Admin queries: allocation of resources to run superuser queries immediately, especially internally generated queries that maintain the database (for example, operations that flush and analyze new table rows).
• Loads and updates: write queries that do batch loads, deletes, and updates must not starve read queries.
• Logging and reporting: log user-defined messages and tag queries as they are executed; learn about system usage in order to adjust future workload management behavior.

Like any other database system, a Yellowbrick appliance has finite resources (memory, CPU, disk), but these resources can be shared in various ways. To optimize resource allocation based on workloads, administrators create WLM objects called rules, resource pools, and profiles. These objects define a flexible set of heuristics to translate typical WLM use cases into an optimal strategy for resource allocation and scheduling. You can set up WLM objects either in the System Management Console or by using SQL commands.

Parent topic:Workload Management