Database Administration
Manage Users, Groups, and Role...

Workload Management Walkthrough

Workload Management (WLM) in the allows a Database Administrator to set restrictions and priorities for groups of users. The main use case for this is to effectively control multiple workflows across the system, prioritizing or limiting resources to groups when needed. Query restrictions are set and assigned using a service class that can be used for multiple user groups. For a full list of restrictions and their definitions, see the documentation.

This example shows how to set up three service classes with differing priorities, explaining the tradeoffs and design decisions one should make when setting up these restrictions.

Create Service Classes

Before creating service classes, the System Administrator or Database Administrator first determines the logical grouping of users on their system and their relative priority. In addition, the response time and concurrency requirements of different user groups are understood. Using these inputs, the administrator can design the service classes that allow their users to receive the proper resource allocations that deliver query requirements.

In this example, define the priority at the service level by creating three service classes to service High, Medium, and Low priority users. Different settings are configured to provide an illustration of the types of configuration that you can use to control features such as concurrency, result sizing, or scheduling priority.

Prerequisites

  1. An system has been set up with an operational SQL Node.
  2. A user with the system administrator role has access to the system

Step 1: Login as a system administrator user

In order to create service classes, the user signed in must be assigned the System Administrator role. For a full list of user permissions and their definitions, see the Data Control Language (DCL).

First, connect to the system database with the command:

Shell


Step 2: Set up the high priority user class

User groups that need fast performance and need access to the most system resources should fall under this service class. Their queries should see minimal impact from outside workloads.

When defining a service class, any service class field that is omitted will be set to the maximum/least restrictive value.

Example command:

SQL


Example system catalog view:

SQL


scheduling_priority - A numeric value that defines the query’s priority in the database. A default query being run with no service class has a priority set to 1.0. This priority number is used as an ordered list for scheduling with higher values receiving priority. It is not a weighted value, so only the ordering, not the relative sizes of the priority levels matter. When setting the priority for a high-priority service class, set this value to be greater than 1.0.

max_concurrent_queries - The number of concurrent queries that can be run at a given time for the entire service class. If the max concurrency is hit, queries will enter a queue. A value of -1 indicates unlimited queries, however it is a best practice to set this value so no individual workloads can monopolize the system.

max_elapsed_time- The maximum amount of time a query can run. This restriction helps reduce the length of queries holding concurrency slots and allows concurrent workloads using the same service class to all make progress. It is recommended to set max_elapsed_time if max_concurrent_queries is set to prevent extremely long running queries from blocking other concurrent queries. If a service class needs to run sets of queries with very different expected elapsed times, it might be an indication that a separate service class is warranted.

Step 3: Set up the medium priority user class

User groups that do not necessarily need the fastest performance will fall under the medium priority service class. The performance of queries in this service class will be impacted by higher priority queries, but these queries will receive priority over lower priority ad hoc workloads.

Example command:

SQL


Example system catalog view:

SQL


scheduling_priority- For a "medium" priority, this value should be set to a number in-between the highest priority service class and the default service class.

max_temp_disk_usage- From 0-100, this field represents the maximum percentage of unused disk space that a query can see before failing. When setting up the cluster, it is important to keep some disk space unused on a foundation node so queries can spill to disk. Note that this percentage is tracked on a system-wide basis per node, not within a service class. As one service class uses its allocation of temporary disk space to fulfill a query, it also reduces the total temporary disk available to all other service classes. Even if those service classes have a max_temp_disk_usage set, if the total available temp disk space is below the amount needed to run a query, it can fail. For example, if a higher priority service class is using over 50% of the unused disk space during a medium priority query that needs to spill to disk, the medium priority query will fail during its first attempt to spill without using any disk space. Spill to disk scheduling uses the same priority used for normal execution.

Step 4: Set up the low priority service class

User groups that are less performance sensitive fall under the low priority service class. The design goal is for these queries to have minimal effect on other workloads. Examples of users in this service class are analysts who want to run ad hoc queries or processes that run time insensitive background reports.

Example command:

SQL


Example system catalog view:

SQL


scheduling_priority- For a "low" priority, this value should be set to a low number like 1.0. If a service class wants to be lower priority than default queries, it can schedule a value lower than 1.0.

max_temp_disk_usage- Setting this limitation to 0 means that no queries in the low priority service class will be allowed to spill to disk. In this example, the designer’s goal is to prevent the low priority service class from impacting more important workloads that might need the entire temp disk space. In other cases, it could be suitable to allow low priority service class queries to use temp disk.

cache_max_bytes, cache_max_time, max_elapsed_time_for_caching - Result set caching is a feature that will attempt to cache result sets for commonly used queries. This result set cache is shared across service classes. For a lower priority user, it makes sense to limit the time allowed in the cache or cap the maximum number of bytes used in the cache so a higher priority service class can take advantage of it. In this example, the result set cache is explicitly disabled for low priority users by setting these values to zero. For more information, see the Result Set Caching.

Step 5: Assign service classes to user groups

After setting up the service classes, you need to assign them to user groups. When a service class is assigned to user groups, users in each user group can run queries under the restrictions set for the given service class. This process can be repeated for all service classes and users.

Example commands:

SQL


Dynamic Workload Management Priority Adjustment

When a query executes, you can set the priority of a query at the service class level, database session level, or the query level. The system chooses the maximum value from these three priority levels.

While the engine executes a query, the engine can dynamically adjust the priority using a specified adjustment interval. You can change the percentage amount to dynamically adjust query priority using the priority_adjustment_factor (or ADJUSTFACTOR), and the time period that indicates the frequency for the adjustment of the query priority during query execution using the priority_adjustment_time (or ADJUSTTIME). You can override this limit at the query level or the session level.

Define the priority at the service level by creating service classes. You can override the priority at the service level by using the priority at the session or query level. For details, see Users, Groups, and Service Classes.

Define the priority at the session level using the command line. This code instructs the engine to dynamically adjust the priority every 15 seconds and decrease the query priority by 25%.

SQL


To reset the priority adjustment factor, enter this SQL statement.

SQL


Or, define the priority at the query level in the SQL statement, such as this statement.

SQL


Alternatively, you can also specify a service class to use at the query level. The following example creates the service class lower_priority and assigns it to the query.

SQL


Using a service class at the query level requires a user to be assigned to a user group that is included in that specific service class.

Other Service Class Considerations

Service classes are a powerful tool to properly allocate resources in the database to the users that need them most. When deciding on the restrictions for your individual use case, consider the total number of service classes that will be created. Some settings like max_concurrent_queries and result set caching values like cache_max_bytes and cache_max_time can have a cumulative effect on resource availability as the number of service classes increases. For details about result set caching, see Result Set Caching.

For example, concurrency limitations are set on an individual service class level. While the intention might be to limit low priority service classes to only a couple of concurrent queries, the total effect of many service classes with a couple of concurrent queries each can start to affect high priority workloads due to the number of concurrent queries running across all lower priority service classes.