Product Documentation

Appearance

WLM Example: Using SQL ​

All of the setup steps in the following example can be completed by using SQL commands (as shown) or in the SMC.

To set up a simple example of workload management:

  1. Create a profile and give it a meaningful name, such as shortquerybias.
premdb=# create wlm profile shortquerybias;
CREATE WLM PROFILE

By default, this profile contains a single resource pool: system.

  1. Create a resource pool named shortquerypool and set shortquerybias as its profile.

Give this pool a max/min concurrency of 20/1, a long queue, a maximum row limit, and a maximum execution time.

premdb=# create wlm resource pool shortquerypool 
(min_concurrency 1, max_concurrency 20, queue_size 100, maximum_row_limit 100000, maximum_exec_time 1, 
profile shortquerybias); 
CREATE WLM RESOURCE POOL
  1. Mark the new resource pool as the default pool for this profile.
premdb=# alter wlm profile shortquerybias (default_pool shortquerypool);
ALTER WLM PROFILE
  1. Create a rule and name it fastlane. Require all queries against the premdb database to run in shortquerypool.
premdb=# create wlm rule fastlane 
(javascript $$ if (w.database === 'premdb') { w.resourcePool = 'shortquerypool';}$$, 
enabled true, profile shortquerybias);
CREATE WLM RULE

By default, rules are created as Compile type rules. See Rule Types.

  1. Create a new active configuration by activating the shortquerybias profile.
premdb=# alter wlm profile shortquerybias activate;
ALTER WLM PROFILE
  1. Start a ybsql session as a non-superuser with select privileges on the tables in the premdb database. For example:
premdb=# create user sqb with password 'sqb';
CREATE ROLE
premdb=# grant select on match, team, season, awayteam, hometeam to sqb;
GRANT
premdb=# \c premdb sqb
Password for user sqb: 
You are now connected to database "premdb" as user "sqb".
premdb=>

Note: When admin users and superusers (such as the yellowbrick user) submit requests, they all go to the admin resource pool.

  1. Run a simple query that you expect to run in shortquerypool because the connected database is premdb. For example:
premdb=> select * from season;
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
        1 | 1992-1993   |       22 | Manchester United
        2 | 1993-1994   |       22 | Manchester United
        3 | 1994-1995   |       22 | Blackburn Rovers
...
  1. Monitor WLM activity in the SMC. Use the Manage > Workloads > Rule Processing feature to see which rules and pools were processed for the query.

Note that shortquerypool was selected and the fastlane rule was applied, but the superuser system rules were not.

Tip: You can navigate to equivalent information by going to Monitor > Query Performance, then double-clicking a specific query, then selecting Rule.

Parent topic:A Simple WLM Example