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

Section 1: Understanding the Default Queue Processing Behavior

Before you start to configure WLM, it’s useful to understand the default behavior of queue processing in Amazon Redshift. In this section, you’ll create two database views that return information from several system tables. Then you’ll run some test queries to see how queries are routed by default. For more information about system tables, see System Tables Reference.

Step 1: Create the WLM_QUEUE_STATE_VW View

In this step, you’ll create a view called WLM_QUEUE_STATE_VW. This view returns information from the following system tables.

You’ll use this view throughout the tutorial to monitor what happens to queues after you change the WLM configuration. The following table describes the data that the WLM_QUEUE_STATE_VW view returns.

Column Description
queue The number associated with the row that represents a queue. Queue number determines the order of the queues in the database.
description A value that describes whether the queue is available only to certain user groups, to certain query groups, or all types of queries.
slots The number of slots allocated to the queue.
mem The amount of memory, in MB per slot, allocated to the queue.
max_execution_time The amount of time a query is allowed to run before it is terminated.
user_* A value that indicates whether wildcard characters are allowed in the WLM configuration to match user groups.
query_* A value that indicates whether wildcard characters are allowed in the WLM configuration to match query groups.
queued The number of queries that are waiting in the queue to be processed.
executing The number of queries that are currently executing.
executed The number of queries that have executed.

To Create the WLM_QUEUE_STATE_VW View

  1. Open psql and connect to your TICKIT sample database. If you do not have this database, see Prerequisites.

  2. Run the following query to create the WLM_QUEUE_STATE_VW view.

    Copy
    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;
  3. Run the following query to see the information that the view contains.

    Copy
    select * from wlm_queue_state_vw;

    The following is an example result.

Step 2: Create the WLM_QUERY_STATE_VW View

In this step, you’ll create a view called WLM_QUERY_STATE_VW. This view returns information from the STV_WLM_QUERY_STATE system table.

You’ll use this view throughout the tutorial to monitor the queries that are running. The following table describes the data that the WLM_QUERY_STATE_VW view returns.

Column Description
query The query ID.
queue The queue number.
slot_count The number of slots allocated to the query.
start_time The time that the query started.
state The state of the query, such as executing.
queue_time The number of microseconds that the query has spent in the queue.
exec_time The number of microseconds that the query has been executing.

To Create the WLM_QUERY_STATE_VW View

  1. In psql, run the following query to create the WLM_QUERY_STATE_VW view.

    Copy
    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;
  2. Run the following query to see the information that the view contains.

    Copy
    select * from wlm_query_state_vw;

    The following is an example result.

Step 3: Run Test Queries

In this step, you’ll run queries from multiple connections in psql and review the system tables to determine how the queries were routed for processing.

For this step, you will need two psql windows open:

  • In psql window 1, you’ll run queries that monitor the state of the queues and queries using the views you already created in this tutorial.

  • In psql window 2, you’ll run long-running queries to change the results you find in psql window 1.

To Run the Test Queries

  1. Open two psql 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.

  2. In psql window 1, run the following query.

    Copy
    select * from wlm_query_state_vw;

    The following is an example result.

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

  3. In psql window 2, run the following query from the TICKIT sample database.

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

    Note

    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. If you find that the query does not run long enough for you to query both views, you can increase the value of l.listid to make it run longer.

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

    Copy
    select * from wlm_queue_state_vw; select * from wlm_query_state_vw;

    The following are example results.

Note the following differences between your previous queries and the results in this step:

  • There are two rows now in WLM_QUERY_STATE_VW. One result is the self-referential query for running a SELECT operation on this view. The second result is the long-running query from the previous step.

  • The executing column in WLM_QUEUE_STATE_VW has increased from 1 to 2. This column entry means that there are two queries running in the queue.

  • The executed column is incremented each time you run a query in the queue.

The WLM_QUEUE_STATE_VW view is useful for getting an overall view of the queues and how many queries are being processed in each queue. The WLM_QUERY_STATE_VW view is useful for getting a more detailed view of the individual queries that are currently running.