Chapter 4
Space
“For the wise man looks into space and he knows there is no limited dimensions.”
Table of contents Chapter 4 – Space
– Perm Space is for Permanent Tables
– Spool Space is work space that builds a User's Answer Sets
– Spool Space is in an AMPs memory and on its Disk
– USERs are Assigned Spool Space Limits
– What is the Purpose of Spool Limits?
– Why did my query Abort and say “Out of Spool”?
– How can Skewed Data cause me to run “Out of Spool”?
– How come my Join caused me to run “Out of Spool”?
– What does my system look like when it first arrives?
– DBC owns all the PERM Space in the system on day one
– DBC's First Assignment is Spool Space
– DBC's 2nd Assignment is to CREATE Users and Databases
– The Teradata Hierarchy Begins
– The Teradata Hierarchy Continues
– Differences between PERM and SPOOL
– What are Similarities between a DATABASE and a USER?
– What is the Difference between a DATABASE and a USER?
– Objects that take up PERM Space
– A Series of Quizzes on Adding and Subtracting Space
– Answer to Space Transfer Quiz
Perm and Spool Space
Space has only to do with space on the data warehouse disks. Each AMP controls their own disk farm and about 60% of each disk will be used for tables and that is called PERM space. The other 40% (Spool ) is work space for user queries and answer sets.
Perm Space is for Permanent Tables
PERM Space is where an AMP keeps its tables. That is what you need to understand. You will also find out that PERM space also houses Secondary Indexes, Join Indexes and Permanent Journals. Just remember that PERM is for the tables and indexes!
Spool Space is work space that builds a User's Answer Sets
Spool space is used by each AMP in order to build the answer set for the user.
Spool Space is in an AMPs memory and on its Disk
Transfer the Sales_Table from the disk (Perm) to FSG Memory.
Get the Product_ID, Sale_Date and the Daily_Sales columns.
Build the Report in FSG Cache.
If there is no more room in FSG Cache than transfer the report to Spool on Disk.
Keep checking if the USER has gone over their SPOOL Limit.
The Report is done so transfer the report to the Parsing Engine over the BYNET.
DELETE the Spool Files.
AMPs have memory called File System Generating Cache (FSG) used for processing.
USERs are Assigned Spool Space Limits
Every User is assigned Spool Space so they can submit SQL and retrieve answer sets. The Spool in the database Marketing is 10 GB so each user defaults to 10 GB of Spool. Any User in Marketing can run queries, but are aborted if they go over the 10 GB limit. All 3 users in Marketing can query simultaneously and use 30 GB of Spool in total . Three users in Sales defaulted to the max (5 GB) but the intern was assigned less. The final user in DBC was given 100 GB of Spool because they are brilliant.
Spool is assigned to users and the only way you are aborted is if you go over your spool limit. Marketing has unlimited spool, but the max for each user in marketing is 10 GB!
What is the Purpose of Spool Limits?
There are two reasons for Spool Limits:
1. If a user makes a mistake and runs a query that could take weeks to run it will abort as soon as the user goes over their allotted spool limit.
2. It keeps users from hogging the system.
Spool is assigned to users and the only way a user is aborted is if they go over their spool limit. Marketing, Sales, and DBC have unlimited spool, but the max for each individual user is 10 GB in Marketing, 5 GB in Sales, and our power user is at 100 GB.
Why did my query Abort and say “Out of Spool”?
How is it possible that I ran out of spool?
You ran out of spool because your query used over your limit of 10 GB of spool.
It is also possible that you have logged onto multiple machines or ran multiple queries and the combination went over 10 GB of spool.
It is also very likely that the data you were working with was NOT evenly distributed (skewed) and this is a major cause of Spool errors.
Spool is assigned to users and the only way a user is aborted is if they go over their spool limit. No user has ever failed because they are in Marketing and Marketing has only 10 GB of spool. It doesn't work that way. Thousands of users in Marketing could run queries simultaneously because Marketing has unlimited amounts of spool, but each user in Marketing is limited to 10 GBs individually.
How can Skewed Data cause me to run “Out of Spool”?
Each User's Spool limit is actually done per AMP so if you are assigned 10 GBs of spool and the system has 10 AMPs you are really assigned 1 GB of Spool per AMP!
If data is skewed and you exceed your 1 GB limit on any AMP you are “out of spool”.
Spool is assigned to every user, but since Teradata is a parallel processing system each AMP is only concerned with themselves. Each AMP processes their portion of the data in parallel. Because of this philosophy your Spool Space (10 GB) is divided among the total AMPs in the system. If you have 10 GBs of Spool and there are 10 AMPs you get 1 GB per AMP. If you go over 1 GB on any AMP you are aborted and “Out of Spool”.
How come my Join caused me to run “Out of Spool”?
 |
