Tutorial: Configuring manual workload management (WLM) queues

Open Amazon Redshift RSQL and connect to your TICKIT sample database. If you do not have this database, see Prerequisites.

Run the following query to create the WLM_QUEUE_STATE_VW view.

create view WLM_QUEUE_STATE_VW as
select (config.service_class-5) as queue
, 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;

Run the following query to see the information that the view contains.

select * from wlm_queue_state_vw;

The following is an example result.

query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
    0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
    1 | (querytype:any)                           |     5 | 836 |        0 |  false | false   |      0 |         1 |      160

In RSQL, run the following query to create the WLM_QUERY_STATE_VW view.

create view WLM_QUERY_STATE_VW as
select query, (service_class-5) as queue, slot_count, trim(wlm_start_time) as start_time, trim(state) as state, trim(queue_time) as queue_time, trim(exec_time) as exec_time
from stv_wlm_query_state;

Run the following query to see the information that the view contains.

select * from wlm_query_state_vw;

The following is an example result.

query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 1249 |     1 |          1 | 2014-09-24 22:19:16 | Executing | 0          | 516

Open two RSQL windows. If you already have one window open, you only need to open a second window. You can use the same user account for both of these connections.

In RSQL window 1, run the following query.

select * from wlm_query_state_vw;

The following is an example result.

query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 1258 |     1 |          1 | 2014-09-24 22:21:03 | Executing | 0          | 549

This query returns a self-referential result. The query that is currently running is the SELECT statement from this view. A query on this view always returns at least one result. Compare this result with the result that occurs after starting the long-running query in the next step.

In RSQL window 2, run a query from the TICKIT sample database. This query should run for approximately a minute so that you have time to explore the results of the WLM_QUEUE_STATE_VW view and the WLM_QUERY_STATE_VW view that you created earlier. In some cases, you might find that the query doesn't run long enough for you to query both views. In these cases, you can increase the value of the filter on l.listid to make it run longer.

To turn off result caching for the current session, set the enable_result_cache_for_session parameter to off, as shown following.

set enable_result_cache_for_session to off;

In RSQL window 2, run the following query.

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

In RSQL window 1, query WLM_QUEUE_STATE_VW and WLM_QUERY_STATE_VW and compare the results to your earlier results.

select * from wlm_queue_state_vw;
select * from wlm_query_state_vw;

The following are example results.

query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
    0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
    1 | (querytype:any)                           |     5 | 836 |        0 |  false | false   |      0 |         2 |      163
                        
query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 1267 |     1 |          1 | 2014-09-24 22:22:30 | Executing | 0          | 684
 1265 |     1 |          1 | 2014-09-24 22:22:36 | Executing | 0          | 4080859

Open RSQL and run the following query. The query uses the WLM_QUEUE_STATE_VW view you created in Step 1: Create the WLM_QUEUE_STATE_VW view. If you already had a session connected to the database prior to the cluster reboot, you need to reconnect.

select * from wlm_queue_state_vw;

The following is an example result.

query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
    0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
    1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         0 |        0
    2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         0 |        0
    3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |        0

Compare these results to the results you received in Step 1: Create the WLM_QUEUE_STATE_VW view. Notice that there are now two additional queues. Queue 1 is now the queue for the test query group, and queue 2 is the queue for the admin user group.

Queue 3 is now the default queue. The last queue in the list is always the default queue. That's the queue to which queries are routed by default if no user group or query group is specified in a query.

Run the following query to confirm that your query now runs in queue 3.

select * from wlm_query_state_vw;

The following is an example result.

query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 2144 |     3 |          1 | 2014-09-24 23:49:59 | Executing | 0          | 550430

Run the following query to route it to the test query group.

set query_group to test;
select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;

From the other RSQL window, run the following query.

select * from wlm_query_state_vw;

The following is an example result.

query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 2168 |     1 |          1 | 2014-09-24 23:54:18 | Executing | 0          | 6343309
 2170 |     3 |          1 | 2014-09-24 23:54:24 | Executing | 0          | 847

The query was routed to the test query group, which is queue 1 now.

Select all from the queue state view.

select * from wlm_queue_state_vw;

You see a result similar to the following.

query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
    0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
    1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         1 |        0
    2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         0 |        0
    3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |        0

