Teradata Space Limit Features
With the Teradata Version 16.00 some interesting features in the area of Space Management have been introduced, which can bring enormous improvement in the daily activities in the area of administration and loading of the data warehouse.
In my current environment, it regularly happens that the daily loading of the data warehouse is interrupted because there is not enough permanent space available in the databases.
As we all know, the maximum available space is defined when a database is created. Here is the syntax to create a database with 80 GB permspace:
CREATE DATABASE Customer AS PERM = 800000000000;
The entire available space is then evenly allocated to all AMPs. For simplicity’s sake, we assume 8 AMPs. In this
Limits for Skewed Tables
This even distribution of the available PermSpace to all AMPs is based on the assumption that all rows of all tables are distributed evenly using the primary index. But what happens if this is not the case?
Let’s assume that we want to load a table with 11GB of data, and the designer of the table structure has chosen the Primary Index so clumsy that all rows are assigned to exactly one AMP. Since the space limit on each AMP is 10GB,
we will receive the following error message:
2644: No more room in database Customer.
This means that although the remaining 7 AMPs each have 10GB available, the table cannot be loaded.
The maximum available space in a database is shown in table DBC.DATABASESPACE in the MaxPermSpace column. The current occupied space can be seen in Column CurrentPermSpace. In CurrentPermSpace, however, only the entire occupied space of the database is displayed, not the occupied space per AMP.
I’ve often seen surprised faces of developers who were surprised that when there was enough space (MaxPermSpace – CurrentPermSpace) the error message “2644: No more room in database Customer” appeared.
In order to reduce or completely prevent such problems, Teradata 16.00 offers the possibility to globally define the available space.
Therefore the new DBC Table GlobalDBSpace, as well as an extended syntax for creating a database was introduced:
CREATE DATABASE Customer AS PERM = 800000000000 SKEW = 10 PERCENT;
The 10 PERCENT Skew given when creating the database mean the following:
The total PermSpace available is still 80GB. Nothing’s gonna change that. However, each of the 8 available AMPs is allowed to hold up to 11GB of data if it does not exceed the absolute upper limit of 80GB of the database.
In our example from above, in which the clumsy selection of the primary index would insert all rows on exactly one AMP, the error “2644 No more room in database Customer” would no longer occur. However, such a situation is recorded in the Software Event Log.
While the feature described above simplifies the handling of skew tables, another feature was introduced with Teradata 16.00, which has the purpose to prevent loads from crashing if not enough database space has been defined:
Global Space Soft Limit.
This is defined via DBSControl GlobalSPaceSoftLimit and specified as a percentage.
In principle, a percentage is defined here above which the defined PermSpace may be exceeded without the error “2644 No more room in database Customer” occurring.
For example, if the database is defined on an 8 AMP system with 80GB PermSpace, and a soft limit of 10%, up to 11GB PermSpace can be used per AMP.
Again, an event is triggered and written to the Software Event Log. This allows database administrators to analyze the situation