Managing Free Memory - Teradata Database

Managing Free Memory

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

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” on page 533. 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.

Note: 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” on page 542.

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