WLM System Tables and Views
WLM configures query queues according to internally-defined WLM service classes. Amazon Redshift creates several internal queues according to these service classes along with the queues defined in the WLM configuration. The terms queue and service class are often used interchangeably in the system tables. The superuser queue uses service class 5. User-defined queues use service class 6 and greater.
You can view the status of queries, queues, and service classes by using WLM-specific system tables. Query the following system tables to do the following:
View which queries are being tracked and what resources are allocated by the workload manager.
See which queue a query has been assigned to.
View the status of a query that is currently being tracked by the workload manager.
|STL_WLM_ERROR||Contains a log of WLM-related error events.|
|STL_WLM_QUERY||Lists queries that are being tracked by WLM.|
|STV_WLM_CLASSIFICATION_CONFIG||Shows the current classification rules for WLM.|
|STV_WLM_QUERY_QUEUE_STATE||Records the current state of the query queues.|
|STV_WLM_QUERY_STATE||Provides a snapshot of the current state of queries that are being tracked by WLM.|
|STV_WLM_QUERY_TASK_STATE||Contains the current state of query tasks.|
|STV_WLM_SERVICE_CLASS_CONFIG||Records the service class configurations for WLM.|
|STV_WLM_SERVICE_CLASS_STATE||Contains the current state of the service classes.|
You use the task ID to track a query in the system tables. The following example shows how to obtain the task ID of the most recently submitted user query:
The following example displays queries that are currently executing or waiting in various service classes (queues). This query is useful in tracking the overall concurrent workload for Amazon Redshift:Copy
select task from stl_wlm_query where exec_start_time =(select max(exec_start_time) from stl_wlm_query); task ------ 137 (1 row)
select * from stv_wlm_query_state order by query; xid |task|query|service_| wlm_start_ | state |queue_ | exec_ | | |class | time | |time | time ----+----+-----+--------+-------------+---------+-------+-------- 2645| 84 | 98 | 3 | 2010-10-... |Returning| 0 | 3438369 2650| 85 | 100 | 3 | 2010-10-... |Waiting | 0 | 1645879 2660| 87 | 101 | 2 | 2010-10-... |Executing| 0 | 916046 2661| 88 | 102 | 1 | 2010-10-... |Executing| 0 | 13291 (4 rows)