We have WLM queue configurations in the parameter groups for multiple clusters that have "*" wild card matches on user groups. To the best of our understanding this configuration should match all queries that don't match a prior queue (i.e. with a lower service class). Yet we're still seeing queries getting dispatched to the DEFAULT queue without transiting a prior queue first.
What if a user is not in a user group? (that's a thing, I think? normal state for a new user? or is there a default group?)
Could you post the content of your WLM_QUEUE_STATE_VW view?
The default queue I think can't be deleted. If you're (intending at least) capturing all queries prior to the default queue, aren't you wasting the memory assigned to its slots?
You nailed it. It was users with no group membership that were leaking into the DEFAULT queue.
Here's a pretty good little query I just wrote to prove it. It lists user/group assignments, including none (NULL):
select u.usesysid, u.usename, g.groname
from pg_user u
left outer join pg_group g on u.usesysid = ANY(g.grolist)
order by g.groname
You rightly question wasting resources assigned to the DEFAULT queue. Because we can't delete it and intend for nothing to run in it we assign minimal resource to it. We assign 1 slot because we can't delete the queue, but we also assign minuscule memory to it also so we don't detract from the slots in the other queues.
Since you asked, here's the augmented query we use instead of the WLM_QUEUE_STATE_VW view in the doc. We feel it's a bit more descriptive for us and allows us to get by the lack of customer assigned names for configured queues and the silly queue number versus service class number mess :
select (config.service_class-5) as q
, config.service_class as sc
, case when config.service_class = 1 then 'fleet health'
when config.service_class = 2 then 'fleet metrics'
when config.service_class = 3 then 'fleet stats'
when config.service_class = 4 then 'RS superuser'
when config.service_class = 5 then 'CUST superuser'
when config.service_class = 6 then 'loader'
when config.service_class = 7 then 'small_query'
when config.service_class = 8 then 'big_query'
when config.service_class = 9 then 'default'
when config.service_class = 14 then 'SQA'
when config.service_class = 15 then 'auto vaccuum/analyze'
end as queue_label
, trim (class.condition) as description
, config.num_query_tasks as slots
, config.query_working_mem as mem
, config.max_execution_time as max_time
, config.user_group_wild_card as "user_*"
, config.query_group_wild_card as "query_*"
, state.num_queued_queries queued
, state.num_executing_queries executing
, state.num_executed_queries executed
class.action_service_class = config.service_class
and class.action_service_class = state.service_class
--and config.service_class > 4
order by config.service_class;
Thanks for answering my question!
- asked 6 months ago
- Accepted Answerasked a year ago
- AWS OFFICIALUpdated 4 months ago
- AWS OFFICIALUpdated a month ago
- How do I identify which Amazon RDS DB parameters are in custom parameter groups and which are in default parameter groups?AWS OFFICIALUpdated 8 months ago
- AWS OFFICIALUpdated a year ago
- EXPERTpublished 4 months ago