How do I use automatic WLM to manage my workload in Amazon Redshift?

7 minute read

I have different workloads and want to create separate queues using automatic workload management (WLM). How do I use Amazon Redshift automatic WLM to manage and prioritize my workload?

Short description

Amazon Redshift automatic WLM dynamically manages memory and concurrency, helping you prioritize queries for mixed workloads. With automatic WLM, Amazon Redshift manages resource assignments according to the following conditions:

  • After the query is submitted to Amazon Redshift, the resources are assigned according to query priority.
  • If there are no competing workloads, then the lower priority queries have access to all the system resources.
  • For concurrent workloads, higher priority queries are chosen. Higher priority queries are then assigned more resources than the lower priority queries are.
  • The predictable performance for a high priority workload comes at the cost of other lower priority workloads.
    Note: Lower priority queries might progress at a slower pace. However, Amazon Redshift makes sure that lower priority queries don't suffer from starvation.
  • Lower priority workloads can run longer because of priority status or from working with less resources.

To effectively use Amazon Redshift automatic WLM, consider the following:

  • Assign priorities to a queue.
  • Change your query priorities.
  • Monitor your query priorities.
  • Check whether the query is running according to assigned priorities.


Assigning priorities to a queue

To manage your workload using automatic WLM, perform the following steps:

1.    Define and separate your workload into categories (such as ETL, dashboards, and analytics).

2.    Identify individual users and group them according to workload.

3.    Create and assign different queues to a particular user or query group. For more information, see Assigning queries to queues.

4.    Turn on concurrency scaling for queues, so that Amazon Redshift automatically adds additional cluster capacity when needed. For example, you can turn on concurrency scaling on queues if you tend to experience bursts in traffic. For more information, see Configuring concurrency scaling queues.

Here's an example JSON configuration for automatic WLM:

[ {
  "query_group" : [ ],
  "query_group_wild_card" : 0,
  "user_group" : [ "ETL_users" ],
  "user_group_wild_card" : 1,
  "priority" : "highest",
  "queue_type" : "auto",
  "auto_wlm" : true    
}, {
  "query_group" : [ ],
  "query_group_wild_card" : 0,
  "user_group" : [ "Dashboard_users" ],
  "user_group_wild_card" : 0,
  "priority" : "high",
  "queue_type" : "auto",
  "auto_wlm" : true
}, {
  "query_group" : [ "Adhoc_query" ],
  "query_group_wild_card" : 1,
  "user_group" : [ "Analytics_users" ],
  "user_group_wild_card" : 1,
  "priority" : "normal",
  "queue_type" : "auto",
  "auto_wlm" : true
}, {
  "query_group" : [ ],
  "query_group_wild_card" : 0,
  "user_group" : [ ],
  "user_group_wild_card" : 0,
  "priority" : "low",
  "queue_type" : "auto",
  "auto_wlm" : true
}, {
  "short_query_queue" : true
} ]

Note: If you don’t set a query priority, all queues are automatically set to "normal" priority status.

Changing query priorities

In Amazon Redshift, you can change the queue priority by using WLM query monitoring rules (QMRs) or built-in functions.

Method 1: WLM query monitoring rules

Use the WLM query monitoring rules when you want to manage workload according to metrics-based performance boundaries. When you set your WLM query monitoring rules, specify the query priority metric and query priority action. For example:

  "query_group" : [ ],
  "query_group_wild_card" : 0,
  "user_group" : [ ],
  "user_group_wild_card" : 0,
  "rules" : [ {
    "rule_name" : "long_running_queries",
    "predicate" : [ {
      "metric_name" : "query_execution_time",
      "operator" : ">",
      "value" : 3600
    } ],
    "action" : "change_query_priority",
    "value" : "high"
  } ],
  "priority" : "low",
  "queue_type" : "auto",
  "auto_wlm" : true
}, {
  "short_query_queue" : true
} ]

Method 2: Built-in functions

Important: Built-in functions require proper permissions. To use a built-in function, you either must be a superuser, or a superuser must grant you the permission to use one.

In Amazon Redshift, built-in functions are independent of WLM configurations. To grant a standard user the permission to use a built-in function, create a stored procedure that specifies SECURITY DEFINER. Then, grant permission to the standard user.

A superuser can change query priority using the following built-in functions:

Note: "Critical" priority status can only be assigned using built-in functions. Only one critical query is allowed in the system at any time.

Monitoring query priorities

To check query priority for a queue or active query, run the following query:

select query, service_class, query_priority, state from stv_wlm_query_state where service_class>=100;

To check query priority for a completed query, use the following query:

select query, service_class, service_class_start_time as starttime, query_priority

from stl_wlm_query where query=<query_id>;

To check whether your query priority changed because of a QMR rule, use the following query:

select * from stl_wlm_rule_action where query= <Query_ID> and action= ‘change_query_priority’;

In your output, check the action_value column to verify the changed priority for your query.

To check your QMR configurations, run the following query:

select * from stv_wlm_QMR_config where action= ‘change_query_priority’;

To check the current value of the query_group parameter, run the following query:

select current_setting(‘query_group’);

To check the automatic WLM queue configuration, run the following query:

select s.service_class,
rtrim( as name, s.num_query_tasks as slots, s.query_working_mem as mem, s.max_execution_time as max_time, s.user_group_wild_card as user_wildcard, s.query_group_wild_card as query_wildcard,
rtrim(c.condition) as condition, s.query_priority from stv_wlm_service_class_config s left join stv_wlm_classification_config c on s.service_class = c.action_service_class where s.service_class > 4 order by service_class;

Note: If auto_wlm is turned on and set to "true", then the service class ID indicates 100-107. The num_query_tasks and query_working_mem columns also indicate a value of -1.

Checking whether the query is running according to assigned priorities

Queries are routed to queues based on assigned priorities, query monitoring rules, and matching wildcards for user groups and query groups. Amazon Redshift then automatically assigns the query to the first matching queue.

If your query doesn't run in the desired queue, check whether the following conditions are true:

  • User or query_group is set to "superuser": If your user or query group is set to "superuser", then the query runs in the superuser queue (service_class = 5).
  • User is listed as a member of a user group, but a different query group is assigned for that particular query: If a query is assigned to a different query group from its listed group membership, then it runs in the first matching queue. By default, queries in Amazon Redshift run according to the queue's set priority.
  • Improper permissions for using built-in functions: If you are using built-in functions (like CHANGE_QUERY_PRIORITY, CHANGE_USER_PRIORITY, and CHANGE_QUERY_PRIORITY), you must have superuser privileges. Or, you must be granted the proper permissions to use a built-in function by a superuser.
  • User is a member of multiple groups: If you're listed as a member of multiple groups, then the query is assigned to the first matching queue. The queue matches are performed according to the WLM query assignment rules.

To confirm whether a query priority was successfully changed, run the following query:

select query, service_class, query_priority, state from stv_wlm_query_state where query= <Query_ID>;

To confirm whether a user is listed as a member of multiple groups, run the following query:

SELECT usename, groname
FROM pg_user, pg_group
WHERE pg_user.usesysid = ANY(pg_group.grolist)
AND pg_group.groname in (SELECT DISTINCT pg_group.groname from pg_group);

To identify whether a query group was set for a query, run the following query:

select q.userid, q.query, rtrim(q.label) as label, w.service_class, w.query_priority from stl_query q join stl_wlm_query w on q.query = w.query where q.query = <Query_ID>;

Check the label column in the output to confirm a query's group membership. If a query has no matching query or user group, then it runs in the default queue.

AWS OFFICIALUpdated a year ago