Defining Query Queues
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
configure WLM properties for each query queue to specify the way that memory is allocated
among slots, how queries can be routed to specific queues at run time, and when to cancel
long-running queries. You can also use the
which is separate from the WLM properties, to temporarily enable queries to use more memory
by allocating multiple slots.
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. You should only use this queue 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. You should not 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
One default user queue.
The default queue is initially configured to run five queries concurrently. 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
wlm_json_configuration) in a parameter group, which can be
associated with one or more clusters. For more information, see Modifying 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:
WLM memory percent to use
Query Monitoring Rules
Queries in a queue run concurrently until they reach
the concurrency level defined for that queue. Subsequent queries
then wait in the queue. Each queue can be configured to run up to 50 queries
concurrently. The maximum total concurrency level for all user-defined queues is 50. The
limit includes the default queue, but doesn't include the reserved Superuser queue.
Amazon Redshift allocates, by default, an equal, fixed share of available memory to each queue,
and an equal, fixed share of a queue's memory to each query slot in the queue. The
proportion of memory allocated to each queue is defined in the WLM configuration using
memory_percent_to_use property. At run time, you can temporarily
override the amount of memory assigned to a query by setting
wlm_query_slot_count parameter to specify the number of slots
allocated to the query.
By default, 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 concurrency level 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.
If you have multiple queries that each access data on a single slice, set up a separate WLM queue to execute those queries concurrently. Amazon Redshift will assign concurrent queries to separate slices, which allows multiple queries to execute 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 will be located on a single slice.
As a best practice, we recommend using a concurrency level of 15 or lower. All of the compute nodes in a cluster, and all of the slices on the nodes, participate in parallel query execution. By increasing concurrency, you increase the contention for system resources and limit the overall throughput.
The memory that is allocated to each queue is divided among the query slots in that queue. The amount of memory available to a query is the memory allocated to the query slot in which the query is running, regardless of the number of queries that are actually running concurrently. A query that can run entirely in memory when the concurrency level is 5 might need to write intermediate results to disk if the concurrency level is increased to 20. The additional disk I/O could degrade performance.
If a specific query needs more memory than is allocated to a single query slot, you
can increase the available memory by increasing the wlm_query_slot_count parameter.
The following example sets
wlm_query_slot_count to 10, performs a
vacuum, and then resets
wlm_query_slot_count to 1.
set wlm_query_slot_count to 10; vacuum; set wlm_query_slot_count to 1;
For more information, see Improving Query Performance.
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 Wildcards
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 run time, you can assign the query group label to a series of queries. Any queries that are assigned to a listed query group will 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 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 '*' wildcard character
matches any number of characters, so if you add
dba_* to the list of user
groups for a queue, then any query that is run by a user that belongs to a group with a
name that begins with
dba_, such as
DBA_primary, is assigned to that queue. The '?' wildcard character
matches any single character, so if the queue includes user-group
then user groups named
dba21 would match, but
dba12 would not match. Wildcards are disabled by default.
WLM Memory Percent to Use
To specify the amount of available memory that is allocated to a query, you can set
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.
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 execute before canceling the query. The timeout is based on query execution time and doesn't include time spent waiting in a queue.
The function of WLM timeout is similar to the statement_timeout configuration parameter, except that, where the
statement_timeout configuration parameter applies to the
entire cluster, WLM timeout is specific to a single queue in the WLM configuration.
WLM Query Queue Hopping
If a read-only query, such as a SELECT statement, is canceled due to a WLM timeout, WLM attempts to route the query to the next matching queue based on the WLM Queue Assignment Rules. If the query doesn't match any other queue definition, the query is canceled; it is not assigned to the default queue. A user-defined function (UDF) or any query that writes to the database cannot be rerouted and is simply canceled. Such queries include data manipulation language (DML) statements, data definition language (DDL) statements, and commands that change the database, such as VACUUM.
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 aborts 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.