Determining Available User Table Data Space | Teradata Vantage - 17.10 - Determining Available User Table Data Space - Advanced SQL Engine

To determine available user table data space, you must take several non-user table data space allotments into account.

First you must determine how much space these allotments account for and then you must subtract them from the total available table space.

The nonuser table space allotments that must be accounted for are those in the following list.

Overhead space, including space reserved for allocation maps and related statistics.
Depot area, which includes cylinders reserved as a staging area for modified-in-place data blocks before they are written to their home disk destinations.
Data blocks that are not modified in place are not written to the depot area.
Tables area, which includes the following.
- Data Dictionary (for the WAL log and system tables)
- Crashdumps user
- User temporary space
- User spool space

The following topics describe specific space requirements and provide equations with which to determine variable space requirements.

Depot Area Overhead

The Depot consists of two types of slots:

Large Depot slots
Small Depot slots

The Large Depot slots are used by aging routines to write multiple blocks to the Depot area with a single I/O.

The Small Depot slots are used when individual blocks that require Depot protection are written to the Depot area by foreground tasks.

The number of cylinders allocated to the Depot area is fixed at startup. Consult your Teradata support representative if you want to change this value.

The number of Depot area cylinders allocated is per pdisk, so their total number depends on the number of pdisks in your system. Sets of pdisks belong to a subpool, and the system assigns individual AMPs to those subpools.

Because it does not assign pdisks to AMPs, the system calculates the average number of pdisks per AMP in the entire subpool from the vconfig GDO when it allocates Depot cylinders, rounding up the calculated value if necessary. The result is then multiplied by the specified values to obtain the total number of depot cylinders for each AMP. Using this method, each AMP is assigned the same number of Depot cylinders.

The concept is to disperse the Depot cylinders fairly evenly across the system. This prevents one pdisk from becoming overwhelmed by all the Depot writes for your system.

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 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 Services personnel.

Tables Area: Dictionary and Spool Space Requirements

After you determine how much space remains when overhead is accounted for, determine how much space is required for the data dictionary and spool.

You should reserve approximately 80 MB for growth of system tables and the WAL log. However, optimum space for the WAL log should be based on how many rows are dropped or updated by concurrently running transactions during your peak workload.

Tables Area: User TEMP Space Requirements

The TEMPORARY clause in the CREATE DATABASE and CREATE USER statements permits you to allocate default space within this database or user for inserting data into global temporary tables materialized by users. Note that Vantage always reserves temporary space prior to spool space for any user defined with this attribute.

Disk usage for a materialized global temporary table is charged against the temporary space allocation of the user who referenced the table.

Global temporary tables also require a minimum of 512 bytes from the PERM space of the containing database or user. This space is used for the GTT table header.

If no default temporary space is defined for a database, then the space allocated for any global temporary tables created in that database is set to the maximum temporary space allocated for its immediate owner.

Subtract the TEMP amount allocated to this database.

Tables Area: CRASHDUMPS User Space Requirements

Subtract the PERM amount allocated to this user. The DIP utility creates this database with a default allocation of 1 GB.

If you modified the default amount when you set up the Crashdumps user, subtract the actual current amount.

Tables Area: User Spool Space Requirements

Whenever you create a new user, reserve a minimum of 20% of user permanent space for spool space. It is better to determine the optimum amount of space according to your application environment. This takes into account such factors as average table size, protection choices, number of rows involved in commonly used transactions, and so forth.

The optimum allocation depends on your application environment. If your requirement is to perform the calculations according to the formulas provided in this section, and the calculated amount exceeds 20% of permanent space, use the actual figure plus 5%. To determine an allowance based on your applications, use the formulas in this section.

Rules for Using the Spool Space Equations

When using the spool space equations, keep the following rules in mind:

The equations are based on the following variables.
- Number of rows in the spool
- Amount of data in each row
- Mode of select operation (Field or Record)
The equations only determine the space needed to return rows to the user.
Response rows are limited to approximately 64,000 bytes.
If the row is longer, a row length error is reported.
Even if an ORDER BY clause is not included, the system creates a minimum length sort key of 8 bytes for both Record mode and Field mode select operations.
All descriptions of CHARACTER columns also apply to BYTE columns.
Be sure the NF, CF, SCF, and RDS values represent the sums of all selected or sorted columns. For example, a FORMAT phrase can cause blanks to be added, which must be figured into the total column length.

Field Mode Spool Space Sizing Equation

Use the following equation to determine the amount of usable data space for Field mode:

Usable Data Space = a(RO + RP + n(PH + NF + CF)) + b(SNF + SCF)

The following parameter definitions are used with this equation:

