16.10 - About Managing Free Memory - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

Free memory is used by:

  • Administrative programs, such as program text and data, message buffers, kernel resources, and other applications such as FastLoad that require memory use.
  • Vprocs for non-file system activity, such as:
    Activity Description
    AWT Pieces of AMP logic used for specific AMP tasks
    Parser tasks Pieces of PE logic responsible for parsing SQL
    Dispatcher tasks Pieces of PE logic responsible for dispatching work
    Scratch segments Temporary work space
    Messages Communication between vprocs
    Dictionary cache Dictionary steps for parsing
    Request cache Temporary space used when executing steps

About Determining Available Free Memory

The ResNode macro displays limited information on free memory. The ResNode report includes the Free Mem% column, which is the percentage of unused memory.

Adjusting for Low Available Free Memory

When the amount of available free memory dips too far below 100 MB (25,000 pages), some sites have experienced issues. 40 MB of free memory is the lowest acceptable amount. You can usually avoid problems if you configure your AMPs to have at least 135 MB of OS-managed memory per AMP. You can adjust the amount of available free memory by performing the following:
  • Use ctl to adjust the FSG Cache percent downward to make more memory available to free memory. If the system takes too much memory for FSG Cache and the OS does not use that memory, the free memory is wasted.
  • If available free memory goes below 100 MB during heavy periods of redistribution, lower the value of the RedistBufSize field in the DBS Control Record (see “RedistBufSize” in Utilities).

Assured Minimum Non-FSG Cache Size

Teradata recommends these guidelines for minimum non-FSG Cache size per AMP.

  • 135 MB per AMP when all nodes are up.
  • 112 MB per AMP when 1 node is down in a clique.
  • 90 MB per AMP when the maximum number of nodes allowed down are down in a clique.

The above configuration guidelines help avoid performance issues with respect to memory swaps and paging, memory depletion, and CPU starvation when memory is stressed.

Performance Management Recommendations

Many sites may require more free memory than the default calculated on About Shared Memory. Teradata recommends that you provide additional memory per AMP to free memory by setting the FSG Cache percent to a value less than 100%.

Use the following calculation:

FSG Cache percent = (FSG Cache - 39 MB * # AMPs) / FSG Cache

Memory Size (GB) Memory for Baseboard Drivers & 10 AMPs/2PE Vprocs FSG Cache (MB) Less 58 MB per AMP Vprocs FSG Cache Percent
6.0 1854 4290 3822 89%
8.0 1914 6278 5810 92%

For large configurations, consider one or more of the following options to resolve I/O bottlenecks or excessive memory swapping:

  • Consider using aggregate join indexes to reduce calculations during query processing.
  • Set RedistBufSize to an incremental value lower; for example, from 4 KB to 3 KB
  • Set FSG Cache percent to less than 100%, taking into account total memory size.
  • Consider modifying the application to reduce row redistribution.
  • Ask your support representative to reduce the internal redistribution buffer size.
    This is an internal tuning parameter, not the user-tunable RedistBufSize.

Potential Problems

A possible problem is when an application on a large configuration generates many messages over the BYNET with concurrent row redistributions involving all nodes.

The following are not a problem:

  • Row duplications
  • Merging of answer sets

Row Redistribution Memory Requirement

To avoid the overhead of sending lots of little messages across the BYNET, buffers are used to batch up individual rows during the row redistribution process. Both load utilities and queries involve such redistribution, but their approach to outbound buffering is different.

Row redistribution for query processing uses separate single buffers per AMP for each node in the system. This means that the amount of memory required for redistribution in a node grows as the system grows.

The DBC Control Record RedistBufSize field controls the size of redistribution buffers. See RedistBufSize Performance Implications.

  • Default query redistribution buffer size = 32 KB per target node
  • Total memory for one sending AMP = 32 KB * number of nodes in system
  • For eight AMPs per node, total memory required per node = 
8 * 32 KB * number of nodes in system

Redistribution Buffer Resizing

The following example provides the calculations for resizing the redistribution buffer based on a configuration of 8 nodes at eight AMPs per node. (The system reserves 32MB per AMP).

  • Single node requirement (single user) = 32 KB * 8 = 256 KB
  • Multi-user (20 concurrent users) = 20 * 256 KB = 5 MB (less than the 32 MB default)

The following example provides the calculations for a configuration of 96 nodes at 8 AMPs per node and shows that the requirement exceeds the system default:

  • Single node requirement (single user) = 32 KB * 96 = 3072 KB (3 MB)
  • Multi-user (20 concurrent users) = 20 * 3072 KB = 64 MB (far exceeding 32 MB per AMP)

Performance effects of high-volume redistribution processing include:

  • Excessive memory paging/swapping
  • Possible I/O bottleneck on BYNET I/O