Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). 

# Workload management
<a name="cm-c-implementing-workload-management"></a>

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](automatic-wlm.md). 

 

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](concurrency-scaling.md).

**Note**  
In most cases we recommend that you use automatic WLM. If you're using manual WLM and you want to migrate from to automatic WLM, see [Migrating from manual WLM to automatic WLM](#wlm-manual-to-automatic).

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

**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](cm-c-defining-query-queues.md).

**Note**  
We recommend configuring your manual WLM query queues with a total of 15 or fewer query slots. For more information, see [Concurrency level](cm-c-defining-query-queues.md#cm-c-defining-query-queues-concurrency-level).

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.

**Topics**
+ [Switching WLM mode](#cm-c-switching-mode)
+ [Modifying the WLM configuration](#cm-c-modifying-wlm-configuration)
+ [Implementing automatic WLM](automatic-wlm.md)
+ [Implementing manual WLM](cm-c-defining-query-queues.md)
+ [Concurrency scaling](concurrency-scaling.md)
+ [Short query acceleration](wlm-short-query-acceleration.md)
+ [WLM queue assignment rules](cm-c-wlm-queue-assignment-rules.md)
+ [Assigning queries to queues](cm-c-executing-queries.md)
+ [WLM dynamic and static configuration properties](cm-c-wlm-dynamic-properties.md)
+ [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md)
+ [WLM system tables and views](cm-c-wlm-system-tables-and-views.md)

## Switching WLM mode
<a name="cm-c-switching-mode"></a>

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

1. Choose **Switch WLM mode**.

1. To set it to automatic WLM, choose **Auto WLM**. With this choice, up to eight queues are used to manage queries, and the **Memory** and **Concurrency on main** fields are both set to **Auto**. Additionally, the default priority of queries is set to **Normal**.

1. To enable manual configuration using the Amazon Redshift console, switching to **Manual WLM**. With this choice, you specify the queues used to manage queries, and the **Memory** and **Concurrency on main** field values. With a manual configuration, you can configure up to eight query queues and set the number of queries that can run in each of those queues concurrently.

## Modifying the WLM configuration
<a name="cm-c-modifying-wlm-configuration"></a>

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](https://docs.aws.amazon.com/redshift/latest/mgmt/workload-mgmt-config.html) in the *Amazon Redshift Management Guide.*

### Migrating from manual WLM to automatic WLM
<a name="wlm-manual-to-automatic"></a>

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](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-parameter-groups.html) in the *Amazon Redshift Management Guide.* 

**Important**  
To change the parameter group or to switch from manual to automatic WLM requires a cluster reboot. For more information, see [WLM dynamic and static configuration properties](cm-c-wlm-dynamic-properties.md).

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:
+ Create a new parameter group and switch to **Auto WLM** mode.
+ Add queues for each of the three workloads: ETL workload, analytics workload, and data science workload. Use the same user groups for each workload that was used with **Manual WLM** mode. 
+ Set the priority for the ETL workload to `High`, the analytics workload to `Normal`, and the data science to `Low`. These priorities reflect your business priorities for the different workloads or user groups. 
+ Optionally, enable concurrency scaling for the analytics or data science queue so that queries in these queues get consistent performance even when the ETL workload is running every 6 hours.

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.

**Note**  
The timeout field is not available in automatic WLM. Instead, use the QMR rule, `query_execution_time`. For more information, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md).
The QMR action, HOP, is not applicable to automatic WLM. Instead, use the `change priority` action. For more information, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md).
Clusters use automatic WLM and manual WLM queues differently, which can lead to confusion with your configurations. For example, you can configure the priority property in automatic WLM queues but not in manual WLM queues. As such, avoid mixing automatic WLM queues and manual WLM queues within a parameter group. Instead, create a new parameter group when migrating to automatic WLM.

# Implementing automatic WLM
<a name="automatic-wlm"></a>

With automatic workload management (WLM), Amazon Redshift manages query concurrency and memory allocation. You can create up to eight queues with the service class identifiers 100–107. Each queue has a priority. For more information, see [Query priority](query-priority.md). 

Automatic WLM determines the amount of resources that queries need and adjusts the concurrency based on the workload. When queries requiring large amounts of resources are in the system (for example, hash joins between large tables), the concurrency is lower. When lighter queries (such as inserts, deletes, scans, or simple aggregations) are submitted, concurrency is higher. 

Automatic WLM is separate from short query acceleration (SQA) and it evaluates queries differently. Automatic WLM and SQA work together to allow short running and lightweight queries to complete even while long running, resource intensive queries are active. For more information about SQA, see [Short query acceleration](wlm-short-query-acceleration.md). 

Amazon Redshift enables automatic WLM through parameter groups:
+ If your clusters use the default parameter group, Amazon Redshift enables automatic WLM for them.
+ If your clusters use custom parameter groups, you can configure the clusters to enable automatic WLM. We recommend that you create a separate parameter group for your automatic WLM configuration. 

