Specifies the maximum amount of memory to be used for disk read operations that involve multiple partitions.
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, Vantage processes a subset of the nonempty, noneliminated partitions together, rather than processing one partition at a time. A context is kept for each nonempty 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 contexts and associated 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 nonempty combined partition. In the following discussions, partition means combined partition.
PPICacheThrP also specifies the percentage value to use for calculating the number of memory segments that can be allocated to buffer the appending of column partition values to column partitions. The sum of the sizes of these memory segments (minus some overhead) divided by the size of a column partition context determines the number of available column partition contexts. If there are more column partitions in a target table than available column partition contexts, multiple passes over the source rows are required to process a set of column partitions, where the number of column partitions in each set is up to the number of available column partition contexts. Note that, in this case, there is only one file context open, but each column partition context allocates buffers in memory.
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:
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 data block size also affects the number of contexts. A smaller data block size allows for more contexts, therefore more partitions can be processed together. If the data block size is 128 KB or less, up to eight contexts may be used, regardless of the PPICacheThrP setting. For larger data block sizes, this may scale down to as low as two contexts.
No more than 256 contexts are ever used, regardless of the PPICacheThrP setting.
PPICacheThrP 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) per AMP on a system with the default setting of 10, the maximum amount of FSG cache 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 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 Information
For more information on partitioning, including row and column partitioning for tables and join indexes, see Teradata Vantage™ - Database Design, B035-1094 and Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.