Amazon Redshift
Database Developer Guide (API Version 2012-12-01)
« PreviousNext »
View the PDF for this guide.Go to the AWS Discussion Forum for this product.Did this page help you?  Yes | No |  Tell us about it...

Defining Query Queues

By default, a cluster is configured with one queue that can run five queries concurrently. In addition, Amazon Redshift reserves one dedicated Superuser queue on the cluster, which has a concurrency level of one. The Superuser queue is not configurable.

Note

The primary purpose of the Superuser queue is to aid in troubleshooting. You should not use it to perform routine queries.

You can modify the WLM configuration for a cluster to define up to eight query queues in addition to the Superuser queue.

Amazon Redshift allocates an equal share of server memory, by default, to each queue defined in the WLM configuration. To configure the proportion of memory used by query queues, you can set the WLM Percent to Use parameter. For more information, see WLM Memory Percent to Use.

At runtime, Amazon Redshift assigns queries to queues based on 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.

For each queue, you specify

  • Concurrency level

  • User groups

  • Query groups

  • Wildcards

  • WLM memory percent to use

  • WLM timeout

Concurrency Level

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, not including the reserved Superuser queue, is 50. Amazon Redshift allocates an equal, fixed share of server memory to each queue, and, by default, an equal, fixed share of a queue's memory to each query slot in the queue.

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 Managing How Queries Use Memory.

User Groups

You can assign a comma-separated list of user groups to a queue. 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.

Query Groups

You can assign a comma-separated list of query groups for each queue. 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 will run in the corresponding queue. There is no set limit to the number of query groups that can be assigned to a queue.

Wildcards

You can assign user groups and query groups to a queue either individually or by using wildcards. For example, if you add 'dba_*' to the list of user groups for a queue, any query that is run by a user with a user name that begins with 'dba_' is assigned to that queue. Wildcards are disabled by default. To enable a queue to use wildcards through the AWS Management Console, select the Enable User Group Wildcards check box or the Enable Query Group Wildcards check box on the Workload Management Configuration tab. To enable wildcards through the API or CLI, set the query_group_wild_card value or the user_group_wild_card value to 1.

WLM Memory Percent to Use

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% of the available memory. The superuser queue has its own allocated memory and cannot be modified. You can assign an integer percentage of memory to each queue, up to a total of 100%. If you attempt to assign more than 100%, the WLM configuration is rejected. If you specify a WLM Memory Percent to Use value for one or more queues but not all of the queues, then the unallocated memory is divided evenly among the remaining queues.

For example, if you configure four queues and assign 30% and 40% to two queues, then 30% is left for the other two queues. The result is that queues are allocated as follows: 30%, 40%, 15%, 15%. If you allocate memory to all of the queues and the total allocation is less than 100%, then the remainder is allocated evenly to all of the queues.

To set the WLM Memory Percent to Use parameter by using the AWS Management Console, go to the Workload Management Configuration tab and specify an integer in the WLM Memory Percent to Use field for each queue. To set the WLM Memory Percent to Use value by using the API or CLI, use the memory_percent_to_use parameter. For information about modifying parameter groups, see Amazon Redshift Parameter Groups.

WLM Timeout

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 will wait for a query in a queue to execute before canceling the query.

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.

To set the WLM timeout parameter by using the AWS Management Console, go to the Workload Management Configuration tab and specify a number of milliseconds in the Timeout field for each queue. Specify 0 or leave the field blank to disable WLM timeout. WLM timeout is disabled by default.

To set the WLM timeout value by using the API or CLI, use the max_execution_time parameter. To set the statement_timeout configuration parameter for a cluster, modify the Parameter Group configuration for the cluster. For information about modifying parameter groups, see Amazon Redshift Parameter Groups

If both WLM timeout (max_execution_time) and statement_timeout are specified, the shorter timeout is used.

Default Queue

The last queue defined in the WLM configuration is the default queue. You can set the concurrency level and the timeout for the default queue, but it cannot include user groups or query groups. The default queue counts against the limit of eight query queues and the limit of 50 concurrent queries.

Superuser Queue

To run a query in the Superuser queue, a user must be logged in as a superuser and must run the query within the predefined 'superuser' query group.

To view a list of superusers, query the PG_USER system catalog table.

select * from pg_user where usesuper = 'true';