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., block-like?
The first thing we come across is a Table Header – much like a place name sign. An Array of 2-byte Pointers works much like a detailed, personalized road map that shows where every row beginning can be found. It is always sorted.
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 SQLs insert, 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 come in a standard size of at least 512 bytes each upon creation. Every community is founded on a piece of land of equal minimum size. Maximum tolerance for crowding policy is in place that foresees a sudden increase of community land every time the population grows up to the point where every address dwells. A block with no more (predicted, standard) space is then added another 512 bytes, and growth can continue. This enlargement can continue up to a maximum block size of traditionally 255 sectors of 255 bytes each before the block is ultimately split into two blocks.
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.
Having reached the point where even the cylinder is about to be consumed up, Teradata can move up to 10 blocks to a neighboring cylinder or a free cylinder if the adjacent cylinder operates under the same scarcity of space.
The problem of sparsely populated blocks
Imagine now the opposite situation of many defined blocks, but each of them is only sparsely populated with rows. Blocks of tables after gross delete operations can be in such a state. In our analogy, this would be a community after a sudden rise in mortality. Here, the issue is not to enlarge blocks, but rather to merge them. From Teradata 13.10 on, up to 8 blocks can automatically merge within the sphere of a cylinder. It is as if Norseland’s government told the small communities of Norrmen of chief Erik, those of chief Einar and those of chief Björn, to please form one community of Norrmen and free the ancient land of their chiefdom each.
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 on a system level whenever it is determined as idle and, therefore, without manual intervention by database administrators.
