Two Levels of Concurrency Control for Load Utilities in Teradata 13.10

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

Have you ever wanted two levels of control on load utilities?  More specifically, have you ever wanted to limit how many load utilities a subset of users are able to run? This was not possible before, but it is something that is supported in the Teradata 13.10 release.  Let me explain how this works.    

First, multiple levels of throttles, whether to manage queries or to manage load utilities, always rely on the presence of TASM.  If you don’t have TASM, you can’t set up workloads.  If you don’t have workloads defined, you can’t create workload throttles.  And you need workload throttles to make up your second level of control, the first level being system throttles, or for the sake of this discussion, utility throttles. 

The graphic below illustrates two levels of concurrency control that exists for queries.

So let’s assume you are using TASM, and you have specific workloads that your load utilities run in.  First consider the behavior prior to Teradata 13.10, then after.

Before Teradata 13.10

Before 13.10, your only level of control over utility concurrency was at the system level with utility throttles (or at the system level with the DBS Control MaxLoadTask parameter).  Once a utility is approved to run by the utility throttle it will classify to a workload.   A common (and recommended) method of classifying a load utility to a workload is by setting up classification on the utility type.  

Prior to 13.10 you could, for example, run all FastLoads in one workload, and all MultiLoads in another, if you wanted.   Usually classification by utility type was accompanied by an additional “who” criteria, such as user name.   So you could run all FastLoads submitted by ETLUser in a higher priority workload than FastLoads submitted by AppUser.  However, before 13.10, FastLoad jobs from both ETLUser and AppUser shared the same system-level utility throttle.   Since they shared the same throttle, AppUser could take an unfair number of those load slots at any point in time, and there was no mechanism to prevent that.   

Prior to 13.10 it was recommended that you not use the workload throttle limits for workloads that support load utilities, because at that time workload throttles didn’t recognize utilities, or consider them as a unit of work.  This lack of compatibility between workload throttles and utilities led to some odd behavior when the two were combined.  For that reason, it was recommended to never put a workload throttle on a workload that was supporting load utilities.

Things are Different in Teradata 13.10

There have been several enhancements for utility throttles In 13.10.  First, classification criteria for utility throttles have been expanded to include “where” criteria such as table name or database name for the load utilities (FastLoad, MultiLoad, FastExport jobs).  In addition, more specific utility types of each load/unload protocol are supported.  In 13.10, utility type can distinguish between stand alone FastLoad utility, TPT Load operator, and JDBC FastLoad.    This gives you the flexibility of having one workload for stand alone FastLoad, and a different workload for the TPT load operator, for example.

But the big change, the one to get really excited about, is the ability to limit the number of utilities at the workload level.  Workload throttles have been enhanced to both recognize load utilities and delay them cleanly.  This means you can now classify different users who submit load jobs to different workloads, and each workload could have a different throttle limit.   At the same time you could use the standard utility throttle to manage concurrency levels at a higher level, for all the users submitting loads.

Enhancements have been made to the workload throttles, so that they can recognize and cleanly delay load jobs, just as they have always been able to cleanly delay queries.   You might have some DDL in a load script, and those statements will be classified and processed as queries, not load utilities.  The load utility classification will include everything between the BEGIN and the END LOADING statements.   Even if a DDL is delayed, it will not be holding onto a utility slot, in 13.10.

So, you can now better manage those groups of users who like to load their own data.  You can classify them to their own workload and then limit them to whatever level of load job concurrency you wish.  When two levels of utility throttles are active, both levels must be satisfied for the load job to start running.   

DBS Control MaxLoadTask Overridden

Just a reminder that as of Teradata 12.0 the DBS Control settings such as MaxLoadTask and MaxLoadAWT will no longer be honored if any system-level throttles for either queries or utilities are active. 

Whether you are using TASM or not, you should no longer continue to rely on DBS Control settings to manage load concurrencies. Instead, I heartily invite you to get on board with utility throttles.  

33 Comments
Carrie,

1. Could we use this feature to reject users from using Load/Unload Utilities? We would like to restrict our sandbox users from using these tools blindly, and avoid contention with Production Job on the utility slots.

