15.00 - PermDBSize - 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

PermDBSize

Purpose  

Specifies the maximum size for multirow data blocks in permanent tables. Rows that are larger than PermDBSize are stored in single-row data blocks, which are not limited by PermDBSize.

Field Group

File System

Valid Range

42 through 2047 sectors for systems that use large cylinders

18 through 512 sectors for systems that use small cylinders

A sector is 512 bytes.

On cliques with 4 KB aligned devices, the setting is rounded up to the next 8-sector multiple.

Default

254 sectors

Setting this field to 0 causes Teradata Database to use the system default size for this type of data block.

Changes Take Effect

After the DBS Control Record has been written.

Usage Notes

When tables are initially populated, Teradata Database stores as many rows as possible into each data block, until the block reaches the size specified by PermDBSize. As tables are subsequently modified, rows can grow such that the existing data blocks would exceed the maximum PermDBSize. When this happens, the data block is split, and roughly half the rows are moved to a new data block, with the result that the original and new data blocks are each one half of the original size. The result of this type of growth and splitting is that data blocks for heavily modified tables tend to be about 75% of the maximum size defined by PermDBSize.

Systems initialized on Teradata Database 13.10 or later release use large cylinders. On these systems, the minimum data block size is 42 sectors (21504 bytes). The minimum data block size accepted by the CREATE TABLE and ALTER TABLE SQL statements is 21248 bytes (41.5 sectors). Tables created or altered to use this minimum size will have the value rounded up by the parser to 42 sectors, so these tables will have an actual minimum data block size of 21504 bytes.

Systems initialized on a Teradata Database release prior to 13.10 and subsequently upgraded to 13.10 or 14.0 use small cylinders. On these systems, the minimum data block size 18 sectors (9216 bytes).

The minimum data block size accepted by the CREATE TABLE and ALTER TABLE statements is 8960 bytes (17.5 sectors). Tables created or altered to use this minimum size will have the value rounded up to by the parser to 18 sectors, so these tables will have an actual minimum data block size of 9216 bytes.

If DBs are compressed, this setting applies to the size of the uncompressed DBs.

The PermDBSize and JournalDBSize settings in DBS Control apply to the uncompressed size of these types of DBs.

Maximum Multirow Data Block Size

The PermDBAllocUnit and PermDBSize fields together determine the maximum size of multirow data blocks. Because data blocks can grow only in steps (allocation units) defined by PermDBAllocUnit, the size of a data block at any time will always be an integer multiple of PermDBAllocUnit, regardless of the PermDBSize setting.

Consequently, if the PermDBAllocUnit setting is not an integer factor of PermDBSize, then the largest multirow data blocks will be smaller than PermDBSize. For example, if PermDBAllocUnit is set to 4 sectors, even if PermDBSize is set to 255, the largest multirow data blocks can be only 252 sectors (the greatest multiple of 4 that is less than or equal to 255). Similarly, if PermDBAllocUnit is set to 16, the largest multirow data blocks can be only 240 sectors.

PermDBSize and System Performance

Database performance can be affected by the relationship of data block size to the type of work typically performed by the database:

  • When database queries are tactical in nature, involving one or a few table rows, it is advantageous to have fewer rows stored per data block to speed data access. Online transaction processing (OLTP) is an example of this type of work.
  • Alternatively, when database queries are strategic in nature, involving complex queries that involve many table rows per table, it is advantageous to have many rows stored in each data block, to minimize costly data I/O operations. Decision support software (DSS) and complex report generation are examples of this type of work.
  • PermDBSize sets the default maximum size used by the system for multirow data blocks in permanent tables. Use a larger value if the database is used primarily for strategic work, and a smaller value if the database is used primarily for tactical work.

    In a mixed-work environment, determine a value for PermDBSize based on the kind of work typically performed by the database. For tables involved in other types of work, PermDBSize can be overridden on a table-by-table basis using the DATABLOCKSIZE option of the CREATE TABLE and ALTER TABLE SQL statements.

    For example, if only a few tables are involved in decision support, overall throughput can be improved by up to 50% using the following strategy:

  • Define a data block size of 254 sectors for the decision support tables.
  • Set PermDBSize to 42, to serve as the default block size for the remaining tables, which are involved in tactical work.
  • Use the smallest value for JournalDBSize, to expedite UPDATE operations.