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/).
# Implementing automatic WLM
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
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
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
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
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
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
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
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
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
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
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
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
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).