Teradata Version 16.00 introduces thrilling Space Management features capable of significantly enhancing daily data warehouse administration and loading processes.
In my current setting, the data warehouse’s daily loading is frequently interrupted due to insufficient permanent database storage.
The maximum capacity of a database is established during its creation. The following syntax creates a database with a permanent space of 80 GB:
CREATE DATABASE Customer AS PERM = 800000000000;
Teradata evenly distributes the defined space across all AMPs in a uniform manner. Assuming the availability of 8 AMPs, each AMP will have 10GB of permanent space allocated.
Limits for Skewed Tables
Is the PermSpace evenly distributed among all AMPs if the table rows are not uniformly distributed?
Assuming we need to load an 11GB table and the Primary Index assigned all rows to only one AMP, which is an inefficient design choice. As each AMP has a space limit of 10GB, we will encounter an error message:
2644: “No more room in database Customer.“
Despite the 10GB available on the remaining 7 AMPs, Teradata cannot load the table.
To view the maximum space available in a database, consult the MaxPermSpace column in table DBC.DATABASESPACE. The CurrentPermSpace column displays the current occupied space, but only for the entire database and not per AMP.
As a frequent observer, I’ve witnessed developers’ bewilderment when they encounter sufficient available space (MaxPermSpace – CurrentPermSpace) yet still receive the error message “2644: No more room in database Customer.”
Teradata 16.00 provides the option to globally define available space, effectively reducing or eliminating related issues.
The introduction of the new DBC Table GlobalDBSpace and an expanded syntax for database creation has been implemented.
CREATE DATABASE Customer AS PERM = 800000000000 SKEW = 10 PERCENT;
The new “SKEW = 10 PERCENT” feature is set when creating the database and means the following:
The PermSpace capacity remains at 80GB. Each of the eight AMPs can store up to 11GB of data if the database’s absolute limit of 80GB is not surpassed. This determines the percentage by which an AMP can surpass its assigned space without the query being terminated.
In the previous example, where the primary index’s inefficient selection would insert all rows onto a single AMP, the occurrence of the “2644 No more room in database Customer” error would be eliminated. Nonetheless, Teradata would still document this event in the Software Event Log.
The above feature simplifies handling skewed tables. Teradata also introduced a feature in Version 16.00 to prevent load failures due to insufficient database space.
Global Space Soft Limit.
The Global Space Soft Limit is defined via DBSControl GlobalSpaceSoftLimit.
We define an additional space in percent of the base size that can be occupied without causing the error “2644 No more room in database Customer” to occur.
Creating an 8 AMP system database with 80GB PermSpace and a defined soft limit of 10% allows for up to 11GB PermSpace usage per AMP. This limit applies to all AMPs collectively.
When the database size exceeds the base size, an event is triggered and recorded in the Software Event Log. These log entries enable administrators to analyze the situation and implement necessary measures.
Question about skew = 10 percent: the error “2644 No more room in database Customer” would no longer occur.
Is it so that you still might get No more room in database. Now you have just 1 gigabytes per AMP more space to follow clumsy primary index?
Yes, you could still get no more room.