In the past, you, like many people, probably considered 62 AMP worker tasks (AWT) as the logical limit that can be used to support user work at any one point in time. That’s how many AWTs by default are available to support Work00 and Work01 work types in combination, after you subtract out the AWTs that are held back for the standard reserve pools.  I’ll explain more about what’s behind that number 62 in a minute, but what I really want to get to in this posting is answering the following question:  How do changes to the number of AWTs per AMP and/or the use of the reserved AWT feature impact how many AMP worker tasks are available to support user-initiated work?

Background

First, a brief e-glossary, to make sure the words I’m using in this discussion mean the same thing to you:

AMP Worker Tasks:   Dedicated tasks on each AMP, created at startup, that support user-initiated or database work on the AMP (the default of 80 per AMP).

Work01/Work00:   Messages are categorized by work type as they are sent to the AMPs, with the Work00 work type (aka "WorkNew") representing a query step coming from the dispatcher, and the Work01 work type representing the first level of spawned work within a query step.  These two work types support most of the user work in the database.

Standard Reserve Pools:   There are 8 work types that are given reserves of 3 AWTs each by default, for a total of 24 reserved AWTs on each AMP.  This leaves 56 AWTs out of the original 80 in an unassigned pool that are available for any work type to use.  Reserve pools ensure that all the important work types have access to some AWTs at any point in time.

Where does the original 62 Limit for Work00 + Work01 Come From?

That 62 number I mentioned earlier is a calculated upper limit for the number of AWTs that are able to support Work00 and Work01 AWTs combined if the total AWTs/AMP are 80.  If you take the 56 unassigned AWTs on each AMP (80 total AWTs - 24 AWTs used in the standard reserves pools) and add to that the AWTs in the standard reserve pools of 3 each for Work00 and Work01, you get 62.  The formula that results in 62 is below:

#  AWTs/AMP - # AWTs in standard reserve pools + (Work00 + Work01 reserves)  

 

 

What Happens if I Increase the Total Number of AWTs/AMP?

When you increase the AWTs/AMP to a number greater than 80, there will still be the same number of AWTs contained with the standard 8 reserve pools (24).  That does not change.  The only thing that changes is that the number of AWTs in the unassigned pool will have grown.  The formula above remains the same.

 

What Happens if I Reserve AWTs for Tactical Apps?

Setting up new reserve pools for tactical queries is a 2-step process:

  1. Specify a reserve count
  2. Expedite one or more tactical workloads, which allows that workload’s queries to use these new reserve pools

When you specify the reserve count, 3 new reserve pools are set up:  Work08 for new expedited work, Work09 for spawned expedited work, and Work10 for the 2nd level of spawned expedited work.  The reserve count  becomes the number of AWTs placed in both Work08 and Work09 reserve pools.  The Work10 reserve pool is always made up of 2 AWTs, primarily because the second level of spawned work doesn’t happen very frequently at most sites. 

When you reserve AWTs for a tactical workload you remove the AWTs that will constitute the new reserve pools from the unassigned AWT pool.  So one result of using the reserve option is that there are fewer AWTs available for non-expedited user work.  If you add up the AWTs held in each of the new reserve pools, that is the number of AWTs removed from the unassigned pool.

With reserved AWTs, the formula becomes: 

#  AWTs/AMP - # AWTs in standard reserve pools -  AWTs in expedited reserve pools + (Work00 + Work01 reserves)

What Happens if I Increase AWTs and also Reserve AWTs for Tactical Apps?

Here's the formula I use for determining the logical limit of Work01 and Work00 when the #AWTs/AMP has changed and at the same time there are expedited reserves in place:

#  AWTs/AMP - # AWTs in standard reserve pools - # expedited reserves + (Work00 + Work01 reserves)

If we specified 100 AWTs/AMP and set a reserve at 5, that would result in 70 AWTs being available to support un-expedited user work.

 

These examples assume you want to know the number of AWTs available for non-expedited user-initiated work.  They can be tweaked to provide similar logical thresholds for both expedited and non-expedited work.   The results provide you with reasonable logical upper limits that you can use as a foundation for monitoring AWT usage, or for setting thresholds.

Discussion
y0ngb00n 6 comments Joined 12/07
07 Dec 2010

Under which circumstance should we increase the default AWT of 80?

In one of the site l worked, the CPU utilization is always 100%, and the AWT constantly between 58 to 62 from ViewPoint (and blocking arise), under this scenario, does increase the AWT help? Will this degrade the overall performance on a busy system?

