PACKDISK - Advanced SQL Engine - Teradata Database

Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
June 2020
Language
English (United States)
Last Update
2020-06-15
dita:mapPath
boh1556732696163.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1102
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

The PACKDISK command arranges (packs and unpacks) data on the storage associated with the tables in the current scope, leaving a specified percentage, the free space percent (FSP), of the storage empty. This allows for table growth within the currently allocated storage, and can free up cylinders for new storage allocations.

Syntax

PACKDISK
  [ /Y ]
  [ { FREESPACEPERCENT | FREE | FSP } [=] number ]
  [ FORCE ]
/Y
To bypass user confirmation.
If you do not specify the /Y option, then Ferret requests for user confirmation before proceeding with PACKDISK.
FREESPACEPERCENT
FREE
FSP
Optionally specifies the percentage of storage space that PACKDISK should leave unoccupied on cylinders for tables in the current scope. This free space percent (FSP) allows tables to grow in place, deferring the need for allocation of new free storage cylinders.
Because read-only tables do not grow, they do not require free space.
The FSP for a table can also be specified when the table is created or modified using CREATE TABLE and ALTER TABLE statements. If defined for a table, this FSP value takes precedence over the PACKDISK FREESPACEPERCENT value, unless the FORCE option is used.
If FREESPACEPERCENT is not specified with PACKDISK, and no FSP was specified with CREATE TABLE or ALTER TABLE, PACKDISK uses the free space percent value defined by the FreeSpacePercent setting in DBS Control.
number
Percentage of storage space to be left free in the cylinders of tables in the current scope.
FORCE
Forces PACKDISK to pack table data to the FREESPACEPERCENT value, regardless of current FSP values that might have been set using CREATE TABLE or ALTER TABLE statements.
For tables that have an FSP set with CREATE TABLE or ALTER TABLE, results from using the FORCE option of PACKDISK are only temporary. When the periodic background AutoCylPack task runs, it returns table free space to the FSP value set using CREATE TABLE or ALTER TABLE statements. IF FORCE is not used, PACKDISK honors the current FSP values that have been explicitly set using CREATE TABLE or ALTER TABLE. For tables with no explicit FSP, PACKDISK uses the system default defined in DBS Control.

The Ferret INQUIRE (or INQ) and ABORT commands can be used to check on the progress or halt this operation during command execution.

Up to 30 seconds can elapse before the ABORT operation begins.

Usage Notes

PACKDISK packs cylinders for tables in the currently defined Ferret scope. For more information on the scope of Ferret commands, see SCOPE.

Packing applies to cylinders, not to the space in individual data blocks within those cylinders. Data block sizes remain the same after the PACKDISK operation.

Teradata Database can isolate some file system errors to a specific data or index subtable, or to a contiguous range of rows ("region") in a data or index subtable. In these cases, Teradata Database marks only the affected subtable or region down. This improves system performance and availability by allowing transactions that do not require access to the down subtable or rows to proceed, without causing a database crash that would require a system restart.

If PACKDISK encounters down regions, it skips these regions, and displays the percentage of total space that was skipped.

Precedence of FSP Settings

FSP specifies the minimum amount free space that should be left on the cylinder during a data load operation. This reserves space for future table updates. Because read-only tables do not grow, they do not require free space.

Because FSP can be specified in a number of places, the amount of space that PACKDISK leaves free is determined in the following way:

  1. If the PACKDISK FREESPACEPERCENT option is used with the FORCE option, PACKDISK packs to the specified FREESPACEPERCENT. See also Limiting Use of the FREESPACEPERCENT Option.
  2. If the PACKDISK FORCE option is not used, the FSP specified when the table was created or modified using the CREATE TABLE or ALTER TABLE statement takes precedence.

    For more information on CREATE TABLE and ALTER TABLE, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

  3. If no FSP was specified when the table was created or modified, PACKDISK packs to the value specified by the PACKDISK FREESPACEPERCENT option.
  4. If no FSP was specified when the table was created or modified, and the PACKDISK FREESPACEPERCENT option is not used, PACKDISK packs to the value of the FSP system default, specified by the FreeSpacePercent DBS Control setting.

    For additional information, see DBS Control (dbscontrol).

Limiting Use of the FREESPACEPERCENT Option