2. Could we disable the Utility Throttle @ system Level, and control all the Utility @ Workload Level? This will avoid one of the workload jobs flooding the utility throttle and prevent other utility jobs from entering.

Thanks,

Regards,
YB.
Teradata Employee
Hi YB.

Thank you for your interesting questions.

As for question 1, you can define utility throttles and select the reject option if you wish. This will cause any load job that exceeds the throttle limit to be rejected. If you reject with a utility throttle, it will reject any load job from any source that would cause the limit to be exceeded. So it will not work for you if you only want to reject a subset of load jobs, such as those coming from sandbox users.

However, starting in Teradata 13.0 you can specify reject for a workload throttle. So you could use that option against sandbox users if you wished, as long as you are on that release.

For question 2, you can control utilities only at the workload level if you wish. If you do that, then assume that the maximum potential load utility concurrency will be the sum of all the defined workload load utility limits.

Note that utility throttles cannot be disabled completely. Even when there is no user-defined utility throttle, Teradata will enforce the following system limits:

FastLoad + MultiLoad: 30
FastLoad: 30
MultiLoad: 30
FastExport: 60
Archive/Restore: 350

Thanks, -Carrie
Enthusiast
Utility throttle will recognize database/table combinations,
workload definition does not have option to define database/tables.
so.. to limit the sandbox users, utility throttle should be defined and set limit zero with reject option, is that a correct understanding?
Carrie,

Thanks a lot for your feedback, Our site still @ TD12 and planning for TD13.10. This will be handy for us to control the sandbox users :)

Thanks for remind @ the 2nd questions, we'll take note on it when planning our new workload @ TD13.10.

Regards,
YB.
Teradata Employee
Hi Emilwu,

While you are correct that the workload throttle does not have the option to limit which databases or tables are under its control, the classification criteria for the workload itself can. If you classify load jobs to a workload and include classification criteria on that workload such as database name ("where" criteria), then you will get the same result as if the workload throttle allowed such definition. Only load jobs that meet that workload classification criteria will classify to that workload, and all load jobs (and only load jobs) that classify to the workload will be under the control of the workload throttle.

So if you classified your sandbox user's load jobs to a workload based on load utility type and the sandbox databasename, for example, and then also had a workload throttle on that workload, you could limit concurrency of sandbox load jobs to whatever level you wanted. You would not need to set the limit to zero or reject, to be able to control the number of those load jobs you wanted to allow sandbox users to run at the same time. You would probably only want to use the reject option if you never wanted sandbox users load jobs to run.

Just a point of clarification. If you set a workload limit to zero with delay, then everything will go into the delay queue, so that limit of zero should only be considered as a temporary setting. No sense letting a delay queue build up if you don't eventually want to the work to run. If you combine the reject option and a zero limit for the workload throttle, then everything that classifies to that workload will be rejected and never run, and the workload throttle will be acting more like a filter.

Thanks, -Carrie

Hi Carrie,

After 13.10 still do we have limit on total number of slots or we can set N number of utility throttle for each utility?

Regards,
Suresh A
Enthusiast
Hello Carrie

I've a question about how we can set up workloads now this new feature is available. Previously it was recommended to have separate workloads for load utilities and for normal SQL jobs. This meant that, for example, your batch workload would be limited to say 4 concurrent queries, but at the same time batch jobs could be running load utilities in another workload. This meant you had to be careful about allocating too many concurrent query slots to the SQL workload to make sure the combination of load jobs and sql jobs didn't use too many AWTs.

With the new options which will throttle utilities in the same way as SQL queries is there any need to run the utilities in a separate workload? Could all the batch tasks run in the same workload, and we don't care whether they are utility jobs or not? To me that seems to be a better way of controlling the work running on the system.

Thanks,
Andy.
Teradata Employee
Suresh,

The answer is yes and no. You still have a limit of 30 for the total number of load job utilties that are running at the same time. And you also have a limit on the number of AMP worker tasks that can be used to support them. However, you can manage the number of load utilities by type you want to run concurrently, both at the object utility level, and by using classification based on load type for the workload throttle described in this blog.

Thanks, -Carrie
Teradata Employee
Hi Andy,

