Managing Workload
Workload Management Overview
Workload Management in Vector is structured around the concept of queues.
A queue manages a set of queries, and contains properties or limits which determine if new queries are allowed to run. If the queue is under its limits, any new query added to the queue is made active and runs immediately. If the queue is at or over its limits, any new query added to the queue waits on the queue's query waitlist. When an active query completes the run in a queue, it is removed from the queue.
The queue's query load drops when the queries complete the run in a queue. If the queue is below the assigned limits, queries in the queue's wait list are made active and start running (until the queue goes over its limits again).
As a general rule, queries in the wait list are executed in a first-in or first-out order. However, the wait list also has priority assignments, similar to the seating priority on airplanes (Business class, first class or economy). For example, if a query with a high wait list priority is added to the wait list, it is added behind any other critical or high wait list priority queries, and ahead of any medium or low wait list priority queries.
A user (or group or role) can be authorized to use a queue (or several queues). When a query is run by the user, it is assigned to one of the queues that the user is allowed to use. If the query is waiting, the session will be displayed as CS_CNDWAIT with a WLM wait list Condition in the iimonitor.
When this query completes the run and when the next query is run, it will be assigned to a queue (possibly a different queue).
A configuration is a named set of one or more queues ( 'day', 'monthend', or any valid string) and automatically includes the following system generated queues:
• $default - Runs any queries which do not have any possible queue assignment. (For example, a user with no queue authorizations)
• $system - Runs any queries that use system catalogs.
• $express - Runs any queries which should run extremely quickly
• $superuser - A queue with no restrictions to be used by assigned superusers
When a configuration is made active, all the queues in the configuration are made active. If a different configuration is made active, all the queries will be moved to queues in the new configuration.
The ALTER WLM statement can be used to set up workload management.
Note: The ALTER WLM statement runs only in iidbdb, and requires auto commit to be enabled via 'set autocommit on'.
Last modified date: 03/21/2024