DBSCacheThr - Advanced SQL Engine - Teradata Database

Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
xha1591998860283.ditamap
dita:ditavalPath
xha1591998860283.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantage™
Specifies the threshold table size that demarcates “small” from “large” tables for purposes of system caching decisions. DBSCacheThr is expressed as a percentage of FSG Cache.
  • Tables that would occupy the DBSCacheThr percentage or less of the cache are considered small. Data blocks from these tables are preferentially cached.
  • Tables that would occupy more than the DBSCacheThr percentage of the cache are considered large. Data blocks from these tables are preferentially excluded from the cache.

DBSCacheThr is effective only when DBSCacheCtrl is set to TRUE.

Field Group

Performance

Valid Range

0 through 100%

Default

10%

Changes Take Effect

After the DBS Control Record has been written. Any operations in progress at the time of the change are not affected.

Usage Notes

Caching frequently accessed data blocks can improve system performance because reading from memory (cache) is much faster than reading from disk. Because the FSG Cache size is limited, older data blocks are removed from the cache (“aged out”) as space is required to cache more recently accessed data.

Reference tables are typically accessed frequently. Caching data from these tables can markedly improve system performance. Because reference tables are often relatively small, data from several reference tables will fit into the cache.

Full-table scans of large, non-reference tables, however, if cached, can quickly overwhelm the cache, displacing all existing cached data. If full table scans of large tables are infrequent occurrences, as is typical, there is little benefit to caching this data.

DBSCacheThr provides a way to exclude data from larger tables from the FSG Cache, helping to ensure that data from smaller tables is retained in the cache as long as possible.

DBSCacheThr specifies the proportion of the FSG Cache that may be occupied by a table so that the table data be cached. Because it is a threshold value, tables equal to or smaller than DBSCacheThr will be preferentially cached. Larger tables will not be cached, under most circumstances.

DBSCacheThr is one of a number of factors that influence whether a data block is cached. It does not solely determine which data blocks are cached.

Considerations

  • DBSCacheThr affects the caching of spool tables in addition to permanent data tables. If typical system work produces large spool tables, setting DBSCacheThr to a small value might prevent spool tables from being cached. This would slow query performance.
  • Large tables that would normally be excluded from the cache by DBSCacheThr may qualify for synchronized table scans if two or more queries perform a full table scan on the large table simultaneously. In these cases, data from large tables may be cached, regardless of the DBSCacheThr setting. For more information see SyncScanCacheThr.

Recommendations

  • Use DBSCacheThr to prevent large, sequentially read or written tables from pushing other data out of the cache. Set DBSCacheThr to a value that corresponds to the demarcation between smaller, more frequently access tables, and larger tables that are infrequently accessed. Ideally, there will be a jump in size between these types of tables which makes distinguishing them easy.
  • If moderately sized tables are accessed frequently, setting DBSCacheThr to cache these tables might cause smaller, less frequently accessed tables to be cached, which could impact system performance. Carefully evaluate the performance impacts of any changes to DBSCacheThr before committing those changes on a production system.
  • Because DBSCacheThr also affects caching of spool tables, set DBSCacheThr to the smallest possible value that will not adversely affect spool tables generated by the typical system workload. For most moderate to large systems today, this would be a DBSCacheThr setting of 1%. If the average spool table size per node or AMP is greater than 1% of FSG Cache per node or AMP, DBSCacheThr can be set to a higher value.
  • To calculate a DBSCacheThr threshold value, determine the size of frequently accessed tables that should be preferentially cached. Assume the table is evenly distributed across all nodes and AMPs of the system, and determine the percentage of cache on each node or AMP that table would occupy. Set DBSCacheThr to this percentage.

    For example: DBSCacheThr setting = Per-node table size/FSG Cache per node

Example: DBSCacheThr field and small, frequently-accessed reference tables

Assume a system with many small reference tables that are frequently accessed. The goal of the DBSCacheThr setting is to preferentially retain these tables in the cache for as long as possible. Base the DBSCacheThr setting on the size of a typical reference table, and the amount of space such a table occupies on each node of the system:
  • Typical small, frequently accessed reference table = 100 MB (one million rows with 100 bytes per row)
  • System has 10 nodes
  • Vantage distributes the table rows evenly across all AMPs and nodes of the system: 100 MB / 10 nodes = 10 MB per node occupied by the table
RAM per Node FSG Cache per Node DBSCacheThr Setting
1GB 500MB 2%
2GB 1.5 GB 1%
4 GB 3.5 GB 1%

These DBSCacheThr settings influence system caching decisions in favor of caching these tables.

Example: DBSCacheThr field and large tables with full table scans

Assume a system with a workload that requires full-table scans of large tables. The goal of the DBSCacheThr setting is to preferentially exclude these tables from the cache, so that smaller, more frequently access tables will stay in the cache longer. Base the DBSCacheThr setting on the size of a typical large table, and the amount of space such a table occupies on each node of the system:
  • Typical large, infrequently accessed table = 1000 MB (10 million rows with 100 bytes per row)
  • System has 10 nodes
  • Vantage distributes the table rows evenly across all AMPs and nodes of the system: 1000 MB / 10 nodes = 100 MB per node
RAM per Node FSG Cache per Node DBSCacheThr Setting
1GB 500MB less than 20%
2GB 1.5 GB less than 6%
4 GB 3.5 GB less than 2%

These DBSCacheThr settings influence system caching decisions in favor of excluding these tables from the cache.

Related Information