I gave a presentation on AMP worker tasks at the Teradata User Group conference last week in Washington DC.   A question came from someone in the audience concerning FastExport jobs in response mode, and whether or not they were holding AMP worker tasks.  This post addresses that question.

AMP Worker Tasks & Response Mode for Standard SQL Requests

Under normal query execution using standard SQL, AMP worker tasks (AWT) will fill the BYNET merge buffers at the end of the last query step.  The AWTs used by the final step in the query plan are then released before the merge process that is part of response mode begins.

The last AMP to complete the build of its final spool collects row counts and gives the total number of rows to the dispatcher.  The dispatcher tells the BYNET driver to begin the merge process.  No additional AWTs are required during the response phase of an SQL request, assuming the rows-to-be-returned in the AMP’s response buffer do not exceed the size of the BYNET driver merge buffers.

However, for large answer sets, AWTs are acquired very briefly during the merge process to refill the BYNET buffers.  These are AWTs that have a very high work type, so it would be rare that there would be any waiting involved, even if your system was experiencing AWT exhaustion.  In addition, the CPU for returning rows to the client runs at a very high system priority, speeding along the entire process of providing an answer set to the client.

How FastExport Works

Turning to FastExport, recognize that there are two flavors:  the standard FastExport that requires spooling, and the NOSPOOL option.  Each behaves differently in response mode. But first, some background on how the standard FastExport utility works.

FastExport, as it was originally designed, gets big data volumes back to the client quickly. Here’s how it does that:   After the final spool file has been built, the resulting rows will be redistributed across all AMPs, so that each AMP will have approximately the same number of data blocks to return.  There is always a horizontal redistribution, which is designed to speed up the export of a large answer set.  In addition, there will be a preceding vertical redistribution if ORDER BY is specified.

During the vertical redistribution process two AMP worker tasks will be in use on each AMP to send and receive rows. Horizontal redistributions, which happen whether or not vertical redistributions have taken place, pack rows from the spool file of each AMP into response blocks and ensure they are evenly spread across all AMPs for efficient return to the client.  It also requires two AWTs.  You can read more about how FastExport works in a previous blog posting of mine from July 2009 titled FastExport for Really Short Queries in Teradata 13.10.

Standard FastExport in Response Mode

All of the redistributions of standard (SPOOL mode) FastExport are performed before the response phase starts.  The additional redistributions required once the answer set has been compiled are not considered as part of the response phase.  No AMP worker tasks are held during response for standard FastExport.  Instead, FastExport relies on a load control task (LCT) on each AMP  to manage the actual row return to the client.

There is one load control task per AMP, and it plays a role in FastLoad, MultiLoad and FastExport jobs.  The LCTs perform initialization and cleanup operations for these utilities.  They have responsibility for passing data parcels down to the AMP worker tasks on the AMPs in the case of FastLoad and MultiLoad.  For FastExport , the LCT task does  the opposite.  It passes data parcels from the AMPs that have just completed their work back to the client.

NOSPOOL FastExport in Response Mode

In Teradata 13.10, FastExport was enhanced to provide better performance when small volumes are involved.  This enhancement includes a “NOSPOOL” option, and is appropriate when just a few rows are going to be processed from a single table.  Common usage for NOSPOOL mode is primary or secondary index access.

The NOSPOOL mode will prevent final spool files from being redistributed across all AMPs.  In-memory buffers will be used to store the final answer set rows, rather than spool files.  Only the AMPs that actually process rows will be involved in final processing, which will reduce unnecessary AMP-level overhead when the number of AMPs in the configuration grow.

With NOSPOOL, the response mode begins before the data has been read.  The FastExport requires two AMP worker tasks throughput its execution, including response mode.   One AWT is performing the read of the data into a buffer.  The other AWT is exporting the data from the buffer to the client.   Keep in mind that this is happening only on the AMPs that are needed to satisfy the request, which could be one or few AMPs. 

Conclusion

The only case when AMP worker tasks are held during response mode for a FastExport job is if the NOSPOOL option has been specified.  Standard FastExport (SPOOL option) will never require AWTs during its response mode.

Discussion
29 Oct 2012

Hello Carrie,

Thank you for this blog. I have asked you this question during Partners presentation.

Can you please tell me during vertical redistribution process or horizontal redistribution process, which type of AWT Fastexport usages?

