Automatic Throttles in Teradata 14.0 and 14.10

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

I just returned from the 2013 Teradata user group Partners Conference in Dallas.  One of the technical topics that I presented at the conference was throttle rules and how they work.  Throttles provide concurrency control in TASM, both on the EDW and the Appliance platforms.   I'd like to share few points about enhancements to throttles in Teradata 14.0 and 14.10 that I discussed at the conference,  in particular automatic throttles.

By the way, I'd like to thank all of you that were at the Partners Conference and who came up and introduced yourself and passed on positive feedback about my blog postings in Developer Exchange.  It was very encouraging for me to hear from so many of you who are reading these comments and finding value in them.   

Concurrency control, whether for queries or utility jobs, is enforced in Teradata by means of throttles.  Throttles are rules within the database, and for the most part are available to all the Teradata family platforms.  For example, both the 1xxx and 2xxx platforms as well as the 5xxx and 5xxx systems benefit from having system throttles and utilility throttles available on both SLES 10 and SLES 11 operating systems.  In addition, once you get to SLES 11, Appliance platforms will have both workload throttles and group throttles available, which previously have only been available on the EDW. 

There are several new capabilities in the concurrency control area when you get to Teradata 14.0 or 14.10, and this includes several throttles rules that are automatically created for you. This posting explores these automatic throttle rules.

Automatic Throttles on the Appliance Platform

Starring in 14.0, if you are on the 1xxx or 2xxx series hardware (the Appliance platform) on either SLES 10 or SLES 11, you will have two automatic throttles that you can see in your Workload Designer screens:

  • GeneralQuery throttle places a limit of 52 on concurrent requests systemwide.
  • OneSecondQuery throttle has a limit of 30 for all Low, Medium or High queries that have an estimated time of 1 second or greater.

The purpose of these throttles is to give you a sensible starting point for managing concurrency on your platform, even if you are unfamiliar with workload management techniques.  These default throttle rules are created during the migration process that takes you to the 14.0, or the 14.10 release.  These are default rules, but can be modified or even deleted if you wish.

These default rules are not included on the EDW platforms.

Note that in Teradata Database 15.0, the OneSecondQuery throttle will be renamed to OverOneSecondQuery, as that better describes the effect it has.

One approach to modifying these two default rules on the Appliance is to limit the impact of the GeneralQuery throttle, so single and few AMP queries can always run without being delayed.  Follow these steps to accomplish that modification:

  • Clone FirstConfig
  • Edit Cloned Ruleset
  • Click on Throttles Category
  • Click on GeneralQuery
  • Click on Classification Tab
  • Choose Query Characteristics
  • Click on Add
  • Choose AMP Limits
  • Include only queries that use all AMPs
  • OK
  • Save

Automatic Throttle to Manage SQL-H queries

A new feature in 14.10 is an automatic throttle that focuses on requests that are using SQL functions used for Hadoop communication.  These are referred to as SQL-H queries.  If a throttle rule already exists in the rule set and it limits SQL-H queries to no more than 20 at a time, no action will be taken.  However, if no such throttle exists, then an automatic throttle with a limit of 2 is added to the rule set.  This applies to both EDW and Appliance platforms.

The purpose of this automatic throttle is to prevent over-use of memory that is possible if a large number of these functions run at the same time.  

The throttle is created at the time the rule set is activated, but only in the cases where there is not already a rule in place to manage these types of functions.   The ability to classify to workloads based on function name is a new feature in 14.10, so the migration to routine looks for throttles based on Hadoop communication functions, and only if it does not find it does it create the automatic throttle.

This automatic throttle is not  recorded in the TDWM database and will not  be visible in Viewpoint.  It is only present in the TDWM rules cache.  But it can be seen in tdwmdmp or using the PMPC APIs.

It’s rule name is Default_load_from_hcatalog, and it carries a rule ID of 800000.  It cannot be deleted.

Conclusion

Automatic throttles are part of the evolution of workload management in Teradata.   In current software, there are several attempts to make set up simpler for you and protect you from things you may not have thought of .  Automatic throttles are one example of that direction.

22 Comments
Enthusiast

Hi Carrie,

I am having a basic doubt. While considering the difference between filters and Throttles, Filters can be used only to restrict the queries at object level and resource level. But in the case of throttles it can be used for both restriction and also delay. 

Please correct me if i am wrong.

Teradata Employee

Vasudev,

Your understanding is correct.