Even with these enhancements I would still tend towards assigning normal SQL jobs and utilities to different workloads. If they shared a workload throttle, utilities could easily block batch jobs that do not use utilities. Utilities generally tie up more resources in getting their work done, at least in certain phases, so you would lose an element of control if you try to mix them with SQL work.

The number of workloads has increased significantly in TASM in the more recent releases, so there is no real downside in using more of them in order to get more granular control. And it's useful to viewpoint displaying different metrics for utilities vs. other batch work, because they are in different workloads. Same is true for ResUsageSPS table data, which on current releases is based on workload. So you can uses the SPS table for analysis in a more granular way if you use different workloads for utilities and for SQL.

You can still map multiple workloads to the same priority scheduler allocation group if they are equivalent priority, but there's a lot of value in classifying them to different workloads.

Thanks, -Carrie
N/A
Carrie -

Is the ability to queue FastLoad sessions (rather than error out when all the utility slots are in use) new in 13.10? Or has that always been available through TASM?

Thanks,
Steve
Enthusiast
it was introduced when TDWM made its way into V2R62. long time;)
Teradata Employee
Hi Steve,

The original utility throttles prior to Teradata 12.0 could not delay FastLoad jobs, they could only reject them, when the limit was reached. It was not until Teradata 12.0 that the ability to delay load jobs was introduced, actually in the 12.0.0.8 release.

Thanks, -Carrie
Carrie,

A couple of questions for you regarding TASM(our system is 13.10):

1. In our TASM settings, we don't have any system level utility throttle defined. so default throttle is in place. which is as below, correct?
FastLoad + MultiLoad: 30
FastLoad: 30
MultiLoad: 30
FastExport: 60
Archive/Restore: 350

2. we have two batch workloads, one for each business group(batch 1 and batch2). Within the batch workload, classification is based on acctstring and utility is (fastload, multiload, fastexport). Then we set workoad throttle limit to 15 for both batch1 and batch2 and the purpose is make sure each business group both have 15 utility slots to run their batch jobs. Did we do it right? Is the throttle limit within the workload at batch job level or session level? My understanding is throttle for report/query workload is at session level. Is it different for batch job?

Thanks, Carrie
Teradata Employee
Your default setting assumptions are correct.

However, a second enforcement is also going to be taking place internally. That is on AMP worker tasks used by the utilities. Only 60% of the AWTs/AMP will be allowed to be used in support of load/unload utilities. So it is possible you may not be able to reach the default numbers, depending on AWT usage. For example, FastLoad requires 3 AWTs/AMP to start (Phase I).

In terms of your second question, when you set a workload throttle limit to 15 for both batch workloads, as you have described, that should work fine. It should provide a second-level limit only for the utility jobs that classify to that workload. The workload-based throttle limit for utilities is at the job level, not the session level. It is the number of jobs that are being managed in terms of concurrency. TASM and Teradata utilities have been enhanced in the Teradata 13.0 and 13.10 releases so that when you specify a "utility" classification for a workload, then the whole load/unload process is treated as one unit of work.

Thanks, -Carrie
Teradata Employee
Carrie - we are using 13.10 on a 2650 appliance. I am trying to create a Utility Throttle for fastload. I see that in our current release of viewpoint that utilities can be controled through creating a session utility limit.

However, when I go into throttles/classification/request source/application there is an mload throttle but not a fastload throttle. From what I am reading in this blog I surmise that their should be a fastload throttle as well. Can you clarify this?

Also, one other thing...after creating the session utility limit and activiating the ruleset it shows active. I closed viewpoint and then logged in again. The ruleset show ready but the active property is now blank (nothing is active). Any ideas on what is happening?

Thanks!!
Teradata Employee
To create a utility throttle on FastLoad from an Appliance platform on 13.10 choose the sessions context, and select "Utility Limit" tab and then hit the "create utility limit" button. Pick a name and a limit count,etc. When you save it you should be able to specify which utility types you want to be included in the throttle.

Once you create the rule you can go to the "Utility Limit by State" tab and see it, as well as the other default throttle rules that you get automatically. You can't change the default rules, but you can add new ones.

With the appliance you do not get workoads so that path you indicated in your posting does not make sense to me on your platform. It sounds like you were trying to create a workload with utility classification specifying multiload. As I said above, to see the default uitlity throttle rules go to Session, then Utility Limit by State. Rules other than the default rule would have been created by someone at your site.

