16.10 - Sizing Databases, Users, and Profiles - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Underestimating space requirements for databases, users, and profiles leads to performance problems. This is because Teradata Database requires free disk cylinders to enable the growth of permanent, temporary, and spool space as well as to enable the growth of permanent journal tables.

These tables cannot share cylinders when you make your permanent, temporary, and spool disk space assignments. For databases with uneven or skewed data distribution, current space assignments may be much higher than normal to accommodate the skewed use. In such cases, global space accounting can help and space assignments can be normal (for example, an average value) because space is managed at a global level. For more information about global space accounting, see About Global Space Accounting.

The best practice for the initial sizing of the disk space required by a database, user, or profile is to make a good estimate of the space these will require when a database, user, or profile is created, and then to modify those assignments at a later time using MODIFY DATABASE, MODIFY USER, or MODIFY PROFILE requests as appropriate.

You should always consider the following disk space requirements when making disk space assignments for databases, users, and profiles.

Teradata Database requires this type of disk space … For …
spool materializing volatile tables.
temporary materializing global temporary tables.

To materialize global temporary tables, temporary space must have enough empty disk cylinders to contain their rows.

Modifying the Disk Space Currently Assigned to Databases, Users, or Profiles

Modifying the permanent, temporary, and spool space assignments for databases, user, and profiles is a simple operation using MODIFY DATABASE, MODIFY USER, and MODIFY PROFILE requests as appropriate.

