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 user gets at least as much space as the size of the largest table the users access concurrently.
- Query workload types.
Decision support queries typically 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 specified user only performs small queries, then allocate less spool space to that user.
If a user performs a large number of large queries, allocate more spool to that user. With the exception of runaway queries, allocating more spool space to a user is never harmful if 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. Therefore, 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.