My Amazon Redshift query has a WLM timeout set, but the query keeps running after this period expires. Why?

The WLM timeout applies to queries only during the execution phase. When it appears that WLM failed to terminate the query within the prescribed time limit, the failure is usually because the query has spent some time in stages other than the execution stage. For example, the query might wait to be parsed or rewritten, wait on a lock, wait for a spot in the WLM queue, hit the return stage, or hop to another queue.

When querying STV_RECENTS, starttime is the time the query entered the cluster, not the time that the query began executing. When the query is in the Running state in STV_RECENTS, it is live in the system, but it does not use any compute node resources until the query enters into the STV_INFLIGHT status. For more information about query planning, see Query Planning and Execution Workflow. To view the status of an executing query, query STV_INFLIGHT instead of STV_RECENTS:

select * from STV_INFLIGHT where query = <queryid>;

Use this query for more information about query stages:

select * from SVL_QUERY_REPORT where query = <queryid> ORDER BY segment, step, slice;

Use this query for the current execution state of the query:

select * from STV_EXEC_STATE where query = <queryid> ORDER BY segment, step, slice;

Here are some common reasons why a query will appear to run longer than the WLM timeout period:

The query is in the "return" phase

There are two "return" steps. Check STV_EXEC_STATE to see if the query has entered one of these return phases:

  • The return to the leader node from the compute nodes
  • The return to the client from the leader node

A rollback is in progress

If a DML operation (insert, update, delete) is performed, but the operation encounters an error and must roll back, the operation will not appear to be killed because it is already in the process of rolling back. You can view rollbacks by querying STV_EXEC_STATE. You can find additional information in STL_UNDONE.

The query spends time queuing prior to execution

You can find queuing time in STV_WLM_QUERY_STATE:

select * from STV_WLM_QUERY_STATE where query = <queryid>;

The query is waiting on a lock

If the query is visible in STV_RECENTS, but not in STV_WLM_QUERY_STATE, it's possible that the query is waiting on a lock and hasn't yet entered the queue. Use a query similar to the following to find any locks the stuck process is waiting on:

select * from SVV_TRANSACTIONS

where granted='f'

and pid in (select pid from STV_RECENTS where query = <stuckqueryid>);

Use a query similar to the following to help identify the blocking process:

select b.* from SVV_TRANSACTIONS b, SVV_TRANSACTIONS w

where b.granted='t'

and w.granted = 'f'

and b.txn_db = w.txn_db

and b.relation = w.relation

and w.pid in (select pid from STV_RECENTS where query = <stuckqueryid>);

To find more information about your possible blocking pid (such as the query it's currently running), run queries similar to the following:

select * from STL_QUERY where pid = <pid of process suspected of blocking your stuck query>;

select * from SVL_STATEMENTTEXT where pid = <pid of process suspected of blocking your stuck query>;

To kill the stuck query, or to kill the process that you suspect is blocking it, run a query similar to the following:

select pg_terminate_backend(pid);

A query hopped to another queue

If a read query reaches the timeout limit for its current WLM queue or there is a query monitoring rule that specifies a hop action, the query is pushed to the next WLM queue for execution. You can confirm whether or not the query was hopped by using STV_WLM_QUERY_STATE while the query is running and by using STL_WLM_QUERY after the query has finished execution. To prevent queries from hopping to another queue, you can configure WLM Query Monitoring Rules to abort the actions in WLM queues. For more information about query queue hopping, see WLM Query Queue Hopping.

A networking or firewall issue

If an Amazon Redshift server has a problem communicating with your client, the server can get stuck in the "return to client" state discussed above. Check for conflicts with any networking components that can block traffic to your server, including inbound on-premises firewall settings, outbound security group rules, or outbound network ACL rules. For more information, see Connecting from Outside of Amazon EC2 —Firewall Timeout Issue.

An issue with the cluster

Issues on the cluster itself, such as hardware issues, might cause the query to hang, though these are rare. Check the status of the cluster in the Amazon Redshift console for the hardware-failure state. If your cluster is in this state, and it is a single-node cluster, recover your cluster from a snapshot. On a multi-node cluster, the failed node should be replaced automatically.


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center.

Published: 2017-04-11

Updated: 2018-02-26