15.00 - Guidelines for Allocating Spool Space - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Guidelines for Allocating Spool Space

Unless you reserve a pool of spool space, the space that is available for spool tends to disappear quickly. When value compressed data is spooled, the compression follows it into the spool, thus saving as much space as was saved by compressing the data on disk. When applications consume all the spool space allocated for a system, processing halts.

The following method is used as a guideline for allocating spool space by a large Teradata customer in the retail business:

1 Create a special database to act as a spool space reservoir.

Allocate 2% of the total user space in the system for this database.

2 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.

Consider the following factors to perform finer tuning of database, user, or profile spool allotment.

  • 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 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.

    For example, you might specify the amount of spool space for a database as in the following CREATE DATABASE request.

         CREATE DATABASE nivv AS SPOOL = 2e5 * (HASHAMP() + 1);

    Note: After creating the database, the spool space does not change if you add more AMPs to your system because the size is based on the number of AMPs in the system at the time the database, user, or profile is created, not on the current configuration.

  • Average spool use per user
  • Query workload types
  • Decision support queries generally require more spool than OLTP queries.

  • Number of concurrent users
  • Number of concurrent queries permitted for any one user
  • Spool space is cumulative per user.