About Freeing Cylinders Using PACKDISK - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™

The PACKDISK command of the Ferret utility reconfigures the contents of an entire disk or an individual table, leaving a percentage of free space for cylinders within a scope defined by the SCOPE command. PACKDISK uses the default Free Space Percent or a new percentage defined as part of the command to pack the entire disk or a table.

The allowable scope for PACKDISK is vprocs or tables but not both.

The system automatically moves data blocks from one cylinder to another, stopping when the required number of free cylinders is available. This operation runs when the number of free cylinders is critically low. You can use the PACKDISK command to force the packing to occur earlier.

About Specifying Free Space Percent

PACKDISK packs either the entire disk or a single table, leaving a specified percentage of the cylinders occupied by the object empty, to account for subsequent insert and update operations. This is the FSP you specify using the tools described in About Setting Free Space Percent Limits.

About Setting the PACKDISK Scope

The PACKDISK command of the Ferret utility reconfigures the contents of an entire disk or an individual table, leaving a percentage of free space for cylinders within a scope defined by the SCOPE command.

About Running PACKDISK

To run PACKDISK, enter in the command window of the Ferret partition, for example:

PACKDISK FREESPACEPERCENT = 25

(where 25 equals the percentage of cylinder free space). Key the command in uppercase, lowercase, or a combination of both.

About Stopping PACKDISK

To stop PACKDISK, enter ABORT.

Packing applies only to entire logical cylinders, not to the space inside individual data blocks within those cylinders. Data block sizes are the same before and after the PACKDISK operation. Also see Managing Free Space.

About Defragmenting Cylinders Using Defragment

Cylinders can become defragmented over time. Use the Ferret utility DEFRAGMENT command to defragment the cylinders on either an AMP or the system, depending on the SCOPE options.

About AutoCylPack and MiniCylPack

AutoCylPack

AutoCylPack (automatic background cylinder packing) manages space on cylinders, finding cylinders with low or high utilization and returning them to their user-defined FSP (Free Space Percent), that is, the percentage of storage space within a cylinder that AutoCylPack leaves free of data to allow for future table growth.

There is a system wide default for AutoCylPack. It can be overridden on a table-by-table basis by specifying the FSP clause in a CREATE TABLE or ALTER TABLE statement.

Although AutoCylPack runs as a background task issuing I/Os, you can adjust its level of impact. For the DBS Control fields that support AutoCylPack, see Teradata Vantage™ - Database Utilities , B035-1102 .

Performance

AutoCylPack helps reduce:
  • The chances that MiniCylPacks run. MiniCylPacks are strictly concerned with reclaiming whole cylinders.
  • The need for you to perform regular Ferret PACKDISK operations.

    PACKDISK can be performed on a table, a range of tables or an entire system, but it affects performance of the foreground tasks and thus system performance.

If cylinders have higher utilization:
  • System performance improves because there is a higher data block to cylinder index ratio and more effective use of Cylinder Read. There will be less unoccupied sectors read in.
  • A table occupies less cylinders. This leaves more cylinders available for other uses.

MiniCylPack

A MiniCylPack moves data blocks in logical sequence from cylinder to cylinder, stopping when the required number of free cylinders is available.

The process continues until one cylinder is completely emptied. The master index begins the next required MiniCylPack at the location that the last MiniCylPack completed.

The Vantage file system will start to MiniCylPack when the number of free cylinders drops to the value set by MiniCylPackLowCylProd. The default is 10.

The File Information Block (FIB) keeps a history of the last five cylinders allocated to avoid MiniCylPacks on them.

Spool files are never cylinder packed.

Use the DBS Control (see “MiniCylPackLowCylProd” in Teradata Vantage™ - Database Utilities , B035-1102 ) to specify the free cylinder threshold that causes a MiniCylPack. If the system needs a free cylinder and none are available, a MiniCylPack occurs spontaneously.

About Migrating Data Blocks

  1. If space can be made available either by migrating blocks forward to the next cylinder or backwards to the previous cylinder, choose the direction that would require moving the fewest blocks.

    If the number of blocks is the same, choose the direction of the cylinder with the most number of free sectors.

  2. If Step 1 fails to free the desired sectors, try migrating blocks in the other direction.
  3. If space can be made available only by allocating a new cylinder, allocate a new cylinder. The preference is to add a new cylinder:
    • Before the current cylinder for permanent tables.
    • After the current cylinder for spool tables and while performing FastLoads.

When migrating either forward or backward, the number of blocks may vary because the system considers different blocks for migration.

Because of the restriction on key ranges within a cylinder, the system, when migrating backward, must move tables and rows with the lowest keys. When migrating forward, the system must move tables and rows with the largest keys.

The system follows special rules for migrating blocks between cylinders to cover special uses, such as sort and restore. There are minor variations of these special rules, such as migrating more data blocks than required in anticipation of additional needs, and looking for subtable breaks on a cylinder to decide how many data blocks to attempt to migrate.

Performance

Although cylinder packing itself has a small impact on performance, it often coincides with other performance impacting conditions or events. When the Vantage file system performs a MiniCylPack, the operation frees exactly one cylinder.

The cylinder packing operation itself runs at the priority of the user whose job needed the free cylinder. The cylinder packing operation is the last step the system can take to recover space in order to perform a write operation, and it is a signal that the system is out of space.

Needing to pack cylinders may be a temporary condition in that a query, or group of queries, with very high spool usage consumes all available free space. This is not a desirable condition.

If space is a problem,
  • Enable AutoCylPack if you have not done so and specify an FSP of 0% for read-only tables using the CREATE TABLE or ALTER TABLE statement
  • Run the Ferret PACKDISK command.

Error Codes

MiniCylPacks are a natural occurrence and serve as a warning that the system may be running short on space. Tightly packed data can encourage future cylinder allocation, which in turn triggers more MiniCylPacks.

The system logs MiniCylPacks in the Software_Event_LogV with the following error codes.

Code Description
340514100 Summary of MiniCylPacks done at threshold set via the DBS Control.
340514200 A MiniCylPack occurred during processing and a task was waiting for it to complete.
340514300 The system could not free cylinders using MiniCylPack. The MiniCylPack failed. This means that the system is either getting too full or that the free cylinder threshold is set unreasonably high. Investigate this error code immediately.

Frequent 340514200 or 340514300 messages indicate that the configuration is under stress, often from large spool file requirements on all AMPs. MiniCylPacks tend to occur across all AMPs until spool requirements subside. This impacts all running requests.

If table data is skewed, you might see MiniCylPacks even if Vantage has not used up most of the disk space.