Synchronization of Parallel Operations | Database Design | Teradata Vantage - 17.10 - Synchronization of Parallel Operations - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

Synchronized Table Scans

Teradata system architecture is not only inherently parallel, but it is also highly optimized to synchronize its parallel operations to effect further performance optimizations.

The database can perform synchronized scanning of cached large tables. Synchronized scanning permits new full-table scans to begin at the current point of an ongoing scan of the same table, thus avoiding any I/O for the subsequent scans. Because synchronized scans might not progress at the same rate, any task can initiate the next read operation.

Example of synchronized scanning of large tables

Spool Reuse

Vantage reuses the intermediate answer sets referred to as spools within a request if they are needed at a later point to process the same query. Two common examples of spool reuse are table self-joins and correlated subqueries.

Synchronized BYNET Operations

Several Vantage features act to minimize message flow within the SQL Engine. Primary among them are dynamic BYNET groups and global semaphores.

A dynamic BYNET group is an ad hoc grouping of the AMPs working on a single step. Any number of dynamic BYNET groups can coexist simultaneously. Dynamic BYNET groups are established when the first step of an optimized request is about to be dispatched to the AMPs.

Before the first step is dispatched, a message is sent via the BYNET to only those AMPs that will be involved in processing the step. As a result, a dynamic BYNET group might be composed of a single AMP, a group of AMPs, or all AMPs in the system. When an AMP acknowledges reception of the message, it is enrolled in the dynamic BYNET group, sparing the database software from having to initiate a separate communication.

Success and failure global semaphores are associated with dynamic BYNET groups. These objects exist in the BYNET software and signal the completion (or failure) of a step by the first and last AMPs in the dynamic BYNET group. Each success semaphore maintains a count of the number of AMPs in the group still processing the step. As each AMP reports its success, the semaphore count is reduced by 1, so when the last AMP in the group reports, the value of the semaphore is 0. Each AMP reads the value of the success semaphore when it finishes processing the step, and when its value reaches 0, that AMP knows it is the last in the group to complete the task. At that point, the AMP sends a message back to the Dispatcher to notify it that all AMPs in the dynamic BYNET group have finished processing the step, so it can then send the next step to the group. This is the only message sent to the Dispatcher for each step irrespective of the number of AMPs in the dynamic BYNET group.

Synchronized BYNET operations process

Failure semaphores are the mechanism the SQL Engine uses to support global abort processing. A failure semaphore signals to the other AMPs within a dynamic BYNET group that one of its members has experienced a serious error condition so they can abort the step and free system resources immediately for other uses.

With success and failure semaphores, no external coordinator is required. Global synchronization is built into the Teradata system architecture, and because there is no single point of control, performance can be scaled up to easily handle an increasing volume of system users.