Thanks & Regards,
Shrinivas Sagare

carrie 431 comments Joined 04/08
31 Oct 2012

Hi Shrinivas,

The vertical and/or horizontal redistribution processes are done as part of the basic processing within FastExport. So they will use the same AMP worker task work types that standard SQL would for their redistributions: WorkNew for the sender task and WorkOne for the receiver task.

This was a good question you asked at the conference. I am glad to have the opportunity to clarify how this works.

Thanks, -Carrie

gsoh 14 comments Joined 08/12
21 Feb 2013

Use of the user session is lost between middleware system, but the Teradata system was not aware of this, the status of the response.In this case, the Teradata System resource(CPU etc) consumption?

carrie 431 comments Joined 04/08
25 Feb 2013

I am not able to help respond to this question, as this is not in my area of experience. Please post it one of the Teradata forums or open an incident with the support center.

Thanks, -Carrie

gsoh 14 comments Joined 08/12
07 Mar 2013

Always thanks,
GS

geethareddy 104 comments Joined 10/11
24 Apr 2013

Hi Carrie,
Today on our production box i have seem 700+ idle sessions. I can see some of them are drawing 0 CPU and some of them using neglegible amount of CPU (eg 0.002).
I think i have seen your response in one of the forums sayng that there is no way to find out the individual session or userid AWT consumption (plz correct me if i am wrong).
My quesiton is, are the idle sessions really consume the awts?
 
Thanks,
Geeta

Thanks,
Geeta

carrie 431 comments Joined 04/08
29 Apr 2013

Idle sessions are sessions that are between database requests.  They do not have any active work that could be holding AMP worker tasks on the AMPs. 
 
And you are correct that there is no machanism to tie a specific use to current AWT usage.  The only way you could maybe make that work is if you set up a special workload in TASM just for a single user, then monitored the AWT usage for that workload across all nodes, using the ResUsageSPS table.  That's a pretty limited approach and I don't think it's worth the effort, since you can look at a query plan and make an educated guess at how many AWTs were in use during the different steps of the plan.
 
Thanks, -Carrie
 

krystynytax 5 comments Joined 03/13
20 May 2013

Hi,
i want to know how I can copy a database or user schema from the development environment to the production environment.
I want to do export of all structure of a database and do the import in other database in other machine
I have ABU Aplicattion for the backup
Can i do this ?? Because i saw how copy tables, but i want to copy the all schema of a user /database
 
Thanks

carrie 431 comments Joined 04/08
21 May 2013

I do not have an answer to the question.
 
In order to get the best response for technical questions you have that are unrelated to the topic of the blog posting, please post them on Teradata Forum at:
 
http://forums.teradata.com/forum
 
Thanks, -Carrie

geethareddy 104 comments Joined 10/11
23 May 2013

 Thank you Carrie. Sorry for the delayed response (was on vacation).
Just want to ask one more question on the same topic. We recently allocated 1 AWT via Reserved AWT for the tactical workload, and we want to see how it is going to help us in completing the crtical applicaiton work. But we didnt mention anything under AWT limit (in WL Designer). Your article saying the below. So i just want to make sure is it STRICTLY recommendable to go with 50 or we can start with small figure and increase the reserved awts slowly if the increased number doesn't hurt the non tactical work (which draws the unreserved pool).
 
 

AWT Limit: This is the second number required to be specified when setting a Reserved AWT pool

(usually referred to as AWT max). It represents the maximum number of processes that can be

active at any point in time supporting the expedited message work type. If Reserved AWTs is set,

it is recommended that this limit be set at 50.

Thanks,
Geeta

carrie 431 comments Joined 04/08
24 May 2013

The AWT Limit setting is by default 50 in Workload Designer. The AWT limit value does not indicate how many AWTs are in the reserved AWT pool.  That is determined by the Reserved AWT setting.   So it is fine to have a Reserved AWT count of 1 (or 2 or 3 or up to 20) and have an AWT Limit setting of 50, the default.
 
AWT limit is much less important, it only acts as an upper limit on how many work8 messages will be allowed to be active at any point in time.  Usually the default fo 50 is fine because concurrency doesn't reach that high, but it is not a strict recommendation. 
 
AWT limit is similar to the limit of 50 on WorkNew messages that are allowed to be active at the same time.   Having an AWT limit of 50 means that if you have more than 50 new messages coming from expedited workloads at the same time, the 51st such message will be placed in the delay queue. 
 
