Optimal WLM Settings for Redshift

in Redshift
 • Updated on 
Dont-get-stuck-in-a-queue

# Fast Queries Running Slower in Redshift?

When you have query performance issues with queries that should be running quickly in Amazon Redshift, one of the reasons might be that the query queue is filled with other queries. When the query queue is filled, your query is enqueued into a waiting list, and has to wait until other queries finish.

This is something that happens quite often. To handle this kind of case, Amazon Redshift provides WLM (workload management) configuration, which enables you to manage the query queues.

In this article, we’ll describe how to decide the optimal WLM configuration using some examples.

# What You Can Configure Using WLM Settings

If you are using the default parameter group, only a "default" queue is defined in your Redshift cluster. The "default" queue comes with a concurrency level of 5, which means you can run up to 5 queries concurrently. You can modify this "default" queue, and add custom queues as you see fit.

Each queue has following configuration items (Fig1):

  • Concurrency level
    • This is the number of queries that can run concurrently within the particular queue.
  • User Groups
    • Each queue can be assigned to specific User Groups. When a query is executed by a member of a user group, that query will then run in the queue assigned to his/her user group.
  • Query Groups
    • By setting query groups to a queue, users can assign queries to a particular queue. The command `set query_group to ''` needs to be executed before running queries which you want to assign.
  • WLM memory percent to use
    • By default, each queue is allocated memory equally. You can change the percentage of memory assigned to each queue by setting `WLM memory percent to use`. If you set this parameter, you have to adjust the total rate for all queries so that they add up to 100%.

Redshift WLM Settings

For a complete list of WLM items you can configure, see here.

# Tips on Optimally Configuring your Redshift WLM Settings

1. Separate out time-consuming queries

One of the cases where you can gain the most from setting WLM is when you have time-consuming queries filling your queue. Without WLM, such time-consuming queries will clog your queue. Even the queries that run fast will take longer if it has to wait for these slower queries to finish running.

To avoid this, creating another queue for faster queries would be a good solution. That way faster queries won’t be affected by the more time-consuming ones.

Additionally, since the faster queries are expected to use a smaller amount of memory, you can set a low percentage to `WLM memory percent to use` for that queue. The image below is a good example of such a configuration (Queue 1 is set for faster queries).

Example of Redshift Queue Setup

2. Set the total of concurrency levels to 15 or lower

Since the resource of each node is shared among concurrent processes, increasing the concurrency level total too high will limit the overall throughput. In the AWS development guide, they recommend setting the concurrency level total (the total of the concurrency levels for all queues) to 15 or lower.

3. Avoid adding too many queues

One note for adding queues is that the memory for each queue is allocated equally by default.

With many queues, the amount of allocated memory for each queue becomes smaller because of this (of course, you can manually configure this by specifying the “WLM memory percent to use”). On top of that, there is the recommended limit of a total of 15 concurrency levels, so it becomes difficult to maximize Redshift performance if you have too many queues.

4. Increase slot count temporarily for heavy queries

If you need more memory for a specific query, you can increase the available memory for that query by increasing the slot count. For example, in a queue with a concurrency level parameter set to 5, you can set the slot count up to 5. When the slot count is set to 5, a single query will use the entire available memory of that queue.

In the following example, we set the slot count to 5 before running VACUUM, and then we reset the slot count back to 1 after the query finishes.

set wlm_query_slot_count to 5;
vacuum;
set wlm_query_slot_count to 1;

If you don’t have too many time-consuming queries, then this might be all you need to do to speed up your slower queries.

5. Use superuser for trouble shooting

Redshift has a reserved "superuser" queue for running commands related to the system, trouble shooting or for manual operations in emergency situations. So, you won't need to set up another queue for such purposes. To use the "superuser" queue, you can set the query group to "superuser" before running your queries, like below.

set query_group to 'superuser';
analyze;
vacuum;
reset query_group;

# Summary

WLM is a feature for managing queues when running queries on Redshift. Although the "default" queue is enough for trial purposes or for initial-use, WLM configuration according to your usage will be the key to maximizing your Redshift performance in production use. I hope the above tips will help you when you configure your WLM settings.

# References

Tutorial: Configuring Workload Management (WLM) Queues to Improve Query Processing http://docs.aws.amazon.com/redshift/latest/dg/tutorial-configuring-workload-management.html

Worry-free replication from source to Redshift & Snowflake
Unlimited sync during trial
No credit-card required
World class support
Try FlyData for free
Amazon Partner Logo Certified AWS
Redshift partner
Get started. Try FlyData.
Quick setup. No credit card required. Unlimited sync during trial.
Fast and secure Enterprise-grade security and near real-time sync.
World-class support Proactive monitoring from technical experts, 24/7.
Straightforward pricing Pay for the rows you use, and nothing you don’t.