There’s a comment on an earlier blog posting of mine from 2009 titled Controlling the Flow of Work in Teradata.  The comment poses a question that is more reasonably answered by making second posting on flow control. 

The question was what happens when spawned work messages cannot be placed on the message queue on one AMP, but can on the others, will all AMPs reject the spawned work message?  Further, what happens if the spawned request originates from a situation where one of the WorkNew AWTS  is itself in flow control?   Below is an explanation of how this all works.  Check out the earlier Flow Control posting for background.

Flow Control for WorkNew  (Work00) messages

When an AMP temporarily closes the door to new work, that AMP is in a state that we call “flow control.”  When in a state of flow control, which often lasts a fraction of second, that AMP will turn away newly-arriving messages.  One type of arriving work can be work messages that the dispatcher has sent to the AMP for processing.  If there are a number of such messages waiting on the message queue for an AWT, and that number that is waiting has exceeded the message queue length for the WorkNew work type, then that AMP will not be able to accept that message.  

While each AMP makes this decision independently of other AMPs, the incoming message cannot be accepted by any AMP unless all AMPs can either give provide an AWT or queue up the message.  If only one AMP is in the state of flow control for that work type, the message is returned to the sender and will be retried.  In fact the full message is not stored in AMP memory until all AMPs can accept the message.

Each work message represents one step in a request, not the entire query.  When the message that is being retried is from the dispatcher, then no work on behalf of that request step will be able to start while the request step is undergoing retry logic.  However, it is possible the request itself is already partially completed at that point, as previous steps may not have encountered the flow control state and could have completed normally.   So flow control can occur in the middle of a query, and if the flow control condition persists, it could unexpectedly delay that query’s execution, even though earlier steps have completed.

Flow Control for WorkOne  (Work01) messages

WorkOne messages are messages that represent spawned work.  Spawned work happens when a WorkNew message is starting up on all AMPs and the step requires an activity such as row redistribution or table duplication, something that requires more than one AWT per AMP. 

Before a message can be sent to all AMPs requesting WorkOne AWTs, all of the AMPs must have successfully acquired a WorkNew AWT for that step.   If any of the AMPs queued up the WorkNew message or were in flow control, then the message that wants to spawn WorkOne AWTs on behalf of that step will not be sent.

When all the AMPs have their WorkNew AWTs, the last AMP to get its WorkNew AWT will spawn the message for WorkOne AWTs to all AMPs.   If one of the AMPs had been in flow control and unable to process its WorkNew message, then the spawned work message will not be sent.  It is only sent when all AMPs have been able to provide a WorkNew AWT.

Flow Control Managed by Work Type

Flow control is managed by work type independently.  It is not likely you will go into a state of flow on more than one work type at a time.  This is because if you are already in flow control for WorkNew work types on an AMP, that will make it less likely that there will be a demand for WorkOne work types, as there is a dependency between them.  Being in flow control for WorkNew lightens the load on WorkOne AWTs. 

Discussion
pinaldba 5 comments Joined 09/10
08 Jun 2012

Hi Carrie,

Thank you very much for nice explanation. Now it is very much clear to me.

Thanks a lot

Regards
Pinal Patel

carrie 431 comments Joined 04/08
16 Jul 2012

Actually, your math is not quite right. You say:

....so the total unreservered awts are
80-24+3+3=64...

The correct math is: 80 - 24 = 56;
56 + 3 + 3 = 62

In addition, that 62 is not the "total unreserved AWTs," because it includes 3 from the reserve pool for worknew and three from the reserve pool for workone. What that 62 represents is the total number of AWTs available to support worknew and workone work in combination, which is usually what constitutes user-defined work.

There is another posting by myself that has some discussion concerning expedited AMP worker tasks and how to make reservations and expedite the allocation group/workload. Please go to:

http://developer.teradata.com/blog/carrie/2011/10/reserving-amp-worker-tasks-don-t-let-the-parameters-confuse-you

Thanks, -Carrie

VP186001 4 comments Joined 11/12
06 Nov 2012

Carrie,

I'm trying to find out which queries could've caused my system to go into Flow Control. Apart from high CPU/IO consuming queries, which other types of queries should I look for?