You might not have put in a Join Condition. |
SELECT First_Name, Last_Name, Department_Name |
 |
You might have Aliased the table and then fully qualified with the real table name. |
SELECT First_Name, Last_Name, Department_Name |
 |
There might be skewed data on one of the tables. |  |
 |
A Lot of NULLs on a table on an Outer Join. |
SELECT e.*, d.* from Employee_Table as E |
What does my system look like when it first arrives?
All Teradata systems start with one USER called DBC.
The first Teradata machine ever built came out in 1988 and it was called the DBC 1012. The DBC portion stood for Database Computer. The 1012 was named because 10 to the 12th power is equal to a Terabyte. So, the DBC 1012 was a Database Computer designed to process Terabytes of data. So, every system starts with one USER called DBC and DBC owns all the PERM Space in the system.
DBC owns all the PERM Space in the system on day one
When the system starts out new and arrives at your company DBC is the only USER. DBC counts up all the disk space attached to each AMP and considers that PERM Space owned by DBC.
DBC owns all the disk space on day one of your systems arrival. DBC will then begin to put out space to other databases or users.
DBC's First Assignment is Spool Space
DBC will create a database called Spool_Reserve (any name will do), but it will reserve between 20% to 40% for Spool. What really happens is that DBC creates Spool_Reserve to claim PERM Space, but never places a table in the database.
When a database is given PERM Space and no object is created in that database it is used for Spool. Spool is unused PERM!
DBC's 2nd Assignment is to CREATE Users and Databases
CREATE USER Retail
FROM DBC
AS
PASSWORD=abc123
PERM=2000000000000
SPOOL=10000000000
TEMPORARY = 10000000000
ACCOUNT='$Med'
DEFAULT DATABASE = DBC ;
CREATE USER Financial
FROM DBC
AS
PASSWORD=abc123
PERM=2000000000000
SPOOL=5000000000
TEMPORARY = 10000000000
ACCOUNT='$Med'
DEFAULT DATABASE = DBC ;
DBC's 2nd assignment will be to create some USERs or DATABASEs and the hierarchy begins. If a USER or DATABASE is assigned PERM space it can CREATE tables.
The Teradata Hierarchy Begins
Notice in example 1 that DBC owns 10 TB of PERM space. Notice that after DBC created Spool_Reserve (4 TB), USER Retail (2 TB) and USER Financial (2 TB) that DBC now only owns only 2 TB of PERM space.
The Teradata Hierarchy Continues
USER Retail and USER Financial now create the databases and users desired.
Differences between PERM and SPOOL
There are 1,000 users in Retail. Since Retail has 10 GB of spool that means that every user gets 10 GB of spool. That is the maximum limit for Retail. What it does NOT mean is that Retail is limited to only 10 GB of spool in total. Every user could logon and run a 9 GB query taking up Terabytes of Spool and nobody would run out of spool. Spool is system wide and calculated on an individual level only.
Databases, Users, and Views
For security purposes the Retail tables will be kept in their own database called Retail_Tbls (in this example). The general Retail User Population will NOT have access directly to these tables. A Database called Retail_Views houses the views that access the tables. So, the DBA will create Access Rights that allow the views to read the tables and the Users to SELECT from the views.
What are Similarities between a DATABASE and a USER?
A Database or a User can be assigned PERM Space
If the Database Marketing is assigned 10 GB of PERM that means it can hold up to 10 GB of Permanent Tables.
If the User Maria is assigned 10 GB of PERM that means she can hold up to 10 GB of Permanent Tables.
A Database or a User can be assigned Spool Space
If the Database Marketing is assigned 10 GB of Spool that means all users under marketing can each run 10 GB queries.
If the User Maria is assigned 10 GB of Spool that means she can run up to 10 GB queries and any user created under Maria will default to 10 GB queries.
What is the Difference between a DATABASE and a USER?
A USER has a login and password and therefore can run queries
Objects that take up PERM Space
Permanent Space (Perm space) is the maximum amount of storage assigned to a user or database for holding:
• Table Rows
• Fallback Tables
• Secondary Index Subtables
• Stored Procedures
• User Defined Functions (UDFs)a
• Permanent Journals
Views and Macros do NOT take up any Perm Space!
A Series of Quizzes on Adding and Subtracting Space
Marketing
10 GB Perm
10 GB Spool
Sales
5 GB Perm
5 GB Spool
Marketing has 10 GB of Perm and Spool. Sales has 5 GB Perm and Spool.
Marketing then Creates Stan and gives him 1 GB Perm and 10 GB Spool.
Sales then Creates Mary and gives her 1 GB Perm and 5 GB Spool.
After creating users how much Perm / Spool is in Marketing and how much is in Sales?
Answer 1 to Quiz on Space
Marketing
10 GB Perm
10 GB Spool
Sales
5 GB Perm
5 GB Spool
Marketing has 10 GB of Perm and Spool. Sales has 5 GB Perm and Spool.
Marketing then Creates Stan and gives him 1 GB Perm and 10 GB Spool.
Sales then Creates Mary and gives her 1 GB Perm and 5 GB Spool.
After creating users how much Perm / Spool is in Marketing and how much is in Sales?
Space Transfer Quiz
If a USER is dropped their PERM Space goes up to their immediate parent.
If a USER is transferred (GIVE Statement) they take their space with them.
Stan has just been transferred to Sales.
After the transfer how much Perm / Spool is in:
Marketing _____________ ________________
Sales _____________ ________________
Stan _____________ ________________
Answer to Space Transfer Quiz
If a USER is dropped their PERM Space goes up to their immediate parent.
If a USER is transferred (GIVE Statement) they take their space with them.
Stan has just been transferred to Sales.
After the transfer how much Perm / Spool is in:
| Marketing | 9 GB Perm | 10 GB Spool |
| Sales | 4 GB Perm | 5 GB Spool |
| Stan | 1 GB Perm | 10 GB Spool |
Drop Space Quiz
If a USER is dropped their PERM Space goes up to their immediate parent.
If a USER is transferred (GIVE Statement) they take their space with them.
What happens NOW if Stan is Dropped?
After the drop how much Perm / Spool is in:
Marketing ___________ ___________
Sales ___________ ___________
Stan ___________ ___________
Answers to Drop Space Quiz
If a USER is dropped their PERM Space goes up to their immediate parent.
If a USER is transferred (GIVE Statement) they take their space with them.
What happens NOW if Stan is Dropped?
After the drop how much Perm / Spool is in:
| Marketing | 9 GB Perm | 10 GB Spool |
| Sales | 5 GB Perm | 5 GB Spool |
| Stan | dropped (0) | dropped (0) |
Watch the Video on Space
Tera-Tom Trivia
Tera-Toms picture was retired and placed inside the wrestling room at the University of Arizona.
The University sent Tom the picture years later and his three kids are pictured with it.
Click on the link below or place it in your browser and watch the video on space.