PACKDISK and FreeSpacePercent - Teradata Database

PACKDISK and FreeSpacePercent

When Teradata Database runs out of free cylinders, you can run Ferret PACKDISK, an expensive overhead operation, to compact data to free up more cylinders.

To reduce the frequency of PACKDISK operations:

When FastLoading tables to which rows will be subsequently added, set FSP to 5-20% to provide enough free space to add rows.

For historical data, when adding and deleting data, provide enough free space to add rows.

For example, you add up to 31 days before deleting on a table with six months history.

Add one month to six months: 1/7 = 14.3%

Safety - plan on 1.5 months, 1.5 / 7.5 = 20%

Set Free Space Percent to 20%.

For historical data and fragmented cylinders:

For large tables, either set FSP to 20 - 35%, or set MaxBlockSize to a smaller size (for example, 16 KB).

Translate free space to the number of data blocks; at least 6-12 blocks of free space.

Specify the IMMEDIATE clause with the ALTER TABLE statement.

The table header contains the FSP for each table. If you change the default FSP, the system uses the new default the next time you modify the table. FSP has no effect on block size.

Running Other Utilities with PACKDISK

If you run PACKDISK frequently, use the following tools, two of which are utilities, to determine the amount of free space:

DBC.DiskSpaceV

SHOWSPACE, a Ferret command, shows you the percent of free space per cylinder.

If this figure is low, it will impact performance by performing dynamic cylinder packs when the system needs contiguous space.

SHOWFSP, a Ferret command like SHOWSPACE, helps find tables that need packing.

SHOWFSP shows the number of cylinders that can be freed up for individual tables by specifying a desired free space percent, and is useful in discovering which tables would free the most cylinders if PACKDISK were run on them

Cylinder Splits

A FreeSpacePercent value of 0% indicates that no empty space is reserved on Teradata Database disk cylinders for future growth when a new table is loaded. That is, the current setting causes each data cylinder to be packed 100% full when a new table is loaded.

Unless data is deleted from the table prior to subsequent row inserts, the 0% value guarantees a cylinder split the first time that a row is inserted into the table (following the initial load). Cylinder splits consume system I/O overhead and often result in poor use of data cylinders.

PACKDISK, FSP, and Cylinder Allocations

Packing data for tables being modified too tightly can result in too many cylinder allocations, which reduces the number of free cylinders. For example, if the data on a table occupying 10 cylinders is packed to 0% free space, inserting one row on each cylinder (for a total of only 10 rows) may require Teradata Database to allocate up to 10 additional new cylinders.

However, if you run PACKDISK on the table with an FSP of 10%, the original table would occupy 11 cylinders. When the same 10 rows are inserted into this table, there will most likely be space available on the existing cylinders and the table would remain, consuming only 11 cylinders. This leaves the other 9 cylinders that were previously used free and available for future cylinder requests. Another way to accomplish the same goal is to update the table first and then perform the PACKDISK operation to free up any cylinders that are not required.

Running the PACKDISK command in this example saved 9 free cylinders, but required manual intervention. Another way to accomplished this is to specify an FSP of 10% in the CREATE TABLE or ALTER TABLE statement, and then let AutoCylPack adjust the table as part of its background activity. If new rows are added to the table in this example on a regular basis, then the AutoCylPack solution is preferable. If new rows are rarely added, then it is better to leave the table packed with 0% free space and run a PACKDISK when required.