15.00 - Data Table Overhead - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Data Table Overhead

The system uses some amount of data space, which includes permanent, spool, and temporary space, as overhead for various purposes.

The percentage of data space required for cylinder indexes is calculated based on the following information:

  • Each cylinder has 2 cylinder indexes.
  • Each cylinder index is 32 KB (64 sectors) in size.
  • Therefore, 128 sectors are reserved for cylinder indexes per cylinder.

  • Each cylinder is 23,232 sectors in size.
  • The percentage of data space reserved for cylinder indexes is determined by the following calculation:

    If the cylinder or cylinder index size changes for a release, use the same equation to determine the estimated percentage of data space required for cylinder indexes, but adjust the values accordingly.

    For the current release, the equation evaluates to the following percentage of data space:

    The quantity of permanent data table space, temporary space, and spool space available on a system is also limited by the amount of disk space required by WAL to process update transactions. The amount of disk required by WAL is a function of the number of update operations being undertaken by the workloads running on the system at any given time, and waxes and wanes as a result. The more updates that are done at a time, the greater the quantity of WAL data that is produced. The amount of WAL data produced is roughly equivalent to twice the amount of transient journal information that is required for updates.

    Allow space for data table overhead as listed in the following table:

     

    Number of Cylinders

    Purpose

    13

    General

    One cylinder each for the following purposes:

  • Permanent space sentinel
  • Permanent journal space sentinel
  • System journals are stored either in user DBC, which is permanent space, or in the WAL log, which is not part of journal space.

  • Global temporary spool space sentinel
  • Spool space
  • Nine cylinders for the following purpose:

  • Write Ahead Logging
  • The additional cylinders for WAL are required to ensure that MiniCylPack is able to run in low disk situations to free space.

    Approximately 1.25% of the available data space (minimum)

    Cylinder indexes

    A minimum of 7% free space per cylinder

    Fragmentation

    You must be careful not to allow too much free space for fragmentation, but you must be equally careful not to allow too little.

    The most practical way to establish an optimal number of cylinders to reserve for fragmentation on your system is trial and error.

    Ranges between 1 and 20 per pdisk.

    Depot

    The number of cylinders assigned to Depot is set at startup. The value for your system can only be changed by Teradata support personnel.