Reserving AMP Worker Tasks? Don’t let the Parameters Confuse You!

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

Reserving AMP worker  tasks (AWT) for tactical applications is a technique to protect business-critical, short queries, when the platform is experiencing AMP worker task exhaustion.   If you are thinking about reserving AWTs, there are two different settings for which you will be required to provide values.  This posting discusses what these two settings are and how to be smart about setting them.

What are Reserved AMP Worker Tasks

By default, each AMP has 80 AMP worker tasks (AWT) to service all work messages that arrive on the AMP.  If the system is very busy, all the AWTs could be in use at the time a new message that requires an AWT arrives.   In that case, the message waits in a message queue until an AWT frees up.  Usually this is a short delay, but even a short delay can be costly for a tactical query, when the response time expectation is 1 or 2 seconds or less.  

For that reason, there is an option for tactical workloads that allows you to set up a special reserve pool of AWTs exclusively for tactical queries.  In order to take advantage of the new reserved pool, the workload or the allocation group must have been flagged as expedited by the administrator.   If you are using TASM, then the expedited status is automatically given to a workload that has a tactical  enforcement priority.   Once a workload has been marked as expedited, all queries that run under its control will use a different, higher message work type and can draw AWTs from the new reserved pools, if a reserve count greater than zero has been specified. 

Notice I said “reserved pools” above, as there is more than one reserved pool set up as a result of your selecting a reserve count greater than zero.

Three Reserve Pools

Some short background.  Reserve pools of AWTS are defined for specific work types.  Eight work types automatically have their own reserve pools built at system startup.  For example, all new, user-initiated work messages (representing query steps coming from the dispatcher) are categorized as MsgWorkNew (aka MsgWork00) work type.  MsgWorkNew has its own reserve pool of 3.   

When you select a number greater than zero at the Reserved AWT prompt, that becomes the number of AWTs that will be moved into the first two of the three new reserve pools that support expedited queries.  The first pool, for MsgWorkEight work type messages, accommodates new messages coming from the dispatcher for all expedited queries.  It is equivalent to MsgWorkNew which is the work type used by non-expedited new messages.  The second pool, for MsgWorkNine work type messages,  accommodates spawned work from MsgWorkEight AWTs.   Both MsgWorkEight and MsgWorkNine are the same size, reflecting whatever reserved count you specified.  

The third pool, for MsgWorkTen work type message, is not used very often, and is intended for spawned-spawned expedited work messages.   MsgWorkTen reserves are always kept at two AWTs,  no matter how high the reserve count you select.

Twin Settings

There are two settings to be aware of when it comes to reserving AWTs.  If you are using TASM, you will see two prompts at the bottom of the Priority Distribution Screen that apply to reserving AWTs.  They follow the “System Values” heading and the "CPU limit" prompt.  They look like this:

The first parameter, Reserved AWTs, is the number of AMP worker tasks that will be placed in the new reserve pools for MsgWorkEight and MsgWorkNine.

The second parameter involved in reserving AWTs is called either AWT Limit or Max Expedited AWTs (in more current releases).   What it represents is the maximum number of messages that will be allowed to get a MsgWorkEight  type of  AWT at any point in time.  If you set AWT Limit to 50, then if you were to have 50 expedited messages representing new work from the dispatcher that are running in MsgWorkEight, then the 51st such message would be placed in the delay queue.   That setting is an upper limit of how many messages of the MsgWorkEight work type are allowed to run concurrently.  It acts sort of like a throttle for new expedited messages.

AWT Limit (or Max Expedited AWTs) enforces  identical functionality to what has always existed in MsgWorkNew, the more standard work type for new work, which has a hard limit of 50 on how many messages it can run.   This limit ensures that not too many new work messages get started, because if they all need to spawn work at some point, if there are more than 50 there would be inadequate AWTs available for the spawned work.   (Technically 3 AWTs would always be available because MsgWorkNew has its own reserve pool of 3, but that would be inadequate to service 50 new messages, all with spawned work.)

