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

4 minute read
0

I want to create and prioritize certain query queues in Amazon Redshift.

Short description

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

To prioritize your queries, use Amazon Redshift workload management (WLM). Amazon Redshift supports Automatic WLM and Manual WLM configurations.

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. To prioritize your queries, choose the WLM configuration that best fits your use case.

Resolution

Automatic WLM

Note: Before you move the configuration to production, it's a best practice to test automatic WLM on existing queries or workloads.

To use automatic WLM to prioritize your workload in Amazon Redshift, complete the following steps:

  1. Sign in to the AWS Management Console.
  2. Open the Amazon Redshift console.
  3. In the navigation pane, choose Configurations.
  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) Update your WLM parameter group. If your WLM parameter group is set to Automatic WLM mode, then no updates are required. If your WLM parameter group is set to Manual WLM mode, then 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.
  8. Choose Add queue to add additional workload queues. You can create up to eight queues for each workload, and associate queues with user groups or query groups.
  9. Configure the priority status for your queue. By default, each queue is assigned a "Normal" priority status.
    Note: Superusers can apply the highest priority level status, "Critical". If a workload is marked as "Critical" priority, then you can run only one of its queries at each time.
  10. (Optional) Create a WLM query monitoring rule to define performance boundaries for queries and queues, such as time limits or concurrency levels. For example, you can set your query monitoring rule to change a query's priority status when a running query exceeds 40 seconds.
  11. Choose Save.
  12. Create and define a queue assignment rule and assign queries to queues.
    Note: Queries are assigned according to user or query group. Queries that aren't assigned run in the default queue.

Manual WLM

If you're managing multiple WLM queues, then you can configure workload management (WLM) queues to improve query processing.

To use manual WLM to prioritize your workload in Amazon Redshift, complete the following steps:

  1. Sign in to the AWS Management Console.
  2. Open the Amazon Redshift console.
  3. In the navigation menu, choose Configurations.
  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) Update your WLM parameter group. If your WLM parameter group is set to Manual WLM mode, then no updates are required. If your WLM parameter group is set to Automatic WLM mode, then 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.
  8. Choose Add queue to add workload queues. You can create up to eight queues for each workload and associate queues with user groups or query groups.
  9. 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.
  10. (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.
  11. Choose Save.
  12. Create and define a queue assignment rule and assign queries to queues.
    Note: Queries are assigned according to user or query group. Queries that aren't assigned run in the default queue.

Related information

WLM system tables and views

STV_WLM_QUERY_QUEUE_STATE

STV_WLM_QUERY_STATE

STV_WLM_SERVICE_CLASS_CONFIG

AWS OFFICIAL
AWS OFFICIALUpdated 3 months ago