15.00 - PPICacheThrP - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

PPICacheThrP

Purpose  

Specifies the maximum amount of memory to be used for disk read operations that involve multiple partitions.

Note: This setting affects operations on tables with all types of partitioning, including row partitioning, column partitioning, and any combinations of the two. It is not limited to operations on tables with partitioned primary indexes (PPIs).

Field Group

Performance

Valid Range

0 through 500

The value is in units of 1/10th of a percent, so the valid range represents 0% through 50.0%.

Default

The default is 10 (1.0%).

Changes Take Effect

After the DBS Control Record has been written.

Usage Notes

For some partitioning operations, Teradata Database processes a subset of the nonempty, noneliminated partitions together, rather than processing one partition at a time. A context is kept for each partition to be processed. The context defines the current position within the corresponding partition.

The PPICacheThrP value can be used to reduce swapping and avoid running out of memory by limiting the amount of memory used for these data blocks during partitioning operations. However, larger values for PPICacheThrP may improve the performance of these partitioning operations by allowing them to use more memory.

For a multilevel partition, a context is associated with a combined partition. In the following discussions, partition means combined partition.

Partitioning Cache Threshold (PCT)

PCT is the amount of memory to be made available for partitioning operations.

On systems where the file system cache per AMP is less than 100 MB:

On systems where the file system cache per AMP is greater than 100 MB:

Note: The output of the DBS Control DISPLAY command includes some additional information for PPICacheThrP. This information can help DBAs determine the actual amount of memory available for multiple-context operations on partitioned tables.

The value PCT is used in various operations for a partitioned table, as the amount of memory to use for data blocks or AMP buffers associated with the multiple partition contexts, which allow reading from, or writing to, a set of combined partitions at the same time.

The following equations define the maximum number of partitions and contexts to process at a time, based on the amount of PCT determined from PPICacheThrP. If there are fewer nonempty, noneliminated partitions, only the actual number will be processed at one time. This means that all the partitions of the table will be processed simultaneously.

Join for a Partitioned Table

In a primary index join, if one table or spool is row partitioned and the other is not, then the maximum number (P) of partitions processed at one time from the partitioned table or spool is equal to the following:

In a primary index join, if both tables or spools are partitioned, then the maximum number of partitions processed at one time from the tables or spools is equal to the following:

where:

 

Formula element…

Is the…

f1

number of partitions to be processed at one time from the left table/spool, as determined by the Optimizer.

f2

number of partitions to be processed at one time from the right table/spool, as determined by the Optimizer.

db1

estimated average data block size of the left table/spool.

db2

estimated average data block size of the right table/spool.

Note: For each partition being processed, one data block remains in memory, if possible.

Aggregation for a Partitioned Table

If an aggregation is performed on the primary index of a partitioned table or join index, the maximum number of partitions processed at one time from the table is equal to the following:

Note: For each partition being processed, one data block remains in memory, if possible.

Accessing Column Partitions

A column partition context is allocated in memory to keep information about a column partition when it is being processed. There is one context for each of the column partitions being accessed simultaneously. Since the memory required for each context can be large, the number of column partitions processed at one time is limited.

For accessing data in a column-partitioned table or join index, the maximum number of partitions processed at one time is equal to the following:

Inserting into Column Partitions

For inserting data to a column-partitioned table or join index, the maximum number of partitions that can be processed at a time is determined by the amount of buffer memory an AMP is allowed to allocate, rather than the PCT, which depends on the amount of file system cache (FSG cache) per AMP. In this case, only one context is used in the FSG cache, but each column partition context requires the AMP to allocate memory for a buffer to hold the context.

If an INSERT does not have enough contexts, the EXPLAIN output for the MERGE step indicates the number of contexts.

Performance Implications

Under most use cases, the default value for PPICacheThrP is adequate, and should not be changed. However, if there are performance issues that might be addressed by adjusting this value, consider the information in this section.

The current data block for the corresponding partition (or buffer, in the case of inserts to column-partitioned tables) is associated with each context. The current set of data blocks or buffers (one for each context) are kept in memory, if possible, to improve the performance of processing the set of partitions at the same time. If there is a shortage of memory, these blocks or buffers may need to be swapped to disk. Excessive swapping, however, can degrade system performance.

Larger values may improve the performance of partitioning operations, as long as the following occur:

  • Data blocks or AMP buffers for each context can be kept in memory. When they can no longer be kept in memory and must be swapped to disk, performance may degrade.
  • The number of contexts does not exceed the number of nonempty, noneliminated partitions for partitioning operations. (If they do, performance will not improve because each partition can have a context, and additional contexts would be unused.)
  • In some cases, increasing the value of PPICacheThrP above the default value can provide a performance improvement for individual queries that do these partitioning operations. However, be aware of the potential for memory contention and running out of memory if too many of these queries are running at the same time.

    The default setting of 10 is conservative, and intended to avoid such memory problems. With 80 AMP Worker Tasks (AWTs) on a system with the default setting of 10, the maximum amount of FSG cache memory per AMP that could be used for these partitioning operations is 80% of FSG cache memory, if all AMPs are simultaneously executing partitioning operations, such as sliding-window joins for 80 separate requests. For nonstandard configurations that have more than 80 AWTs defined as the maximum, the setting is scaled to the number AWTs. For example, at the default setting of 10, a cap of 80% of FSG cache memory per AMP would still be in effect on such systems.

    For many sites, the default may be too conservative. All 80 AWTs might not be running partitioning operations at the same time. If, at most, 60 partitioning operations are expected to occur at the same time, the value of PPICacheThrP could possibly be raised to 15. If at most 40 are expected, the value could possibly be raised to 20, and so on. The best value for this parameter is dependent on the maximum concurrent users expected to be on the system and their workload. No one value is appropriate for all systems.

    Also, consider that the number of concurrent partitioning operations, such as sliding-window joins, may increase as partition usage is increased. Increasing the value may increase performance now without memory contention or running out of memory but, in the future, as more partitioning operations run concurrently, performance may decrease, or out of memory situations may occur.

    If less than 80 concurrent partitioning operations are expected for your site, and you think that better performance may be possible with an increased value for PPICacheThrP, you can experiment with PPICacheThrP settings to determine an increased PPICacheThrP setting that is optimal for your site and safe for your workloads. Measure pre- and post-change performance and degree of memory contention under expected current and future workloads to evaluate the effects of the change. If increasing the value to one that is reasonably safe for your site does not yield adequate performance for partitioning operations such as sliding-window joins, consider defining partitions with larger granularity, so fewer partitions are involved in the sliding-window join.

    Related Topics

    For more information on partitioning, including row- and column-based partitioning for tables and join indexes, and partitioned primary indexes (PPIs), see Database Design and SQL Data Definition Language.