Throttling Concurrency ​

You can use WLM rules to set an action that limits query concurrency when a condition is met. This kind of rule provides another layer of concurrency control for query workloads, in addition to the minimum and maximum concurrency settings that you define per resource pool. Throttling is a mechanism for making sure that lower-priority workloads are queued to protect other higher-priority workloads.

To set up one of these rules, use the Prepare rule type and the Limit Concurrent Queries action. The result is a wlm.throttle rule. A "throttle" rule is primarily used to reduce rather than increase traffic to a pool. This type of rule accepts one or two attributes:

• A count value, which limits concurrency to some number of queries (given some condition)
• A throttleName attribute that defines a concurrency limit per user, role, or application (for example). In theory this name can refer to any classification of queries, but is mainly intended to provide per-user or per-role controls over concurrency.

You can use the count attribute by itself in a rule, or you can use both attributes.

Examples ​

For example, write a rule that limits user yb100 to 1 query, effectively turning off concurrency for that user:

if (w.user === 'yb100') { wlm.throttle(1, w.user); }

If this user submits two queries in succession, the second query may not start executing until the first is finished. Now rewrite this rule to use a count of 3:

if (w.user === 'yb100') { wlm.throttle(3, w.user); }

User yb100 can now start three queries in succession and they can all run concurrently (assuming that slots are available).

The following example applies concurrency control at the application level. The ybload application is limited to using resources one load at a time. Any user with load privileges can run a single load, but two concurrent loads would be prohibited:

if (w.application === 'ybload') {wlm.throttle(1);}

The effect of this rule is similar to always assigning ybload operations to a resource pool with only one slot (minimum and maximum concurrency values of 1/1).

If you create this rule in the SMC, it looks like this. Note that the action chosen is Limit Concurrent Queries, and that no throttle name is required (just the count value of 1).

In the following example, assume that the users are developers who belong to a role called dev. The WLM rule in this case disables concurrency for individual users in that role but enables it at the role level:

if (String(w.roles).match(/dev/)) { wlm.throttle(1, w.user); }

In other words, if 5 developers belong to this role, maximum concurrency for the pool when this rule is applied is 5.

Tip: When you are monitoring the application of WLM rules, you can check the wait_concurrency_ms statistics in the sys.log_query view to find out how long queries had to wait because a throttle rule was in effect. This wait time is distinct from general WLM queue time and time spent on execution.

Parent topic:Creating Rules