Sorry but I am do not have an answer to your final question.

Thanks, - Carrie
Enthusiast
Carrie,
We are on Teradata 13.10 and our lower environments recently increased FastExports. To accomadate more load utilities plus giving high preperence to FastExports, I am thinking to change the below two settings in DBSControl. MaxLoadAWT = 48 and Maxload Tasks = 12. This way if 12 Fastloads are in a Loading state ( 12*3 AWT = 36 AWT) , then 12 of Fexports can run at one time. As we are on appliance, we cannot depend on Viewpoint workload designer. Could you please tell me the pros and cons of my approach.

Thanks!
Raj
Teradata Employee
Hi Raj,

Changing MaxLoadAWT to 48 and MaxLoadTasks to 12 is reasonable if that load job concurrency level is adequate for your needs. You are correct that 12 FastLoads all in phase I will use 3 AWTS each, totalling 36, so you will never reach that AWT limit of 48, at least from FastLoad jobs.

However, the MaxLoadTasks setting ONLY applies to FastLoad and MultiLoad jobs combined once you have set MaxLoadAWT to a non-zero value. (MaxLoadTask WILL control FastLoad, MultiLoad and FastExport combined if MaxLoadAWT is set at the default of zero.)

If MaxLoadAWT is non-zero, then FastExports are always allowed to run unless the combined number of FastLoads + MultiLoads + FastExport jobs is already = 60. MaxLoadAWT only has an impact on MultiLoad and FastLoad. So with your settings, 48 FastExport jobs could run at the same time as 12 FastLoads.

But there is something else to think about since you are on 13.10. If you have any throttles (to control query concurrency, for example) active in your environment, or if the throttle category is enabled even if you don't have any throttles active, then you will need to rely on utility throttles to control load job concurrency. If the throttle category is enabled in 13.10, the DBS Control settings will not be honored, so your only choice is to use utility throttles. With utility throttles you can set FastLoads, MultiLoads and FastExports each with different limits, rather than relying on the less granular DBS Control settings.

Thanks, -Carrie
Enthusiast
Carrie,
Thank you very much.
1) We are on appliance box, can we still depend on utility throttles?
2) Is there any setting where we can change FastLoads + MultiLoads + FastExport jobs = to 25 instead of 60. This way I can allow 12 Fload and MLoad jobs and 13 Fexports at one time.

Thanks !
Raj
Teradata Employee
Hi Raj,

If you are on the appliance and you are on 13.10, you can create system throttles and/or utility throttles.

Without using utility throttles there is no way to specify something less for FastExport than what I stated earlier, unless you make MaxLoadAWT zero and don't use any throttles of any kind at all. If you do that, then you can set a combined system-wide limit for Multiload + FastLoad + FastExport to something lower, but it would be all together.

Utility throttles offer you more flexibility. They provides a very straightforward way for you to limit FastLoad + MultiLoad to 12, and also to limit FastExports to 13, if that is what you would like to do.

Thanks, -Carrie
Hi Carrie,

So when creating a utility workload, the query limit for that workload acts as the job limit field does in an Utility Limit?

Thanks

Brian
Teradata Employee
Brian,

Yes, exactly. But at the workload throttle level you can target a subset of the load jobs of that type, which gives you significantly more control over who is getting how many of the utility throttle load slots.

Thanks, -Carrie

Can you help to point me to where the utility limits are stored in the DBC database?

I'm able to see live in Viewpoint which sessions are using or waiting for a specific utility to become available, but I'd like to be able to measure this using metrics in the DBC database.  We are on v13.10.  Is this possible?

Teradata Employee

Utility limit definitions and current utility throttle counters are kept in memory in the parsing engine.

If you want to view what TASM sees in terms of throttle definitions and current throttle statistics, you can issue tdwmdmp -t from the operating system prompt.  You have to have DBC password to do that, however.  

There are also are some APIs that deliver real-time details about throttles and their statistics.  I have never used the APIs so I cannot advise you, but you can read about them in the API manual .  It's titled:

Workload Management API:  PM/API and Open API

There is a version for 13.10.