Please re-read the response I made to you last week, as I think that suggests a reasonable direction for you to take going forward.

Best regards, -Carrie

http://developer.teradata.com/comment/120100#comment-120100

Enthusiast

Thank you very much for your Response.

Thanks,

Vasudev

Fan

Hi Carrie

We have a throttle limits on, do you know whether it is possible to bypass / implement the throttles based on the statement type?

We have a number of users that are being held up when trying a simple delete because the explain plan is greater than our thereshold limit of 1 second. Increasing it beyond a second generically is not currently an option and we may see better throughput if we can simple let deletes go into a priority workload.

Thoughts?

BBPaul

Teradata Employee

Starting in Teradata 13.10, you can apply "common classification" to system throttles.  This means that you can create a system throttle and add classification criteria of the Query Characteristics type based on Statement Type (such as SELECT or DML).  For example, you could choose classification such as INCLUDE  ONLY SELECT for a system throttle, in which case all deletes or other updates that otherwise might classify there based on user or account, etc. would be bypassed.

Thanks, -Carrie

Enthusiast

Hi Carrie,

I know i am diverting to different topic but need your suggestion on the below issue. Can you please route me to post this question on the right thread.

As part of 14.10 upgrade, we need to find the whether reserved words are used in any procedures. I know we can find from DBQL logs or need to manually find the reserved word in each procedure by finding the reserved word in each ddl of the procedure but it wont give accurate results. So, please share your thoughts to find the best way to identify the procedure name which used the reservered words.

Thanks,

Reddy.

Teradata Employee

Hi Reddy,

Unfortunately, that's out of my area of experience.  My suggestion is to try posting this question on Teradata Forum or other Teradata-specific email groups.

Thanks, -Carrie

Enthusiast

Yes, i already did, Thank you, Carrie.

Teradata Employee

Carrie,

I've recently been assigned a task of setting up TASM on a raw box.

Is there a list of prerequisites (e.g.enabling certain DBScontrol parameters, running DIP etc.) which need to be performed/done before  starting with the WD Designer?

-V

Teradata Employee

Review the Viewpoint Configuration Guide to make sure that the system has been correctly configured in Viewpoint and so the Viewpoint data collectors can detect the database version.  Other than that, I can't think of anything.

Teradata CS usually has run DIPTDWM on a new system so rulesets can be written to the TDWM database.  You should make sure that step has been done.

Thanks, -Carrie

Enthusiast

Hi Carrie,

I was just going through the Orange book of SLES 11 priority scheduler and one of the comments in that book was:

"Workloads in the SLG tiers can consume more than their workload share percent if resources cannot be used below them in the hierarchy. "

I wanted to know how these ununsed resources are shared? in the ratio of their shared percent?

For eg: Assume I create a SLG tier with 3 workloads(wd1, wd2 and wd3) with share percent of the ratio 30%:20%:10% (3:2:1) and the rest of the 40% goes to "Remaning". If the resources in the "Remaining" workload are unused by the workloads below(in the hierarchy) and tasks in the 3 workloads are still running and need more CPU/IO, how is this 40% shared amongst the 3 workloads?

Do each workload get the unused resources in the ratio of their shared percent? (i.e. wd1 gets additional 40% *3/6 ==> 20%, wd2 gets additional 40% * 2/6 ==> 13.33% and wd3 gets additional 40% * 1/6 ==> 6.66%)

Regards,

Suhail

Teradata Employee

Suhail,

Because your comment/question concerns a  different topic from the blog posting it was attached to, it would make more sense for me to address it in a posting that is directly related to SLES 11 priority scheduler.

I will try to put something on Developer Exchange in the next few days that goes into detail about how resources in Teradata's new priority scheduler are shared, and will address your question at that time.

Thanks, -Carrie

Enthusiast

Thank you Carrie. I'll look forward to your posting.

-Suhail

Teradata Employee

Suhail,

The new blog posting has been completed.  Hopefully it will answer your questions.

Thanks, -Carrie

Carrie

We have some questions around the throttle rule type definitions of Collective, Individual, Member and Global.  When we first upgraded from v13.10/SLES 10 to v14.10/SLES 11, the TASM rules converted had selected a rule type of 'Collective', and thus with a concurrency limit of 4, we were only getting 4 queries across the entire system at a time; all others were delayed.  We quickly found the issue, and changed the throttle setting to Member, which seemed to resolve the issue.

