Product Documentation

Appearance

All platforms

A Simple WLM Example ​

This section explains how to create a very simple set of WLM objects and implement a single rule that is applied when certain queries are run. All of the setup steps in the following example can be completed in Yellowbrick Manager (as shown) or by using SQL commands. SQL is generated at each step and can be downloaded for the profile, if needed.

To set up a simple example of workload management:

  1. Log into Yellowbrick Manager as an administrator and go to Workload Management > Profiles > +Profile.

Note: Regular users cannot see the Workload Management tab.

  1. Create a profile and give it a meaningful name, such as sqb (for "shortquerybias").

By default, this new profile contains no resource pools.

  1. Click Create Resource Pool under the sqb profile. Name the new pool shortquerypool, and give it 20 slots (maximum concurrency), a queue size of 100, a maximum row limit of 100000, and a maximum execution time of 1 second. Also make this pool the default pool for the profile.

  1. Create a second resource pool in the same profile. Name the new pool longpool, and give it 2 slots (maximum concurrency), a queue size of 100, no maximum row limit, and no maximum execution time.

When the sqb profile is activated for a running cluster, it will also accrue the system pool, so three resource pools will be in operation.

  1. Go to Workload Management > Global Rules > +Rule.

  2. Create a Compile type rule in the sqb profile and name it fastlane.

  1. Click Next, then set a filter where Database=premdb.

  1. Click Next, specify an action to set the resource pool to shortquerypool, and click Save.

  1. Go back to Profiles, then click the green info icon next to sqb, which shows the profile changes that are ready to be activated.

  1. Prepare to activate the sqb profile by selecting the profile, then selecting Activate from the Actions menu.

  1. Select the large-default-cluster target for activation, accept all the default options, and click Activate. Note that the system pool has been added to the list of resource pools, and one rule is specific to this profile.

  1. Go to the Query Editor and run a query as a regular user with select privileges on the tables in the premdb database (and USAGE granted on the large-default-cluster. For example:

  1. Log in as an administrator so you can monitor WLM activity.

  2. Go to Workload Management > Rule Processing to see which rules and pools were processed for the query.

Note that the fastlane rule was applied during the compile phase of the query, and the resource pool was set to shortquerypool.

You can also check rule-processing behavior by selecting from the sys.log_query view. For example:

You can download the SQL for the sqb profile for future use:

CREATE WLM PROFILE "sqb";
CREATE WLM RESOURCE POOL "shortquerypool" (
            PROFILE "sqb"
          , REQUESTED_MEMORY NULL
          , MAX_SPILL_PCT NULL 
          , MAX_CONCURRENCY 20
          , MIN_CONCURRENCY 1
          , QUEUE_SIZE 100
 , MAXIMUM_ROW_LIMIT 100000
 , MAXIMUM_EXEC_TIME 1);
CREATE WLM RESOURCE POOL "longpool" (
            PROFILE "sqb"
          , REQUESTED_MEMORY NULL
          , MAX_SPILL_PCT NULL 
          , MAX_CONCURRENCY 2
          , MIN_CONCURRENCY 1
          , QUEUE_SIZE 100);
CREATE WLM RULE "fastlane" (
            PROFILE "sqb"
          , TYPE compile
          , RULE_ORDER 1
          , SUPERUSER false
          , ENABLED true
          , JAVASCRIPT $$if (w.database === 'premdb') {
	w.resourcePool = 'shortquerypool';
}$$);
ALTER WLM PROFILE "sqb" (DEFAULT_POOL "shortquerypool");