To configure WLM, edit the `wlm_json_configuration` parameter in a parameter group that can be associated with one or more clusters. For more information, see [Modifying the WLM configuration](cm-c-implementing-workload-management.md#cm-c-modifying-wlm-configuration).

You define query queues within the WLM configuration. You can add additional query queues to the default WLM configuration, up to a total of eight user queues. You can configure the following for each query queue: 
+ Priority 
+ Concurrency scaling mode 
+ User groups 
+ Query groups 
+ Query monitoring rules 

## Priority
<a name="wlm-auto-query-priority"></a>

You can define the relative importance of queries in a workload by setting a priority value. The priority is specified for a queue and inherited by all queries associated with the queue. For more information, see [Query priority](query-priority.md).

## Concurrency scaling mode
<a name="wlm-auto-concurrency-scaling-mode"></a>

When concurrency scaling is enabled, Amazon Redshift automatically adds additional cluster capacity when you need it to process an increase in concurrent read and write queries. Your users see the most current data, whether the queries run on the main cluster or on a concurrency scaling cluster. 

You manage which queries are sent to the concurrency scaling cluster by configuring WLM queues. When you enable concurrency scaling for a queue, eligible queries are sent to the concurrency scaling cluster instead of waiting in a queue. For more information, see [Concurrency scaling](concurrency-scaling.md).

## User groups
<a name="wlm-auto-defining-query-queues-user-groups"></a>

You can assign a set of user groups to a queue by specifying each user group name or by using wildcards. When a member of a listed user group runs a query, that query runs in the corresponding queue. There is no set limit on the number of user groups that can be assigned to a queue. For more information, see [Assigning queries to queues based on user groups](cm-c-executing-queries.md#cm-c-executing-queries-assigning-queries-to-queues-based-on-user-groups). 

## User roles
<a name="wlm-auto-defining-query-queues-user-roles"></a>

You can assign a set of user roles to a queue by specifying each user role name or by using wildcards. When a member of a listed user role runs a query, that query runs in the corresponding queue. There is no set limit on the number of user roles that can be assigned to a queue. For more information,see [Assigning queries to queues based on user roles](cm-c-executing-queries.md#cm-c-executing-queries-assigning-queries-to-queues-based-on-user-roles). 

## Query groups
<a name="wlm-auto-defining-query-queues-query-groups"></a>

You can assign a set of query groups to a queue by specifying each query group name or by using wildcards. A *query group* is simply a label. At runtime, you can assign the query group label to a series of queries. Any queries that are assigned to a listed query group run in the corresponding queue. There is no set limit to the number of query groups that can be assigned to a queue. For more information, see [Assigning a query to a query group](cm-c-executing-queries.md#cm-c-executing-queries-assigning-a-query-to-a-query-group). 

## Wildcards
<a name="wlm-auto-wildcards"></a>

If wildcards are enabled in the WLM queue configuration, you can assign user groups and query groups to a queue either individually or by using Unix shell–style wildcards. The pattern matching is case-insensitive. 

For example, the '\$1' wildcard character matches any number of characters. Thus, if you add `dba_*` to the list of user groups for a queue, any user-run query that belongs to a group with a name that begins with `dba_` is assigned to that queue. Examples are `dba_admin` or `DBA_primary`. The '?' wildcard character matches any single character. Thus, if the queue includes user-group `dba?1`, then user groups named `dba11` and `dba21` match, but `dba12` doesn't match. 

By default, wildcards aren't enabled.

## Query monitoring rules
<a name="wlm-auto-query-monitoring-rules"></a>

Query monitoring rules define metrics-based performance boundaries for WLM queues and specify what action to take when a query goes beyond those boundaries. For example, for a queue dedicated to short running queries, you might create a rule that cancels queries that run for more than 60 seconds. To track poorly designed queries, you might have another rule that logs queries that contain nested loops. For more information, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md).

## Checking for automatic WLM
<a name="wlm-monitoring-automatic-wlm"></a>

To check whether automatic WLM is enabled, run the following query. If the query returns at least one row, then automatic WLM is enabled.

```
select * from stv_wlm_service_class_config 
where service_class >= 100;
```

The following query shows the number of queries that went through each query queue (service class). It also shows the average execution time, the number of queries with wait time at the 90th percentile, and the average wait time. Automatic WLM queries use service classes 100 through 107.

```
select final_state, service_class, count(*), avg(total_exec_time), 
percentile_cont(0.9) within group (order by total_queue_time), avg(total_queue_time) 
from stl_wlm_query where userid >= 100 group by 1,2 order by 2,1;
```

To find which queries were run by automatic WLM, and completed successfully, run the following query.

```
select a.queue_start_time, a.total_exec_time, label, trim(querytxt) 
from stl_wlm_query a, stl_query b 
where a.query = b.query and a.service_class >= 100 and a.final_state = 'Completed' 
order by b.query desc limit 5;
```

# Query priority
<a name="query-priority"></a>

With Amazon Redshift, you can manage query prioritization and resource allocation across concurrent queries and workloads using Workload Management (WM). The following sections detail how to configure WM query queues, define queue properties like memory allocation and concurrency scaling, and implement priority rules tailored to your workload requirements.

Not all queries are of equal importance, and often performance of one workload or set of users might be more important. If you have enabled [automatic WLM](automatic-wlm.md), you can define the relative importance of queries in a workload by setting a priority value. The priority is specified for a queue and inherited by all queries associated with the queue. You associate queries to a queue by mapping user groups and query groups to the queue. You can set the following priorities (listed from highest to lowest priority):

1. `HIGHEST`

1. `HIGH`

1. `NORMAL`

1. `LOW`

1. `LOWEST`

Administrators use these priorities to show the relative importance of their workloads when there are queries with different priorities contending for the same resources. Amazon Redshift uses the priority when letting queries into the system, and to determine the amount of resources allocated to a query. By default, queries run with their priority set to `NORMAL`. 

An additional priority, `CRITICAL`, which is a higher priority than `HIGHEST`, is available to superusers. To set this priority, you can use the functions [CHANGE\$1QUERY\$1PRIORITY](r_CHANGE_QUERY_PRIORITY.md), [CHANGE\$1SESSION\$1PRIORITY](r_CHANGE_SESSION_PRIORITY.md). and [CHANGE\$1USER\$1PRIORITY](r_CHANGE_USER_PRIORITY.md). To grant a database user permission to use these functions, you can create a stored procedure and grant permission to a user. For an example, see [CHANGE\$1SESSION\$1PRIORITY](r_CHANGE_SESSION_PRIORITY.md). 

**Note**  
Only one `CRITICAL` query can run at a time.  
Rollbacks always run as CRITICAL priority.

Let's take an example where the priority of an extract, transform, load (ETL) workload is higher than the priority of the analytics workload. The ETL workload runs every six hours, and the analytics workload runs throughout the day. When only the analytics workload is running on the cluster, it gets the entire system to itself, yielding high throughput with optimal system utilization. However, when the ETL workload starts, it gets the right of the way because it has a higher priority. Queries running as part of the ETL workload get the right of the way during admission and also 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. Thus, it provides predictable performance and the ability for administrators to provide service level agreements (SLAs) for their business users. 

Within a given cluster, the predictable performance for a high priority workload comes at the cost of other, lower priority workloads. Lower priority workloads might run longer either because their queries are waiting behind more important queries to complete. Or they might run longer because they're getting a smaller fraction of resources when they are running concurrently with higher priority queries. Lower priority queries don't suffer from starvation, but rather keep making progress at a slower pace.

In the preceding example, the administrator can enable [concurrency scaling](concurrency-scaling.md) for the analytics workload. Doing this enables that workload to maintain its throughput, even though the ETL workload is running at high priority. 

## Configuring queue priority
<a name="concurrency-scaling-queues"></a>

If you have enabled automatic WLM, each queue has a priority value. Queries are routed to queues based on user groups and query groups. Start with a queue priority set to `NORMAL`. Set the priority higher or lower based on the workload associated with the queue's user groups and query groups. 

You can change the priority of a queue on the Amazon Redshift console. On the Amazon Redshift console, the **Workload Management** page displays the queues and enables editing of queue properties such as **Priority**. To set the priority using the CLI or API operations, use the `wlm_json_configuration` parameter. For more information, see [Configuring Workload Management](https://docs.aws.amazon.com/redshift/latest/mgmt/workload-mgmt-config.html) in the *Amazon Redshift Management Guide*.

The following `wlm_json_configuration` example defines three user groups (`ingest`, `reporting`, and `analytics`). Queries submitted from users from one of these groups run with priority `highest`, `normal`, and `low`, respectively.

```
[
    {
        "user_group": [
            "ingest"
        ],
        "priority": "highest",
        "queue_type": "auto"
    },
    {
        "user_group": [
            "reporting"
        ],
        "priority": "normal",
        "queue_type": "auto"
    },
    {
        "user_group": [
            "analytics"
        ],
        "priority": "low",
        "queue_type": "auto",
        "auto_wlm": true
    }
]
```

## Changing query priority with query monitoring rules
<a name="query-priority-qmr"></a>

Query monitoring rules (QMR) enable you to change the priority of a query based on its behavior while it is running. You do this by specifying the priority attribute in a QMR predicate in addition to an action. For more information, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md). 

For example, you can define a rule to cancel any query classified as `high` priority that runs for more than 10 minutes.

```
"rules" :[
  {
    "rule_name":"rule_abort",
    "predicate":[
      {
        "metric_name":"query_cpu_time",
        "operator":">",
        "value":600
      },
      {
        "metric_name":"query_priority",
        "operator":"=",
        "value":"high"
      }
    ],
    "action":"abort"
  }
]
```

Another example is to define a rule to change the query priority to `lowest` for any query with current priority `normal` that spills more than 1 TB to disk. 

```
"rules":[
  {
    "rule_name":"rule_change_priority",
    "predicate":[
      {
        "metric_name":"query_temp_blocks_to_disk",
        "operator":">",
        "value":1000000
      },
      {
        "metric_name":"query_priority",
        "operator":"=",
        "value":"normal"
      }
    ],
    "action":"change_query_priority",
    "value":"lowest"
  }
]
```

## Monitoring query priority
<a name="query-priority-monitoring"></a>

To display priority for waiting and running queries, view the `query_priority` column in the stv\$1wlm\$1query\$1state system table.

```
query    | service_cl | wlm_start_time             | state            | queue_time | query_priority
---------+------------+----------------------------+------------------+------------+----------------
2673299  | 102        | 2019-06-24 17:35:38.866356 | QueuedWaiting    | 265116     | Highest
2673236  | 101        | 2019-06-24 17:35:33.313854 | Running          | 0          | Highest
2673265  | 102        | 2019-06-24 17:35:33.523332 | Running          | 0          | High
2673284  | 102        | 2019-06-24 17:35:38.477366 | Running          | 0          | Highest
2673288  | 102        | 2019-06-24 17:35:38.621819 | Running          | 0          | Highest
2673310  | 103        | 2019-06-24 17:35:39.068513 | QueuedWaiting    | 62970      | High
2673303  | 102        | 2019-06-24 17:35:38.968921 | QueuedWaiting    | 162560     | Normal
2673306  | 104        | 2019-06-24 17:35:39.002733 | QueuedWaiting    | 128691     | Lowest
```

To list query priority for completed queries, see the `query_priority` column in the stl\$1wlm\$1query system table.

```
select query, service_class as svclass, service_class_start_time as starttime, query_priority 
from stl_wlm_query order by 3 desc limit 10;
```

```
  query  | svclass |         starttime          |    query_priority
---------+---------+----------------------------+----------------------
 2723254 |     100 | 2019-06-24 18:14:50.780094 | Normal
 2723251 |     102 | 2019-06-24 18:14:50.749961 | Highest  
 2723246 |     102 | 2019-06-24 18:14:50.725275 | Highest
 2723244 |     103 | 2019-06-24 18:14:50.719241 | High
 2723243 |     101 | 2019-06-24 18:14:50.699325 | Low
 2723242 |     102 | 2019-06-24 18:14:50.692573 | Highest
 2723239 |     101 | 2019-06-24 18:14:50.668535 | Low
 2723237 |     102 | 2019-06-24 18:14:50.661918 | Highest
 2723236 |     102 | 2019-06-24 18:14:50.643636 | Highest
```

To optimize the throughput of your workload, Amazon Redshift might modify the priority of user submitted queries. Amazon Redshift uses advanced machine learning algorithms to determine when this optimization benefits your workload and automatically applies it when all the following conditions are met. 
+ Automatic WLM is enabled.
+ Only one WLM queue is defined.
+ You have not defined query monitoring rules (QMRs) which set query priority. Such rules include the QMR metric `query_priority` or the QMR action `change_query_priority`. For more information, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md). 

# Implementing manual WLM
<a name="cm-c-defining-query-queues"></a>

With manual WLM, you can manage system performance and your users' experience by modifying the WLM configuration to create separate queues for the long-running queries and short-running queries.

When users run queries in Amazon Redshift, the queries are routed to query queues. Each query queue contains a number of query slots. Each queue is allocated a portion of the cluster's available memory. A queue's memory is divided among the queue's query slots. You can enable Amazon Redshift to manage query concurrency with automatic WLM. For more information, see [Implementing automatic WLM](automatic-wlm.md).

Or you can configure WLM properties for each query queue. You do so to specify the way that memory is allocated among slots and how queries can be routed to specific queues at runtime. You can also configure WLM properties to cancel long-running queries.

By default, Amazon Redshift configures the following query queues:
+  **One superuser queue** 

  The superuser queue is reserved for superusers only and it can't be configured. Use this queue only when you need to run queries that affect the system or for troubleshooting purposes. For example, use this queue when you need to cancel a user's long-running query or to add users to the database. Don't use it to perform routine queries. The queue doesn't appear in the console, but it does appear in the system tables in the database as the fifth queue. To run a query in the superuser queue, a user must be logged in as a superuser, and must run the query using the predefined `superuser` query group.
+  **One default user queue** 

  The default queue is initially configured to run five queries concurrently. When you use manual WLM, you can change the concurrency, timeout, and memory allocation properties for the default queue, but you cannot specify user groups or query groups. The default queue must be the last queue in the WLM configuration. Any queries that are not routed to other queues run in the default queue. 

Query queues are defined in the WLM configuration. The WLM configuration is an editable parameter (`wlm_json_configuration`) in a parameter group, which can be associated with one or more clusters. For more information, see [Configuring Workload Management](https://docs.aws.amazon.com/redshift/latest/mgmt/workload-mgmt-config.html) in the *Amazon Redshift Management Guide*. 

You can add additional query queues to the default WLM configuration, up to a total of eight user queues. You can configure the following for each query queue: 
+ Concurrency scaling mode 
+ Concurrency level 
+ User groups 
+ Query groups 
+ WLM memory percent to use
+ WLM timeout
+ WLM query queue hopping
+ Query monitoring rules

## Concurrency scaling mode
<a name="concurrency-scaling-mode"></a>

When concurrency scaling is enabled, Amazon Redshift automatically adds additional cluster capacity when you need it to process an increase in concurrent read and write queries. Users see the most current data, whether the queries run on the main cluster or on a concurrency scaling cluster. 

You manage which queries are sent to the concurrency scaling cluster by configuring WLM queues. When you enable concurrency scaling for a queue, eligible queries are sent to the concurrency scaling cluster instead of waiting in a queue. For more information, see [Concurrency scaling](concurrency-scaling.md).

## Concurrency level
<a name="cm-c-defining-query-queues-concurrency-level"></a>

Queries in a queue run concurrently until they reach the WLM query slot count, or *concurrency* level, defined for that queue. Subsequent queries then wait in the queue.

**Note**  
WLM concurrency level is different from the number of concurrent user connections that can be made to a cluster. For more information, see [Connecting to a Cluster](https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-to-cluster.html) in the *Amazon Redshift Management Guide*.

In an automatic WLM configuration, which is recommended, the concurrency level is set to **Auto**. Amazon Redshift dynamically allocates memory to queries, which subsequently determines how many to run concurrently. This is based on the resources required for both running and queued queries. Auto WLM isn't configurable. For more information, see [Implementing automatic WLM](automatic-wlm.md). 

In a manual WLM configuration, Amazon Redshift statically allocates a fixed amount of memory to each queue. The queue's memory is split evenly among the query slots. To illustrate, if a queue is allocated 20% of a cluster's memory and has 10 slots, each query is allocated 2% of the cluster's memory. The memory allocation remains fixed regardless of the number of queries running concurrently. Because of this fixed memory allocation, queries that run entirely in memory when the slot count is 5 might write intermediate results to disk if the slot count is increased to 20. In this instance each query's share of the queue's memory is reduced from 1/5th to 1/20th. The additional disk I/O could degrade performance. 

The maximum slot count across all user-defined queues is 50. This limits the total slots for all queues, including the default queue. The only queue that isn't subject to the limit is the reserved superuser queue.

By default, manual WLM queues have a concurrency level of 5. Your workload might benefit from a higher concurrency level in certain cases, such as the following:
+ If many small queries are forced to wait for long-running queries, create a separate queue with a higher slot count and assign the smaller queries to that queue. A queue with a higher concurrency level has less memory allocated to each query slot, but the smaller queries require less memory.
**Note**  
If you enable short-query acceleration (SQA), WLM automatically prioritizes short queries over longer-running queries, so you don't need a separate queue for short queries for most workflows. For more information, see [Short query acceleration](wlm-short-query-acceleration.md). 
+ If you have multiple queries that each access data on a single slice, set up a separate WLM queue to run those queries concurrently. Amazon Redshift assigns concurrent queries to separate slices, which allows multiple queries to run in parallel on multiple slices. For example, if a query is a simple aggregate with a predicate on the distribution key, the data for the query is located on a single slice. 

### A manual WLM example
<a name="cm-c-defining-query-queues-concurrency-level-example"></a>

 This example is a simple, manual WLM scenario to show how slots and memory can be allocated. You implement manual WLM with three queues, which are the following: 
+ *data-ingestion queue* – This is set up for ingesting data. It's allocated 20% of the cluster's memory and it has 5 slots. Subsequently, 5 queries can run concurrently in the queue and each is allocated 4% of the memory.
+ *data-scientist queue* – This is designed for memory-intensive queries. It's allocated 40% of the cluster's memory and it has 5 slots. Subsequently, 5 queries can run concurrently and each is allocated 8% of the memory.
+ *default queue* – This is designed for the majority of the users in the organization. This includes sales and accounting groups that typically have short or medium running queries that aren't complicated. It's allocated 40% of the cluster's memory and it has 40 slots. 40 queries can run concurrently in this queue, with each query allocated 1% of the memory. This is the maximum number of slots that can be allocated for this queue because between all queues the limit is 50.

If you're running automatic WLM and your workload requires more than 15 queries to run in parallel, we recommend turning on concurrency scaling. This is because increasing the query slot count above 15 might create contention for system resources and limit the overall throughput of a single cluster. With concurrency scaling, you can run hundreds of queries in parallel, up to a configured number of concurrency scaling clusters. The number of concurrency scaling clusters is controlled by [max\$1concurrency\$1scaling\$1clusters](r_max_concurrency_scaling_clusters.md). For more information about concurrency scaling, see [Concurrency scaling](concurrency-scaling.md). 

For more information, see [Query performance improvement](query-performance-improvement-opportunities.md). 

## User groups
<a name="cm-c-defining-query-queues-user-groups"></a>

You can assign a set of user groups to a queue by specifying each user group name or by using wildcards. When a member of a listed user group runs a query, that query runs in the corresponding queue. There is no set limit on the number of user groups that can be assigned to a queue. For more information, see [Assigning queries to queues based on user groups](cm-c-executing-queries.md#cm-c-executing-queries-assigning-queries-to-queues-based-on-user-groups). 

## User roles
<a name="cm-c-defining-query-queues-user-roles"></a>

You can assign a set of user roles to a queue by specifying each user role name or by using wildcards. When a member of a listed user role runs a query, that query runs in the corresponding queue. There is no set limit on the number of user roles that can be assigned to a queue. For more information, see [Assigning queries to queues based on user roles](cm-c-executing-queries.md#cm-c-executing-queries-assigning-queries-to-queues-based-on-user-roles). 

## Query groups
<a name="cm-c-defining-query-queues-query-groups"></a>

You can assign a set of query groups to a queue by specifying each query group name or by using wildcards. A query group is simply a label. At runtime, you can assign the query group label to a series of queries. Any queries that are assigned to a listed query group run in the corresponding queue. There is no set limit to the number of query groups that can be assigned to a queue. For more information, see [Assigning a query to a query group](cm-c-executing-queries.md#cm-c-executing-queries-assigning-a-query-to-a-query-group). 

## Wildcards
<a name="wlm-wildcards"></a>

If wildcards are enabled in the WLM queue configuration, you can assign user groups and query groups to a queue either individually or by using Unix shell-style wildcards. The pattern matching is case-insensitive. 

For example, the '\$1' wildcard character matches any number of characters. Thus, if you add `dba_*` to the list of user groups for a queue, any user-run query that belongs to a group with a name that begins with `dba_` is assigned to that queue. Examples are `dba_admin` or `DBA_primary`, . The '?' wildcard character matches any single character. Thus, if the queue includes user-group `dba?1`, then user groups named `dba11` and `dba21` match, but `dba12` doesn't match. 

Wildcards are turned off by default.

## WLM memory percent to use
<a name="wlm-memory-percent"></a>

In an automatic WLM configuration, memory percent is set to **auto**. For more information, see [Implementing automatic WLM](automatic-wlm.md). 

In a manual WLM configuration, to specify the amount of available memory that is allocated to a query, you can set the `WLM Memory Percent to Use` parameter. By default, each user-defined queue is allocated an equal portion of the memory that is available for user-defined queries. For example, if you have four user-defined queues, each queue is allocated 25 percent of the available memory. The superuser queue has its own allocated memory and cannot be modified. To change the allocation, you assign an integer percentage of memory to each queue, up to a total of 100 percent. Any unallocated memory is managed by Amazon Redshift and can be temporarily given to a queue if the queue requests additional memory for processing. 

For example, if you configure four queues, you can allocate memory as follows: 20 percent, 30 percent, 15 percent, 15 percent. The remaining 20 percent is unallocated and managed by the service.

## WLM timeout
<a name="wlm-timeout"></a>

WLM timeout (`max_execution_time`) is deprecated. Instead, create a query monitoring rule (QMR) using `query_execution_time` to limit the elapsed execution time for a query. For more information, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md). 

To limit the amount of time that queries in a given WLM queue are permitted to use, you can set the WLM timeout value for each queue. The timeout parameter specifies the amount of time, in milliseconds, that Amazon Redshift waits for a query to run before either canceling or hopping the query. The timeout is based on query execution time and doesn't include time spent waiting in a queue. 

WLM attempts to hop [CREATE TABLE AS](r_CREATE_TABLE_AS.md) (CTAS) statements and read-only queries, such as SELECT statements. Queries that can't be hopped are canceled. For more information, see [WLM query queue hopping](wlm-queue-hopping.md).

WLM timeout doesn't apply to a query that has reached the returning state. To view the state of a query, see the [STV\$1WLM\$1QUERY\$1STATE](r_STV_WLM_QUERY_STATE.md) system table. COPY statements and maintenance operations, such as ALTER, ANALYZE and VACUUM, are not subject to WLM timeout.

The function of WLM timeout is similar to the [statement\$1timeout](r_statement_timeout.md) configuration parameter. The difference is that, where the `statement_timeout` configuration parameter applies to the entire cluster, WLM timeout is specific to a single queue in the WLM configuration. 

If [statement\$1timeout](r_statement_timeout.md) is also specified, the lower of statement\$1timeout and WLM timeout (max\$1execution\$1time) is used. 

## Query monitoring rules
<a name="wlm-query-monitoring-rules"></a>

Query monitoring rules define metrics-based performance boundaries for WLM queues and specify what action to take when a query goes beyond those boundaries. For example, for a queue dedicated to short running queries, you might create a rule that cancels queries that run for more than 60 seconds. To track poorly designed queries, you might have another rule that logs queries that contain nested loops. For more information, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md).

# WLM query queue hopping
<a name="wlm-queue-hopping"></a>

With Amazon Redshift, you can manage workload concurrency and resource allocation by enabling WLM (Workload Management) query queue hopping. This feature allows queries to temporarily "hop" from an assigned queue to a higher priority queue when resources are available, improving overall query performance and system utilization. The following sections provide detailed guidance on configuring and utilizing WLM query queue hopping in Amazon Redshift.

A query can be hopped due to a [WLM timeout](cm-c-defining-query-queues.md#wlm-timeout) or a [query monitoring rule (QMR) hop action](cm-c-wlm-query-monitoring-rules.md#cm-c-wlm-defining-query-monitoring-rules). You can only hop queries in a manual WLM configuration. 

When a query is hopped, WLM attempts to route the query to the next matching queue based on the [WLM queue assignment rules](cm-c-wlm-queue-assignment-rules.md). If the query doesn't match any other queue definition, the query is canceled. It's not assigned to the default queue. 

## WLM timeout actions
<a name="wlm-queue-hopping-summary"></a>

The following table summarizes the behavior of different types of queries with a WLM timeout.

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/wlm-queue-hopping.html)

## WLM timeout queue hopping
<a name="wlm-timeout-queue-hopping"></a>

WLM hops the following types of queries when they time out:
+ Read-only queries, such as SELECT statements, that are in a WLM state of `running`. To find the WLM state of a query, view the STATE column on the [STV\$1WLM\$1QUERY\$1STATE](r_STV_WLM_QUERY_STATE.md) system table. 
+ CREATE TABLE AS (CTAS) statements. WLM queue hopping supports both user-defined and system-generated CTAS statements. 
+ SELECT INTO statements.

Queries that aren't subject to WLM timeout continue running in the original queue until completion. The following types of queries aren't subject to WLM timeout:
+ COPY statements
+ Maintenance operations, such as ALTER, ANALYZE and VACUUM
+ Read-only queries, such as SELECT statements, that have reached a WLM state of `returning`. To find the WLM state of a query, view the STATE column on the [STV\$1WLM\$1QUERY\$1STATE](r_STV_WLM_QUERY_STATE.md) system table. 

Queries that aren't eligible for hopping by WLM timeout are canceled when they time out. The following types of queries are not eligible for hopping by a WLM timeout:
+ INSERT, UPDATE, and DELETE statements
+ UNLOAD statements
+ User-defined functions (UDFs)

## WLM timeout reassigned and restarted queries
<a name="wlm-timeout-reassigned-and-restarted-queries"></a>

When a query is hopped and no matching queue is found, the query is canceled.

When a query is hopped and a matching queue is found, WLM attempts to reassign the query to the new queue. If a query can't be reassigned, it's restarted in the new queue, as described following.

A query is reassigned only if all of the following are true:
+ A matching queue is found.
+ The new queue has enough free slots to run the query. A query might require multiple slots if the [wlm\$1query\$1slot\$1count](r_wlm_query_slot_count.md) parameter was set to a value greater than 1.
+ The new queue has at least as much memory available as the query currently uses. 

If the query is reassigned, the query continues executing in the new queue. Intermediate results are preserved, so there is minimal effect on total execution time. 

If the query can't be reassigned, the query is canceled and restarted in the new queue. Intermediate results are deleted. The query waits in the queue, then begins running when enough slots are available.

## QMR hop actions
<a name="qmr-hop-action-queue-hopping"></a>

The following table summarizes the behavior of different types of queries with a QMR hop action.

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/wlm-queue-hopping.html)

To find whether a query that was hopped by QMR was reassigned, restarted, or canceled, query the [STL\$1WLM\$1RULE\$1ACTION](r_STL_WLM_RULE_ACTION.md) system log table.

## QMR hop action reassigned and restarted queries
<a name="qmr-hop-action-reassigned-and-restarted-queries"></a>

When a query is hopped and no matching queue is found, the query is canceled.

When a query is hopped and a matching queue is found, WLM attempts to reassign the query to the new queue. If a query can't be reassigned, it's restarted in the new queue or continues execution in the original queue, as described following.

A query is reassigned only if all of the following are true:
+ A matching queue is found.
+ The new queue has enough free slots to run the query. A query might require multiple slots if the [wlm\$1query\$1slot\$1count](r_wlm_query_slot_count.md) parameter was set to a value greater than 1.
+ The new queue has at least as much memory available as the query currently uses. 

If the query is reassigned, the query continues executing in the new queue. Intermediate results are preserved, so there is minimal effect on total execution time. 

If a query can't be reassigned, the query is either restarted or continues execution in the original queue. If the query is restarted, the query is canceled and restarted in the new queue. Intermediate results are deleted. The query waits in the queue, then begins execution when enough slots are available.

# Tutorial: Configuring manual workload management (WLM) queues
<a name="tutorial-configuring-workload-management"></a>

With Amazon Redshift, you can configure manual workload management (WLM) queues to prioritize and allocate resources for different types of queries and users. Manual WLM queues allow you to control the memory and concurrency settings for specific queues, ensuring that critical workloads receive the necessary resources while preventing low-priority queries from monopolizing the system. The following sections guide you through the process of creating and configuring manual WLM queues in Amazon Redshift to meet your workload management requirements. 

## Overview
<a name="tutorial-wlm-overview"></a>

We recommend configuring automatic workload management (WLM) in Amazon Redshift. For more information about automatic WLM, see [Workload management](cm-c-implementing-workload-management.md). However, if you need multiple WLM queues, this tutorial walks you through the process of configuring manual workload management (WLM) in Amazon Redshift. By configuring manual WLM, you can improve query performance and resource allocation in your cluster.

Amazon Redshift routes user queries to queues for processing. WLM defines how those queries are routed to the queues. By default, Amazon Redshift has two queues available for queries: one for superusers, and one for users. The superuser queue cannot be configured and can only process one query at a time. You should reserve this queue for troubleshooting purposes only. The user queue can process up to five queries at a time, but you can configure this by changing the concurrency level of the queue if needed. 

When you have several users running queries against the database, you might find another configuration to be more efficient. For example, if some users run resource-intensive operations, such as VACUUM, these might have a negative impact on less-intensive queries, such as reports. You might consider adding additional queues and configuring them for different workloads. 

**Estimated time:** 75 minutes

**Estimated cost:** 50 cents

### Prerequisites
<a name="tutorial-wlm-prereq"></a>

You need an Amazon Redshift cluster, the sample TICKIT database, and the Amazon Redshift RSQL client tool. If you do not already have these set up, go to [Amazon Redshift Getting Started Guide](https://docs.aws.amazon.com/redshift/latest/gsg/new-user.html) and [Amazon Redshift RSQL](https://docs.aws.amazon.com/redshift/latest/mgmt/rsql-query-tool.html). 

### Sections
<a name="tutorial-wlm-steps"></a>
+ [Section 1: Understanding the default queue processing behavior](#tutorial-wlm-understanding-default-processing)
+ [Section 2: Modifying the WLM query queue configuration](#tutorial-wlm-modifying-wlm-configuration)
+ [Section 3: Routing queries to queues based on user groups and query groups](#tutorial-wlm-routing-queries-to-queues)
+ [Section 4: Using wlm\$1query\$1slot\$1count to temporarily override the concurrency level in a queue](#tutorial-wlm-query-slot-count)
+ [Section 5: Cleaning up your resources](#tutorial-wlm-cleaning-up-resources)

## Section 1: Understanding the default queue processing behavior
<a name="tutorial-wlm-understanding-default-processing"></a>

Before you start to configure manual WLM, it’s useful to understand the default behavior of queue processing in Amazon Redshift. In this section, you create two database views that return information from several system tables. Then you run some test queries to see how queries are routed by default. For more information about system tables, see [System tables and views reference](cm_chap_system-tables.md). 

### Step 1: Create the WLM\$1QUEUE\$1STATE\$1VW view
<a name="tutorial-wlm-create-queue-state-view"></a>

In this step, you create a view called WLM\$1QUEUE\$1STATE\$1VW. This view returns information from the following system tables.
+ [STV\$1WLM\$1CLASSIFICATION\$1CONFIG](r_STV_WLM_CLASSIFICATION_CONFIG.md)
+ [STV\$1WLM\$1SERVICE\$1CLASS\$1CONFIG](r_STV_WLM_SERVICE_CLASS_CONFIG.md)
+ [STV\$1WLM\$1SERVICE\$1CLASS\$1STATE](r_STV_WLM_SERVICE_CLASS_STATE.md)

You use this view throughout the tutorial to monitor what happens to queues after you change the WLM configuration. The following table describes the data that the WLM\$1QUEUE\$1STATE\$1VW view returns. 

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/tutorial-configuring-workload-management.html)

#### To create the WLM\$1QUEUE\$1STATE\$1VW view
<a name="how-to-wlm-create-queue-state-view"></a>

1. Open [Amazon Redshift RSQL](https://docs.aws.amazon.com/redshift/latest/mgmt/rsql-query-tool.html) and connect to your TICKIT sample database. If you do not have this database, see [Prerequisites](#tutorial-wlm-prereq).

1. Run the following query to create the WLM\$1QUEUE\$1STATE\$1VW view.

   ```
   create view WLM_QUEUE_STATE_VW as
   select (config.service_class-5) as queue
   , trim (class.condition) as description
   , config.num_query_tasks as slots
   , config.query_working_mem as mem
   , config.max_execution_time as max_time
   , config.user_group_wild_card as "user_*"
   , config.query_group_wild_card as "query_*"
   , state.num_queued_queries queued
   , state.num_executing_queries executing
   , state.num_executed_queries executed
   from
   STV_WLM_CLASSIFICATION_CONFIG class,
   STV_WLM_SERVICE_CLASS_CONFIG config,
   STV_WLM_SERVICE_CLASS_STATE state
   where
   class.action_service_class = config.service_class 
   and class.action_service_class = state.service_class 
   and config.service_class > 4
   order by config.service_class;
   ```

1. Run the following query to see the information that the view contains.

   ```
   select * from wlm_queue_state_vw;
   ```

   The following is an example result.

   ```
   query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
   ------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
       0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
       1 | (querytype:any)                           |     5 | 836 |        0 |  false | false   |      0 |         1 |      160
   ```

### Step 2: Create the WLM\$1QUERY\$1STATE\$1VW view
<a name="tutorial-wlm-create-query-state-view"></a>

In this step, you create a view called WLM\$1QUERY\$1STATE\$1VW. This view returns information from the [STV\$1WLM\$1QUERY\$1STATE](r_STV_WLM_QUERY_STATE.md) system table.

You use this view throughout the tutorial to monitor the queries that are running. The following table describes the data that the WLM\$1QUERY\$1STATE\$1VW view returns.

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/tutorial-configuring-workload-management.html)

#### To create the WLM\$1QUERY\$1STATE\$1VW view
<a name="how-to-wlm-create-query-state-view"></a>

1. In RSQL, run the following query to create the WLM\$1QUERY\$1STATE\$1VW view.

   ```
   create view WLM_QUERY_STATE_VW as
   select query, (service_class-5) as queue, slot_count, trim(wlm_start_time) as start_time, trim(state) as state, trim(queue_time) as queue_time, trim(exec_time) as exec_time
   from stv_wlm_query_state;
   ```

1. Run the following query to see the information that the view contains.

   ```
   select * from wlm_query_state_vw;
   ```

   The following is an example result.

   ```
   query | queue | slot_count | start_time          | state     | queue_time | exec_time
   ------+-------+------------+---------------------+-----------+------------+-----------
    1249 |     1 |          1 | 2014-09-24 22:19:16 | Executing | 0          | 516
   ```

### Step 3: Run test queries
<a name="tutorial-wlm-run-test-queries"></a>

In this step, you run queries from multiple connections in RSQL and review the system tables to determine how the queries were routed for processing. 

For this step, you need two RSQL windows open: 
+ In RSQL window 1, you run queries that monitor the state of the queues and queries using the views you already created in this tutorial.
+ In RSQL window 2, you run long-running queries to change the results you find in RSQL window 1.

#### To run the test queries
<a name="how-to-wlm-run-test-queries"></a>

1. Open two RSQL windows. If you already have one window open, you only need to open a second window. You can use the same user account for both of these connections.

1. In RSQL window 1, run the following query.

   ```
   select * from wlm_query_state_vw;
   ```

   The following is an example result.

   ```
   query | queue | slot_count | start_time          | state     | queue_time | exec_time
   ------+-------+------------+---------------------+-----------+------------+-----------
    1258 |     1 |          1 | 2014-09-24 22:21:03 | Executing | 0          | 549
   ```

   This query returns a self-referential result. The query that is currently running is the SELECT statement from this view. A query on this view always returns at least one result. Compare this result with the result that occurs after starting the long-running query in the next step.

1. In RSQL window 2, run a query from the TICKIT sample database. This query should run for approximately a minute so that you have time to explore the results of the WLM\$1QUEUE\$1STATE\$1VW view and the WLM\$1QUERY\$1STATE\$1VW view that you created earlier. In some cases, you might find that the query doesn't run long enough for you to query both views. In these cases, you can increase the value of the filter on `l.listid `to make it run longer.
**Note**  
To reduce query execution time and improve system performance, Amazon Redshift caches the results of certain types of queries in memory on the leader node. When result caching is enabled, subsequent queries run much faster. To prevent the query from running to quickly, disable result caching for the current session.

   To turn off result caching for the current session, set the [enable\$1result\$1cache\$1for\$1session](r_enable_result_cache_for_session.md) parameter to `off`, as shown following.

   ```
   set enable_result_cache_for_session to off;
   ```

   In RSQL window 2, run the following query.

   ```
   select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid < 100000;
   ```

1. In RSQL window 1, query WLM\$1QUEUE\$1STATE\$1VW and WLM\$1QUERY\$1STATE\$1VW and compare the results to your earlier results.

   ```
   select * from wlm_queue_state_vw;
   select * from wlm_query_state_vw;
   ```

   The following are example results.

   ```
   query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
   ------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
       0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
       1 | (querytype:any)                           |     5 | 836 |        0 |  false | false   |      0 |         2 |      163
                           
   query | queue | slot_count | start_time          | state     | queue_time | exec_time
   ------+-------+------------+---------------------+-----------+------------+-----------
    1267 |     1 |          1 | 2014-09-24 22:22:30 | Executing | 0          | 684
    1265 |     1 |          1 | 2014-09-24 22:22:36 | Executing | 0          | 4080859
   ```

Note the following differences between your previous queries and the results in this step:
+ There are two rows now in WLM\$1QUERY\$1STATE\$1VW. One result is the self-referential query for running a SELECT operation on this view. The second result is the long-running query from the previous step.
+ The executing column in WLM\$1QUEUE\$1STATE\$1VW has increased from 1 to 2. This column entry means that there are two queries running in the queue.
+ The executed column is incremented each time you run a query in the queue.

The WLM\$1QUEUE\$1STATE\$1VW view is useful for getting an overall view of the queues and how many queries are being processed in each queue. The WLM\$1QUERY\$1STATE\$1VW view is useful for getting a more detailed view of the individual queries that are currently running.

## Section 2: Modifying the WLM query queue configuration
<a name="tutorial-wlm-modifying-wlm-configuration"></a>

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.

**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/redshiftv2/](https://console.aws.amazon.com/redshiftv2/).

1. On the navigation menu, choose **Configurations**, then choose **Workload management** to display the **Workload management** page. 

1. Choose **Create** to display the **Create parameter group** window. 

1. 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. 

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

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

1. Choose **Manual WLM**, then choose **Save** to switch to manual WLM. 

1. Choose **Edit workload queues**. 

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

1. 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.
   + Set the **Concurrency on main** value for the default queue to a value greater than or equal to 1. Don't make any other changes to the **Default queue**. WLM assigns unallocated memory to the default queue. 

1. 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/redshiftv2/](https://console.aws.amazon.com/redshiftv2/).

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

1. Choose your cluster, such as `examplecluster` to display the details of the cluster. Then choose the **Properties** tab to display the properties of that cluster. 

1. In the **Database configurations** section, choose **Edit**, **Edit parameter group** to display the parameter groups window. 

1. For **Parameter groups** choose the **wlmtutorial** parameter group that you previously created. 

1. Choose **Save changes** 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.

1. Choose your cluster, and then choose **Reboot** for **Actions**. 

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

## Section 3: Routing queries to queues based on user groups and query groups
<a name="tutorial-wlm-routing-queries-to-queues"></a>

Now you have your cluster associated with a new parameter group and you've configured WLM. Next, run some queries to see how Amazon Redshift routes queries into queues for processing.

### Step 1: View query queue configuration in the database
<a name="tutorial-wlm-view-query-config"></a>

First, verify that the database has the WLM configuration that you expect.

#### To view the query queue configuration
<a name="how-to-wlm-view-query-config"></a>

1. Open RSQL and run the following query. The query uses the WLM\$1QUEUE\$1STATE\$1VW view you created in [Step 1: Create the WLM\$1QUEUE\$1STATE\$1VW view](#tutorial-wlm-create-queue-state-view). If you already had a session connected to the database prior to the cluster reboot, you need to reconnect.

   ```
   select * from wlm_queue_state_vw;
   ```

   The following is an example result.

   ```
   query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
   ------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
       0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
       1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         0 |        0
       2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         0 |        0
       3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |        0
   ```

   Compare these results to the results you received in [Step 1: Create the WLM\$1QUEUE\$1STATE\$1VW view](#tutorial-wlm-create-queue-state-view). Notice that there are now two additional queues. Queue 1 is now the queue for the test query group, and queue 2 is the queue for the admin user group.

   Queue 3 is now the default queue. The last queue in the list is always the default queue. That's the queue to which queries are routed by default if no user group or query group is specified in a query.

1. Run the following query to confirm that your query now runs in queue 3.

   ```
   select * from wlm_query_state_vw;
   ```

   The following is an example result.

   ```
   query | queue | slot_count | start_time          | state     | queue_time | exec_time
   ------+-------+------------+---------------------+-----------+------------+-----------
    2144 |     3 |          1 | 2014-09-24 23:49:59 | Executing | 0          | 550430
   ```

### Step 2: Run a query using the query group queue
<a name="tutorial-wlm-query-group"></a>

#### To run a query using the query group queue
<a name="how-to-wlm-query-group"></a>

1. Run the following query to route it to the `test` query group.

   ```
   set query_group to test;
   select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
   ```

1. From the other RSQL window, run the following query.

   ```
   select * from wlm_query_state_vw;
   ```

   The following is an example result.

   ```
   query | queue | slot_count | start_time          | state     | queue_time | exec_time
   ------+-------+------------+---------------------+-----------+------------+-----------
    2168 |     1 |          1 | 2014-09-24 23:54:18 | Executing | 0          | 6343309
    2170 |     3 |          1 | 2014-09-24 23:54:24 | Executing | 0          | 847
   ```

   The query was routed to the test query group, which is queue 1 now.

1. Select all from the queue state view.

   ```
   select * from wlm_queue_state_vw;
   ```

   You see a result similar to the following.

   ```
   query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
   ------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
       0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
       1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         1 |        0
       2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         0 |        0
       3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |        0
   ```

1. Now, reset the query group and run the long query again:

   ```
   reset query_group;
   select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
   ```

1. Run the queries against the views to see the results.

   ```
   select * from wlm_queue_state_vw;
   select * from wlm_query_state_vw;
   ```

   The following are example results.

   ```
   query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
   ------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
       0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
       1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         0 |        1
       2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         0 |        0
       3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         2 |        5
    
   query | queue | slot_count | start_time          | state     | queue_time | exec_time
   ------+-------+------------+---------------------+-----------+------------+-----------
    2186 |     3 |          1 | 2014-09-24 23:57:52 | Executing | 0          | 649
    2184 |     3 |          1 | 2014-09-24 23:57:48 | Executing | 0          | 4137349
   ```

   The result should be that the query is now running in queue 3 again.

### Step 3: Create a database user and group
<a name="tutorial-wlm-create-db-user-and-group"></a>

Before you can run any queries in this queue, you need to create the user group in the database and add a user to the group. Then you log in with RSQL using the new user’s credentials and run queries. You need to run queries as a superuser, such as the admin user, to create database users.

#### To create a new database user and user group
<a name="how-to-wlm-create-db-user-and-group"></a>

1. In the database, create a new database user named `adminwlm` by running the following command in an RSQL window.

   ```
   create user adminwlm createuser password '123Admin';
   ```

1. Then, run the following commands to create the new user group and add your new `adminwlm` user to it.

   ```
   create group admin;
   alter group admin add user adminwlm;
   ```

### Step 4: Run a query using the user group queue
<a name="tutorial-wlm-user-group-query"></a>

Next you run a query and route it to the user group queue. You do this when you want to route your query to a queue that is configured to handle the type of query you want to run.

#### To run a query using the user group queue
<a name="how-to-wlm-user-group-query"></a>

1. In RSQL window 2, run the following queries to switch to the `adminwlm` account and run a query as that user.

   ```
   set session authorization 'adminwlm';
   select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
   ```

1. In RSQL window 1, run the following query to see the query queue that the queries are routed to.

   ```
   select * from wlm_query_state_vw;
   select * from wlm_queue_state_vw;
   ```

   The following are example results.

   ```
   query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
   ------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
       0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
       1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         0 |        1
       2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         1 |        0
       3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |        8
    
   query | queue | slot_count | start_time          | state     | queue_time | exec_time
   ------+-------+------------+---------------------+-----------+------------+-----------
    2202 |     2 |          1 | 2014-09-25 00:01:38 | Executing | 0          | 4885796
    2204 |     3 |          1 | 2014-09-25 00:01:43 | Executing | 0          | 650
   ```

   The queue that this query ran in is queue 2, the `admin` user queue. Anytime you run queries logged in as this user, they run in queue 2 unless you specify a different query group to use. The chosen queue depends on the queue assignment rules. For more information, see [WLM queue assignment rules](cm-c-wlm-queue-assignment-rules.md). 

1. Now run the following query from RSQL window 2.

   ```
   set query_group to test;
   select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
   ```

1. In RSQL window 1, run the following query to see the query queue that the queries are routed to.

   ```
   select * from wlm_queue_state_vw;
   select * from wlm_query_state_vw;
   ```

   The following are example results.

   ```
   query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
   ------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
       0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
       1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         1 |        1
       2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         0 |        1
       3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |       10
    
   query | queue | slot_count | start_time          | state     | queue_time | exec_time
   ------+-------+------------+---------------------+-----------+------------+-----------
    2218 |     1 |          1 | 2014-09-25 00:04:30 | Executing | 0          | 4819666
    2220 |     3 |          1 | 2014-09-25 00:04:35 | Executing | 0          | 685
   ```

1. When you’re done, reset the query group.

   ```
   reset query_group;
   ```

## Section 4: Using wlm\$1query\$1slot\$1count to temporarily override the concurrency level in a queue
<a name="tutorial-wlm-query-slot-count"></a>

Sometimes, users might temporarily need more resources for a particular query. If so, they can use the wlm\$1query\$1slot\$1count configuration setting to temporarily override the way slots are allocated in a query queue. *Slots* are units of memory and CPU that are used to process queries. You might override the slot count when you have occasional queries that take a lot of resources in the cluster, such as when you perform a VACUUM operation in the database. 

You might find that users often need to set wlm\$1query\$1slot\$1count for certain types of queries. If so, consider adjusting the WLM configuration and giving users a queue that better suits the needs of their queries. For more information about temporarily overriding the concurrency level by using slot count, see [wlm\$1query\$1slot\$1count](r_wlm_query_slot_count.md).

### Step 1: Override the concurrency level using wlm\$1query\$1slot\$1count
<a name="tutorial-wlm-override-slot-count"></a>

For the purposes of this tutorial, we run the same long-running SELECT query. We run it as the `adminwlm` user using wlm\$1query\$1slot\$1count to increase the number of slots available for the query.

#### To override the concurrency level using wlm\$1query\$1slot\$1count
<a name="how-to-wlm-override-slot-count"></a>

1. Increase the limit on the query to make sure that you have enough time to query the WLM\$1QUERY\$1STATE\$1VW view and see a result. 

   ```
   set wlm_query_slot_count to 3; 
   select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
   ```

1. Now, query WLM\$1QUERY\$1STATE\$1VW with the admin user to see how the query is running.

   ```
   select * from wlm_query_state_vw;
   ```

   The following is an example result.

   ```
   query | queue | slot_count | start_time          | state     | queue_time | exec_time
   ------+-------+------------+---------------------+-----------+------------+-----------
    2240 |     2 |          1 | 2014-09-25 00:08:45 | Executing | 0          | 3731414
    2242 |     3 |          1 | 2014-09-25 00:08:49 | Executing | 0          | 596
   ```

   Notice that the slot count for the query is 3. This count means that the query is using all three slots to process the query, allocating all of the resources in the queue to that query.

1. Now, run the following query.

   ```
   select * from WLM_QUEUE_STATE_VW;
   ```

   The following is an example result.

   ```
   query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
   ------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
       0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
       1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         0 |        4
       2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         1 |        3
       3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |       25
   ```

   The wlm\$1query\$1slot\$1count configuration setting is valid for the current session only. If that session expires, or another user runs a query, the WLM configuration is used.

1. Reset the slot count and rerun the test.

   ```
   reset wlm_query_slot_count;
   select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
   ```

   The following are example results.

   ```
   query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
   ------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
       0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
       1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         0 |        2
       2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         1 |        2
       3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |       14
    
   query | queue | slot_count | start_time          | state     | queue_time | exec_time
   ------+-------+------------+---------------------+-----------+------------+-----------
    2260 |     2 |          1 | 2014-09-25 00:12:11 | Executing | 0          | 4042618
    2262 |     3 |          1 | 2014-09-25 00:12:15 | Executing | 0          | 680
   ```

### Step 2: Run queries from different sessions
<a name="tutorial-wlm-run-queries-from-different-sessions"></a>

Next, run queries from different sessions.

#### To run queries from different sessions
<a name="how-to-wlm-run-queries-from-different-sessions"></a>

1. In RSQL window 1 and 2, run the following to use the test query group.

   ```
   set query_group to test;
   ```

1. In RSQL window 1, run the following long-running query.

   ```
   select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
   ```

1. As the long-running query is still going in RSQL window 1, run the following. These commands increase the slot count to use all the slots for the queue and then start running the long-running query.

   ```
   set wlm_query_slot_count to 2;
   select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
   ```

1. Open a third RSQL window and query the views to see the results.

   ```
   select * from wlm_queue_state_vw;
   select * from wlm_query_state_vw;
   ```

   The following are example results.

   ```
   query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
   ------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
       0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
       1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      1 |         1 |        2
       2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         0 |        3
       3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |       18
    
   query | queue | slot_count | start_time          | state         | queue_time | exec_time
   ------+-------+------------+---------------------+---------------+------------+-----------
    2286 |     1 |          2 | 2014-09-25 00:16:48 | QueuedWaiting | 3758950    | 0
    2282 |     1 |          1 | 2014-09-25 00:16:33 | Executing     | 0          | 19335850
    2288 |     3 |          1 | 2014-09-25 00:16:52 | Executing     | 0          | 666
   ```

   Notice that the first query is using one of the slots allocated to queue 1 to run the query. In addition, notice that there is one query that is waiting in the queue (where `queued` is `1` and `state` is `QueuedWaiting`). After the first query completes, the second one begins running. This execution happens because both queries are routed to the `test` query group, and the second query must wait for enough slots to begin processing.

## Section 5: Cleaning up your resources
<a name="tutorial-wlm-cleaning-up-resources"></a>

Your cluster continues to accrue charges as long as it is running. When you have completed this tutorial, return your environment to the previous state by following the steps in [Find Additional Resources and Reset Your Environment](https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-clean-up-tasks.html) in *Amazon Redshift Getting Started Guide*.

For more information about WLM, see [Workload management](cm-c-implementing-workload-management.md).

# Concurrency scaling
<a name="concurrency-scaling"></a>

With the Concurrency Scaling feature, you can support thousands of concurrent users and concurrent queries, with consistently fast query performance. When you turn on concurrency scaling, Amazon Redshift automatically adds additional cluster capacity to process an increase in both read and write queries. Users see the most current data, whether the queries run on the main cluster or a concurrency-scaling cluster.

You can manage which queries are sent to the concurrency-scaling cluster by configuring WLM queues. When you turn on concurrency scaling, eligible queries are sent to the concurrency-scaling cluster instead of waiting in a queue.

You're charged for concurrency-scaling clusters only for the time they're actively running queries. For more information about pricing, including how charges accrue and minimum charges, see [Concurrency Scaling pricing](https://aws.amazon.com/redshift/pricing/#Concurrency_Scaling_pricing).

**Topics**
+ [Concurrency scaling capabilities](#concurrency-scaling-capabilities)
+ [Limitations for concurrency scaling](#concurrency-scaling-limitations)
+ [AWS Regions for concurrency scaling](#concurrency-scaling-regions)
+ [Concurrency scaling candidates](#concurrency-scaling-candidates)
+ [Configuring concurrency scaling queues](#concurrency-scaling-queues)
+ [Monitoring concurrency scaling](#concurrency-scaling-monitoring)
+ [Concurrency scaling system views](#concurrency-scaling-monitoring-system-views)

## Concurrency scaling capabilities
<a name="concurrency-scaling-capabilities"></a>

When you turn on concurrency scaling for a WLM queue, it works for read operations, such as dashboard queries. It also works for commonly used write operations, such as statements for data ingestion and processing.

### Concurrency scaling capabilities for write operations
<a name="concurrency-scaling-capabilities-write-operations"></a>

Concurrency scaling supports frequently used write operations, such as extract, transform, and load (ETL) statements. Concurrency scaling for write operations is especially useful when you want to maintain consistent response times when your cluster receives a large number of requests. It improves throughput for write operations contending for resources on the main cluster.

Concurrency scaling supports COPY, INSERT, DELETE, UPDATE, CREATE TABLE AS (CTAS), and VACUUM statements. Additionally, concurrency scaling supports manual refresh of materialized views (MVs) and automatic vacuum operations. Other data-manipulation language (DML) statements and data-definition language (DDL) statements aren't supported. When non-supported write statements, such as CREATE without TABLE AS, are included in an explicit transaction before the supported write statements, none of the write statements will run on concurrency-scaling clusters. 

When you accrue credit for concurrency scaling, this credit accrual applies to both read and write operations.

## Limitations for concurrency scaling
<a name="concurrency-scaling-limitations"></a>

 The following are limitations for using Amazon Redshift concurrency scaling: 
+ It doesn't support queries on tables that use interleaved sort keys.
+ It doesn't support queries on temporary tables.
+ It doesn't support queries that access external resources that are protected by restrictive network or virtual private cloud (VPC) configurations.
+ It doesn't support queries that contain Python user-defined functions (UDFs) and Lambda UDFs.
+ It doesn't support queries that access system tables, PostgreSQL catalog tables, or no-backup tables.
+ It doesn’t support COPY or UNLOAD queries that access an external resource when restrictive IAM policy permissions are in place. This includes permissions applied either to the resource, like an Amazon S3 bucket or DynamoDB table, or to the source. IAM sources can include the following:
  + `aws:sourceVpc` – A source VPC.
  + `aws:sourceVpce` – A source VPC endpoint.
  + `aws:sourceIp` – A source IP address.

  In some cases, you might need to remove permissions that restrict either the resource or the source, so that COPY and UNLOAD queries accessing the resource are sent to the concurrency-scaling cluster.

   For more information about resource policies, see [Policy types](https://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies.html#access_policy-types) in the AWS Identity and Access Management user guide and [Controlling access from VPC endpoints with bucket policies](https://docs.aws.amazon.com/AmazonS3/latest/userguide/example-bucket-policies-vpc-endpoint.html).
+ Amazon Redshift concurrency scaling for write operations is not supported for most DDL operations, such as CREATE TABLE.
+ It doesn't support ANALYZE for the COPY command.
+ It doesn't support write operations on a target table where DISTSTYLE is set to ALL.
+ It doesn't support COPY from the following file formats:
  + Parquet
  + ORC
+ It doesn't support write operations on tables with identity columns.
+ Amazon Redshift supports concurrency scaling for write operations on only Amazon Redshift RA3 nodes. Concurrency scaling for write operations isn't supported on other node types.

## AWS Regions for concurrency scaling
<a name="concurrency-scaling-regions"></a>

With Amazon Redshift, you can use concurrency scaling to manage concurrent workload demands across Redshift clusters. This topic details in which regions you can use concurrency scaling with Amazon Redshift.

Concurrency scaling is available in these AWS Regions: 
+ US East (N. Virginia) Region (us-east-1)
+ US East (Ohio) Region (us-east-2)
+ US West (N. California) Region (us-west-1)
+ US West (Oregon) Region (us-west-2) 
+ Africa (Cape Town) Region (af-south-1)
+ Asia Pacific (Mumbai) Region (ap-south-1)
+ Asia Pacific (Hyderabad) Region (ap-south-2)
+ Asia Pacific (Seoul) Region (ap-northeast-2)
+ Asia Pacific (Osaka) Region (ap-northeast-3)
+ Asia Pacific (Singapore) Region (ap-southeast-1)
+ Asia Pacific (Sydney) Region (ap-southeast-2)
+ Asia Pacific (Jakarta) Region (ap-southeast-3)
+ Asia Pacific (Malaysia) Region (ap-southeast-5)
+ Asia Pacific (New Zealand) Region (ap-southeast-6)
+ Asia Pacific (Thailand) Region (ap-southeast-7)
+ Asia Pacific (Hong Kong) Region (ap-east-1)
+ Asia Pacific (Taipei) Region (ap-east-2)
+ Asia Pacific (Tokyo) Region (ap-northeast-1)
+ Canada (Central) Region (ca-central-1)
+ Canada West (Calgary) Region (ca-west-1)
+ China (Beijing) Region (cn-north-1)
+ China (Ningxia) Region (cn-northwest-1)
+ Europe (Frankfurt) Region (eu-central-1)
+ Europe (Ireland) Region (eu-west-1)
+ Europe (London) Region (eu-west-2)
+ Europe (Paris) Region (eu-west-3)
+ Europe (Stockholm) Region (eu-north-1) 
+ Europe (Zurich) Region (eu-central-2) 
+ Europe (Milan) Region (eu-south-1) 
+ Europe (Spain) Region (eu-south-2) 
+ Israel (Tel Aviv) Region (il-central-1) 
+ Middle East (Bahrain) Region (me-south-1) 
+ Mexico (Central) Region (mx-central-1)
+ South America (São Paulo) Region (sa-east-1)
+ AWS GovCloud (US-East)
+ AWS GovCloud (US-West)

## Concurrency scaling candidates
<a name="concurrency-scaling-candidates"></a>

With Amazon Redshift, you can scale out query processing to accelerate execution of concurrent queries. The following topic describes the criteria that Amazon Redshift uses to determine what queries to route to concurrency scaling.

Queries are routed to the concurrency scaling cluster only when the main cluster meets the following requirements:
+ EC2-VPC platform. 
+ Node type must be dc2.8xlarge, dc2.large, ra3.large, ra3.xlplus, ra3.4xlarge, or ra3.16xlarge. Concurrency scaling for write operations is supported on only Amazon Redshift RA3 nodes.
+ Maximum of 32 compute nodes for clusters with ra3.xlplus, ra3.4xlarge, or ra3.16xlarge node types. In addition, the number of nodes of the main cluster can't be larger than 32 nodes when the cluster was originally created. For example, even if a cluster currently has 20 nodes, but was originally created with 40, it does not meet the requirements for concurrency scaling. Conversely, if a DC2 cluster currently has 40 nodes, but was originally created with 20, it does meet the requirements for concurrency scaling.
+ Not a single-node cluster. 

## Configuring concurrency scaling queues
<a name="concurrency-scaling-queues"></a>

With Amazon Redshift, you can manage concurrency and system resources by configuring concurrency scaling. Concurrency scaling queues allow you to set limits on the number of queries or user sessions that can be executed concurrently. The following section provides instructions on how to enable concurrency scaling queues in Amazon Redshift, enabling you to effectively handle concurrent queries and user sessions.

You route queries to concurrency scaling clusters by enabling concurrency scaling in a workload manager (WLM) queue. To turn on concurrency scaling for a queue, set the **Concurrency Scaling mode** value to **auto**.

When the number of queries routed to a queue with concurrency scaling enabled exceeds the queue's concurrency capacity, whether the capacity is configured manually or determined automatically, eligible queries are sent to the concurrency scaling cluster. When queue slots become available on the main cluster, queries are routed to and run on the main cluster. As with any WLM queue, you route queries to a concurrency scaling queue based on user groups, or by labeling queries with query group labels, or according to matching conditions defined in [Assigning queries to queues](https://docs.aws.amazon.com/redshift/latest/dg/cm-c-executing-queries.html). You can also route queries by defining [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md). For example, you might route all queries that take longer than 5 seconds to a concurrency scaling queue. Keep in mind that queuing behavior can vary, depending on whether you're using automatic WLM or manual WLM. For more information, see [Implementing automatic WLM](https://docs.aws.amazon.com/redshift/latest/dg/automatic-wlm.html) or [Implementing manual WLM](https://docs.aws.amazon.com/redshift/latest/dg/cm-c-defining-query-queues.html).

The default number of concurrency scaling clusters is one. The number of concurrency scaling clusters that can be used is controlled by [max\$1concurrency\$1scaling\$1clusters](r_max_concurrency_scaling_clusters.md). 

## Monitoring concurrency scaling
<a name="concurrency-scaling-monitoring"></a>

With Amazon Redshift, you can monitor and manage concurrency scaling to optimize performance and cost efficiency for your data warehousing workloads. Concurrency scaling allows Amazon Redshift to automatically add additional cluster capacity when workload demands increase, and remove that capacity when demands decrease. The following section provides guidance on monitoring concurrency scaling for your Amazon Redshift clusters.

You can see whether a query is running on the main cluster or a concurrency scaling cluster by navigating to **Cluster** in the Amazon Redshift console and choosing a cluster. Then choose the **Query monitoring** tab and **Workload concurrency** to view information about running queries and queued queries.

To find execution times, query the STL\$1QUERY table and filter on the `concurrency_scaling_status` column. The following query compares the queue time and execution time for queries run on the concurrency scaling cluster and queries run on the main cluster.

```
SELECT w.service_class AS queue
, CASE WHEN q.concurrency_scaling_status = 1 THEN 'concurrency scaling cluster' ELSE 'main cluster' END as concurrency_scaling_status
, COUNT( * ) AS queries
, SUM( q.aborted ) AS aborted
, SUM( ROUND( total_queue_time::NUMERIC / 1000000,2) ) AS queue_secs
, SUM( ROUND( total_exec_time::NUMERIC / 1000000,2) ) AS exec_secs
FROM stl_query q
JOIN stl_wlm_query w
USING (userid,query)
WHERE q.userid > 1
AND q.starttime > '2019-01-04 16:38:00'
AND q.endtime < '2019-01-04 17:40:00'
GROUP BY 1,2
ORDER BY 1,2;
```

Adjust the `starttime` and `endtime` values according to your requirements.

## Concurrency scaling system views
<a name="concurrency-scaling-monitoring-system-views"></a>

With Amazon Redshift, you can use Concurrency scaling system views to monitor and manage concurrency scaling activity in your cluster. The following section describes querying these system views and interpreting the results to effectively leverage concurrency scaling in your Amazon Redshift environment.

A set of system views with the prefix SVCS provides details from the system log tables about queries on both the main and concurrency scaling clusters.

The following views have similar information as the corresponding STL views or SVL views: 
+ [SVCS\$1ALERT\$1EVENT\$1LOG](r_SVCS_ALERT_EVENT_LOG.md) 
+ [SVCS\$1COMPILE](r_SVCS_COMPILE.md) 
+ [SVCS\$1EXPLAIN](r_SVCS_EXPLAIN.md) 
+ [SVCS\$1PLAN\$1INFO](r_SVCS_PLAN_INFO.md) 
+ [SVCS\$1QUERY\$1SUMMARY](r_SVCS_QUERY_SUMMARY.md) 
+ [SVCS\$1STREAM\$1SEGS](r_SVCS_STREAM_SEGS.md) 

The following views are specific to concurrency scaling.
+ [SVCS\$1CONCURRENCY\$1SCALING\$1USAGE](r_SVCS_CONCURRENCY_SCALING_USAGE.md) 

For more information about concurrency scaling, see the following topics in the *Amazon Redshift Management Guide*.
+ [Viewing Concurrency Scaling Data](https://docs.aws.amazon.com/redshift/latest/mgmt/performance-metrics-concurrency-scaling.html) 
+ [Viewing Cluster Performance During Query Execution](https://docs.aws.amazon.com/redshift/latest/mgmt/performance-metrics-query-cluster.html) 
+ [Viewing Query Details](https://docs.aws.amazon.com/redshift/latest/mgmt/performance-metrics-query-execution-details.html) 

# Short query acceleration
<a name="wlm-short-query-acceleration"></a>

Short query acceleration (SQA) prioritizes selected short-running queries ahead of longer-running queries. SQA runs short-running queries in a dedicated space, so that SQA queries aren't forced to wait in queues behind longer queries. SQA only prioritizes queries that are short-running and are in a user-defined queue. With SQA, short-running queries begin running more quickly and users see results sooner. 

If you enable SQA, you can reduce workload management (WLM) queues that are dedicated to running short queries. In addition, long-running queries don't need to contend with short queries for slots in a queue, so you can configure your WLM queues to use fewer query slots. When you use lower concurrency, query throughput is increased and overall system performance is improved for most workloads. 

 [CREATE TABLE AS](r_CREATE_TABLE_AS.md) (CTAS) statements and read-only queries, such as [SELECT](r_SELECT_synopsis.md) statements, are eligible for SQA.

Amazon Redshift uses a machine learning algorithm to analyze each eligible query and predict the query's execution time. By default, WLM dynamically assigns a value for the SQA maximum runtime based on analysis of your cluster's workload. Alternatively, you can specify a fixed value of 1–20 seconds. If the query's predicted run time is less than the defined or dynamically assigned SQA maximum runtime and the query is waiting in a WLM queue, SQA separates the query from the WLM queues and schedules it for priority execution. If a query runs longer than the SQA maximum runtime, WLM moves the query to the first matching WLM queue based on the [WLM queue assignment rules](cm-c-wlm-queue-assignment-rules.md). Over time, predictions improve as SQA learns from your query patterns. 

SQA is enabled by default in the default parameter group and for all new parameter groups. To disable SQA in the Amazon Redshift console, edit the WLM configuration for a parameter group and deselect **Enable short query acceleration**. As a best practice, we recommend using a WLM query slot count of 15 or fewer to maintain optimum overall system performance. For information about modifying WLM configurations, see [Configuring Workload Management](https://docs.aws.amazon.com/redshift/latest/mgmt/workload-mgmt-config.html) in the *Amazon Redshift Management Guide*.

## Maximum runtime for short queries
<a name="wlm-sqa-max-run-time"></a>

When you enable SQA, WLM sets the maximum runtime for short queries to dynamic by default. We recommend keeping the dynamic setting for SQA maximum runtime. You can override the default setting by specifying a fixed value of 1–20 seconds.

In some cases, you might consider using different values for the SQA maximum runtime values to improve your system performance. In such cases, analyze your workload to find the maximum execution time for most of your short-running queries. The following query returns the maximum runtime for queries at about the 70th percentile. 

```
select least(greatest(percentile_cont(0.7) 
within group (order by total_exec_time / 1000000) + 2, 2), 20) 
from stl_wlm_query 
where userid >= 100
and final_state = 'Completed';
```

After you identify a maximum runtime value that works well for your workload, you don't need to change it unless your workload changes significantly.

## Monitoring SQA
<a name="wlm-monitoring-sqa"></a>

To check whether SQA is enabled, run the following query. If the query returns a row, then SQA is enabled.

```
select * from stv_wlm_service_class_config 
where service_class = 14;
```

The following query shows the number of queries that went through each query queue (service class). It also shows the average execution time, the number of queries with wait time at the 90th percentile, and the average wait time. SQA queries use in service class 14.

```
select final_state, service_class, count(*), avg(total_exec_time), 
percentile_cont(0.9) within group (order by total_queue_time), avg(total_queue_time) 
from stl_wlm_query where userid >= 100 group by 1,2 order by 2,1;
```

To find which queries were picked up by SQA and completed successfully, run the following query.

```
select a.queue_start_time, a.total_exec_time, label, trim(querytxt) 
from stl_wlm_query a, stl_query b 
where a.query = b.query and a.service_class = 14 and a.final_state = 'Completed' 
order by b.query desc limit 5;
```

To find queries that SQA picked up but that timed out, run the following query.

```
select a.queue_start_time, a.total_exec_time, label, trim(querytxt) 
from stl_wlm_query a, stl_query b 
where a.query = b.query and a.service_class = 14 and a.final_state = 'Evicted' 
order by b.query desc limit 5;
```

For more information about evicted queries and, more generally, rule-based actions that can be taken on queries, see [WLM query monitoring rules](cm-c-wlm-query-monitoring-rules.md).

# WLM queue assignment rules
<a name="cm-c-wlm-queue-assignment-rules"></a>

With Amazon Redshift, you can control the allocation of memory and CPU resources to user queries by defining queue assignment rules in a workload management (WLM) configuration. The following section describes creating and managing WLM queue assignment rules to achieve efficient resource allocation and meet service-level agreements for diverse workloads in Amazon Redshift.

When a user runs a query, WLM assigns the query to the first matching queue, based on the WLM queue assignment rules:

1. If a user is logged in as a superuser and runs a query in the query group labeled superuser, the query is assigned to the superuser queue.

1. If a user is part of a role, belongs to a listed user group, or runs a query within a listed query group, the query is assigned to the first matching queue.

1. If a query doesn't meet any criteria, the query is assigned to the default queue, which is the last queue defined in the WLM configuration.

The following diagram illustrates how these rules work.

![\[Flowchart illustrating the rules WLM uses to assign queries to queues.\]](http://docs.aws.amazon.com/redshift/latest/dg/images/queue-assignment-rules-with-rbac.png)


## Queue assignments example
<a name="cm-c-wlm-queue-assignment-rules-queue-assignments-example"></a>

The following table shows a WLM configuration with the superuser queue and four user-defined queues.

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-queue-assignment-rules.html)

The following illustration shows how queries are assigned to the queues in the previous table according to user groups and query groups. For information about how to assign queries to user groups and query groups at runtime, see [Assigning queries to queues](cm-c-executing-queries.md) later in this section.

![\[Sample list of queries assigned to queues according to user groups and query groups.\]](http://docs.aws.amazon.com/redshift/latest/dg/images/queues-assignment-2.png)


In this example, WLM makes the following assignments:

1. The first set of statements shows three ways to assign users to user groups. The statements are run by the user `adminuser`, which is not a member of a user group listed in any WLM queue. No query group is set, so the statements are routed to the default queue.

1. The user `adminuser` is a superuser and the query group is set to `'superuser'`, so the query is assigned to the superuser queue.

1. The user `test_user` is assigned the role `test_db_rw` listed in queue 1, so the query is assigned to queue 1.

1. The user `admin1` is a member of the user group listed in queue 1, so the query is assigned to queue 1.

1. The user `vp1` is not a member of any listed user group. The query group is set to `'QG_B'`, so the query is assigned to queue 2. 

1. The user `analyst1` is a member of the user group listed in queue 3, but `'QG_B'` matches queue 2, so the query is assigned to queue 2. 

1. The user `ralph` is not a member of any listed user group and the query group was reset, so there is no matching queue. The query is assigned to the default queue. 

# Assigning queries to queues
<a name="cm-c-executing-queries"></a>

With Amazon Redshift, you can manage workload concurrency and prioritize queries by assigning them to queues. Queues allow you to allocate resources like memory and CPU for different types of queries or users, ensuring critical queries get prioritized over less important ones. The following sections describe how to create queues, configure their properties, and assign incoming queries based on criteria you define.

The following examples assign queries to queues according to user roles, user groups, and query groups.

## Assigning queries to queues based on user roles
<a name="cm-c-executing-queries-assigning-queries-to-queues-based-on-user-roles"></a>

 If a user is assigned to a role and that role is attached to a queue, then queries run by that user are assigned to that queue. The following example creates a user role named `sales_rw` and assigns the user `test_user` to that role. 

```
create role sales_rw;
grant role sales_rw to test_user;
```

You can also combine permissions of two roles by explicitly granting one role to another role. Assigning a nested role to a user grants permissions of both roles to the user.

```
create role sales_rw;
create role sales_ro;
grant role sales_ro to role sales_rw;
grant role sales_rw to test_user;
```

To see the list of users that have been granted roles in the cluster, query the SVV\$1USER\$1GRANTS table. To see the list of roles that have been granted roles in the cluster, query the SVV\$1ROLE\$1GRANTS table.

```
select * from svv_user_grants;
select * from svv_role_grants;
```

## Assigning queries to queues based on user groups
<a name="cm-c-executing-queries-assigning-queries-to-queues-based-on-user-groups"></a>

If a user group name is listed in a queue definition, queries run by members of that user group are assigned to the corresponding queue. The following example creates user groups and adds users to groups by using the SQL commands [CREATE USER](r_CREATE_USER.md), [CREATE GROUP](r_CREATE_GROUP.md), and [ALTER GROUP](r_ALTER_GROUP.md).

```
create group admin_group with user admin246, admin135, sec555;
create user vp1234 in group ad_hoc_group password 'vpPass1234';
alter group admin_group add user analyst44, analyst45, analyst46;
```

## Assigning a query to a query group
<a name="cm-c-executing-queries-assigning-a-query-to-a-query-group"></a>

You can assign a query to a queue at runtime by assigning your query to the appropriate query group. Use the SET command to begin a query group.

```
SET query_group TO group_label             
```

Here, *`group_label`* is a query group label that is listed in the WLM configuration.

All queries that you run after the `SET query_group` command run as members of the specified query group until you either reset the query group or end your current login session. For information about setting and resetting Amazon Redshift objects, see [SET](r_SET.md) and [RESET](r_RESET.md) in the SQL Command Reference.

The query group labels that you specify must be included in the current WLM configuration; otherwise, the *SET query\$1group* command has no effect on query queues.

The label defined in the TO clause is captured in the query logs so that you can use the label for troubleshooting. For information about the query\$1group configuration parameter, see [query\$1group](r_query_group.md) in the Configuration Reference.

The following example runs two queries as part of the query group 'priority' and then resets the query group.

```
set query_group to 'priority';
select count(*)from stv_blocklist;
select query, elapsed, substring from svl_qlog order by query desc limit 5; 
reset query_group;
```

## Assigning queries to the superuser queue
<a name="cm-c-executing-queries-assigning-superuser-queue"></a>

To assign a query to the superuser queue, log on to Amazon Redshift as a superuser and then run the query in the superuser group. When you are done, reset the query group so that subsequent queries do not run in the superuser queue.

The following example assigns two commands to run in the superuser queue.

```
set query_group to 'superuser';

analyze;
vacuum; 
reset query_group;
```

To view a list of superusers, query the PG\$1USER system catalog table.

```
select * from pg_user where usesuper = 'true';
```

# WLM dynamic and static configuration properties
<a name="cm-c-wlm-dynamic-properties"></a>

The WLM configuration properties are either dynamic or static. You can apply dynamic properties to the database without a cluster reboot, but static properties require a cluster reboot for changes to take effect. However, if you change dynamic and static properties at the same time, then you must reboot the cluster for all the property changes to take effect. This is true whether the changed properties are dynamic or static. 

While dynamic properties are being applied, your cluster status is `modifying`. Switching between automatic WLM and manual WLM is a static change and requires a cluster reboot to take effect.

The following table indicates which WLM properties are dynamic or static when using automatic WLM or manual WLM.


****  

| WLM Property | Automatic WLM | Manual WLM | 
| --- | --- | --- | 
| Query groups | Dynamic | Static | 
| Query group wildcard | Dynamic | Static | 
| User groups | Dynamic | Static | 
| User group wildcard | Dynamic | Static | 
| User roles | Dynamic | Static | 
| User role wildcard | Dynamic | Static | 
| Concurrency on main | Not applicable | Dynamic | 
| Concurrency Scaling mode | Dynamic | Dynamic | 
| Enable short query acceleration | Not applicable | Dynamic | 
| Maximum runtime for short queries | Dynamic | Dynamic | 
| Percent of memory to use | Not applicable | Dynamic | 
| Timeout | Not applicable | Dynamic | 
| Priority | Dynamic | Not applicable | 
| Adding or removing queues | Dynamic  | Static | 

If you add a query monitoring rule (QMR), or modify or delete an existing QMR, the change happens automatically without the need to restart the cluster.

**Note**  
When using manual WLM, if the timeout value is changed, the new value is applied to any query that begins running after the value is changed. If the concurrency or percent of memory to use are changed, Amazon Redshift changes to the new configuration dynamically. Thus, currently running queries aren't affected by the change. For more information, see [WLM Dynamic Memory Allocation.](https://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-dynamic-memory-allocation.html)

**Topics**
+ [WLM dynamic memory allocation](cm-c-wlm-dynamic-memory-allocation.md)
+ [Dynamic WLM example](cm-c-wlm-dynamic-example.md)

# WLM dynamic memory allocation
<a name="cm-c-wlm-dynamic-memory-allocation"></a>

In each queue, WLM creates a number of query slots equal to the queue's concurrency level. The amount of memory allocated to a query slot equals the percentage of memory allocated to the queue divided by the slot count. If you change the memory allocation or concurrency, Amazon Redshift dynamically manages the transition to the new WLM configuration. Thus, active queries can run to completion using the currently allocated amount of memory. At the same time, Amazon Redshift ensures that total memory usage never exceeds 100 percent of available memory.

The workload manager uses the following process to manage the transition:

1. WLM recalculates the memory allocation for each new query slot. 

1. If a query slot is not actively being used by a running query, WLM removes the slot, which makes that memory available for new slots. 

1. If a query slot is actively in use, WLM waits for the query to finish. 

1. As active queries complete, the empty slots are removed and the associated memory is freed. 

1. As enough memory becomes available to add one or more slots, new slots are added. 

1. When all queries that were running at the time of the change finish, the slot count equals the new concurrency level, and the transition to the new WLM configuration is complete.

In effect, queries that are running when the change takes place continue to use the original memory allocation. Queries that are queued when the change takes place are routed to new slots as they become available. 

If the WLM dynamic properties are changed during the transition process, WLM immediately begins to transition to the new configuration, starting from the current state. To view the status of the transition, query the [STV\$1WLM\$1SERVICE\$1CLASS\$1CONFIG](r_STV_WLM_SERVICE_CLASS_CONFIG.md) system table. 

# Dynamic WLM example
<a name="cm-c-wlm-dynamic-example"></a>

With Amazon Redshift, you can automatically manage workload distribution and resource allocation across your Amazon Redshift clusters using Dynamic WLM (Workload Management). Dynamic WLM is an example of a Workload Management (WLM) configuration that dynamically adjusts memory allocations based on workload demands, allowing for optimal concurrency and performance. The following section provides details on implementing and configuring Dynamic WLM for your Amazon Redshift clusters.

Suppose that your cluster WLM is configured with two queues, using the following dynamic properties. 

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-dynamic-example.html)

Now suppose that your cluster has 200 GB of memory available for query processing. (This number is arbitrary and used for illustration only.) As the following equation shows, each slot is allocated 25 GB. 

```
(200 GB * 50% ) / 4 slots  = 25 GB
```

Next, you change your WLM to use the following dynamic properties.

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-dynamic-example.html)

As the following equation shows, the new memory allocation for each slot in queue 1 is 50 GB. 

```
(200 GB * 75% ) / 3 slots = 50 GB 
```

Suppose that queries A1, A2, A3, and A4 are running when the new configuration is applied, and queries B1, B2, B3, and B4 are queued. WLM dynamically reconfigures the query slots as follows. 

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-dynamic-example.html)

1. WLM recalculates the memory allocation for each query slot. Originally, queue 1 was allocated 100 GB. The new queue has a total allocation of 150 GB, so the new queue immediately has 50 GB available. Queue 1 is now using four slots, and the new concurrency level is three slots, so no new slots are added. 

1. When one query finishes, the slot is removed and 25 GB is freed. Queue 1 now has three slots and 75 GB of available memory. The new configuration needs 50 GB for each new slot, but the new concurrency level is three slots, so no new slots are added. 

1. When a second query finishes, the slot is removed, and 25 GB is freed. Queue 1 now has two slots and 100 GB of free memory. 

1. A new slot is added using 50 GB of the free memory. Queue 1 now has three slots, and 50 GB free memory. Queued queries can now be routed to the new slot. 

1. When a third query finishes, the slot is removed, and 25 GB is freed. Queue 1 now has two slots, and 75 GB of free memory. 

1. A new slot is added using 50 GB of the free memory. Queue 1 now has three slots, and 25 GB free memory. Queued queries can now be routed to the new slot. 

1. When the fourth query finishes, the slot is removed, and 25 GB is freed. Queue 1 now has two slots and 50 GB of free memory. 

1. A new slot is added using the 50 GB of free memory. Queue 1 now has three slots with 50 GB each and all available memory has been allocated. 

The transition is complete and all query slots are available to queued queries.

# WLM query monitoring rules
<a name="cm-c-wlm-query-monitoring-rules"></a>

In Amazon Redshift workload management (WLM), query monitoring rules define metrics-based performance boundaries for WLM queues and specify what action to take when a query goes beyond those boundaries. For example, for a queue dedicated to short running queries, you might create a rule that cancels queries that run for more than 60 seconds. To track poorly designed queries, you might have another rule that logs queries that contain nested loops. 

You define query monitoring rules as part of your workload management (WLM) configuration. You can define up to 25 rules for each queue, with a limit of 25 rules for all queues. Each rule includes up to three conditions, or predicates, and one action. A *predicate* consists of a metric, a comparison condition (=, <, or > ), and a value. If all of the predicates for any rule are met, that rule's action is triggered. Possible rule actions are log, hop, and abort, as discussed following. 

The rules in a given queue apply only to queries running in that queue. A rule is independent of other rules. 

WLM evaluates metrics every 10 seconds. Amazon Redshift applies query monitoring rules at the child query level when queries are automatically rewritten. If more than one rule is triggered during the same period, WLM chooses the rule with the most severe action. If the action for two rules has the same severity, WLM runs the rules in alphabetical order, based on the rule name. If the action is hop or abort, the action is logged and the query is evicted from the queue. If the action is log, the query continues to run in the queue. WLM initiates only one log action per query per rule. If the queue contains other rules, those rules remain in effect. If the action is hop and the query is routed to another queue, the rules for the new queue apply. For more information about query monitoring and tracking actions taken on specific queries, see the collection of samples at [Short query acceleration](wlm-short-query-acceleration.md).

When all of a rule's predicates are met, WLM writes a row to the [STL\$1WLM\$1RULE\$1ACTION](r_STL_WLM_RULE_ACTION.md) system table. In addition, Amazon Redshift records query metrics for currently running queries to [STV\$1QUERY\$1METRICS](r_STV_QUERY_METRICS.md). Metrics for completed queries are stored in [STL\$1QUERY\$1METRICS](r_STL_QUERY_METRICS.md). 

**Note**  
For Amazon Redshift Serverless, you can configure query queues and monitoring rules using the `wlm_json_configuration` parameter. This allows you to create multiple queues with different user roles, query groups, and monitoring rules. For more information about configuring serverless query queues, see [Setting query queues](https://docs.aws.amazon.com/redshift/latest/mgmt/serverless-workgroup-query-queues.html) in the *Amazon Redshift Management Guide*.

## Defining a query monitoring rule
<a name="cm-c-wlm-defining-query-monitoring-rules"></a>

You create query monitoring rules as part of your WLM configuration, which you define as part of your cluster's parameter group definition.

You can create rules using the AWS Management Console or programmatically using JSON. 

**Note**  
If you choose to create rules programmatically, we strongly recommend using the console to generate the JSON that you include in the parameter group definition. For more information, see [Creating a query monitoring rule](https://docs.aws.amazon.com/redshift/latest/mgmt/parameter-group-modify-qmr-console.html) and [Configuring Parameter Values Using the AWS CLI](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-parameter-groups.html#configure-parameters-using-the-cli) in the *Amazon Redshift Management Guide*. 

To define a query monitoring rule, you specify the following elements:
+ A rule name – Rule names must be unique within the WLM configuration. Rule names can be up to 32 alphanumeric characters or underscores, and can't contain spaces or quotation marks. You can have up to 25 rules per queue, and the total limit for all queues is 25 rules.
+ One or more predicates – You can have up to three predicates per rule. If all the predicates for any rule are met, the associated action is triggered. A predicate is defined by a metric name, an operator ( =, <, or > ), and a value. An example is `query_cpu_time > 100000`. For a list of metrics and examples of values for different metrics, see [Query monitoring metrics for Amazon Redshift provisioned](#cm-c-wlm-query-monitoring-metrics) following in this section. 
+ An action – If more than one rule is triggered, WLM chooses the rule with the most severe action. Possible actions, in ascending order of severity, are:
  + Log – Record information about the query in the STL\$1WLM\$1RULE\$1ACTION system table. Use the Log action when you want to only write a log record. WLM creates at most one log per query, per rule. Following a log action, other rules remain in force and WLM continues to monitor the query. 
  + Hop (only available with manual WLM) – Log the action and hop the query to the next matching queue. If there isn't another matching queue, the query is canceled. QMR hops only [CREATE TABLE AS](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_AS.html) (CTAS) statements and read-only queries, such as SELECT statements. For more information, see [WLM query queue hopping](wlm-queue-hopping.md). 
  + Abort – Log the action and cancel the query. QMR doesn't stop COPY statements and maintenance operations, such as ALTER, ANALYZE and VACUUM. 
  + Change priority (only available with automatic WLM) – Change the priority of a query. 

To limit the runtime of queries, we recommend creating a query monitoring rule instead of using WLM timeout. For example, you can set `max_execution_time` to 50,000 milliseconds as shown in the following JSON snippet.

```
"max_execution_time": 50000
```

But we recommend instead that you define an equivalent query monitoring rule. The following example demonstrates a query monitoring rule that sets `query_execution_time` to 50 seconds:

```
"rules": 
[
    {
        "rule_name": "rule_query_execution",
        "predicate": [
            {
                "metric_name": "query_execution_time",
                "operator": ">",
                "value": 50
            }
        ],
        "action": "abort"
    }            
]
```

For steps to create or modify a query monitoring rule, see [Creating a query monitoring rule](https://docs.aws.amazon.com/redshift/latest/mgmt/parameter-group-modify-qmr-console.html) and [Properties in the wlm\$1json\$1configuration Parameter](https://docs.aws.amazon.com/redshift/latest/mgmt/workload-mgmt-config.html#wlm-json-config-properties) in the *Amazon Redshift Management Guide*.

You can find more information about query monitoring rules in the following topics: 
+  [Query monitoring metrics for Amazon Redshift provisioned](#cm-c-wlm-query-monitoring-metrics) 
+  [Query monitoring rules templates](#cm-c-wlm-query-monitoring-templates) 
+  [Creating a query monitoring rule](https://docs.aws.amazon.com/redshift/latest/mgmt/parameter-group-modify-qmr-console.html) 
+  [Configuring Workload Management](https://docs.aws.amazon.com/redshift/latest/mgmt/workload-mgmt-config.html) 
+  [System tables and views for query monitoring rules](#cm-c-wlm-qmr-tables-and-views) 

## Query monitoring metrics for Amazon Redshift provisioned
<a name="cm-c-wlm-query-monitoring-metrics"></a>

The following table describes the metrics used in query monitoring rules. (These metrics are distinct from the metrics stored in the [STV\$1QUERY\$1METRICS](r_STV_QUERY_METRICS.md) and [STL\$1QUERY\$1METRICS](r_STL_QUERY_METRICS.md) system tables.) 

For a given metric, the performance threshold is tracked either at the query level or the segment level. For more information about segments and steps, see [Query planning and execution workflow](c-query-planning.md).

**Note**  
The [WLM timeout](cm-c-defining-query-queues.md#wlm-timeout) parameter is distinct from query monitoring rules.

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-query-monitoring-rules.html)

**Note**  
The hop action is not supported with the `query_queue_time` predicate. That is, rules defined to hop when a `query_queue_time` predicate is met are ignored. 
Short segment execution times can result in sampling errors with some metrics, such as `io_skew` and `query_cpu_usage_percent`. To avoid or reduce sampling errors, include segment execution time in your rules. A good starting point is `segment_execution_time > 10`.

The [SVL\$1QUERY\$1METRICS](r_SVL_QUERY_METRICS.md) view shows the metrics for completed queries. The [SVL\$1QUERY\$1METRICS\$1SUMMARY](r_SVL_QUERY_METRICS_SUMMARY.md) view shows the maximum values of metrics for completed queries. Use the values in these views as an aid to determine threshold values for defining query monitoring rules.

## Query monitoring metrics for Amazon Redshift Serverless
<a name="cm-c-wlm-query-monitoring-metrics-serverless"></a>

The following table describes the metrics used in query monitoring rules for Amazon Redshift Serverless. 

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-query-monitoring-rules.html)

**Note**  
The hop action is not supported with the `max_query_queue_time` predicate. That is, rules defined to hop when a `max_query_queue_time` predicate is met are ignored. 
Short segment execution times can result in sampling errors with some metrics, such as `max_io_skew` and `max_query_cpu_usage_percent`.

For Amazon Redshift Serverless, you can configure query queues and monitoring rules using the `wlm_json_configuration` parameter. This allows you to create multiple queues with different user roles, query groups, and monitoring rules using the metrics listed above. For more information about configuring serverless query queues, see [WLM JSON configuration structure](https://docs.aws.amazon.com/redshift/latest/mgmt/serverless-workgroup-query-queues.html#serverless-wlm-json-configuration) in the *Amazon Redshift Management Guide*.

## Query monitoring rules templates
<a name="cm-c-wlm-query-monitoring-templates"></a>

When you add a rule using the Amazon Redshift console, you can choose to create a rule from a predefined template. Amazon Redshift creates a new rule with a set of predicates and populates the predicates with default values. The default action is log. You can modify the predicates and action to meet your use case. 

The following table lists available templates. 

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-query-monitoring-rules.html)

## System tables and views for query monitoring rules
<a name="cm-c-wlm-qmr-tables-and-views"></a>

When all of a rule's predicates are met, WLM writes a row to the [STL\$1WLM\$1RULE\$1ACTION](r_STL_WLM_RULE_ACTION.md) system table. This row contains details for the query that triggered the rule and the resulting action. 

In addition, Amazon Redshift records query metrics the following system tables and views.
+ The [STV\$1QUERY\$1METRICS](r_STV_QUERY_METRICS.md) table displays the metrics for currently running queries.
+ The [STL\$1QUERY\$1METRICS](r_STL_QUERY_METRICS.md) table records the metrics for completed queries. 
+ The [SVL\$1QUERY\$1METRICS](r_SVL_QUERY_METRICS.md) view shows the metrics for completed queries. 
+ The [SVL\$1QUERY\$1METRICS\$1SUMMARY](r_SVL_QUERY_METRICS_SUMMARY.md) view shows the maximum values of metrics for completed queries.

# WLM system tables and views
<a name="cm-c-wlm-system-tables-and-views"></a>

WLM configures query queues according to WLM service classes, which are internally defined. Amazon Redshift creates several internal queues according to these service classes along with the queues defined in the WLM configuration. The terms *queue* and *service class* are often used interchangeably in the system tables. The superuser queue uses service class 5. User-defined queues use service class 6 and greater.

You can view the status of queries, queues, and service classes by using WLM-specific system tables. Query the following system tables to do the following:
+ View which queries are being tracked and what resources are allocated by the workload manager.
+ See which queue a query has been assigned to.
+ View the status of a query that is currently being tracked by the workload manager.

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-system-tables-and-views.html)

 You use the task ID to track a query in the system tables. The following example shows how to obtain the task ID of the most recently submitted user query: 

```
select task from stl_wlm_query where exec_start_time =(select max(exec_start_time) from stl_wlm_query); 

task 
------ 
137 
(1 row)
```

 The following example displays queries that are currently executing or waiting in various service classes (queues). This query is useful in tracking the overall concurrent workload for Amazon Redshift: 

```
select * from stv_wlm_query_state order by query;


xid |task|query|service_| wlm_start_  |  state  |queue_ | exec_
    |    |     |class   | time        |         |time   | time
----+----+-----+--------+-------------+---------+-------+--------
2645| 84 | 98  | 3      | 2010-10-... |Returning|   0   | 3438369
2650| 85 | 100 | 3      | 2010-10-... |Waiting  |   0   | 1645879
2660| 87 | 101 | 2      | 2010-10-... |Executing|   0   | 916046
2661| 88 | 102 | 1      | 2010-10-... |Executing|   0   | 13291
(4 rows)
```

## WLM service class IDs
<a name="wlm-service-class-ids"></a>

The following table lists the IDs assigned to service classes.

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-system-tables-and-views.html)