How do I create and prioritize query queues in my Amazon Redshift cluster?

Short description

In Amazon Redshift, you can create extract transform load (ETL) queries, and then separate them into different queues according to priority. Queries can be prioritized according to user group, query group, and query assignment rules.

To prioritize your queries, use Amazon Redshift workload management (WLM). Amazon Redshift supports the following WLM configurations:

• Automatic WLM: When you enable automatic WLM, your query concurrency and memory allocation are managed by Amazon Redshift. Amazon Redshift determines the amount of resources that queries need, and adjusts the amount accordingly. Unlike manual WLM, automatic WLM allows you to set a query priority value to indicate the relative priority of workloads. For both automatic and manual WLM, you can create separate query queues.
• Manual WLM: Manual WLM is used to manage multiple WLM queues in Amazon Redshift. With manual WLM, you must specify values for WLM query concurrency slots and memory allocation properties. For both automatic and manual WLM, you can create separate query queues. For manual WLM, the default concurrency value is five queries and memory allocation is divided equally. Use manual WLM if you want to manage your own workload or manually assign resources to queries.

To prioritize your queries, choose the WLM configuration that best fits your use case.

Resolution

Automatic WLM

When you enable automatic WLM, Amazon Redshift automatically determines how resources are allocated to each query. Concurrency is adjusted according to your workload.

Note: It's a best practice to test automatic WLM on existing queries or workloads before moving the configuration to production.

To prioritize your workload in Amazon Redshift using automatic WLM, perform the following steps:

1. Sign in to the AWS Management Console.
2. Open the Amazon Redshift console.
3. From the navigation menu, choose CONFIG.
4. Choose Workload management.
5. Choose the parameter group that you want to modify.
6. Choose the Workload management tab to view the current WLM configuration.
7. (Optional) If your WLM parameter group is set to Automatic WLM mode, then no updates are required.
8. (Optional) If your WLM parameter group is set to Manual WLM mode, modify the WLM configuration for your parameter group. Choose Edit workload queues to edit the WLM configuration. Then, choose Switch WLM mode to switch to Automatic WLM.
9. Choose Add queue to add additional workload queues. You can create separate queues (with a maximum of eight queues) for each workload, associating queues with user groups or query groups.
10. Choose Save.
11. Assign queries to queues.
12. Configure the priority status for your queue. By default, each queue is assigned a "Normal" priority status.
    Note: Superusers can apply a "Critical" priority status (the highest priority level) to a query. If a workload is marked as "Critical" priority, only one of its queries can be run at each time.
13. (Optional) Create a WLM query monitoring rule to define performance boundaries (such as time limits or concurrency levels) for queries and queues. For example, you can set your query monitoring rule to change a query's priority status if a running query exceeds 40 seconds.

Manual WLM

When you enable manual WLM, each queue is allocated a portion of the cluster's available memory. A queue's memory is divided equally amongst the queue's query slots. If you're managing multiple WLM queues, you can configure workload management (WLM) queues to improve query processing.

To prioritize your workload in Amazon Redshift using manual WLM, perform the following steps:

1. Sign in to the AWS Management Console.
2. Open the Amazon Redshift console.
3. From the navigation menu, choose CONFIG.
4. Choose Workload management.
5. Choose the parameter group that you want to modify.
6. Choose the Workload management tab to view the current WLM configuration.
7. (Optional) If your WLM parameter group is set to Manual WLM mode, then no updates are required.
8. (Optional) If your WLM parameter group is set to Automatic WLM mode, modify the WLM configuration for your parameter group. Choose Edit workload queues to edit the WLM configuration. Then, choose Switch WLM mode to switch to Manual WLM.
9. Choose Add queue to add additional workload queues. You can create separate queues (with a maximum of eight queues) for each workload, associating queues with user groups or query groups.
10. Choose Save.
11. Create and define a query assignment rule.
    Note: Queries are assigned according to user or query group. Queries that aren't assigned run in the default queue.
12. Use WLM dynamic memory allocation to configure the amount of memory allocated to each queue. WLM dynamic memory configuration helps you prioritize your workload according to memory usage.
13. (Optional) Create and define a query monitoring rule to manage queries that reach metric boundaries. For example, you can use a query monitoring rule to hop or cancel a long-running query.

Related information

WLM queue assignment rules

WLM system tables and views

STV_WLM_QUERY_QUEUE_STATE

STV_WLM_QUERY_STATE

STV_WLM_SERVICE_CLASS_CONFIG