carrie 431 comments Joined 04/08
07 Nov 2012

Being in the state of flow control is difficult to pin on one query or even one small group of queries. It is a result of the system having more work presented to it than it can manage at that time. Being in flow control happens when the totality of work is too great. Think more about collective guilt rather than individual fault.

That said, managing flow control usually means identifying the lower priority workloads, which are usually the longer-running and more resource-intensive requests (the ones that would hold AMP worker tasks longer than average anyway), and sharply reducing their concurrency levels. This can be done by using workload management throttles, available with and without TASM, or it can be done at the client end, by restricting what gets submitted when.

Also, consider using filter rules (also available without TASM), to reject queries that have undesirable query characteristics, such as large unconstrained product joins, that you probably don't want to run to start with, and that will chew up resources and maybe run a long time.

In particular, look at long-running requests that might be under the control of a low CPU limit (I have another blog posting about that problem), like you could have if you had set up a penalty box. Those queries typically hold resources such as AWTs for a long time, reducing availability for other requests.

Skewed queries can also result in AWTs held for inappropriately long times, so you could look for long-running highly-skewed queries.

Thanks, -Carrie

VP186001 4 comments Joined 11/12
07 Nov 2012

Thank you Carrie for your valuable input.
I have another question for you in relation to what you said about the skewed queries.
Yesterday early morning we noticed a lot of Skewing on AMP 131 on our system. Turns out we had a bad disk drive that was throwing a lot of errors and this had been going on since Friday at 3PM California time.

So my questions
We had CS guys replace the disk and the issue is resolved now but could we create an alert for this in the future? If yes,how?
Can we also produce a report that shows the impact this bad disk had on our system? I mean, I can get all types of charts from PDCR toolkit but how will I show the impact on the system due to this issue?

carrie 431 comments Joined 04/08
09 Nov 2012

I am not aware of any user-available alert mechanisms for bad disks. However, you can look at ResUsageSLDV (logical device detail), specifically at the LdvReadRespMax and LdvWriteRespMax and cross-compare across devices.

Teradata Customer Services provides array drive failure alerting (and many other hardware failure alertings), as part of the support service they provide. From what you say, they did detect this condition. If you would like more detail on how the disk monitoring and alerting part of this CS service works, please get in touch with your local CS Site Team as they can provide a lot more detail than I am able to.

Thanks, -Carrie

08 Dec 2012

