Product Documentation

Appearance

Creating Rules ​

Given knowledge of the workloads on your system, you can write WLM classification rules that define specific scheduling behavior and resource allocation for queries as they come in. 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:

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

To use the SQL command, you need to know how to write the JavaScript logic for rule conditions and actions (as with advanced mode in the SMC).

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.

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

Rule Processing ​

To see how specific rules are applied to logged and active queries, go to Manage > Workloads > Rule Processing. This screen presents detailed information about the application of each rule to each query, including when rule processing begins and ends. Use the Filter field to narrow the scope of the entries.

When and how rules are processed depends on the rule type.

Note: When profiles and resource pools change, these changes affect rule processing. For example, if at the end of all processing, a rule action for a query specifies a resource pool that does not exist, the rule returns an error and the query runs in the default pool. To monitor and find runtime errors like this, go to Rule Processing, enter error in the Filter field, and look for error under Type.

In This Section

Parent topic:How WLM Works