17.10 - RedistBufSize - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

Determines the buffer size for AMP-level hashed row redistributions, as used by load utilities (MultiLoad and FastLoad), and archive/recovery operations.This field also determines the size of the buffers used to redistribute USI rows when creating an index on a populated table with the CREATE UNIQUE INDEX SQL statement.

This field should be changed only under the direction of Teradata Support Center personnel.

Field Group

Performance

Valid Settings

  • -1 specifies the optimal buffer size to avoid extra memory overhead for sending row redistribution messages on the current system.
  • 0 specifies the default buffer size for the current system.
  • 1 through 63 specifies the buffer size in units of kilobytes. For settings within this value range, the actual buffer size may be adjusted internally by Vantage for better memory utilization.
  • 512 through 65024 specifies the buffer size in units of bytes. This is equivalent to 0.5 through 63.5 kilobytes. For settings within this value range, the buffer size is fixed, and used exactly as is, without any adjustment by Vantage.

Default

The default is about 3,832 bytes, to optimize message efficiency.

To see the exact default value for RedistBufSize, use the ‘help perf’ DBS Control command and read the online information for the RedistBufSize field. This information is updated dynamically to reflect the most current default value.

Changes Take Effect

After the DBS Control Record has been written.

Usage Notes

For the redistribution of data from AMP to AMP, the system reduces message overhead by grouping individual rows before sending them on to their destination AMPs. The rows are grouped into buffers on the originating AMP, one for each destination AMP.

When all the rows to be sent to a particular AMP have been collected in the corresponding buffer, they are sent to their destination AMP with a single message.

If there are N AMPs in the system, then each AMP has N buffers for managing redistribution data, making a total of N2 buffers in the system used per redistribution. Multiplying the number of redistribution buffers per node by the value of RedistBufSize gives the total amount of system memory that will be used on each node for each redistribution. For example, each FastLoad or MultiLoad job that is importing data at a given time requires a separate redistribution.

Example: The RedistBufSize DBS Control field and redistributions

Assume a system has 12 nodes, with 8 AMPs per node.

The system would have a total of 12 x 8 = 96 AMPs.

Therefore, each AMP would need to use 96 buffers for each redistribution.

To calculate the amount of memory per node used for each AMP-level redistribution, first multiply the number of buffers per AMP by the number of AMPs per node:

96 x 8 = 768 redistribution buffers per node per redistribution

Then multiply the number of redistribution buffers by the RedistBufSize value:

768 x 4 KB = 3,072 KB or 3 MB per node used for each AMP-level redistribution

The memory used for redistributions scales with system size. Adding more nodes or more AMPs to a system necessitates more memory for redistributions.

RedistBufferSize Performance Implications

If a system has relatively few AMPs, a larger redistribution buffer size usually has a positive effect on load performance. However, on larger systems with many AMPs, a large buffer size can consume excessive memory, especially if many load jobs are run concurrently.

For more information on row redistribution and performance, see Teradata Vantage™ - Database Administration, B035-1093.