CODEWITHSUNDEEP
amazon-redshift
Sanjewa Ranasinghe
Sanjewa Ranasinghe

Reputation: 15

How to check the current Queue slot concurrency level in redshift

I need to know the current slot count value in redshift Queue configuration for a specific redshift user. Is there any system table in redshift which provide this user level information.

Upvotes: 0

Views: 2046

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

You can find a list of the internal tables at "WLM System Tables and Views" http://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-system-tables-and-views.html

This query summarizes things:

SELECT
      wlm.service_class                      queue
    , TRIM( wlm.name )                       queue_name
    , LISTAGG( TRIM( cnd.condition ), ', ' ) condition
    , wlm.num_query_tasks                    query_concurrency
    , wlm.query_working_mem                  per_query_memory_mb
    , ROUND(((wlm.num_query_tasks * wlm.query_working_mem)::NUMERIC / mem.total_mem::NUMERIC) * 100, 0)::INT  cluster_memory_pct
    , wlm.max_execution_time
    , wlm.user_group_wild_card
    , wlm.query_group_wild_card
FROM stv_wlm_service_class_config wlm
JOIN stv_wlm_classification_config cnd ON wlm.service_class = cnd.action_service_class
CROSS JOIN (SELECT SUM( num_query_tasks * query_working_mem ) total_mem
            FROM pg_catalog.stv_wlm_service_class_config
            WHERE service_class > 5) mem
WHERE wlm.service_class > 5
GROUP BY wlm.service_class, TRIM( wlm.name ), wlm.num_query_tasks, wlm.query_working_mem, mem.total_mem,
    wlm.max_execution_time, wlm.user_group_wild_card, wlm.query_group_wild_card
ORDER BY 1
;

Output

 queue |    queue_name    |    condition     | query_concurrency | per_query_memory_mb | cluster_memory_pct | max_execution_time | user_group_wild_card | query_group_wild_card
-------+------------------+------------------+-------------------+---------------------+--------------------+--------------------+----------------------+-----------------------
     6 | Service class #1 | (querytype: any) |                 5 |                1208 |                100 |                  0 | false                | false
(1 row)

Upvotes: 2

Related Questions