Redshift’s Workload Management (WLM) is a powerful feature that allows you to prioritize and allocate resources for queries in your Redshift cluster. By properly configuring WLM, you can ensure that critical queries get the necessary resources and are processed efficiently. Let’s explore how to make the most out of Redshift’s WLM.

Understanding Workload Management

WLM helps in managing query execution by assigning queries to query queues with different priorities. Each query queue has a specified amount of memory and concurrency level. Queries with higher priority will be allocated more resources, enabling them to complete faster.

By default, Redshift provides three predefined queues: “superuser”, “default”, and “normal”. The “superuser” queue is reserved for administrative queries, while the “default” and “normal” queues are used for user queries. You can customize these queues or create new ones based on your specific workload requirements.

Configuring Workload Management

Configuring WLM involves defining the number of query queues, their memory allocation, and concurrency settings. Here’s an example configuration for three custom query queues:

CREATE QUEUE critical_queue WITH (MEMORY_PERCENT = 50, MAX_CONCURRENCY = 3);
CREATE QUEUE standard_queue WITH (MEMORY_PERCENT = 30, MAX_CONCURRENCY = 5);
CREATE QUEUE casual_queue WITH (MEMORY_PERCENT = 20, MAX_CONCURRENCY = 10);

CREATE RESOURCE QUEUE my_resource_queue WITH (MAX_MEMORY_PERCENT = 100, MAX_CONCURRENCY = 15);

CREATE WLM CONFIGURATION my_wlm_config
  WITH
  ( 
    QUERY_GROUP [
      'admin_group' => 'superuser',
      'critical_group' => 'critical_queue',
      'standard_group' => 'standard_queue',
      'casual_group' => 'casual_queue'
    ],
    CONCURRENCY_SCALING = PARALLEL,
    CONCURRENCY_LEVEL = 5,
    USER_GROUP_MAPPING = [
      'superusers' => 'admin_group',
      'critical_users' => 'critical_group',
      'standard_users' => 'standard_group',
      'casual_users' => 'casual_group'
    ]
  );

In this example, we create three query queues (critical_queue, standard_queue, and casual_queue) with different memory and concurrency settings. We also create a resource queue (my_resource_queue) to specify the overall maximum memory and concurrency available for all queries.

Next, we create a WLM configuration (my_wlm_config) that maps user groups to query groups. This mapping assigns each user group to a specific query queue based on their priority. The CONCURRENCY_SCALING parameter allows queries in a queue to share resources beyond the specified concurrency limit.

Assigning Users to Workload Management Groups

Once the WLM configuration is set up, you can assign users to specific workload management groups. For example:

ALTER USER superuser_group GROUP admin_group;
ALTER USER critical_user GROUP critical_group;
ALTER USER standard_user GROUP standard_group;
ALTER USER casual_user GROUP casual_group;

In this example, users superuser_group, critical_user, standard_user, and casual_user are assigned to their respective workload management groups.

Monitoring Workload Management

You can monitor the performance of your WLM configuration and query queues using the SVL_QUERY_METRICS and SVL_QUERY_QUEUE system tables. These tables provide insight into query execution times, memory usage, and more.

Conclusion

Redshift’s Workload Management is a valuable tool for optimizing query performance and resource allocation. By configuring and utilizing WLM effectively, you can prioritize important queries, allocate resources accordingly, and ensure that your Redshift cluster runs efficiently.