Chances are that if you’re into workload management at all, your using at least one throttle. Maybe you are using more. On the other hand, maybe you’re just thinking about it. Object Throttles (as opposed to Workload Throttles) allow you stray from the straight and narrow, because they offer you an array of options and can be used creative combinations. This makes it possible for you to get in over your head when your throttles overlap or conflict. If you are not able to keep your throttles simple, this article is intended to preview what are getting yourself into.
First, a brief explanation. Throttles are rules you define in TDWM that allow you to control concurrency of specific groups of queries. Workload throttles are only usable when TASM is active, and allow you to limit concurrency of all queries that have classified to a given Workload, as a choice. Object Throttles (the kind of throttle we are focused on here) are applied to incoming queries before they are classified to Workloads (and can be applied to incoming queries when TASM is not being used), and once defined, are usually associated to context or database objects. The object association establishes the throttle rule's scope.
When a Query is Controlled By Multiple Object Throttles
Here’s the first type of complexity to think about. You can slip into this quandry without intending to, so pay attention. It has to do with defining multiple Object Throttles in such a way that a single query might be caught up by more than one of them at the same time. That can happen when different throttles are associated to different types of objects (accounts, profiles, tables, views, users, etc.). I refer to this as “overlapping” throttles.
Using the scenario expressed in the figure below as an example, you could set up 3 throttles something like this:
If a query comes in that qualifies for being controlled by all 3 of those throttles (that is, the query was submitted by User X, within Account Y, and belongs to Profile Z), the counters of all 3 throttles must be below their respective limits in order for the query to be able to execute. For example, if the query comes in and User X already has 2 queries running, the new query will go into the delay queue. When one of User X’s active queries complete, allowing the new query to begin executing from the User X throttle perspective, it is possible that Account Y has already reached its limit of 12. And when the Account Y throttle falls below its limit, it could be that Profile Z has reached its limit of 20.
Multiple Object Throttles that control the same queries may result in some queries staying in the delay queue for an indeterminable length of time. All Object Throttle rules must be below their counters at the same time before the query can be freed from the delay queue.
Here’s a different scenario that is equally, if not more complex. In this situation there are multiple Object Throttles, all associated to the same object, Account X.
Here’s a description of those three Object Throttle rules
The use of different estimated processing times is intended to let all queries with an estimated time of less than 1 second run without ever being delayed, while at the same giving a higher concurrency limit to queries with estimated times that are greater than 1 second and less than 4 seconds (limit of 10), compared to queries with an estimate of 4 seconds or more (limit of 5), against queries within Account = X.
The important behavior to note is that TDWM in Teradata 12 (and earlier releases) only keeps one throttle counter for each object. Even though 3 Object Throttles have been defined against Account X, any point in time Account X will only have a single counter and will enforce a single limit. This changes in Teradata 13, at which time TDWM keeps one counter for each object/rule combination.
So if you are on Teradata 12 or an earlier release, the throttle count will change based on the limit for the rule that applied to the last request. Keep in mind a second behavior of TDWM in Teradata 12: If more than one throttle rule applies to a query, the one with the lowest limit is used. Let’s examine the implications of these behaviors for 2 of these 3 Object Throttles.
• Account X submits 6 very long requests
• Account X submits a short query (estimated time = 2 sec)
• The long delayed query from Rule A is released to run because the limit is now 10 for Account X
If you set up throttles in such a way, it will be difficult to know at any point in time what counter is being used to control concurrency. You are unlikely to experience the behavior that you want.
Instead of wallowing in complexity and driving your fellow DBA’s crazy as they attempt to predict what concurrency will actually be at any point in time, consider using Workload Throttles. Similar functionality can be achieved substituting Workloads with classification that is equivalent to the step time threshold number of seconds, as illustrated below, and then applying Workload Throttles as needed:
This type of setup is less confusing, and will make it easier to project actual concurrency levels. With fewer options, there are fewer opportunities for poor design using Workload Throttles.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.