The FREESPACEPERCENT option of PACKDISK is not intended for routine use. It is intended for exceptional situations, such as:
  • Preliminary tests scoped to a small prototype table to determine the optimal value for the FSP.
  • Later tests of various FSPs on live tables that might require adjustment to ensure more optimal cylinder packing in the future.

The AutoCylPack background task runs periodically to return cylinder FSPs to the levels specified when tables were created or modified, or, if no FSP was specified for the tables, AutoCylPack returns cylinders to the system default for FSP defined in DBS Control.

If the PACKDISK FREESPACEPERCENT option is used to explicitly specify a different FSP value for a PACKDISK run, the AutoCylPack background task will, at some point, return the FSP values to those defaults.

Selecting an Optimal Value for FSP

The optimal amount of free space to leave on cylinders is highly dependent on the individual mix of workload, tables, and applications in use by a site.

Because of the way PACKDISK packs cylinders, some cylinders might still be fragmented after the procedure completes. If this happens, use the DEFRAGMENT command to defragment the cylinder. For information, see DEFRAGMENT. The scope for PACKDISK can be either VPROC or TABLE (but not both).

The SHOWFSP command helps estimate the effectiveness of PACKDISK commands by providing information regarding cylinder utilization. In particular, you can use SHOWFSP to find loosely packed tables.

The SHOWFSP command displays table names and space utilization for those tables that would free or consume some number of cylinders if PACKDISK is executed at a particular FSP. For information, see SHOWFSP. The scope can include one or more tables, one or more vprocs, or the entire system.

For general guidelines for selecting optimal FSP values, see FreeSpacePercent.

For additional guidelines for selecting an appropriate value for FSP, see Teradata Vantage™ - Database Administration, B035-1093.

Mini Cylinder Packs

The file system automatically performs a mini cylinder pack (MiniCylPack) background operation if the number of free cylinders equals or falls below the threshold value specified for MiniCylPackLowCylProd in the DBS Control record. For additional information, see MiniCylPackLowCylProd.

MiniCylPack begins by attempting to honor the FSP that was specified for a table. either when the table was created or modified using CREATE TABLE or ALTER TABLE, or the last time PACKDISK was run on the table. If the previously defined FSP does not allow enough space to be reclaimed, MiniCylPack decreases the FSP by five percent and tries again. This continues until either the necessary cylinders are freed or MiniCylPack reaches zero FSP and still no space can be freed.

When a table affected by a MiniCylPack requires the availability of free space to permit growth without requiring the addition of new cylinders, this operation can result in non-optimal system performance. For example, when many MiniCylPacks occur, they can produce a state known as thrashing. This happens because storage is packed too tightly to permit growth, so frequent allocation of new cylinders to the table space is required. Because this action removes cylinders from the available cylinder pool, more MiniCylPacks are required and an ongoing cycle of thrashing results.

AutoCylPack, a background task the periodically runs to maintain an appropriate level of free space on cylinders, helps alleviate the need for PACKDISK and MiniCylPacks, however, because all MiniCylPacks have a negative effect on system performance, you should monitor system cylinder usage.

To monitor … Do the following …
cylinder utilization Use the Ferret SHOWSPACE command.
occurrences of MiniCylPacks Check the software event log (DBC.SW_Event_Log), or the Linux event log, /var/log/messages.

Schedule PACKDISK operations as often as is necessary to prevent MiniCylPacks from occurring.

For more information about MiniCylPacks, see Teradata Vantage™ - Database Administration, B035-1093.

Example: Running Ferret PACKDISK

Ferret  ==>
packdisk

Tue Dec 08, 2009  16:10:54 : Packdisk will be started
                           On All AMP vprocs
        Do you wish to continue based upon this scope?? (Y/N)
y
Tue Dec 08, 2009  16:10:56 : Packdisk has been started
                           On All AMP vprocs

Type 'ABORT' to stop the command before completion
Type 'INQUIRE' to check on progress of command

Tue Dec 08, 2009  16:18:29 : vproc  0  response
 
Packdisk completed successfully.  Freed up 24 (000018) large cylinders
 
Tue Dec 08, 2009  16:18:32 : vproc  1  response
 
Packdisk completed successfully.  Freed up 25 (000019) large cylinders
Packdisk has completed