When Load Utility Throttle Slots Are Released

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

When Load Utility Throttle Slots Are Released

 

Many sites are managing concurrency of their utility jobs by means of utility throttles combined with workload throttles.  Utility throttles set system-wide limits for total number of utility jobs of a specific type that can run concurrently.  For example, you may not want more than 20 load jobs from any source running at the same time.  Workload throttles (specifically on workloads that include “Utility” classification criteria) can be used to limit load jobs by user or by application.  For a given load utility to run, both the counter for the utility throttle and the counter for any applicable workload throttle must be under their respective limits.

 

Utility jobs running in the Teradata Database have a utility unit of work (UOW), as illustrated in the graphic below. The UOW begins on the utility's CHECK WORKLOAD END statement. The UOW completes on the END LOADING statement.  If a utility is delayed due to throttles, that delay happens on the CHECK WORKLOAD END statement, the point at which the UOW begins.  The check to see if the utility can run immediately, or be delayed is made before any utility sessions are logged on, so no active sessions are held while a utility job is in the delay queue.  

 

Utility_UOW.jpg

 

 

 

 

 

 

 

 

 

 

Both utility throttles and workload throttles (specifically, workloads with “Utility” classification) will allocate their throttle slot (if one is available) as part of the CHECK WORKLOAD END statement.  As mentioned above, this is also the statement where all the utility sessions are logged on.  On a busy system there could be a gap between the CHECK WORKLOAD END STATEMENT and the BEGIN LOADING statement, due to the time it is taking to connect all the utility sessions under those conditions.

 

When the UOW is complete at the END LOADING statement, the workload throttle will release the throttle slot and decrement its counter. At that point the workload throttle will be able to give that throttle slot to another utility job.

 

However, the utility throttle will not release its throttle slot until all of the clean up work related to the utility has been completed. This clean up includes SQL, such as DROP TABLE, that takes place after the END LOADING statement. This clean up work is not part of the utility job’s UOW and will classify to a workload as a normal SQL statement. But from the perspective of workload management, this post-UOW clean up is part of the loading activity, and has to be completed before a system-level utility throttle slot is made available to another utility job.

 

Normally, this window between when the workload-level throttle slot is released and the system-level utility throttle slot is released is very minor and not usually noticeable.  But if the system is very congested, and especially if there is contention on the AccessRights table, the clean up work may take longer. Under those circumstances it may appear that utility throttles are holding their throttle slots longer than they should.  Recognize that the system-level utility throttle slot is being held during this clean up activity.  This is normal behavior, and can be resolved by addressing the causes of contention that are slowing down the clean up work.

4 Comments
Senior Apprentice

Hi Carrie,

Thanks for that info.

 

In this article you refer to the "utility's CHECK WORKLOAD END statement", specifically stating that the UOW starts at this point.

 

I'm not aware of any of the utilities having this command. I'm guessing that this is this an 'internal' command issued by each utility.

 

What I'm trying to do is to line up this processing with what I'd see in a script. If I can do that then I know (and I can tell customers) about the start/stop of the UOW as it relates to their script, and also to the times of their script statements as shown in output logs.

 

My initial stab at this would be that the UOW is as follows for TPT Load (FLoad) and TPT Update (MLoad).

TPT Load

  • UOW starts/ends with the 'step' that contains the APPLY to the LOAD operator.

FLOAD

  • UOW starts with the INSERT statement.
  • UOW ends when the 'END LOADING' has completed.

 

TPT Update

  • UOW starts/ends with the 'step' that contains the APPLY to the UPDATE operator.

MLOAD

  • Starts with the BEGIN MLOAD command.
  • Ends with the END MLOAD command.

 

All help greatly appreciated.

 

Cheers,

Dave

Teradata Employee

Hi Dave,

 

Although technically the CHECK WORKLOAD END is the point in which the unit of work begins for load utilities, you will not see that statement in the load script. You are correct in your assumption that this is an internal command. (It will however, show up in DBQL.)

 

     For the FLOAD script the UOW beginning point is BEGIN LOADING

 

     For the MLOAD script the UOW beginning point is BEGIN MLOAD

 

I’ll have to validate the TPT assumptions you are making, and will post another response when I have that information at hand.

 

Best regards, -Carrie

 

Teradata Employee

Dave,

 

A continuation of the previous response...

 

The TPT operators hide a lot of the commands that are sent to the DBS, and that show up in the legacy utility scripts as part of their script language.  What you have assumed above about when the UOW starts for the TPT operators is about as accurate as one can get from the  point of view TPT operator scripts.

 

Best regards, -Carrie

 

 

Senior Apprentice

Great. Thanks!