Dynamic WLM example - Amazon Redshift

Dynamic WLM example

Suppose that your cluster WLM is configured with two queues, using the following dynamic properties.

Queue Concurrency % Memory to Use

1

4

50%

2

4

50%

Now suppose that your cluster has 200 GB of memory available for query processing. (This number is arbitrary and used for illustration only.) As the following equation shows, each slot is allocated 25 GB.

(200 GB * 50% ) / 4 slots = 25 GB

Next, you change your WLM to use the following dynamic properties.

Queue Concurrency % Memory to Use

1

3

75%

2

4

25%

As the following equation shows, the new memory allocation for each slot in queue 1 is 50 GB.

(200 GB * 75% ) / 3 slots = 50 GB

Suppose that queries A1, A2, A3, and A4 are running when the new configuration is applied, and queries B1, B2, B3, and B4 are queued. WLM dynamically reconfigures the query slots as follows.

Step Queries Running Current Slot Count Target Slot Count Allocated Memory Available Memory
1 A1, A2, A3, A4 4 0 100 GB 50 GB
2 A2, A3, A4 3 0 75 GB 75 GB
3 A3, A4 2 0 50 GB 100 GB
4 A3, A4, B1 2 1 100 GB 50 GB
5 A4, B1 1 1 75 GB 75 GB
6 A4, B1, B2 1 2 125 GB 25 GB
7 B1, B2 0 2 100 GB 50 GB
8 B1, B2, B3 0 3 150 GB 0 GB
  1. WLM recalculates the memory allocation for each query slot. Originally, queue 1 was allocated 100 GB. The new queue has a total allocation of 150 GB, so the new queue immediately has 50 GB available. Queue 1 is now using four slots, and the new concurrency level is three slots, so no new slots are added.

  2. When one query finishes, the slot is removed and 25 GB is freed. Queue 1 now has three slots and 75 GB of available memory. The new configuration needs 50 GB for each new slot, but the new concurrency level is three slots, so no new slots are added.

  3. When a second query finishes, the slot is removed, and 25 GB is freed. Queue 1 now has two slots and 100 GB of free memory.

  4. A new slot is added using 50 GB of the free memory. Queue 1 now has three slots, and 50 GB free memory. Queued queries can now be routed to the new slot.

  5. When a third query finishes, the slot is removed, and 25 GB is freed. Queue 1 now has two slots, and 75 GB of free memory.

  6. A new slot is added using 50 GB of the free memory. Queue 1 now has three slots, and 25 GB free memory. Queued queries can now be routed to the new slot.

  7. When the fourth query finishes, the slot is removed, and 25 GB is freed. Queue 1 now has two slots and 50 GB of free memory.

  8. A new slot is added using the 50 GB of free memory. Queue 1 now has three slots with 50 GB each and all available memory has been allocated.

The transition is complete and all query slots are available to queued queries.