Teradata Database uses spool space as temporary storage for result rows that are returned for user requests. Because spool space is a form of temporary space, it is frequently overlooked in capacity planning, yet it is critical to the operations of the database. Spool space needs vary from table to table, user to user, application to application, and with frequency of use. Very large systems use even more spool than smaller systems. Spool rows have a maximum length of approximately 1MB.
- Intermediate
- Output
- Persistent
- Volatile
Intermediate Spool Space
Intermediate spool results are retained until no longer needed. You can determine when intermediate spool is flushed by examining the output of an EXPLAIN. The first step performed after intermediate spool has been flushed is designated “Last Use.”
Output Spool Space
Output spool results are the final information returned for a query or the rows updated within, inserted into, or deleted from a base table. The length of time output spool is retained depends on the subsystem and various system conditions, as described in this table:
| Subsystem/Condition | When Output Spool Is Released |
|---|---|
| BTEQ | Last spool response. |
| Embedded SQL | The open cursor is closed. |
| CLIv2 |
|
Session terminates asynchronously due to any number of conditions, including the following.
|
At the time the termination occurs. |
| System restart | At the time the restart occurs. |
Persistent Spool Space
When Redrive protection is enabled, Teradata Database stores responses for sessions that participate in Redrive in persistent spool tables. Persistent spools are not deleted following a Teradata restart or node failure. Persistent spools are retained until the SQL request completes and the application has fully received the response. For details about Redrive protection, see Teradata Vantage™ - Database Administration, B035-1093.
Volatile Spool Space
The system uses volatile spool space for volatile tables. This is necessary because volatile tables do not have a persistent stored definition.
Sources of Spool Space
Spool space is taken only from disk cylinders that are not being used for data. Data blocks and spool blocks cannot coexist on the same cylinder.
When spool is released, the file system returns the cylinders it was using to the free cylinder list.
Spool Limits
If you find that queries do not run because they run out of spool space, then increase the spool assignment for the user or database having the problem using the MODIFY USER or MODIFY DATABASE statements, respectively. For the syntax and usage notes for these statements, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144. If out of spool space errors are occurring due to uneven data distribution across AMPs, use global space accounting to avoid some of these errors. Global space accounting dynamically allocates and deallocates space to and from AMPs as needed while the underlying tables of the database or user are modified. For more information about global space accounting, see About Global Space Accounting.
The maximum size for a spool row is approximately 1MB. This larger row size enhances DML operations that are limited by small spool rows.
The amount of spool space allocated to each user and database is assigned at CREATE USER or CREATE DATABASE time.
Guidelines for Allocating Spool Space
Unless you reserve a pool of spool space, the space available for spool tends to disappear quickly. When applications consume all the spool space allocated for a system, processing halts.
A large Teradata customer in the retail business uses the following method as a guideline for allocating spool space:
- Create a special database to act as a spool space reservoir.
Allocate 20% of the total user space in the system for this database.
- Assign roughly 0.25% of the total space to each user as an upper limit, ensuring that each receives at least as much space as the size of the largest table they access concurrently.
- Query workload types.
Decision support queries generally require more spool than OLTP and tactical queries.
- Average spool use per user.
- Number of concurrent users.
- Number of concurrent queries permitted for any one user.
Spool space is cumulative per user.
- Query size
The smaller the query, the less spool space required. If a particular user only performs small queries, then allocate less spool space to that user.
If a user performs many large queries, then allocate more spool to that user. With the exception of runaway queries, allocating more spool space to a user is never harmful as long as system resources are not wasted.
- Database size
The more AMPs in the configuration, the more thinly spread the data, so the more spool is required per AMP. Because of this, you should consider defining the spool space for a database or user using a constant expression to scale the amount of spool space assigned based on the number of AMPs on the system.
CREATE DATABASE spool_reserve AS PERM = 2000000*(HASHAMP()+1)
where the specified value for PERM space is roughly 20% of the total available disk space for the system, which is based on the multiplier of 2,000,000 bytes.
This specification uses the constant expression 2,000,000 * (HASHAMP()+1) to calculate the number of AMPs in the current system and then scales the PERM space for the spool_reserve database to that size.