In point of fact, you can only specify a reserve count of 20 AWTs, so you will run out of reserved AWTs before you will hit the default limit of 50 concurrent active messages on MsgWorkEight AWTs.  However,  expedited queries will not necessarily be delayed if you run out of reserves.  If you do exceed your reserve count, the 21st MsgWorkEight message would have to try to get an AWT from the unreserved pool, if any are available there, since all the AWTs in the special reserve pool had been taken.   The unassigned pool is able to service any type of work request, even if queries running in that work type have exceeded its reserve count.

What Makes a Good Setting

The reserve pools are intended to service tactical work only.  Usually this is small portion of the overall work running on a Teradata system.  In  order to understand how many AWTs will be supporting the new reserves, when you select a reserve count, double that number (for MsgWorkEight and MsgWorkNine pools) then add 2 (for MsgWorkTen).  That is the number of AWTs that will be taken out of the unassigned pool of AWTs .

Because tactical reserves comes out of the unassigned pool, you will need to balance the needs of your tactical work against the needs of the other active work.   A good starting point is to find out what the peak demand for AWTs is for the tactical work.   Look at the WorkTypeMax08 numbers in the ResUsageSAWT table.  If you have given the workload an enforcement priority of tactical, then it will already be expedited and will be using MsgWorkEight work type.   So all of its activity will show up in WorkTypeInuse08 and WorkTypeMax08 fields. Consider making your reserve count equal to the usual high usage numbers for WorkTypeMax08.

Precision is not required when setting the limit parameter.  You just want to make sure it is not too low so it will lead to MsgWorkEight messages being queued when only a few are active.   For example, you wouldn't want to set the limit at 5 or 6, or probably anything below 20.  Its not a bad idea to set it the same as MsgWorkNew, at 50, since the theory behind that is to prevent new work from flooding the system.  But also keep in mind that while there may not be any real harm in doing so, there is also no value in setting it too high, over 80 for example, as its unlikely you would have that many available AWTs to support that level of concurrency for MsgWorkEight anyway.  Bottom line, setting the limit on MsgWorkEight AWTs anywhere between 30 and 60 is probably reasonable.  If you're not sure how to set it, make it the same as the MsgWorkNew limit, at 50.

Conclusion

There are actually 3 actions that will need to be taken when reserving AWTs, so let’s take a final look at what they are and what they do:

  1. Expedite the workload or allocation group:  All queries that run in an expedited workload or allocation group will use the higher work types (MsgWorkEight, MsgWorkNine) and will be eligible for specal internal priority boosts that have been written into the DBS code.  If no reserves have been defined, expedited messages will draw their AWTs from the unassigned pool.
  2. Specify a reserve count:   Sets up new reserve pools for exclusive use by expedited work.  Will reduce the size of the unassigned pool, but will not provide any benefit if there are no expedited queries that can use the reserves.  In other words, don't specify a reserve count in the absence of expedited work.
  3. Specify a max or limit:  Creates a limit on how many concurrent MsgWorkEight AMP worker task may be in-use at any point in time.    
19 Comments
Hello Carrie,

We've been seeing some AWT exhaustion in our system from time to time. Is there a way to catch which queries saturated a given AMP (combined consumed more than 52 AWTs)?

Thanks
Teradata Employee
Good questions. Unfortunately there is no way to track AWT usage by individual query. Other than you can look at the explain and make an educated guess, based on parallel steps being present.

However, in Teradata 13 and upwards, the ResusageSPS table carries AMP worker task usage levels by workload, broken down by message work type. This should provide more information about which types of queries are having a greater impact than others, if you start tracking those counts and max numbers in particular.

See the ResUsage manual for more detail.

Thanks, -Carrie
Thank you Carrie.

Regards
Fan
You showed an example about 80 ..So, What if the 120
Thanks GS
Teradata Employee
If you have 120 AMP worker tasks per AMP defined, you will still have the same limit of 20 on how many reserved AWTs you can define for that system. The reserve AWT setting is independent of the number of AWTs per AMP you have defined.