Regards,
YB.

carrie 595 comments Joined 04/08
08 Dec 2010

YB,

There is no value in increasing the number of AMP worker tasks per AMP if your platform resources are already maxed out. If you are at 100% CPU utilitization, then there will be no additional CPU available to give to additional tasks that might use those added AWTs. By increasing AWTs under these conditons you will be increasing contention and creating more competition for scarce resources.

In addition, each AWT will require some amount of memory, perhaps as much as 1 to 2 MB for each AWT/AMP added. In addition, Priority Scheduler will have more tasks to swap in and out and there could be contention for other internal structures.

The times when it is appropriate to consider an increase in AWTs is when hardware resources are not able to fully utilized with the default of 80, or if reserved AWTs for tactical work have left the unassigned pool under-supplied.

It's advisable to talk to the support center before deciding that an increase in AWTs/AMP is right for your site. They can help you make the correct decision.

y0ngb00n 6 comments Joined 12/07
08 Dec 2010

Carrie, thanks again for your wisdom guide, interesting to note the overhead on Priority Scheduler/TASM to swap in/out.

Merry Christmas!

Regards,
YB.

AbeK 37 comments Joined 08/09
09 Dec 2010

Carrie, thanks for this clarification. Is there any documentation to map what type of step within an SQL would need what type of MSGWORK**
Your help is greatly appreciated.

carrie 595 comments Joined 04/08
13 Dec 2010

Basically, user-initiated work primarily uses Work00 (aka WorkNew) and Work01 work types. A step coming directly from the dispatcher is always a Work00 type. Spawned work, such as the receiver task being used during row redistribution or table duplication, will use a WorkOne work type.

Thanks, -Carrie

rp161400 1 comment Joined 10/09
26 Jan 2011

Hello Carrie,

How the MaxloadAWT setting impact the overall AWT availability. If we set the Maxloadawt parameter=48 does this reserve 48 awt from the avilable awt pool for load tasks?.

carrie 595 comments Joined 04/08
27 Jan 2011

Setting MaxLoadAWT to 48 does not hold back any AWTs in reserve to only be used for load jobs. What it does is prevent new load jobs from starting up if they would cause that limit of 48 to be exceeded. In other words, it puts a limit on how many AWTs can be used to support load jobs. It is a way to make sure only a specific number of AWTs will ever be used for loading.

Thanks, -Carrie

rajanimandava 5 comments Joined 01/11
25 May 2011

Hi Carie

IN TD Manager, I always see 49 AWTs for my linux 5550 box. Is there a change in Linux nodes?

Thanks -Rajni

carrie 595 comments Joined 04/08
26 May 2011

Hello Rajni,

There is no change with Linux in terms of how AMP worker tasks are being used or how counts are being collected.

If you are using Teradata Manager VPROC report to get AMP worker task counts, I would suggest you switch to one of the other more recommended approaches for assessing in-use counts.

The most reliable approaches are to use the AWTMON utility or puma -c (from the operating system prompt) or the ResUsageSAWT table. All of those approaches have proven to be highly reliable, and break down AMP worker task usage by work type, which can be helpful.

You can also see AMP worker tasks levels, without the breakdown by work type, in the Metrics Analysis portlet in Viewpoint. You can see both the average and the max total AMP worker tasks being used over a time window, which can be useful for making assessments of how fully this resource is being used. Then drill down to ResUsageSAWT if you need more detailed information.

Thanks, -Carrie

SmarakDas 25 comments Joined 02/12
01 Oct 2012

Hi Carrie,
Thanks for the article.
1 Question though, when we receive Alerts for AWT Saturation, is it applicable for all the 80 AWTs (Reserved + Unreserved) or is it meant for the 62 (i.e. Excluding Tactical Reserved). Again, What qualities are assumed from Tactical Queries ? Are these more like Canary Queries with more Business Orientation ?

carrie 595 comments Joined 04/08
09 Oct 2012

If your question is about Viewpoint Alerts as documented in the Teradata Alerts manual, this alert considers available AWTs, not AWT saturation:

Viewpoint will generate Alerts based on the ‘Available AWTs’ as reported by the AMPLoad utility (up to Teradata 13.10) or by the “MONITOR AWT RESOURCE” PM/API (Teradata 14.0 +).

Available AWTs are AWTs remaining in the unassigned pool, so tactical reserves will not be a factor.

