Section 1: Understanding the default queue processing behavior - Amazon Redshift

Section 1: Understanding the default queue processing behavior

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

Step 1: Create the WLM_QUEUE_STATE_VW view

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

You 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 running.
executed The number of queries that have been run.

To create the WLM_QUEUE_STATE_VW view

  1. Open Amazon Redshift RSQL 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.

    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.

    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 create a view called WLM_QUERY_STATE_VW. This view returns information from the STV_WLM_QUERY_STATE system table.

You 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 running.

To create the WLM_QUERY_STATE_VW view

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

    select * from wlm_query_state_vw;

    The following is an example result.

Step 3: Run test queries

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

For this step, you need two RSQL windows open:

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

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

To run the test queries

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

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

    select * from wlm_query_state_vw;

    The following is an example result.

    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.

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

    Note

    To reduce query execution time and improve system performance, Amazon Redshift caches the results of certain types of queries in memory on the leader node. When result caching is enabled, subsequent queries run much faster. To prevent the query from running to quickly, disable result caching for the current session.

    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;
  4. 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.

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.