Running out of free Cylinders in Teradata
Encountering a situation where free cylinders are exhausted is a significant concern when managing a system, and no more Teradata Space is available. It’s an issue that can adversely impact the operations and efficiency of the database, leading to potential slowdowns or even complete halts in data processing and storage activities.
Free cylinders are the basic building blocks that accommodate the growth of various spaces within the database, such as permanent, temporary, and spool spaces, along with permanent journal tables. Therefore, depletion of these free cylinders can severely limit the capacity for these components to expand, thus impeding the overall functioning of the Teradata database. Ensuring a sufficient supply of free cylinders is vital in maintaining optimal database performance.
A crucial point to note here is that there’s a lack of shared space in this setup. In other words, the permanent, temp, spool space, and permanent journal tables cannot coexist within the same cylinder. This restriction maintains the integrity and efficiency of data processing within the Teradata database system.
How Teradata frees up Cylinders?
Teradata has two automatic efficient mechanisms to manage space-related issues: Defragmentation and Mini-Cylinder Packs.
Defragmentation, commonly called ‘Defrag’, is not designed to liberate cylinders. Instead, it’s a process that consolidates the free space within the cylinders currently in use. This process can be managed using the DBS Control by setting the “DefragLowCylProd” tunable parameter. This parameter allows administrators to dictate the free cylinder threshold, which, when reached, triggers the defragmentation process:
On the other hand, Mini-Cylinder Packs, or Mini-CylPacks, serve the purpose of freeing up cylinders. They do this by sequentially shifting data blocks from one cylinder to another. The process halts once the required number of free cylinders is achieved:
Again, DBS Control comes into play in managing this process. The “MiniCylPackLowCylProd” tunable parameter can be set to specify the free cylinder threshold that will prompt a Mini-CylPack. A Mini-CylPack is spontaneously triggered to resolve the issue if a free cylinder is required, but none are available.
How to avoid running out of free Teradata Cylinders?
Several noteworthy strategies and features can be used to handle space-related issues effectively.
One such tool at our disposal is Pack Disk, a process that is not automatic but rather triggered on demand through the use of the Ferret Utility. Pack Disk can be a valuable asset for maximizing the efficiency of data storage and retrieval.
Next, it’s important to consider reserving cylinders for Perm or permanent space. By setting aside cylinders specifically for permanent space (creating a reserved database, keeping it empty), we ensure that there’s always space for critical data that requires constant storage.
Moving on, we find the concept of setting spool limits on user definitions. These limits can help maintain a balance in resource allocation, preventing any user from monopolizing the available spool space and ensuring smooth operation for all users.
Teradata Mini-Cylinder Packs
In the Teradata Database system context, encountering a constant stream of Mini-Cylpack messages in the DBC Software Event Log may stir up concerns. However, it’s important to recognize that Mini-Cylpacks are a natural part of the system’s operation and serve as a warning mechanism that the system could be nearing its space capacity.
In the following sections, we will explore potential solutions to frequent Mini-Cylpacks.
For a more granular understanding, let’s examine how Mini-Cylpacks are logged in the Software Event Log. Three distinct error codes represent them:
- Error code 5141: This logs the summary of Mini-Cylpacks done at the threshold set in the DBS Control record.
- Error code 5142: This is logged when a Mini-Cylpack occurs during processing and a task is waiting for completion.
- Error code 5143 represents the most concerning situation when the system could not free cylinders using Mini-Cylpack and the operation failed.
Among these, error code 5143 is particularly worrisome. It indicates that the system verges on being too full or that the free cylinder threshold has been set unreasonably high. An instance of error code 5143 calls for immediate investigation.
Teradata Pack Disk and the Ferret Utility
This command, part of the Ferret Utility, enables the initiation of a comprehensive cylinder packing process, also known as a CYLPACK.
The PACKDISK command is flexible; it can pack an entire disk or focus on a single table. In the process, it keeps a specified percentage of the cylinder space free. This empty space is thoughtfully reserved to accommodate future INSERT and UPDATE operations. However, it’s important to understand that the packing process applies exclusively to entire logical cylinders, not to the space within individual data blocks inside those cylinders. Consequently, the sizes of data blocks remain unchanged before and after the execution of the PACKDISK operation.
The Free Space Percent can be set within the command window of the Ferret utility.
The Ferret SCOPE command can define the range of tables and/or Vprocs to be displayed or reconfigured with the PACKDISK command. When working with PACKDISK, the selected scope must encompass either Vprocs or tables, but not both simultaneously.
A critical point to remember is that if the desired free space percentage is omitted while executing the PACKDISK command, the system will default to using the percentage specified for the table (for instance, at the time of table creation) or the default value from DBS Control. This built-in default mechanism ensures the operation proceeds seamlessly, even if a specific parameter is overlooked.
Reserving Teradata Cylinders for Permanent Space
Space management within the Teradata Database system involves several configurable parameters, including the ‘Cylinders Saved for Perm’. This DBS Control Record parameter is crucial in optimizing spool file management routines.
By adjusting this parameter, you can prevent exhausting the entire system’s free space, which can help mitigate the frequency of automatically triggered Mini CylPacks. In other words, by setting aside a specific amount of cylinders for permanent space, we can potentially minimize the occurrence of Mini CylPacks.
Additionally, this parameter safeguards against query tasks claiming excessive free space. By leveraging the ‘Cylinders Saved for Perm’ parameter, we can effectively prevent most MiniCylPacks. It’s important to note that a trade-off is involved: a reduction in the space available per AMP for queries.
‘MiniCylPackLowCylProd’ and ‘Cylinders Saved for Perm’ parameters are set to a default value, ensuring minimal impact on the space available per AMP. This default setting helps balance space optimization and query task needs but can be optimized on demand.
Teradata Free Space Percentage
The Teradata ‘Free Space Percent’ parameter is a key control influencing the amount of space left unoccupied on a cylinder. This parameter can be fine-tuned to optimize your database’s space utilization and performance.
The ‘FreeSpacePercent’ value in the DBS Control Record is set to 0% by default. This means that, in its initial configuration, no space is left free on a cylinder. However, this default setting can be altered to suit your needs. You can change the DBS Control Record value to affect all tables that use the system default. Alternatively, you can specify a unique free space percent for a particular table at its creation.
To illustrate the potential impact of this parameter, consider a scenario where you’re using the default 0% setting. After a FastLoad operation, no space would remain on a cylinder. Consequently, adding a row following the FastLoad immediately triggers a cylinder split. So, if you plan to modify or add data after a FastLoad, it may be wise to set aside some free space to expand.
Each table’s free space percentage is stored in the table header. If a table utilizes the system default and the default is modified, the new default will come into effect the next time the table is updated.
However, one important point is that the ‘Free Space Percent’ does not influence the data block size. This parameter solely pertains to managing free space within the cylinders, ensuring your Teradata system runs efficiently and effectively.
it’s important to recognize that not all operations consider the free space percent value recorded in the DBS Control Record. In practice, some operations adhere to the specified free space percent, while others disregard it.
Operations that honor the Teradata Free Space Percent include:
- FastLoad: This utility is designed to quickly load large amounts of data into empty tables, honoring the Free Space Percent during the process.
- MultiLoad: This operation adheres to the Free Space Percent, but only when appending data to the end of a table or when loading an empty table.
- Restore This operation, used for data recovery, respects the Free Space Percent.
- SQL to create a secondary index: The Free Space Percent is respected during this operation.
- INSERT…SELECT into an empty table: When performing an INSERT…SELECT operation into an empty table, the Free Space Percent is considered.
- PACKDISK: This command honors the Free Space Percent.
On the other hand, some operations tend to disregard the Free Space Percent:
- SQL inserts and updates: The Free Space Percent is typically disregarded during regular SQL insert and update operations.
- TPump: This utility, designed for near real-time updates, doesn’t adhere to the Free Space Percent.
- MultiLoad: While MultiLoad does respect the Free Space Percent in certain scenarios, it disregards this parameter when inserting or updating data in a populated table.
Reapply Teradata Free Space Percentage
When dealing with a Teradata system that is tightly packed and you want to apply or reapply the ‘Free Space Percent’ parameter, you have several options at your disposal. However, it’s crucial to remember that these strategies must involve operations that respect the Free Space Percent, ensuring cylinders are filled up to the parameter’s specified value:
- FastLoad after DROP: One of the options is to DROP your largest tables and then repopulate them using FastLoad. By doing this, you’re employing a utility that honors the Free Space Percent, effectively maintaining the specified cylinder space.
- RESTORE after DUMP: Another method is to DUMP your largest tables and then RESTORE them. Again, the restoration process respects the Free Space Percent, ensuring the correct allocation of cylinder space.
- FERRET with PACKDISK: The third option involves using the Ferret Utility. Here, you’d set the SCOPE to the table of interest and then execute the PACKDISK command. If you wish to override the default Free Space Percent, specify the FSP value in the PACKDISK command.
Conclusion
Effective management of Teradata space is crucial to maintain optimal database performance. One significant concern is the exhaustion of free cylinders, the building blocks that allow the growth of various spaces in the database. Teradata provides automatic mechanisms such as Defragmentation and Mini-Cylinder Packs to manage space-related issues.
Defragmentation, or ‘Defrag’, consolidates free space within used cylinders. On the other hand, Mini-Cylinder Packs free up cylinders by sequentially shifting data blocks. Both processes can be controlled using DBS Control by setting tunable parameters.
Manual interventions like Pack Disk, part of the Ferret Utility, can also manage space effectively. This command packs an entire disk or table, freeing a specified percentage of the cylinder space to accommodate future operations.
Teradata also allows the reservation of cylinders for permanent space, minimizing the occurrence of Mini-CylPacks. It provides parameters like ‘Free Space Percent’ to control the amount of space left unoccupied on a cylinder, optimizing space utilization and performance.
However, some operations disregard the ‘Free Space Percent’ value, while others honor it. To apply or reapply the ‘Free Space Percent’, you can use operations that respect this parameter, such as FastLoad after DROP, RESTORE after DUMP, or using the FERRET with PACKDISK.
Remember, the proper management of Teradata space is a blend of understanding your database’s specific needs and leveraging the available tools and parameters to optimize performance and prevent issues related to space exhaustion.