Qualities that are assumed for tactical queries include that they are very short, light resource-consuming queries of an business-operational nature, that they have a very short response time expectation, that they are highly tuned, repetitive (as opposed to ad hoc) and are used for tactical-type (as opposed to strategic) decision making purposes.

SmarakDas 25 comments Joined 02/12
09 Oct 2012

Thank You Carrie. Your comments are always highly anticipated and appreciated. Thanks for the help again.

vasudev 35 comments Joined 12/12
21 Jan 2013

Hi Carrie,

Thank you very much for such a nice blog on AWT. So in short AWT's are
1. They are limited to 62(active) per AMP.
2.They can be reserved for special use.
3. Additional AWTs are created when needed.
4. They are acquired at the beginning of the query and released at the end of the query.
Please correct me if i am wrong.

carrie 595 comments Joined 04/08
23 Jan 2013

Response 1: 62 is the number of AWTs that will be available to support user work if you have the default number of 80 AWTs per AMP. I think that is what you were trying to say, but I wanted to clarify the point.

Response 2: Yes. But AWT can only be reserved by users for workloads marked "tactical".

Response 3: No, additional AWTs are not created as needed. You have to make change to internal system settings if you want more AWTs per AMP, beyond the default of 80, and this usually involves calling the support center and asking for their guidance.

Response 4: No, AWTs are not acquired at the beginning of a query. They are acquired at the beginning of a query step, and released when that query step is complete. A given query may have one step or it may have hundreds of steps, each step has to get its own AWT when the message for that step arrives on the AMP.

Thanks, -Carrie

gsoh 14 comments Joined 08/12
24 Jan 2013

HI, Carrie
This article is really helpful.
I've got a question.Formula mentioned above, please explain once again. Why Reserve at 5, But Why -12...

If we specified 100 AWTs/AMP and set a reserve at 5, that would result in 70 AWTs being available to support un-expedited user work.

carrie 595 comments Joined 04/08
29 Jan 2013

You second paragraph is correct. If you have 100 AWTs per AMP, 24 will be used for the standard reserve pools, leaving 76 (100 - 24 = 76). Add 6 to that 76 (because you can use three each for the reserve pools for work00 and work01) and you end up with 82 AWTs available to support user-initiated non-expedited work for of the work00 and work01 work types.

If you specify a reserve of 5, that will take 12 AWTs out of the unassigned pool (5 for work08, 5 for work09, and 2 for work10), leaving 70 available for the non-expedited queries.

Thanks, -Carrie

macktd 6 comments Joined 09/12
08 Feb 2013

Hi, Carrie
I Have Dubious on Work Eight, Supports 20 unreserve pools
Work nine, Supports 20 unreserve Pools
Work ten supports 2 unreserve pools

My Question is 24 are reserved out of 80

80-24+3(workoo)+3(work01)=72 awts
42awts (work8+work9+work10) out of 72 awts =30 awts

What about the remaining 32 awts i want? Please Suggest me.

carrie 595 comments Joined 04/08
11 Feb 2013

If your question is how many AWTs will be available for unexpedited user-intitiated work if you select a reserve count of 20, here is the math on that:

You start with 80 AWTs per AMP, and then take away 24 for the standard 8 reserve pools of 3 AWTs each:

80 - 24 = 56

Then you add 3 back in for work00 reserves and 3 back in for work01 reserves, because those are available to support unexpedited user-initiatied work.

56 + 3 + 3 = 62

Then, if you have chose a reserve count 20, you will have to remove from the unassigned pool 20 for work08, 20 for work09 and 2 for work10:

62 - 20 - 20 - 2 = 20

If your reserve count is 20 that will leave only 20 AWTs available to support unexpedited user-initiated work of the work00 and work01 work types.

You might want to recheck your math in this calculation of yours:

80-24+3 (for work00 reserves)+3 (for work01 reserves)=72 awts

The resultl from the calculation should be 62 I think, not 72.

However, most site do not make reserve counts as high as 20, a reserve count of 2 to 5 reserves are much more common. But that is how the math works if you were to choose a reserve count of 20.

Thanks, -Carrie

macktd 6 comments Joined 09/12
11 Feb 2013

Thanks a lot Carrie, Ur reply as well as Correction my math.

-mack

vasudev 35 comments Joined 12/12
14 Apr 2013

Hi Carrie,
Whether the performace of tactical queries can be improved by creating a reserve of AWT for workload defn or Isolate the queries in their own workload definition? Please advise
 

