Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

Section 4: Using wlm_query_slot_count to Temporarily Override 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.

If you find that users often need to set wlm_query_slot_count for certain types of queries, you should 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’ll run the same long-running SELECT query. We’ll 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.

    Copy
    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 use the masteruser account to see how the query is running.

    Copy
    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.

    Copy
    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.

    Copy
    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 psql window 1 and 2, run the following to use the test query group.

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

    Copy
    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 psql window 1, run the following to increase the slot count to use all the slots for the queue and then start running the long-running query.

    Copy
    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 psql window and query the views to see the results.

    Copy
    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, and that there is one query that is waiting in the queue (where queued is 1 and state is QueuedWaiting). Once the first query completes, the second one will begin executing. 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.