Amazon Redshift
Database Developer Guide

Section 2: Modifying the WLM Query Queue Configuration

Now that you understand how queues work by default, you can learn how to configure query queues using manual WLM. In this section, you create and configure a new parameter group for your cluster. You create two additional user queues and configure them to accept queries based on the queries' user group or query group labels. Any queries that don't get routed to one of these two queues are routed to the default queue at runtime.

Note

A new console is available for Amazon Redshift. Choose either the New Console or the Original Console instructions based on the console that you are using. The New Console instructions are open by default.

New Console

To create a manual WLM configuration in a parameter group

  1. Sign in to the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshift/.

  2. On the navigation menu, choose CONFIG, then choose Workload management to display the Workload management page.

  3. Choose Create to display the Create parameter group window.

  4. Enter WLMTutorial for both Parameter group name and Description, and then choose Create to create the parameter group.

    Note

    The Parameter group name is converted to all lower case format when created.

  5. On the Workload management page, choose the parameter group wlmtutorial to display the details page with tabs for Parameters and Workload management.

  6. Confirm that you're on the Workload management tab, then choose Switch WLM mode to display the Concurrency settings window.

  7. Choose Manual WLM, then choose Save to switch to manual WLM.

  8. Choose Edit workload queues, then choose Save to switch to manual WLM.

  9. Choose Add queue twice to add two queues. Now there are three queues: Queue 1, Queue 2, and Default queue.

  10. Enter information for each queue as follows:

    • For Queue 1, enter 30 for Memory (%), 2 for Concurrency on main, and test for Query groups. Leave the other settings with their default values.

    • For Queue 2, enter 40 for Memory (%), 3 for Concurrency on main, and admin for User groups. Leave the other settings with their default values.

    • Don't make any changes to the Default queue. WLM assigns unallocated memory to the default queue.

  11. Choose Save to save your settings.

Next, associate the parameter group that has the manual WLM configuration with a cluster.

To associate a parameter group with a manual WLM configuration with a cluster

  1. Sign in to the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshift/.

  2. On the navigation menu, choose CLUSTERS, then choose Clusters to display a list of your clusters.

  3. Choose your cluster, such as examplecluster, and for Actions choose Modify.

  4. In the Database configuration section, choose the wlmtutorial parameter group that you created for Parameter groups.

  5. Choose Modify cluster to associate the parameter group.

    The cluster is modified with the changed parameter group. However, you need to reboot the cluster for the changes to also be applied to the database.

  6. Choose your cluster, and then choose Reboot cluster for Actions.

After the cluster is rebooted, its status returns to Available.

Original Console

To create a manual WLM configuration and associate it to a cluster.

Step 1: Create a Parameter Group

In this step, you create a new parameter group to use to configure WLM for this tutorial.

To Create a Parameter Group

  1. Sign in to the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshift/.

  2. In the navigation pane, choose Workload management.

  3. Choose Create parameter group.

  4. In the Create Cluster Parameter Group dialog box, enter wlmtutorial for Parameter group name and enter WLM tutorial for Description. You can leave the Parameter group family setting as is. Then choose Create.

Step 2: Configure WLM

In this step, you modify the default settings of your new parameter group. You add two new query queues to the WLM configuration and specify different settings for each queue.

To Modify Parameter Group Settings

  1. On the Parameter Groups page of the Amazon Redshift console, choose wlmtutorial. Doing this opens the Parameters page for wlmtutorial.

  2. Choose Switch WLM mode. On the WLM settings page, choose Manual WLM and Save.

  3. Choose the Workload Management tab. Choose Add queue twice to add two new queues to this WLM configuration. Configure the queues with the following values:

    • For queue 1, enter 2 for Concurrency on main, test for Query groups, and 30 for Memory (%). Leave the other settings with their default values.

    • For queue 2, enter 3 for Concurrency on main, admin for User groups, and 40 for Memory (%). Leave the other settings with their default values.

    • Don't make any changes to the Default queue. WLM assigns unallocated memory to the default queue.

  4. Choose Save.

Step 3: Associate the Parameter Group with Your Cluster

In this step, you open your sample cluster and associate it with the new parameter group. After you do this, you reboot the cluster so that Amazon Redshift can apply the new settings to the database.

To Associate the Parameter Group with Your Cluster

  1. In the navigation pane, choose Clusters, and then click your cluster to open it. If you are using the same cluster from Amazon Redshift Getting Started, your cluster is named examplecluster.

  2. On the Configuration tab, choose Modify for Cluster.

  3. In the Modify Cluster dialog box, choose wlmtutorial for Cluster Parameter Group, and then choose Modify.

    The statuses shown in the Cluster Parameter Group and Parameter Group Apply Status change from in-sync to applying as shown in the following.

    After the new parameter group is applied to the cluster, the Cluster Properties and Cluster Status show the new parameter group that you associated with the cluster. You need to reboot the cluster so that these settings can be applied to the database also.

  4. For Cluster, choose Reboot. The status shown in Cluster Status changes from available to rebooting. After the cluster is rebooted, the status returns to available.