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.

Discussion
TeraAbe 37 comments Joined 08/09
09 May 2012

Thanks for the detailed information, this would be very helpful.

Lords 1 comment Joined 07/12
09 Jul 2012

Hi,

I have 10 different .txt file, which contains the BTEQ script within.

I can able to run those filese through the command prompt.

Now i want to call all the .txt files within a .bat file.
So that, i can schedule the .bat into a windows scheduler.

Can some one help me in geting the script for that .bat file?

Thanks,
Lords

carrie 412 comments Joined 04/08
09 Jul 2012

Lords,

Unfortunately, your question is not related to the topic of this posting, and is not an area that I have experience in. I would suggest you post your question on Teradata Forum.

http://forums.teradata.com/forum

Thanks, -Carrie

mikesteeves 3 comments Joined 10/11
13 Nov 2012

Excellent description of the AMP Worker Task event and how it changes in 13.10...exactly what I was looking for. Thank you Carrie!

carrie 412 comments Joined 04/08
13 Nov 2012

Mike,

Thank you so very much for the thoughtful comments. Glad the posting helped. -Carrie

gsoh 14 comments Joined 08/12
07 Mar 2013

Carrie, 
Reserved awt number was changed the v13.10 ?

Because, why expedited awt calculated like this number 9

so, when i setting with number 9?, that number was 9+9 +2=20?

still confuse.

many thanks

carrie 412 comments Joined 04/08
11 Mar 2013

GS,
The maximum number of reserved AMP worker tasks that you can specify has been at 20 since Teradata 12.0.1 release.  It has not changed in 13.10.
If you select a reserve of 9 for expedited queries, three new reserve pools will be set up for you:
9 AWTs will go to Work08 reserve (new expedited work)
9 AWTs will go to Work09 reserve (spawned expedited work)
2 AWTs will go to Work10 reserve(spawned spawned expedited work)
9 +  9 + 2 = 20
However, if you select 20 for your reserve count, then the reserve pools for expedited work will be 20 + 20 + 2 = 42.
I think I have already responded to you on this question on the Developer Exchange carrie blog posting titled "Reserving AWTs, Don't Let Parameters Confuse you," in case you did not see it there already.
Thanks, -Carrie

ramnathjk 1 comment Joined 03/14
10 Apr 2014

Just wanted to understand, how much the default system can handle the requests simulatenously....
We say that the limitations of :
a. one PE can handle 120 sessions in parallel (http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/Utilities/B035_1102_111A/Gtwglobal.36.001.html)
b. Each session can run 16 requests in parallel
 (http://www.terateamuk.com/teradata_glossary_of_terms/teradata_terms_a-d/teradata_Amp_Worker_tasks.html)
(http://my.safaribooksonline.com/book/databases/9781940540184/chapter-3-amp-worker-tasks/sub4_chapter03_xhtm)
 
c. Each AMP will 80 tasks in parallel
So,
1. PE sessions, handled in parallel would be = 120 sessions * 16 requests = 1920
2. Tasks handled by each amp = 80 in parallel, So No.of Amps required to handle 1920 requests would be,
1920 / 80 tasks = 24 AMPs (Does this mean that we require minimum 24 AMPs to handle 1920 requests for the above mentioned limitation)
 
Is this correct ?
 
 
 

carrie 412 comments Joined 04/08
14 Apr 2014

Requests are different from tasks.  The request comes from the user within a user session.  One request can be sumitted through a session at one time.  
 
Usually, each AMP will use from 1 to 2 tasks to support it's work on behalf of one request.   Most user work that enters Teradata is all AMP work, so each AMP will have at least one task (possibly 2) working in parallel on behalf of the same query.  So having more AMPs does not mean you get to run more requests in parallel, unless you are doing single-AMP access only.  Having more AMPs means the work of a query step is divided across more AMPs and more parallelized.
 
All 80 AMP worker tasks on an AMP are rarely used at the same time.  Generally, with 80 AWTs per AMP, you will never see more than 62 AWTs active supporting user work.  This is because there are reserve pools that are holding back some number of those AWTs for special, internal purposes.  So if you assume each of the active requests are using 2 AWTs per AMP, then you could be supporting 31 requests at the same time if they were all-AMP requests.    More commonly you have a mix of requests some that require 1 AWT per AMP and others that require 2 AWTs per AMP.  In that case you could see anywhere from 45 to 60 requests actually executing at the same time.  Of course, there could be many other requests in the process of executing that don't happen to be running in the AMPs at that point in time, that have steps that are waiting to get an AWT on one AMP or another, or that are waiting in a throttle delay queue to start executing.
 
Thanks, -Carrie

srinivas486 2 comments Joined 02/12
1 month ago

Carrie,
As per your last comment, "Usually, each AMP will use from 1 to 2 tasks to support it's work on behalf of one request"
If we issue a multi statement request involving selects from different tables or if we issue a join query involving different tables. My assumption regarding the query execution is, The operations can be performed parallely. even though if it is a single request, AMPS can make use of more AWT's (more than 1 to 2) to perform the parallel operations.
I think No of AWT's that requires to run a user request depends on how many parallel operation steps the query/request can be broken into.
Please correct me if i am wrong and help me understand.
 

-Lakshmi Srinivas Bodepu

carrie 412 comments Joined 04/08
1 month ago

Lakshimi,
 
You are correct that parallel steps can result in more than 1 or 2 AMP worker tasks being used by a single request, particularly if it is multistatement request.    But even with multiple parallel steps in the plan, most requests will be limited to 4 AWTs at the same time. 
 
This is because of limitations related to other internal structures (called channels) that manage cross-AMP communication within a request.   Things like step completion coordination or abort coordination are performed by channels behind the scenes.  So you could have a plan with 8 parallel steps in the plan, but at run-time they will not all execute at the same time.   If the first 4 steps only use a single AWT each, then all 4 could run at the same time.  But if the first two required 2 AWTs at the same time, then only the first two would actually run at the same time. 
 
Even if you hade two parallel steps and each was doing row redistribution and so required 2 AWTs each, and you had 4 AWTs active at a time, there is usually one of the two parallel steps that is longer running while the other is usually much shorter, so the actual time that 4 AWTs are held by the request might be shorter than you would expect.  However,  If there are other parallel steps to be run in that high-level step, they will run, as long as the combination of sub-steps doesn't require more than 4 AWTs.
 
Thanks, -Carrie

srinivas486 2 comments Joined 02/12
1 month ago

Thanks for the infomation provided Carrie. It really helps me understand AWT's better.

-Lakshmi Srinivas Bodepu

You must sign in to leave a comment.