You can use the system views listed here to help determine new values to for permanent, temporary, and spool space assignments. For more information about these views, see Data Dictionary.

  • DBC.AllSpaceV[X]

    Use this view to report disk space usage, including spool space, for any account, database, table, or user.

    The following request reports how the space currently used by the department table is distributed on each AMP.

         SELECT DatabaseName, TableName, AMP, CurrentPerm
         FROM DBC.AllSpaceV
         WHERE TableName = ’department’
         ORDER BY 1,2,3;
         DatabaseName   TableName      AMP       CurrentPerm
         ------------   ----------     ----      ------------
         test           department     1-0       1,024
         test           department     1-1         512
         test           department     1-2       1,024
         test           department     1-3         512
         personnel      department     1-0       2,048
         personnel      department     1-1       1,536
         personnel      department     1-2       1,536
         personnel      department     1-3       1,536
         user1          department     1-0       2,048
         user1          department     1-1       1,536
         user1          department     1-2       1,536
         user1          department     1-3       1,536

    The following request reports the values for the MaxPerm and CurrentPerm columns for each table contained by user. Because user only contains one table, employee, the request only returns information about employee and All, where the All “table” represents all of the tables contained by the specified database or user. In this case, All represents all of the tables contained by the user named user.

    The MaxPerm value for All is the amount of permanent space defined for user. Because user contains only one table, the number of bytes on each AMP is the same for both All and employee. All of the reported values represent the size of the employee table.

    Note that employee returns 0 bytes in the MaxPerm column because tables do not have MaxPerm space. Only databases and users have MaxPerm space, represented by All.

         SELECT Vproc, TableName (FORMAT ‘X(20)’), MaxPerm, CurrentPerm
         FROM DBC.AllSpaceV
         WHERE DatabaseName = user
         ORDER BY TableName, Vproc;
    Vproc    TableName        MaxPerm       CurrentPerm
    -----    ---------        ---------     -----------
        0    All              2,621,440          64,000
        1    All              2,621,440          64,000
        2    All              2,621,440         112,640
        3    All              2,621,440         112,640
        …    …                       …               …
        0    employee                 0          41,472
        1    employee                 0          41,472
        2    employee                 0          40,960
        3    employee                 0          40,960
        …    …                       …               …
  • DBC.DiskSpaceV[X]

    Use this view to report disk space usage, including spool space, for any account, database, or user.

    The following request reports the permanent disk space across all AMPs.

         SELECT AMP, DatabaseName, CurrentPerm, MaxPerm
         FROM DBC.DiskSpaceV;
         AMP  DatabaseName     CurrentPerm       MaxPerm
         ---  ------------     -----------      ---------
         .        .                     .          .
         .        .                     .          .
         0-0      stst14                0       125,000
         0-0      ud12                  0       125,000
         1-0      atest             1,536       125,000
         1-0      a1                    0       247,500
         1-0      btest             3,584         5,000
         1-0      b2test           49,664       250,000
         .        .                     .          .
         .        .                     .          .
         1-1      atest             1,536       125,000
         1-1      a1                    0       247,500
         1-1      btest             3,584         5,000
         1-1      b2test           50,688       250,000
         .        .                     .          .
         .        .                     .          .
         1-2      atest             1,536       125,000

    Similarly, you can submit a request like the following to calculate the percentage of space used by a particular database. Note that the request uses a NULLIFZERO specification to avoid a divide by zero exception.

         SELECT DatabaseName, SUM(MaxPerm), SUM(CurrentPerm),
                ((SUM (CurrentPerm))/ NULLIFZERO (SUM(MaxPerm)) * 100)
                (FORMAT ‘zz9.99%’, TITLE ‘Percent // Used’)
         FROM DBC.DiskSpaceV
         GROUP BY 1
         ORDER BY 4 DESC;

    This request reports the following information from DBC.DiskSpaceV.

         DataBaseName     Sum(MaxPerm)     Sum(CurrentPerm)   Percent
                                                               Used
         ------------     --------------   -----------------  -------
         Finance           1,824,999,996      1,796,817,408   98.46%
         DBC              12,000,000,006      8,887,606,400   73.98%
         Spool_Reserve     2,067,640,026        321,806,848   15.56%
         CrashDumps          300,000,000         38,161,408   12.72%
         SystemFE              1,000,002             70,656    7.07%
  • DBC.GlobalDBSpaceV[X][_SZ]
    Use this view to see global space accounting information, including the following data:
    • The space limits for a database or user at the system level
    • The sizes of the total space allocated to AMPs in the system
    • The total peak allocated space sizes across the system
    • The skew limits associated with the different space values

    The following request reports unallocated space remaining in the database:

    SELECT databasename, allocatedperm, maxperm,maxperm-allocatedperm (TITLE 'UnallocatedSpace'), permskew
    FROM DBC.GlobalDBSpaceV where databasename='example2';
    

    This request reports the following information:

        DatabaseName  example2
       AllocatedPerm                       5,148
             MaxPerm                       5,200
    UnallocatedSpace                    52
            PermSkew   10,000
    
    The maximum allocated peak spool for a given user:
    
    SELECT peakallocatedspool, databasename
    FROM DBC.GlobalDBSpaceV ORDER BY 1 DESC;
    
     *** Query completed. 30 rows found. 2 columns returned.
     *** Total elapsed time was 1 second.
    
            PeakAllocatedSpool DatabaseName
    -------------------------- ------------------------------------------------
                71,273,111,876 example
                71,273,111,876 example2
                             0 LockLogShredder
                             0 TDMaps
                             0 tdwm
                             0 SysAdmin
                             0 Default
                             0 All
                             0 PUBLIC
                             0 SYSSPATIAL
                             0 SYSUIF
                             0 TDPUSER
                             0 TD_SYSXML
                             0 TD_SYSGPL
                             0 SYSLIB
                             0 External_AP
                             0 Crashdumps
                             0 SYSJDBC
                             0 TD_SERVER_DB
                             0 TDStats
                             0 SYSUDTLIB
                             0 SYSBAR
                             0 SystemFe
                             0 SQLJ
                             0 Sys_Calendar
                             0 TD_SYSFNLIB
                             0 TDQCD
                             0 EXTUSER
                             0 dbcmngr
                             0 DBC
  • DBC.TableSizeV[X]

    Use this view to report disk space usage excluding spool space, for any account or table.

    The following request reports the total disk space currently used by the employee table with its peak space usage.

         SELECT SUM(PeakPerm), SUM(CurrentPerm)
         FROM DBC.TableSizeV
         WHERE TableName = ’employee’;
         Sum(PeakPerm)   Sum(CurrentPerm)
         -------------   ----------------
         260,608         260,608

    In this case, the 2 sums match, indicating that the disk space currently used by the employee table is also its peak space usage.

    The following request reports poorly distributed tables by returning the CurrentPerm values for table_2 and table_2_nusi, the only tables contained by user across all AMPs.

         SELECT Vproc, TableName (FORMAT ‘X(20)), CurrentPerm, PeakPerm
         FROM DBC.TableSizeV
         WHERE DatabaseName = user
         ORDER BY TableName, Vproc;
         Vproc     TableName         CurrentPerm     PeakPerm
         -----     ---------         -----------     --------
             0     table_2                41,472       53,760
             1     table_2                41,472       53,760
             2     table_2                40,960       52,736
             3     table_2                40,960       52,736
             4     table_2                40,960       52,760
             5     table_2                40,960       52,760
             6     table_2                40,960       52,272
             7     table_2                40,960       52,272
             0     table_2_nupi           22,528       22,528
             1     table_2_nupi           22,528       22,528
             2     table_2_nupi           71,680       71,680
             3     table_2_nupi           71,680       71,680
             4     table_2_nupi            9,216        9,216
             5     table_2_nupi            9,216        9,216
             6     table_2_nupi           59,392       59,392
             7     table_2_nupi           59,392       59,392

    The report indicates that table_2 is evenly distributed across the AMPs, but that table_2_nusi is not, with its current permanent space clumped into spaces of 9,216 bytes, 22,528 bytes, 59,392 bytes, and 71,680 bytes across 2 AMPs each.

Different Disk Space Views Return Different Results

It is important to understand that because the DBC.AllSpaceV, DBC.DiskSpaceV, and DBC.TableSizeV system views report different information, using superficially identical requests to return information using those views often returns conflicting results. For example, while DBC.AllSpaceV and DBC.DiskSpaceV include information about spool space in their reports, DBC.TableSizeV does not.

Similarly, selecting SUM(CurrentPerm) from each of these views returns different results. For example, requesting SUM(CurrentPerm) from DBC.AllSpaceV returns a sum of All “tables,” representing the database or user total plus the sum of each table in the database or user. Submitting the same request using DBC.TableSizeV returns sums for all tables except for All, and submitting the query using DBC.DiskSpaceV returns sums only for All.

The following requests illustrate the different results the same request returns for the same information, but using different system views to access the data.

  • DBC.AllSpaceV
         SELECT MAX(CurrentPerm), SUM(CurrentPerm)
         FROM DBC.AllSpaceV
         WHERE DatabaseName = user;
         Maximum(CurrentPerm)      Sum(CurrentPerm)
         --------------------      ----------------
                      112,640             1,308,672

    The reported values represent the disk space for all of the tables contained by user plus the disk space for the All table.

  • DBC.DiskSpaceV
         SELECT MAX(CurrentPerm), SUM(CurrentPerm)
         FROM DBC.DiskSpaceV
         WHERE DatabaseName = user;
         Maximum(CurrentPerm)      Sum(CurrentPerm)
         --------------------      ----------------
                      112,640               654,336

    The reported values represent the disk space for the All table.

  • DBC.TableSizeV
         SELECT MAX(CurrentPerm), SUM(CurrentPerm)
         FROM DBC.TableSizeV
         WHERE DatabaseName = user;
         Maximum(CurrentPerm)      Sum(CurrentPerm)
         --------------------      ----------------
                       71,680               654,336

    The reported values represent the disk space for all of the tables contained by user, but do not include the All table.

Use the following table to determine which system views are appropriate for requesting disk space information for different database objects.

To report disk space information at this level … You should use this system view …
table DBC.TableSizeV
database, user, or profile DBC.DiskSpaceV
database or user with global space accounting DBC.GlobalDBSpaceV
tables plus database, user, or profile DBC.AllSpaceV