DWHPro Insights

Teradata Physical Storage: Exploring Data Blocks and Storage Demographics

By Roland Wenzlofsky ·

Teradata Physical Storage: Exploring Data Blocks and Storage Demographics

This is a conceptual explanation with historical implementation examples. Logical cylinders and sectors do not imply mechanical disk geometry, and exact packing thresholds must be checked for the target release. It is not an instruction to run storage-maintenance utilities on a live system.

Introduction to Teradata Physical Storage

In the second part of this series on Teradata Physical Storage, let me take over from Roland to continue where we stopped in part 1. We will enter the world of data blocks and dive deeper into the sequence of actions and consequences for the lives of rows populating data blocks.

Recall that each AMP holds rows from all tables, but the rows of different tables are never put together in the same data block.

Using an analogy to illustrate this, think of an AMP as one federal state (of some union of countries that make up your system), a table as a tie for rows much like a tribe or ethnic group, only more binding, and the individuals belonging to it as the rows. Then, the data block can be seen as a gated community concept resulting from strict social stratification at the local level. So while a person (row) comes together with others from all kinds of backgrounds to perform tasks (query) in every state (AMP), people from different tribes (tables) never reside in the same quarter (blocks).

 Inside the Teradata data block

What does the interior of such a gated community, i.e., a block, look like?

datablock

A data block contains block metadata and a row-pointer array. The pointer array provides ordered access to row locations; a data-block header is not the table header, which is a separate structure.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

The concrete sorting of the rows upon arrival at the block – translate this to the question of who moves in what house next to whom when the community is opened – is determined by the loading mechanism applied or, more generally, the way the row was told to be stored for the table. Fastload and Multiload inserts lead to rows being stored in ascending order by their ROWHASH value while rows arriving via SQL INSERT or updates of TPUMP will populate the data block wherever space is free at the time they arrive at the block. The ROWHASH value could be seen as a passport ID in our analogy.

Teradata data block demographics

Data blocks occupy logical sectors and can grow or split as rows change. The historical description uses 512-byte sectors; the former phrase “255 sectors of 255 bytes” was inconsistent. Current block-size limits and allocation increments depend on the release and configuration, so do not use that historical limit as a sizing rule.

These standard size thresholds change as new Teradata versions are introduced, much like a government authority can change its definition, policy, or budget for housing and community size regulations. In these days of ever-larger data amounts that translate into ever broader table rows, the general tendency is upward, much like the demographic trend of rising population and obesity becoming an issue in developed, well-nourished countries.

Depending on the particular usage of storage, the arrival of a new row can trigger one of the following activities for a block:

If there is enough free space in one end-to-end chunk, the row can be stored smoothly. Having been stored, the Pointer Array and the Cylinder Index are refreshed to reflect the change.

Crowded blocks and defragmentation

If, on the contrary, there is free space for housing another row as such, but not in one piece anymore, a defragmentation process tidies up storage space usage before the row can be added. This would be the community chief walking from tent to tent, asking the Meyers to move a few meters to the left and the Millers to park their car at the other side of the street, over the entire community until free space is one chunk where the newcomers can settle.

If even the most efficient use of space reveals that there is no more room to settle one more row, a new 512 bytes piece of land is declared new settlement ground for the community, but not without the Cylinder Index, much like a government agency for housing, land use, and resettlement affairs, being contacted first to ask for an uninhabited new ground to settle the entire current community and another 512 bytes for future row generations. The Cylinder Index Free List returns such a piece of land, and, as a consequence, the block moves to a new location. The land once used by the block is categorized as free again. Furthermore, adjacent free blocks or sectors are merged into one more substantial piece of free land and are then kept in the Cylinder Index Free List as one entry.

If the enlarged block to move is so large that there are not enough adjacent sectors in a cylinder, all existing blocks of a cylinder have to move such that a larger chunk of space is created and the enlarged original block to move finds its space. Imagine having to relocate the bigger-to-be state’s capital. For this to succeed without transgressing the county limits, all towns have to move closer to each other and the north so that the new larger capital finds a resulting piece of land.

When space is tight, block movement and packing may make room. The number of blocks moved and the conditions that trigger it are implementation-specific; use the target release’s storage-administration documentation.

 The problem of sparsely populated blocks

After large deletions, sparsely populated blocks may be merged by packing mechanisms. The former fixed eight-block threshold described an older implementation and is not a universal current limit.

The process of dealing with the automatic merging of blocks to economize is called MiniCylpack. It works over the cylinders over one AMP. An all-AMP equivalent that has to be triggered manually is the Packdisk process. With the introduction of Teradata 13.10, AutoCylpack does what Packdisk does on a system level whenever it is determined as idle and, therefore, without manual intervention by database administrators.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam