Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

Creating WLM Rules ​

Given knowledge of the workloads on your system, you can write WLM classification rules that define specific scheduling behavior, resource allocation, and other actions that apply to queries at different points in their life cycle (from submission to completion). Rule conditions may look for matches to a particular table referenced in a query or a particular user who is running it. When a query qualifies for the conditions defined in a rule, a given action is taken, such as to place the query in a given resource pool, restrict memory use, or even abort the query.

To create a rule, use one of the following methods:

  • Go to Manage > Workloads > Rules > Create Rule in the SMC. You can use assisted mode or advanced mode.

In assisted mode, JavaScript is generated for the rule based on your choices of conditions and actions. Simple logical operators are provided, such as equals, not equals, greater than, less than, and so on. Where appropriate, drop-down lists provide specific choices, such as database names, table names, and names of resource pools. In other cases, you can use regular expressions to write conditions based on pattern matching.

In advanced mode, you can type in the JavaScript logic yourself, making use of the same building blocks that assisted mode provides.

  • Use the SQL command CREATE WLM RULE. In this case, you need to know how to write the JavaScript logic for rule conditions and actions (as with advanced mode in the SMC).

Note: Choosing the rule type is a critical step. The type affects when the rule is evaluated during the query lifecyle and determines what information is available to the rule, and in turn what kinds of actions can be taken. For details, see Rule Types Rule Examples.

To see a list of rules that are currently in the system, either query the sys.wlm_pending_rule view or use the ybsql \dwr command.

Rule Order ​

All rules are evaluated for every query. The order in which they run is controlled by the rule order. Lower-numbered rules are run first. For example, a rule with a rule order of 10 runs before a rule with a rule order of 100. Therefore, a rule with a higher rule order number may override settings that were set by a rule with a lower number.

This override behavior may be set on purpose. For example, consider the following rules:

  • Rule 1, order 10:
w.priority = ‘normal’;
 w.requestedMemoryPercent = 10;
  • Rule 2, order 100:
if (w.application === ‘SAS’) {
w.priority = ‘high’;
w.requestedMemoryPercent = 50;
}

Both rules set the priority and requestedMemoryPercent values, but the final values set for the query are governed by the last rule to run.

System Rules ​

Several WLM rules are predefined in the default and maintenance profiles, and a number of global system rules belong to all profiles. The database applies system rules as needed in conjunction with the rules you define. You can remove or modify certain system rules. To see if a rule is modifiable or removable, double-click the rule name in the main Rules screen.

System rules are named default_*, maintenance_*, or global_*. For example, by default the global_defaultRowLimit rule prevents queries run by non-superusers from returning more than 5,000,000 rows. These queries are aborted and return the following error:

premdb=> select * from newmatchstats;
ERROR:   WLM row limit exceeded

You can increase this limit globally by changing the definition of the rule action. For example:

After changing rules, you have to activate the changes, as discussed in Creating WLM Profiles.

In This Section

Parent topic:How WLM Works