Thanks, -Carrie

geethareddy 104 comments Joined 10/11
29 May 2013

Thanks for clarifying this. 
I have one more question on the same note, we have been seeing sessions with high PJI and UIO (Unnecessary IO) and Skew. But sometimes one Skew & UIO for that session is less but PJI is exception something around 20 (ex: 20.18) will be high. And sometimes PJI is less where as UIO and Skew will be more. So i am trying to understand is there any thumbrule or more facts available on UIO and PJI. I know more PJI will lead to bad product join and impact the system. But can i take that as the yard stick and abort that session from the box. Same question to UIO If i hav huge UIO is that alone a measurement to decide the session is a bad one. All i am tyring to understand is how to evaulate a session with high PJI and UIO.
 
Appreciate your inputs on this.

Thanks
Geeta.

Thanks,
Geeta

carrie 431 comments Joined 04/08
30 May 2013

Geeta,
The product join indicator uses this formula:
 
(AMPCPUTime * 1000)/TotalIOCount
 
Usually if it is 3 or higher it may indicate something that needs further investigation, but it could certainly go much higher and be an acceptable query.  It is only an indicator.  By itself it is usually not a reason to abort a query.  Same with UIO.
 
Best to post these types of questions that are off the topic of the blog posting on Teradata Forum so you can get wider exposure and benefit from opinions of others who are working more directly in your area of concern.
 
Thanks, -Carrie

geethareddy 104 comments Joined 10/11
30 May 2013

Got it. Thank you Carrie. 

Thanks,
Geeta

VeluNatarajan 3 comments Joined 11/11
31 Jul 2013

My session is in RESPONSE state for more than 12hrs while trying to export 1.2 billion rows, 53.5GB size. BYNET (merge) buffer can hold all the data and release the AWT's? what is the size of BYNET buffer?

 

Appreciate your inputs on this.

 

Best Regards,

Velu N

carrie 431 comments Joined 04/08
01 Aug 2013

Velu,
 
BYNET buffers only hold a small portion of the data being returned when it is a very large data set involved.  In the case of FastExport in spool mode, AMP worker tasks are released before data begins to be returned.  The LCT tasks mentioned above manage the return of parcels to the client.
 
Unfortunately, I am not familiar with how to determine the actual size of the BYNET buffers on a specific platform. 
If you think you have a performance problem or need greater detail about the sizing of your platform's BYNET buffers when FastExport is running, please open an incident with the support center or work with your local account team. 
 
Thanks, -Carrie

Samyyy 5 comments Joined 02/13
28 Nov 2013

Thank You Carrie for the important piece of information.
I have always been concerned about sessions stuck in long responding state .
Based on the statement :
"In addition, the CPU for returning rows to the client runs at a very high system priority, speeding along the entire process of providing an answer set to the client."
May I assume an important part of very high priority CPU is being held just because of an unwanted long responding session  ??

carrie 431 comments Joined 04/08
04 Dec 2013

There is a  high priority given to the CPU usage required when returning rows. This is because it is in internal activity, as opposed to a user-initiated SQL execution type of activity, and because very little CPU is actually involved it was decided to give it a high priority.  The database was architected in a way to favor completing work that is already underway.
 
Thanks, -Carrie

VeluNatarajan 3 comments Joined 11/11
31 Dec 2013

Thank You Carrie, with reference to your previous post dated - 01 Aug 2013

 

FastExport - AMP worker tasks are released before data begins to be returned when it is a very large data set.

 

Could you clarify,

Bteq session - AMP worker tasks are released or in hold until all data are returned when it is a very large data set.

 

Best Regards,

Velu N

 

carrie 431 comments Joined 04/08
02 Jan 2014

Velu,
BTEQ sessions execute standard SQL, so the AWT usage in response mode for a request within a BTEQ session is the same as described in the first part of the blog posting above:
 
Under normal query execution using standard SQL, AMP worker tasks (AWT) will fill the BYNET merge buffers at the end of the last query step.  The AWTs used by the final step in the query plan are then released before the merge process that is part of response mode begins...However, for large answer sets, AWTs are acquired very briefly during the merge process to refill the BYNET buffers.
 
Thanks, -Carrie

You must sign in to leave a comment.