With the Teradata Version 16.00, some exciting features in Space Management have been introduced, which can bring enormous improvement in the daily activities in the administration and loading of the data warehouse.
In my current environment, it regularly happens that the data warehouse’s daily loading 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 perm space:
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 case, each AMP has 10GB of permanent space available.
Limits for Skewed Tables
This even distribution of the available PermSpace to all AMPs is based on the assumption that all tables’ rows 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 precisely 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 currently 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 astonished that there was enough space (MaxPermSpace – CurrentPermSpace), the error message “2644: No more room in database Customer” appeared.
To reduce or completely prevent such problems, Teradata 16.00 offers the possibility to define the available space globally.
Therefore the new DBC Table GlobalDBSpace and an extended syntax for creating a database were introduced:
CREATE DATABASE Customer AS PERM = 800000000000 SKEW = 10 PERCENT;
The 10 PERCENT Skew given when creating the database means the following:
The total PermSpace available is still 80GB. Nothing’s going to change that. However, each of the eight available AMPs can 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 primary index’s clumsy selection 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 of preventing 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 afterward and take appropriate action.