Hi Carrie, two very simple question:
1 - how can I know what is the maximum number of AWT(s) per AMP(s) ? (I run Teradata 13.10)
2 - In the resAWT macro, the max columns include all the AWT(s) in use ? (that's say if I have 80 AWT(s) per AMP and I read max awt per AMP = 55, this number include also all the reserved AWT(s) ? So I have 80 (total awt) - 55 (max awt) = 25 awt(s) available until getting in flow control AMP state ? ).
Thank you very much for your reply.
Regards,
Pietro.

Pietro Nardella
pietro.nardella@teradata.com
Teradata Italia

carrie 431 comments Joined 04/08
13 Dec 2012

Pietro,

Max number of AWTs per AMP as of 13.0 is 500, although we never advise anyone to go much above 110 or 120 today. It's there for the future. Prior to 13.0 is was 120.

The Max fields in the ResAWT macro output (for example "Max Work One AWTs", or "Max Work Two AWTs") report the maximum in-use AWT counts for a single worktype, not the combination of work types. There is a max provided for each work type. You cannot add them together or you will get an inflated value, so the macro is not going to tell you how close you are to running out of total AWTs. Each of those max counts probably were observed at different points in the logging interval. If you want to see the max in-use across all work types, which will tell you how close you have come to running out, look at the base AWT table InuseMax column. The macro does not carry an in-use count for all AWT work types combined.

Thanks, -Carrie

mikecyl 3 comments Joined 01/12
18 Dec 2012

Hi Carrie,

I wonder whether there is a way to find out that a query is delayed by the flow control of AWT.

There are some information telling us that in DBQL?

Thanks
C. Y.

carrie 431 comments Joined 04/08
19 Dec 2012

Hello C.Y.

There is no way that I know of to identify if an individual query was delayed waiting to get an AWT. You can, however, see whether requests within a workload were delayed waiting for an AWT, and a count of how many per logging interval. That is by looking at the ResUsageSPS table, which in 13.10 carries information about delays from waiting for AWTs within workloads. 14.0 has even clearer information, as it doesn't rely on SpareCount columns, as does 13.10.

The two fields in the SPS table you want to look at are WorkMsgReceiveDelayMax (SpareCount03 in 13.10) and QWaitTimeMax. Those two columns will tell you what the max wait times within that workload are (for all queries that classify to that workload), due to AMP worker task shortages. You can see the explanations for those columns in the ResUsage manual, the chapter on ResusageSPS.

There will be a new orange book on using Resusage table information for AMP worker task analysis that will be posted in early January, which goes into quite a bit more detail an analyzing delays caused by AWTs.

Thanks, -Carrie

03 Jan 2013

Hi Carrie,
thanks for your reply. I'd like to redo my question:

" 1 - how can I know what is the maximum number of AWT(s) per AMP(s) ? (I run Teradata 13.10) "

and ask if there is a system table where I can read the max AWT number per AMPs defined for example for my customer teradata system.

Thank you & regards,

Pietro.

Pietro Nardella
pietro.nardella@teradata.com
Teradata Italia

carrie 431 comments Joined 04/08
04 Jan 2013

Pietro,

DBS Control has an internal field MaxAMPWorkerTasks (internal field #4) that tells you the number of AWTs allocated to each AMP for that platform. The contents of internal DBS Control fields are usually not viewable apart from Teradata support staff. If you are not able to view this information, check with your CS representative from Teradata for that site for validation of how many AMP worker tasks are assigned to each AMP at start up.

Thanks, -Carrie

Smarak0604 15 comments Joined 08/12
13 Apr 2013

Hello Carrie,
 
Few Questions.
 
(a) The 1st line says "When an AMP temporarily closes the door to new work, that AMP is in a state that we call “flow control.”" So, Does it means Flow Control is restricted to only New Work ?
 
Say, for 80 AWTs/AMP, all the 56 AWTs are curretly in use in addition to 03 Reserved by Work00 & Work01. Now, no Work00 & Work01 Work will be Accepted. But, Work02 to Work07 can be accepted by virtue of 03 AWTs Reservations. But, that AMP will be in Flow Control as no Work00 AWTs are available. [Assuming Message Queue is Full].
 
(b) When you say Message Queue, does the AMP maintain Message Queue for each Worktype [08 Message Queue for 08 Work types] or a Single Message Queue with Worktype indicator/marker ?
 
Can I say the following:
Flow Control/Closing the Door for New Work = No More Space @ Message Queue Or, Work-New Waiting Quota Exceeded [Rather than No Work-New AWTs Available]

carrie 431 comments Joined 04/08
16 Apr 2013

(a).  Flow control may or may not be restricted to MsgWorkNew work types.   I was using "new" in the generic sense in the text above.  No additional work will be accepted for whatever type of work is in flow control. And yes, flow control happens for a given work type. Usually flow control happens to MsgWorkNew first, however.   You are correct that other work types (such as MsgWork02) could accept work when another work type is in flow control, since they have their own reserves that they can use.
 
(b).  There is a single message queue, but the AMP worker task manager keeps track of the work types of each message on the queue and how many of each type are queued.  The message queue is ordered by work type descending. 
 
You can say "Flow control closes the door for the AMP receiving any additional messages of the same work type." 
 
Thanks, -Carrie

SKS 1 comment Joined 02/08
25 Apr 2013

Hi Carrie,
Thanks a lot for such valuable input.
one question: Do we have any specific 'work type' AWT (out of 0-15 work type) that is used for dedicately maintaining the message queue itself? Or is it maintained by system internal processes?
Thanks
 

carrie 431 comments Joined 04/08
29 Apr 2013

The message queue is not maintained using AMP worker tasks.  A combination of system services and event-driven PDE internal processes do that work.
Thanks, -Carrie

You must sign in to leave a comment.