WLM query queue hopping - Amazon Redshift

WLM query queue hopping

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 or a query monitoring rule (QMR) hop action. 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. 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

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

Query type Action
INSERT, UPDATE, and DELETE Cancel
User-defined functions (UDFs) Cancel
UNLOAD Cancel
COPY Continue execution
Maintenance operations Continue execution
Read-only queries in a returning state Continue execution
Read-only queries in a running state Reassign or restart
CREATE TABLE AS (CTAS), SELECT INTO Reassign or restart

WLM timeout queue hopping

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_WLM_QUERY_STATE 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 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_WLM_QUERY_STATE 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

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_query_slot_count 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

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

Query type Action
COPY Continue execution
Maintenance operations Continue execution
User-defined functions (UDFs) Continue execution
UNLOAD Reassign or continue execution
INSERT, UPDATE, and DELETE Reassign or continue execution
Read-only queries in a returning state Reassign or continue execution
Read-only queries in a running state Reassign or restart
CREATE TABLE AS (CTAS), SELECT INTO Reassign or restart

To find whether a query that was hopped by QMR was reassigned, restarted, or canceled, query the STL_WLM_RULE_ACTION system log table.

QMR hop action reassigned and restarted queries

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