Step Time Threshold: a new life for an old feature

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

Long before TASM, there were throttles.  We call them “system throttles” or “object throttles” today in order to differentiate them from TASM’s workload throttles.  These classic concurrency control mechanisms, that can delay queries before they begin execution, are alive and thriving.  And interestingly, they offer sometimes-forgotten power and flexibility that can make them very useful for customers without full TASM implementation or capabilities, but who still want more control over their workload environment.

In particular I’d like to draw your attention to the step time threshold option.  It’s been a part of throttles since they were first introduced, and I’ve always found it to be surprisingly helpful when used in a thoughtful way.  Here’s how a step time threshold works, and some thoughts on why it seems to be taking on a new life today.

What is a Step Time Threshold?

A step time threshold is optional.  It’s a number of seconds that you can specify when you define a system throttle.  Prior to Teradata 12.0, the number of seconds had to be whole seconds, as the setting you selected was converted to an integer before being stored in the database.   But starting in Teradata 12.0, you can define a step time threshold with one decimal place if you wish. 

Just a reminder:   System throttles are only available with Teradata Dynamic Workload Manager (TDWM), either TDWM with full functionality, or TDWM in restricted mode.  The following comments are targeted to sites that that fall into one of those two categories.   

The way the step time threshold works, only queries with at least one step with an estimated processing time greater than the specified step time threshold number of seconds will be delayed or rejected by the throttle rule.   All queries whose database access steps have estimates equal to or below the step time threshold will run immediately and never be delayed.  

How Were Step Time Thresholds Originally Used?

The step time threshold option proved most useful in the case where a system throttle covered queries with contrasting characteristics.  Suppose you had an application, and all the users shared the same account.  Some of the queries in this application were very quick and used very little resource, and the remaining queries were very long and ran for some time.  Further, suppose you had a throttle associated with this account.  

Without the step time threshold option, all queries, short and long, would be subject to being placed in the delay queue due to presence of this throttle rule on the account string.  And the delay queue is first-in-first-out without consideration for the size of the request.  But if you looked at the query plans, and found that the short query plans never contained step estimates that exceeded 3 seconds, you could use that information in the throttle definition.  By setting the step time threshold parameter to 3 seconds, all the short queries would run immediately, and only the long queries would be subject to delays.  You’d get a better query completion count, you’d reward users who had tuned their queries, and you probably will not have slowed down the longer-running work so that anyone would notice.  A total win-win.

As Teradata sites began to take advantage of TASM classification and TASM workload throttles, step time thresholds on system throttles began to fade away in popularity.  Today with TASM, estimated time in the plan can be used to send the short queries to their own high priority workload, typically with no workload throttle to slow them down.  And the same estimated time can be used to send the long-running queries to a low priority workload, usually with a throttle to limit their concurrency.

The full TASM solution is more thorough than the step time threshold on a system throttle option because it not only differentiates which queries get delayed (the short query workload doesn’t have a throttle, but the long query workload does), but it can also differentiate query priority once the queries begin execution.  The long queries classify to a lower priority workload, the short ones to a higher priority.  So over time, step time thresholds became passé and were replaced with more targeted TASM workloads.

 Usefulness of the Step Time Threshold Today

Here’s why I think step time thresholds are getting a new life.  For simple applications running on one of the Teradata Family platforms that do not include full TASM, basic workload management techniques such as this can be very helpful for providing rudimentary, yet effective control when differences in query characteristics exist.

For example, I recently saw a situation where most of the queries within a single application was executing very quickly against aggregate join indexes (AJI), but there was a mix of a few poorly-written ad hoc queries arriving in little bursts.  All the queries in this application used the same user ID and the same account string, so could not be separated easily for throttling purposes.  Because this platform did not have TASM workloads available, they did not have the power of classification.

The solution was to use a system throttle on this application, associated to the account , with a step time threshold that would allow all the short queries using the AJI to bypass the throttle rule.  They set the throttle limit to 1 so that no matter what the arrival patterns of the ad hoc queries, they would be held down to only one query being active at a time.  They combined the throttle with the step time threshold with query milestone demotions, that automatically demoted the longer running queries after a few seconds of CPU was consumed per node. 

Closing Thoughts

We tend to give most of our attention to new enhancements and flashy features.  And that’s usually the right thing to do.  But I would encourage you to not completely discard old established techniques, techniques which can offer continuing value in novel situations. Thinking out of the box sometimes means looking backwards.

But while looking backwards, don’t neglect to keep your eye on future enhancements.  In Teradata 13.10, system throttles will gain some additional flexibility with the  “common classification” feature.   A larger array of criteria, similar to workload classification options, will be available for determining which queries will be controlled by a throttle. 

In Teradata 13.10, for example, you could set up a system throttle that will only control queries with specific query characteristics, or that come from a specific source.  Common classification will tend to make the step time threshold option obsolete, since common classification incorporates estimated processing time of the query as a possible criteria for a system throttle.