Changes to the TASM AMP Worker Task Event in Teradata 13.10

AMP worker tasks are the dedicated tasks inside each AMP that get the database work done.  As there are a limited number, this important resource is actively monitored by DBAs on busy systems.  TASM system-level events can take care of the monitoring for you, and based on a threshold you have set set, will trigger changes in workload management setup to manage the AWT shortage.  How AMP worker task events do their monitoring has changed in Teradata Database 13.10, and this posting describes those changes.

Background

When all AMP worker tasks are in-service, new messages arriving on an AMP will have to wait for their work to get started.   Workload management techniques can be applied to ensure AMP worker task exhaustion rarely happens, so that the more important work can run without delay, whenever that work enters the system. 

Please refer to my earlier two blog posting for more background on AMP worker tasks:

1. How to Calculate Your Max Number of Usable AMP Worker Tasks (December 2010)
2. Reserving AMP Worker Tasks (October 2011)

How TASM AMP Worker Task Events worked prior to 13.10

In Teradata Database 12.0 and 13.0, the AMP worker task event was triggered based on the in-use counts for Work00 (aka WorkNew) and Work01 work types having reached the in-use threshold defined within the event rule on a given AMP.   When you set up this event, you had to have specified this information:

1. An in-use AMP worker task upper limit (for Work00 and Work01 combined)
2. The number of AMPs that must be at, or exceed that threshold
3. A qualification time

Typically, users on 12.0 and 13.0 have set the in-use AWT threshold to be a little under the point of exhaustion, which is usually 62.  If you have increased your AWTs/AMP to be something greater than 80, the point of exhaustion will be higher.  If you have reserved AWTs for expedited queries, it may be less. 

See the posting How to Calculate Your Max Number of Usable AMP Worker Tasks  for details on how to know your AWT in-use exhaustion point under different situations.

http://developer.teradata.com/blog/carrie/2010/12/how-to-calculate-your-max-number-of-usable-amp-worker-tasks

How TASM AMP Worker Task Events Changed in 13.10

In Teradata Database 13.10, the AMP worker task event has been turned upside down.  While the event used to trigger on the in-use count for Work00 and Work01,  it now triggers on the number of AWTs remaining in the unassigned pool that can service Work00 messages. 

The option to specify a number of AMPs greater than 1 that must meet the condition is no longer present  in the AWT available event as it is redefined in 13.10.  The event will trigger when the first AMP reaches the specified threshold for the qualification time.   

In addition, only AWTs in the unassigned pool that are able to service Work00 (aka WorkNew) messages will be considered as “available”.  There are multiple factors that are considered when coming up with a formula to determine what constitutes “available” for the redefined AMP worker task event.

The formula for determining if the event criteria has been met has to factor in how many AMP worker tasks of a specific work type are exceeding their reserve pool count.   For example, if we know there are 3 AWTs in a reserve pool specifically for Work00 work type messages, and there are 5 AWTs of the Work00 work type active, we can conclude that only 2 AWTs have been removed from the unassigned pool to cover all 5 that are in use. The first 3 AWTs will have beed provided from the reserve pool.  The count of AWTs that are in use that are over their respective reserve counts is an important factor to consider, otherwise you will be over-counting the number of AWTs in use. 

The following graphic assumes that in addition to the standard 8 reserve pools (of 3 AWTs each) there are 3 additional reserve pools set up to support expedited queries.  In this example, the reserve count has been set at 9.   This means that Work08 reserve pool will have 9 and Work09 reserve pool will also have 9, and Work10 reserve pool will have 2.  Based on those assumptions, you can see what the in-use-over-reserves values would be for different work types using the specified in-use counts.

Please note: This graphic and the one following use the traditional work type names (WorkNew instead of Work00, WorkAbort instead of Work12).

Once the formula establishes the in-use-over-reserves count, it can plug that information into the additional calculations that will be needed.  A second important part of the available AWT formula, shown below, is identifying how many AWTs remain before the limit of 50 on Work00 is reached.  If there are already 50 AWTs of the Work00 work type active, then the available AWT event will show zero AWTs as being available, even if there are still AWTs sitting in the unassigned pool.  This is because the available AWT event only cares about AWTs that are available to service Work00 work messages.

In this graphic below, the assumption behind the example are shown in the gray box at the top.

Migration to 13.10

If you have an AWT in-use event defined in either 12.0 or 13.0, and then upgrade to 13.10, your event will be translated automatically for you.   For example, if you had specified 58 in-use AWTs as your threshold before going to 13.10, after migration your available AWT event will have as a threshold 4 available AWTs.   

Migration assumes the default number of AWTs of 80 per AMP.  If you have changed this default, or if you have reserved AWTs for expedited requests, the results of rule migration may not meet your needs.    In cases such as this, you can adjust your AWT event rule after migration is complete, to a threshold better suited for your situation.

On final point:   There is an enhancement underway to allow more than one AMP to be specified as part of the available AMP worker task event.  This enhancement may be available in one of the 13.10 point releases.  Contact the support center if you need this flexibility and would like more information on the enhancement’s availability.