Thanks, -Carrie
Fan
Thanks Carrie,
I have a question more...
According to the document, 56 of the 80 AMP AMP is available.
So, Reserved 24 AMP of 120 AMP(MaxAMPWorkerTasks) did not changed ?
Teradata Employee
If you increase total AWTs per AMP from 80 to 120, you will be adding 40 new AWTs per AMP. Those new AWTs will all go into the unassigned pool. The standard 8 reserve pools will still have 3 reserves each, totalling 24 reserves, leaving 56 if you had the standard default settings.

So that means with the increase of 120 AWTs per AMP you will have 56 + 40 = 96 AWTs in the unassigned pool, that are not reserved by any work type.

Thanks, -Carrie
Fan
I have another question,
If i set the reserved AWTs 20, That number was revered with 20 AWTs for each AMP ? or within all Nodes.
Becaues, I think when i setting with 20, AMP will start with 42 right ?( eight : 10 * 2 + nine : 10 * 2 + ten : 2)
Teradata Employee
The reserve is for each AMP. AMP worker tasks are AMP-specific, not node-specific. You are correct that if you specify a reserve of 20 each AMP will have 42 AMP worker tasks taken out of the unassigned pool and used for the new reserve pools.

Thanks, -Carrie
Enthusiast

Hi Carrie,

If we want to intelligently set the AWT Limit(rather than blindly set the value of 50), then should we be doing the following analysis?

1.Check history resusage data and probe values of WorkTypeMax08 to find the maximum value(on an average) we get in this column. Here I have one question. The value in WorkTypeMax08 is the maximum "NEW" expedited messages received right?

2. Check if tactical work is getting delayed anytime in the day using the delaytime column in DBQL

3. If there is ever delay in tactical work, then deep dive all such delay instances using rewind feature of viewpoint and find out the average number of requests that get delayed whenever tactical stuff suffers delay. Lets say the number is x

4. Set awt limit as (reservedawts * 2) + 2  + x. 

Regards,

Suhail

Teradata Employee

Suhail,

WorkEight is the number of expedited new work requests (similar to WorkNew).   WorkNine is the number of expedited spawned requess (similar to WorkOne).   AWT Limit is the setting in Workload Designer that lets you set a limit on how many WorkEight messages can be active at any point in time.

I don't think it's good to over-think putting a limit less than 50 on WorkEight.   For WorkEight, an AWT limit of 50 has been fine in all cases I am familiar with.  Setting it lower risks hurting tactical work by delaying new tactical work (WorkEight) from starting up if you had some unusually high arrival rates.   If you truly want to limit concurrency of tactical work, think about a workload throttle instead.  That will be cleaner, and you easily can see how many and how  long queries were delayed in DBQLogTbl, if you want.

The reason max WorkEight is probably fine at 50 is because there are really no reasons to penalize new tactical work (as long as it is short, low on CPU, and highly-tuned) if it exceeds 50, as there would be for worknew (too much new complex query work getting started up).   

Thanks, -Carrie

b2s
Fan

Hi Carrie,

 If you are using TASM, then the expedited status is automatically given to a workload that has a tactical enforcement priority

We have two versions of Teradata ie Teradata 12 and Teradata 14.

1)In Teradata 14, we do have WD's that have tactical enforcement priority. So far I have come across WorkTypeMax08 having 6 as Max. So, theoretically this means the actual AWT reserved is 14(6+6+2)..is this right?

 I'm aware of the limit of 20 AWT's for expedited AWT's, however, will this apply to the automatically reserved AWT's because of tactical enforcement priority?

2)In Teradata 12, even though we have WD's that have tactical enforcement priority, I'm unable to see the WorkTypeMax08 go beyond 0 ? Am I missing something?

 Bottom line, setting the limit on MsgWorkEight AWTs anywhere between 30 and 60 is probably reasonable. If you're not sure how to set it, make it the same as the MsgWorkNew limit, at 50.

As you said earlier, Max Expeditied AWT's can be 20(9+9+2), what is the use of having even 30?

If indeed there is a need for throttling, shouldn't Max Expedited AWTs be less than 9?

Teradata Employee