Parameter	Definition
a	Number of rows being selected.
RO	Row overhead (22 bytes per row).
RP	Row parcel indicators; 8 bytes per row (rec start, rec end).
n	Number of columns being selected.
PH	Parcel headers (4 bytes per field).
NF	Formatted size of each numeric column (spaces, dollar signs, and commas should be included).
CF	Formatted size of each character column. For example, if a selected column is defined as VARCHAR(200), it takes up 200 characters even if it contains only 3 nonblank characters. The value for CF can be less than the CREATE TABLE definition of the string only if there is a FORMAT phrase.
b	Number of numeric columns in the ORDER BY clause (sort key).
SNF	Sorted numeric fields (8 bytes each).
SCF	Formatted size of each character column in the ORDER BY clause. If an order operation is done on a column defined as VARCHAR(200), 200 characters are allowed, even if there are only 3 nonblank characters. The value for CF can be less than the CREATE TABLE definition of the string only if there is a FORMAT phrase. The formatted length of the character string should be rounded up to the next even value, then 2 more bytes should be added to the number.

Record and Indicator Mode Spool Space Sizing Equation

Use the following equation to determine the amount of usable data space for Record and Indicator modes:

The following parameter definitions are used with this equation:

Parameter	Definition
a	Number of rows being selected.
RO	Row overhead (12 bytes per row).
RP	Row parcel indicators; 8 bytes per row (rec start, rec end).
RDS	Raw data size of each field returned to the user.
b	Number of numeric columns in the ORDER BY clause (sort key).
SNF	Sorted numeric fields (8 bytes each).
SCF	Formatted size of each character column in the ORDER BY clause. If an order operation is done on a column defined as VARCHAR(200), 200 characters are allowed, even if the value contains only 3 nonblank characters. The value for CF can be less than the CREATE TABLE definition of the string only if there is a FORMAT phrase. The formatted length of the character string should be rounded up to the next even value, then 2 more bytes should be added to the number.
n	Number of columns being selected.
IF	Record mode selects use indicator variables to represent nulls. IF is the constant 8, meaning that for each 8 columns selected, 1 byte is needed to represent a null column.

Calculating Total PERM Space Requirements

Use the following procedure to determine how much PERM space to allocate to user SysAdmin.

Estimate the size of each database, as follows.
1. Estimate the size of the primary data table, including fallback (see Sizing Base Tables, LOB Subtables, XML Subtables, and Index Subtables for instructions for how to do this) and LOB and XML subtables.
  LOB and XML values are also stored in the permanent journal, so LOB and XML column space must account for additional permanent journal append and storage costs in addition to LOB and XML subtable storage costs.
2. Estimate the size of unique and nonunique secondary index subtables, join indexes, and hash indexes.
3. Add the primary data table estimate and index and LOB and XML subtable estimates (including fallback) to obtain the estimated total table size.
Estimate the total table storage by adding together the space estimates of all table sizes (Substeps a, b, and c). Use this sum for step 3.
Estimate the space requirement for the application.
If the calculated sum is greater than the remainder calculated in step 4, contact your Teradata sales representative to discuss a system expansion.
1. Add 7% for fragmentation.
2. Add extra space for spooling, TEMP space, and for variability in hashing. Although this space requirement varies according to the applications, adding 20% to 30% is usually adequate if you do not use permanent journaling.
3. Estimate the size of each journal table per database.
4. Note that if journaling is used, the extra space requirement depends on two factors:
  - Size of the data tables that write to that journal
  - Number of changes applied to those tables before the journal table is dropped
5. Add the sums calculated for steps 1 through 4 and use their sum for the calculation in step 5.
6. For each disk, add 3 cylinders per disk as spaces and allow 2% of the formatted space for cylinder indexes.
Subtract the amount of space needed to accommodate user DBC contents plus your maximum WAL log from the user DBC PERM space.
Specify about 80 MB for the growth of system tables and the WAL log plus another 5% of the current PERM space for spool growth.

Use the remainder for step 5.
If possible, subtract the sum of step 3 from the result of step 4.
Deduct the number of cylinders that make up the default free space specified by the DBS Control record for operations that use the percent freespace (FSP) value.
For example, if your FSP is set to 15, deduct an additional 15%.

If you plan to create large tables with a freespace percentage greater than the GDO default, then use the larger percentage.

For example, if you define your largest tables with an FSP of 30, then deduct an additional 30%.

The remainder is the amount of PERM space you should allocate to the SysAdmin user.

Table sizing is normally the responsibility of Teradata field support personnel.

Determining Available User Table Data Space | Teradata Vantage - 17.10 - Determining Available User Table Data Space - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design