However, we've recently found that under heavy usage, our throttles don't seem to be working as we expected.  For example, we have a throttle named 'Cognos User', which is set at a Rule Type of Member.  Its Classification is set at Request Source, Include (Profiles: AG_PROFILE, COGNOS_USER_PROFILE), and Query Characteristics set to include only queries using all AMPs.  State settings set the Concurrency to 4, Delay. These 2 profiles define all our Cognos users. 

We've found that we are only getting 4 Cognos queries running concurrently; other cognos queries are getting delayed.  With the above throttle settings, we expected the 'Member' Rule Type to limit to 4 concurrent processes per each member of the group.  But that is not what we are seeing.

Are we not understanding the Rule Type definitions?  The definition of rule type 'Individual' says it creates 1 queue for each object.  'Member' syas it creates 1 queue for each user, which is why we went with Member.

Appreciate any insights you have on the Throttle Rule Type definitions...

Thx!

Joe

Teradata Employee

Hi Joe,

As far as I can see from your comments below, you are setting up the rule correctly.  There are no changes in 14.10 to how throttle enforcement options (Individual, Collective, Members) are supposed to work.  Plus, enforcement options seem to have been working for you in other cases.

Each user with those profiles that submits a query should get his own throttle counter, so each user should be able to run 4 queries concurrently if you have selected the "member" option when creating the system throttle rule.   You've probably already done this, but you could check to see if there are other defined throttles (such as workload level throttles) that could be causing the delays that don't seem to make sense based on this one rule.   Or check if there is some kind of "global" system throttle in place that keeps all usage for all users below a specified level.

This may not apply to you, but there is a recently-identified issue related to the system throttle "individual" and  "members" option that comes up when using extended object names.   You might want to contact the support center and see whether your situation matches.

Thanks, -Carrie  

Enthusiast

Hi Carrie,

We are using appliance box and are on TD 14.10, by default, we see that, a rule set is ACTIVE.

I have disabled/deleted the throttle settings, but, unable to deactivate the default rule set.

Can you please let me know how can I disable/deactivate the default TIWM rule set.  

I dont see the default ruleset entry in ruledefs table in TDWM database as well.

Thanks,

Sravan.

Teradata Employee

Sravan,

I'm assuming you are on SLES 11, as you mention TIWM.  You are not allowed to deactivate the default rule set on an appliance, once you start using TIWM on SLES 11.  You used to be able to do that in SLES 10 before TIWM and before you had workloads, but deactivating the default workload is no longer allowed.  

You are intentionally being prevented from deactivating the default rule set by Workload Designer because of the requirements of TIWM.  Once you get to SLES 11 and are using TIWM, you have workloads, the system has to ensure you always have at least one rule set at all times.  You need the ruleset to hold the definitions of your workloads and things like throttles or filters that you may decide to use as well.     

Thanks, -Carrie

Hi Carrie,

I am supporting a newly upgraded 2650 appliance from 13.10 to 14.10. There is already a ruleset defined on the system but I could not view it because it says:

This ruleset's version 1310 is not compatible with the Teradata database version 1410 SLES 10. To fix this problem, please delete this ruleset locally and then copy it from Teradata to your working rulesets.

How could I take advantage of the two automatic throttles for TD 14.10? Do I need to delete the existing rulset and create one? Or it is automatically applied once I deleted the existing ruleset?

Thanks,

Teraboy

Teradata Employee

Teraboy,

There are a couple of questions here.

1.)  The most likely reason you received that error message regarding a ruleset version not being compatible is that the "Working" ruleset in Viewpoint is at 13.10.   The recommendation in such cases is to move the ruleset from "Ready" which is from the TDWM database, to "Working".  A ruleset in Ready should have been upgraded to 14.10 format during the upgrade.      

2.)  I believe that you only get those automatic rule sets if you are starting off fresh with a FirstConfig ruleset.    You may not have a FirstConfig rule set.  So if you don't see those two throttles, they are simple to create using the text and illustration in the blog posting above.   

The way it works is if there is already a rule set in the the TDWM database when you do the upgrade, FirstConfig is not created.  It is only created when there is nothing to convert.   

That information is missing from the blog posting, so I will add that in to alleviate any confusion any other reader might have.   Thanks for asking about that.  

 -Carrie

Carrie.

Thanks for the tip. Since my existing ruleset is no longer compatible to Teradata 14.10 version, could I just delete it and create a new one?

Thanks

Teradata Employee

Yes, you can do that.

Thanks, -Carrie