carrie 595 comments Joined 04/08
16 Apr 2013

Tactical query performance may be improved by specifying a reserve count for expedited queries.  It will depend on whether or not the system is short on AWTs.  If there is no shortage of AWTs, there still may be some advantage, as expedited queries (such as tactical queries) will receive some small additional internal performance boosts.  But mainly the reserve pools are the big advantage, and they will only help if AWTs are exhausted and that condition is impacting the tactical work. 
 
It is always better to isolate tactical work into its own workload, so you can give it a high priority apart from non-tactical work.  It is also easier to monitor its performance and resource usage when it is in its own workload.
 
Thanks, -Carrie

usmans 5 comments Joined 01/13
10 Jun 2013

Hello Carrie,
Thanks for the read. I got to this after doing a WBT of yours on AWTs. (made the reading all the more easier).
I have a question.
The project I am currently working on consistently uses AWTs in the range of 50-60 and above. The system is maxed out, mostly due to tactical queries being run with stale stats (I think).
I have run Larry Higa Graph macros and in the concerned graph regarding AWT I find mentioned "MAX MAX AWTs" and "AVG MAX AWTs".
Can you please explain what they are? So I can come to a solution to prevent the system from going into "Flow Control" on a fairly regular basis?
Would appreciate the answer.
Thanks

--Regards
Usmans

carrie 595 comments Joined 04/08
12 Jun 2013

It is good to hear that the webinar was helpful.
 
I don't use the Higa charts, but I believe that the MAX of MAX AWTs is the number that represents the highest inusemax count across all the AMPs in the system.  It represents the worst case AWT usage on any AMP.   I believe the AVG of MAX is the number of AWTs that represents an average of each AMP's inusemax count.
 
You may want to look at the macro code itself, if you have access to it, and validate that those are the calculations behind those labels.
 
The best way to keep from running out of AWTs or going into flow control is to use throttles on the moderate and low priority work.  If you control how much work you allow to become active at the same time, then fewer AWTs will be required.
 
Thanks, -Carrie

usmans 5 comments Joined 01/13
12 Jun 2013

Hello Carrie,
Thanks for the quick reply.
What you have said makes sense of using throttles and limiting the number of concurrent queries from the user using the AWTs so much. I cannot give him a low priority workload because then the queries will take a longer time to run and will keep their AWTs with them. (again no more AWTs).
I understand if the system is already maxed out then increasing the number AWTs per AMP would have no real effect on performance, am I correct?
Also when you say:

 the MAX of MAX AWTs is the number that represents the highest inusemax count across all the AMPs in the system.

does that mean, (for the sake of my understanding) if AMP 1 is using 62 AWTs and AMP 2 is using 42 AWTs at any instant on the system then the graph would show 62 AWTs being used at that moment?
And when you say:

the AVG of MAX is the number of AWTs that represents an average of each AMP's inusemax count.

