Section 4: Using wlm_query_slot_count to temporarily override the concurrency level in a queue - Amazon Redshift

Section 4: Using wlm_query_slot_count to temporarily override the concurrency level in a queue

Sometimes, users might temporarily need more resources for a particular query. If so, they can use the wlm_query_slot_count configuration setting to temporarily override the way slots are allocated in a query queue. Slots are units of memory and CPU that are used to process queries. You might override the slot count when you have occasional queries that take a lot of resources in the cluster, such as when you perform a VACUUM operation in the database.

You might find that users often need to set wlm_query_slot_count for certain types of queries. If so, consider adjusting the WLM configuration and giving users a queue that better suits the needs of their queries. For more information about temporarily overriding the concurrency level by using slot count, see wlm_query_slot_count.

Step 1: Override the concurrency level using wlm_query_slot_count

For the purposes of this tutorial, we run the same long-running SELECT query. We run it as the adminwlm user using wlm_query_slot_count to increase the number of slots available for the query.

To override the concurrency level using wlm_query_slot_count

  1. Increase the limit on the query to make sure that you have enough time to query the WLM_QUERY_STATE_VW view and see a result.

    set wlm_query_slot_count to 3; select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
  2. Now, query WLM_QUERY_STATE_VW with the admin user to see how the query is running.

    select * from wlm_query_state_vw;

    The following is an example result.

    Notice that the slot count for the query is 3. This count means that the query is using all three slots to process the query, allocating all of the resources in the queue to that query.

  3. Now, run the following query.

    select * from WLM_QUEUE_STATE_VW;

    The following is an example result.

    The wlm_query_slot_count configuration setting is valid for the current session only. If that session expires, or another user runs a query, the WLM configuration is used.

  4. Reset the slot count and rerun the test.

    reset wlm_query_slot_count; select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;

    The following are example results.

Step 2: Run queries from different sessions

Next, run queries from different sessions.

To run queries from different sessions

  1. In RSQL window 1 and 2, run the following to use the test query group.

    set query_group to test;
  2. In RSQL window 1, run the following long-running query.

    select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
  3. As the long-running query is still going in RSQL window 1, run the following. These commands increase the slot count to use all the slots for the queue and then start running the long-running query.

    set wlm_query_slot_count to 2; select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;
  4. Open a third RSQL window and query the views to see the results.

    select * from wlm_queue_state_vw; select * from wlm_query_state_vw;

    The following are example results.

    Notice that the first query is using one of the slots allocated to queue 1 to run the query. In addition, notice that there is one query that is waiting in the queue (where queued is 1 and state is QueuedWaiting). After the first query completes, the second one begins running. This execution happens because both queries are routed to the test query group, and the second query must wait for enough slots to begin processing.