Managing Free Space - Teradata Database

Managing Free Space

Evaluating FreeSpacePercent

Reserved free space allows tables to expand within their currently allocated cylinders. This can prevent or delay the need for additional cylinders to be allocated, which incurs the overhead of moving data to the new cylinders. Avoiding new cylinder allocations can improve overall system performance.

Choosing an appropriate FSP value for a table involves both performance and space considerations, and depends on the growth needs of the table:

Reference tables that experience no modifications or growth require no FSP, so FSP can be zero for these types of tables. If the system is primarily these types of tables, set FreeSpacePercent to zero, and use the FREESPACE option of the CREATE TABLE and ALTER TABLE SQL statements to set a different FSP value for tables that will experience growth.

Tables that are expected to experience large scale growth require higher FSP values than tables that grow to lesser degrees. However, larger FSP values consume more storage, so FSP choice should balance space and performance considerations.

A table that would require 100 cylinders of storage with 0% FSP, requires 134 cylinders when FSP is set to 25%. If FSP is 75%, that same table would require 400 cylinders. Ensure that the requisite cylinders are available to satisfy the storage overhead added by the FSP, or performance can suffer.

With time, the available free space may change, due to table modifications that do not honor the FreeSpacePercent setting. The AutoCylPack background task runs periodically to check and restore the FSP for tables. The Ferret PACKDISK command can be run manually to force FSP to be restored, or to temporarily set a table FSP to a different value. MiniCylPack may change the available free space if there is a shortage of cylinders on the system.

Effects of FreeSpacePercent

FreeSpacePercent (FSP) is a system-wide parameter. FSP does not override a value you specify for an individual table in a CREATE or ALTER TABLE request.

MiniCylPack operations attempt to honor the FSP specified in a CREATE TABLE and ALTER TABLE statements or, if no table-specific FSP is specified, the system default FSP. If MiniCylPack cannot reclaim space while honoring those values, it will keep trying to use a degraded (smaller FSP) value until space can be reclaimed. This continues until MiniCylPack attempts to use an FSP value of 0 for all cylinders.

In some situations, Teradata Database runs out of free cylinders even though over 20% of the permanent disk storage space is available. This is due to a:

Higher FSP setting than necessary, which causes the system to allocate unneeded space

Lower FSP setting than necessary, which causes the system to over-allocate new cylinders

Low storage density (utilization) on large tables due to cylinder splits

Operations Honoring the FSP

When adding rows to a table, the file system can choose either to use 100% of the storage cylinders available or to honor the FSP. The following operations honor FSP:

FastLoad

MultiLoad into empty tables

Restore

Table Rebuild

Reconfiguration

SQL to add fallback

SQL to create an SI

Operations that Disregard FSP

The following operations disregard FSP:

SQL inserts and updates

Tpump

MultiLoad inserts or updates to populated tables

If your system is tightly packed and you want to apply or reapply FSP, you can:

Specify the IMMEDIATE clause with the ALTER TABLE statement on your largest tables.

DROP your largest tables and FastLoad them.

DUMP your largest tables and RESTORE them.

In Ferret, set the SCOPE to TABLE and PACKDISK FSP = xxxx

In each case, table re-creation uses utilities that honor the FSP value and fills cylinders to the FSP in effect. These options are only viable if you have the time window in which to accomplish the processing. Consider the following guidelines:

If READ ONLY data, pack tightly (0%).

For INSERTs:

Estimate growth percentage to get FSP. Add 5% for skewing.

After initial growth, FSP has no impact.

Reapply FSP with DROP/FASTLOAD, DUMP/RESTORE or PACKDISK operations.

Experiment with different FSP values before adding nodes or drives.