CODEWITHSUNDEEP
amazon-redshift
nmakb
nmakb

Reputation: 1235

Redshift WLM - how are unused slots used

My understanding of WLM in Redshift is that there are queues and each queue has slots (by default 5). And let's say the system is idle, and I run a query. By default it occupies one slot and runs the query, even though other slots are free and available. Right ?

If so, is it not a waste of resources to let other slots remain idle and not do any work when they can?

Upvotes: 0

Views: 800

Answers (1)

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

Yep, that's pretty much how Redshift WLM works.

For example if it's configured with one queue and that queue has five slots each with 20% of memory then your one query is by default going to run in one slot and use up to 20% memory max. The rest of the slots will sit idle waiting for another query to hit the same queue.

However, you can override the default behaviour, e.g. by specifying wlm_query_slot_count as 2 the query will use two slots instead of one, and therefore up to 40% of memory.

e.g.

set wlm_query_slot_count to 2;
select * from public.mytable;

Be careful doing this though as if you up the slot count to 5 (in this example) then no other queries will be able to run as there are now no available slots. This is not quite true if short query acceleration (SQA) is enabled on the cluster, as in that case very short queries (typically 5 seconds or less) will be run in a special hidden queue if there are no normal slots available.

Many cluster WLM configurations will have more than one queue, maybe one or two with few slots and therefore a higher resource-to-slot ratio that can be used for a smaller number of resource hungry tasks like data loads and updates, and some with more slots with a lower amount of resources per slot. Those queues will typically be used by BI and analytics tools to query the data.

You can also use WLM Query Queue Hopping to manage queries that end up waiting for slots to become free by diverting them to a different queue. Also bear in mind that "concurrency" and "percent of memory" are dynamic WLM configuration properties and you can change them using the CLI (ModifyClusterParameterGroup) without having to restart the cluster, e.g. reallocate resources from a data load queue to an analytics queue once all data has been loaded.

Upvotes: 1

Related Questions