For TASM in Teradata 12 it is not enough to set the enforcement priority to tactical.  You must explictly expedite the allocation group.  This is done in the GUI screen that shows all the workload management setup, including RP, AG,  AG relative weight, and WD.   If a AG/WD has a tactical enforcement priority, it will have a checkbox under a column named "expedite".  If you check that column then requests within that AG/WD will use the work08/09 work types.

As you have discovered, that is not necessary in Teradata 14.  Once you make the AG/WD as tatical, it is automatically expedited and will use Work08 work types. 

WorkTypeMax08 has nothing to do with the the number of reserved AWTs that you select.  WorkTypeMax08 is reporting the maximum number of tasks that were using work08 within that logging interval, but it is not telling you how many of those tasks were using AWTs from the reserve pool for expedited queries, or how many were using AWTs from the unassigned pool. 

You have to explicitly select a reserve count in order for work08 tasks to use a reserved AWT.  When you select a reserve count, that is the number of AWTs that will go into a special reserve pool.  Work08 tasks can draw AWTs from that pool, but if that pool is empty they will try to draw AWTs out of the unassigned pool. 

You can set the reserve count at 2 and still have 10 or 20 or 30 tasks running in the work08 work type.  Those are two different things.  The fact that WorkTypeMax08 = 6 does not tell you what the tactical reserve count is.  It could be there are zero tactical reserves, but if there are expedited workloads that are doing work, you will see usage in work08 even with no reserves defined.

The limit of 20 you mention is for the number of reserved AWTs you are able to define.  But you can have a greater number of tasks running that use the work08 work type.   If that happens, all of them will not be able to count on having a reserved AWT waiting for them and will attempt to use an AWT from the unassigned pool.

Max Expedited AWTs puts a limit on how many tasks will be allowed to start up using the work08 work type. The default is 50, because there is a similar limit of 50 on how many tasks can be active using the worknew work type.   This number of 50 has no relationship with the number of reserved AWTs you can select.  It only controls how many tasks can use the work08 work type at one time, whether or not they are able use a reserved AWT for their work.

Thanks, -Carrie

Good Night!

How do I download the Teradata database express to install?

tks

Teradata Employee

Hi Carrie,

Thank You for sharing above information, it is really helpful.

I need your help,

Do we have any query to identify to which AMP/Node the Parser Express Request Goes usually ? Is it one particular Node/AMP or it goes randomly to any.

As per Resusage data, I do see WorkType01 is queueing up when Parse Express Request is very high (> 100 sec) for those user Session.

Teradata Employee

Hi Sachin,

An express request is usually issued in order to get particular information from the data dictionary, such as to validate the columns in a table that the query uses, or to get a user's profile or account information.   The express request will use primary index access based on information contained in the session or in the SQL and then go to which ever AMP the primary index value hashes to.  So express requests do not go to random AMPs, they know exactly the AMP they need to access to get the information that they are looking for in order to perform session management or for query parsing/optimizing. 

It is possible for one particular AMP to have more express request activity, if multiple queries are going after the same data dictionary rows.  Although in that case, that data dictionary information would tend to stay in the dictionary cache in the parsing engine, and then an express request to the AMPs would not have to be issued.     

There is no way I know of to monitor which AMPs are receiving express requests, and which are not.  Most express requests are very, very short, and unless you are out of AWTs, they should happen very quickly and not hold AWTs very long.

Thanks, -Carrie

Teradata Employee

Thank You Carrie,

In our environment, we are observing high Parser Express Request ( > 100sec) for some time of the day. 

Shall we go with reserve AWT(expedite as mention in above article) or any other DBS Performance parameter we should look initially ?

Teradata Employee

Sachin,

You can consider applying the suggestions in my blog posting titled: " Expediting Express Requests".  You have to reserve at least two AWTs in the tactical reserves for express requests to use the tactical reserve pool.   Expediting express requests may reduce the time spent on parser express requests, but only if running out of AWTs is causing the long parsing times.   On the other hand, if AWT exhaustion is not causing the long express request times, then expediting express requests will not help.

Here is the link to that blog post.

http://developer.teradata.com/blog/carrie/2014/06/expediting-express-requests

Thanks, -Carrie

Teradata Employee

Thank You Carrie