Thanks, -Carrie

Teradata Employee

Hi Carrie,

We are using 14.10 here.

In TASM we have created one tactical workload with criteria "Include queries that do not use all AMPs".

When we checked DBQL data we saw some TPTEXP queries (like sel * from table all amp query) getting qualified in tactical workload and their corresponding NOCLASSIFICATION and WDOVERRIDE flags set to True in DBQL.

We are not able to figure out why these flags are getting set to true and why these queries are getting classified in tactical workload.

Could you please help here?

Thanks,

Imad.

Hi Carrie,

We are using 14.10 here.

In TASM we have created one tactical workload with criteria "Include queries that do not use all AMPs".

When we checked DBQL data we saw some TPTEXP queries (like sel * from table) getting qualified in tactical workload and their corresponding NOCLASSIFICATION and WDOVERRIDE flags set to True in DBQL.

We are not able to figure out why these flags are getting set to true and these queries getting classified in tactical workload.

Could you please help here?

Thanks,

Imad.

Teradata Employee

Imad,

NOCLASSIFICATION and WDOVERRIDE flags are set to TRUE for statements that are within utility jobs.

It looks like the CHECK WORKLOAD END statement of TPTEXP was classified into the tactical workload instead of a utility workload with utility type criteria.  Consequently, requests following the CHECK WORKLOAD END on the control SQL session were not classified correctly because they were part of the same utility unit of work.

Make sure the utility workload with utility type criteria, where you want the TPTEXP jobs to run, is higher than the tactical workload in the workload evaluation order.  

Thanks, -Carrie

Teradata Employee

Imad,

Another alternative is to add exclude criteria to the tactical workload classification that excludes on TPT Export, or all utility types.

But check evalation order first.  

Thanks, -Carrie

Teradata Employee

Hi Carrie,

We tested with separate workload for utilities and kept it above tactical workload and its working fine now.

Thanks for quick response.

Imad.

Hi Carrier,

Could you please help me with what are all the possible cases/scenerios  this "WDOverride" flag got updated as "T" in dbc.dbqlogtbl.

Teradata Employee

For load utilities all work between the BEGIN WORKLOAD and END WORKLOAD are considered to be a Unit Of Work (UOW).  When a BEGIN WORKLOAD is received the UOW is defined and classified. All work within that UOW is run in the same workload (overriding where TDWM would have normally classified the individual queries).  Each request in the UOW has the WDOverride flag set to ‘T’ to reflect this override of the WD.

The WD override flag is also set if a user does a Set Session WD command to assign all work for a session to a specific WD.

Thanks, -Carrie

Hi Carrie,

Are the sessions in RESPONDING state considered for the Utility Throttle?

Does the sessions in RESPONDING state have any impact on the delay queue due to TASM throttles?

Please confirm.

Thanks..

Teradata Employee

A utility throttle evaluates and considers delaying a utility job at the CHECK WORKLOAD END statement.  CHECK WORKLOAD END is an SQL statement that procedes the utility logon.   If a utility job is delayed by a utility throttle the delay will happen before the load utility begins to run and before any sessions supporting the load utility are logged on.   Sessions supporting a utility can only be in the responding state after those sessions have been logged on.   For that reason, sessions in a responding state have no impact on the delay queue. 

A utility job holds onto the utility throttle slot until the end of the job, marked by the END LOADING statement.  The utility throttle slot cannot be freed until all AMP sessions are logged off.

Thanks, -Carrie

Fan

Hi Carrie,
While this blog focuses more on maintaing two levels of concurrency for Load utilities,trying to understand more if we can implement the same for ARC jobs.
In our case we have two PROD systems and data transfer happens between the systems both the ways using Datamover ARC protocal.Typically we see three
kinds of ARC jobs ie.Datamover writer jobs,Datamover reader jobs and BAR jobs , we have at high level utility limit defined which is classified as backup utilities.
Now after reviewing this blog,planning to implement three different workloads(to give DM wrtirers high throttle slots than remaining two)
that will get classified at username and utility level.
Requesting your suggestion to understand if TASM fairly throttle the ARC jobs(not at query level) if we classify new WDs at utility and user level?

 

Thanks,
Rakesh.