Are AMP Worker Tasks used by FastExport in Response Mode?
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.
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.