does that mean, (for the sake of my understanding let's take the above example of 62 and 42 AWTs) that the graph would show an average value of 52 AWTs being used at that time?
Thanks
 
 
 

--Regards
Usmans

carrie 595 comments Joined 04/08
14 Jun 2013

You would need to look at the macro code SQL to know exactly how those fields are being calculated.  I am not an authority on the macro you are using, and unfortunately I do not have a copy with me.   What you say makes sense, but I cannot say for sure.   I suggest you post your question on Teradata Forum so a broader audience can be reached.  Likely there is someone with more familiarity with those macros who can give you a solid answer.
 
You are correct that if you system is maxed out, adding AWTs per AMP will not help, it could make things worse.
 
Thanks, -Carrie

usmans 5 comments Joined 01/13
16 Jun 2013

Thanks for the response, Carrie

--Regards
Usmans

Dwaipayan 1 comment Joined 09/08
18 Jan 2014

Hi Carrie,
Thank you so much for the nice explanation on AWT. reading your blog my almost my doubts are getting cleared.
I have one question,
If i want to increase the AMP/AWT max limit =100 ( from default 80 to 100 AWT/AMP). which parameter i have to change it and where i have to make the changes. clould you please advise me. i am using the TD12 and TD 13.10 version of teradata.
 
Thanks,
Das
 

carrie 595 comments Joined 04/08
21 Jan 2014

Das,
 
Changing the number of AMP worker tasks per AMP requires the modification of an internal DBS Control setting called MaxAMPWorkerTasks.  It will require a database restart. 
 
Because it is an internal setting and has a non-trivial impact on the system, I recommend that people who want to change the total number of AWTs per AMP on their platform call the support center for assistance/guidance.  They are able to provide recommendations to you based on looking at the entire environment and considering all the tradeoffs.  
 
Internal settings such as this are not considered to be customer tunables and are not something you want to do your own using trial and error.
 
Thanks, -Carrie

Ana 2 comments Joined 11/09
12 Aug 2014

Hi Carrie, could you please help me with the following question regarding AWTs consumption?
- My customer has 50% COD in his TD platform (2+1HSN 6700H)
- Max of AWTs crosses 62 several times which indicates high concurrency and is a cause of concern.
- CPU is about 90% from 5am to 7pm.
My question is, releasing CoD will not help because this will not increase #AWT right?
To lower concurrency we have to work with tasm right?
 
 
 
 

Ana 2 comments Joined 11/09
12 Aug 2014

Hi Carrie, could you please help me with the following question regarding AWTs consumption?
- My customer has 50% COD in his TD platform (2+1HSN 6700H)
- Max of AWTs crosses 62 several times which indicates high concurrency and is a cause of concern.
- CPU is about 90% from 5am to 7pm.
My question is, releasing CoD will not help because this will not increase #AWT right?
To lower concurrency we have to work with tasm right?

carrie 595 comments Joined 04/08
13 Aug 2014

Hi Ana,
 
Releasing COD will not increase the number of AMP worker tasks to your platform, but it will increase CPU and I/O resources, so requests that are holding AWTs should be able to complete sooner, release their AWT sooner, and allow AWTs to be available for other work sooner.  I would expect you would see lower AWT usage levels with more processing power available if the active work running on the platform is the same.
 
It is not necessarily problematic to have more than 62 AWTs active at the same time (assuming you have the default of 80 AWTs/AMP defined).  As long as a delay in getting an AWT for some of the requests is not causing problems meeting a response time goals.  However, if you are reaching 62 in-use AWTs from time to time, it is an indication that you are begininng to run out of AWTs, so workload management throttles are usually a good idea to consider, if you haven't already.  If yo use throttle rules, you can decide which workloads you want to experience a wait at busy times (by means of a throttle delay queue), rather than having it be randomly determined which queries will have their work messages wait in the work message queue.
 
Thanks, -Carrie

dins2k2 9 comments Joined 05/13
03 Nov 2015

Carrie,
Is there any way to calculate AWT consumed by a user/session? If no, can we use any colums to find out the same?
 
Thanks,
Dinesh

carrie 595 comments Joined 04/08
05 Nov 2015

Dinesh,
 
There is no metric that reports the number of AWTs being used within a request or by a sessoin at any point in time, or even at the end of the request.   The ResUsageSAWT table tells you the total  number of in-use AWTs by work type by AMP within a logging interval, and the ResUsageSPS table tells you the total number of in-use AWTs by workload by node.  That's as low as the reporting granularity goes when it comes to AWTs.
 
However, if you look at a query plan, you can guess how many AWTs are being used (on each AMP) by a query, if the query is doing all-AMP steps.  
 
From the AMP Worker Task orange book, Section 4.1:
 
4.1.         All-AMP Steps
 
Queries dispatched from the optimizer may use one or more AMP worker tasks per AMP at any point in time.   A simple step, such as scanning a table, will require only one AWT per AMP.  All-AMP steps that include such operations as row redistribution, table duplication or global aggregation will use two AWTs per AMP.
 
There could be up to four AWTs per AMP, possibly more, being used by a single query at one point in time. This might be the case if the query plan includes two parallel steps, and both steps include row redistribution, or if there were four parallel steps and each was using a single AWT.  Even when the optimizer specifies two or three or more steps to be executed in parallel, it is the execution code in the AMP that determines which and how many of those parallel steps actually can be run at one time.  Because there are limits on the number of BYNET channels that a given query can use, it is unusual to see more than four AWTs per AMP in use by any one query.  
 
As discussed in Section 2.3, BYNET channels, which are used for inter-AMP activities such as completion coordination and error signaling, come with limits.  Redistribution and other spawned work requires more channels to manage the more extensive inter-AMP work they are doing, therefore fewer steps can actually be executed in parallel when they involve spawned work. 
 
Thanks, -Carrie
 

You must sign in to leave a comment.