Workload management

You can configure Amazon Redshift WLM to run with either automatic WLM or manual WLM.

With Amazon Redshift, you can manage and prioritize concurrent queries and user workloads to optimize performance and resource utilization. Workload management (WLM) allows you to define queues, user groups, and other constructs to control the resources allocated to different types of queries or users.

The following sections outline the specific workload management features in Amazon Redshift, and guide you through their configuration and monitoring.

Automatic WLM

To maximize system throughput and use resources effectively, you can enable Amazon Redshift to manage how resources are divided to run concurrent queries with automatic WLM. Automatic WLM manages the resources required to run queries. Amazon Redshift determines how many queries run concurrently and how much memory is allocated to each dispatched query. Use Auto WLM when you want Amazon Redshift to manage how resources are divided to run concurrent queries. For more information, see Implementing automatic WLM.

Working with concurrency scaling and automatic WLM, you can support virtually unlimited concurrent users and concurrent queries, with consistently fast query performance. For more information, see Concurrency scaling.

With Auto WLM, it's possible to define query priorities for workloads in a queue. For more information about query priority, see Query priority.

Manual WLM

You might have multiple sessions or users running queries at the same time. Some queries might consume cluster resources for long periods and affect the performance of others. Manual WLM can help manage this for specialized use cases. Use Manual WLM when you want more control over concurrency.

You can manage system performance by modifying your WLM configuration to create separate queues for long-running queries and short-running queries. At runtime, you can route queries to these queues according to user groups or query groups.

You can set up rules to route queries to particular queues based on the user running the query or labels that you specify. You can also configure the amount of memory allocated to each queue, so that large queries run in queues with more memory than other queues. You can also configure a query monitoring rule (QMR) to limit long-running queries. For more information, see Implementing manual WLM.

Note that in regards to a manual WLM configuration, the maximum slots you can allocate to a queue is 50. However, this doesn't mean that in an automatic WLM configuration, a Amazon Redshift cluster always runs 50 queries concurrently. This can change, based on the memory needs or other types of resource allocation on the cluster.

Switching WLM mode

You can enable automatic or manual WLM using the Amazon Redshift console:

Modifying the WLM configuration

The easiest way to modify the WLM configuration is by using the Amazon Redshift console. You can also use the AWS CLI or the Amazon Redshift API.

When you switch your cluster between automatic and manual WLM, your cluster is put into pending reboot state. The change doesn't take effect until the next cluster reboot.

For detailed information about modifying WLM configurations, see Configuring Workload Management in the Amazon Redshift Management Guide.

Migrating from manual WLM to automatic WLM

To maximize system throughput and use resources most effectively, we recommend that you set up automatic WLM for your queues. Consider taking the following approach to set up a smooth transition from manual WLM to automatic WLM.

To migrate from manual WLM to automatic WLM and use query priorities, we recommend that you create a new parameter group, and then attach that parameter group to your cluster. For more information, see Amazon Redshift Parameter Groups in the Amazon Redshift Management Guide.

Let's take an example where there are three manual WLM queues. One each for an ETL workload, an analytics workload, and a data science workload. The ETL workload runs every 6 hours, the analytics workload runs throughout the day, and the data science workload can spike at any time. With manual WLM, you specify the memory and concurrency that each workload queue gets based on your understanding of the importance of each workload to the business. Specifying the memory and concurrency is not only hard to figure out, it also results in cluster resources being statically partitioned and thereby wasted when only a subset of the workloads is running.

You can use automatic WLM with query priorities to indicate the relative priorities of the workloads, avoiding the preceding issues. For this example, follow these steps:

With query priorities, when only the analytics workload is running on the cluster, it gets the entire system to itself. This yields high throughput with better system utilization. However, when the ETL workload starts, it gets the right of the way since it has a higher priority. Queries running as part of the ETL workload get priority during admission, in addition to preferential resource allocation after they are admitted. As a consequence, the ETL workload performs predictably regardless of what else might be running on the system. The predictable performance for a high priority workload comes at the cost of other, lower priority workloads that run longer either because their queries are waiting behind more important queries to complete. Or, because they are getting a smaller fraction of resources when they are running concurrently with higher priority queries. The scheduling algorithms used by Amazon Redshift facilitate that the lower priority queries do not suffer from starvation, but rather continue to make progress albeit at a slower pace.