Can queries be assigned directly to the DEFAULT queue?

0

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.

Is there any mechanism a SQL client, any part of the server side configuration, or user definition can bypass the prior queues and get queries directly dispatched into the DEFAULT queue?

I can't seem to find anything in the Redshift documentation that allows doing this.

Thanks,
-Kurt

klarson
preguntada hace 5 años310 visualizaciones
2 Respuestas
0

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?

Toebs2
respondido hace 5 años
0

Hi Toebs2,

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'
			else 'unknown'
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
from
STV_WLM_CLASSIFICATION_CONFIG class,
STV_WLM_SERVICE_CLASS_CONFIG config,
STV_WLM_SERVICE_CLASS_STATE state
where
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!
-Kurt

klarson
respondido hace 5 años

No has iniciado sesión. Iniciar sesión para publicar una respuesta.

Una buena respuesta responde claramente a la pregunta, proporciona comentarios constructivos y fomenta el crecimiento profesional en la persona que hace la pregunta.

Pautas para responder preguntas