Now, reset the query group and run the long query again:

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

Run the queries against the views to see the results.

select * from wlm_queue_state_vw;
select * from wlm_query_state_vw;

The following are example results.

query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
    0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
    1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         0 |        1
    2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         0 |        0
    3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         2 |        5
 
query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 2186 |     3 |          1 | 2014-09-24 23:57:52 | Executing | 0          | 649
 2184 |     3 |          1 | 2014-09-24 23:57:48 | Executing | 0          | 4137349

The result should be that the query is now running in queue 3 again.

In the database, create a new database user named adminwlm by running the following command in an RSQL window.

create user adminwlm createuser password '123Admin';

Then, run the following commands to create the new user group and add your new adminwlm user to it.

create group admin;
alter group admin add user adminwlm;

In RSQL window 2, run the following queries to switch to the adminwlm account and run a query as that user.

set session authorization 'adminwlm';
select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;

In RSQL window 1, run the following query to see the query queue that the queries are routed to.

select * from wlm_query_state_vw;
select * from wlm_queue_state_vw;

The following are example results.

query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
    0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
    1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         0 |        1
    2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         1 |        0
    3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |        8
 
query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 2202 |     2 |          1 | 2014-09-25 00:01:38 | Executing | 0          | 4885796
 2204 |     3 |          1 | 2014-09-25 00:01:43 | Executing | 0          | 650

The queue that this query ran in is queue 2, the admin user queue. Anytime you run queries logged in as this user, they run in queue 2 unless you specify a different query group to use. The chosen queue depends on the queue assignment rules. For more information, see WLM queue assignment rules.

Now run the following query from RSQL window 2.

set query_group to test;
select avg(l.priceperticket*s.qtysold) from listing l, sales s where l.listid <40000;

In RSQL window 1, run the following query to see the query queue that the queries are routed to.

select * from wlm_queue_state_vw;
select * from wlm_query_state_vw;

The following are example results.

query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
    0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
    1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         1 |        1
    2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         0 |        1
    3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |       10
 
query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 2218 |     1 |          1 | 2014-09-25 00:04:30 | Executing | 0          | 4819666
 2220 |     3 |          1 | 2014-09-25 00:04:35 | Executing | 0          | 685

When you’re done, reset the query group.

reset query_group;

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;

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.

query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 2240 |     2 |          1 | 2014-09-25 00:08:45 | Executing | 0          | 3731414
 2242 |     3 |          1 | 2014-09-25 00:08:49 | Executing | 0          | 596

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.

Now, run the following query.

select * from WLM_QUEUE_STATE_VW;

The following is an example result.

query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
    0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
    1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         0 |        4
    2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         1 |        3
    3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |       25

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.

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.

query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
    0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
    1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      0 |         0 |        2
    2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         1 |        2
    3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |       14
 
query | queue | slot_count | start_time          | state     | queue_time | exec_time
------+-------+------------+---------------------+-----------+------------+-----------
 2260 |     2 |          1 | 2014-09-25 00:12:11 | Executing | 0          | 4042618
 2262 |     3 |          1 | 2014-09-25 00:12:15 | Executing | 0          | 680

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

set query_group to test;

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;

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;

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.

query | description                               | slots | mem | max_time | user_* | query_* | queued | executing | executed
------+-------------------------------------------+-------+-----+----------+--------+---------+--------+-----------+----------
    0 | (super user) and (query group: superuser) |     1 | 357 |        0 |  false | false   |      0 |         0 |        0
    1 | (query group: test)                       |     2 | 627 |        0 |  false | false   |      1 |         1 |        2
    2 | (suser group: admin)                      |     3 | 557 |        0 |  false | false   |      0 |         0 |        3
    3 | (querytype:any)                           |     5 | 250 |        0 |  false | false   |      0 |         1 |       18
 
query | queue | slot_count | start_time          | state         | queue_time | exec_time
------+-------+------------+---------------------+---------------+------------+-----------
 2286 |     1 |          2 | 2014-09-25 00:16:48 | QueuedWaiting | 3758950    | 0
 2282 |     1 |          1 | 2014-09-25 00:16:33 | Executing     | 0          | 19335850
 2288 |     3 |          1 | 2014-09-25 00:16:52 | Executing     | 0          | 666

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.