Global Space Accounting | Teradata Vantage - About Global Space Accounting - Advanced SQL Engine - Teradata Database

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
qby1588121512748.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantage™
DBAs can adopt one of two strategies for managing permanent, spool, and temporary space:
  • AMP level only. The per-AMP space quota is the maximum permissible AMP-level space. This is the default.
  • Global level, which maintains both an overall system limit and AMP limits. This strategy can provide extra space to AMPs when needed by temporarily reducing space for AMPs that do not need the space now.
When DBAs manage space at the AMP level, transactions or long-running load jobs abort when space use exceeds hard limits set for the database or user. If a DBA manages space at the global level, two allowances can be made to increase space to AMPs when needed:
Space Allowance Creation Method Description
Skew factor The SKEW option in a CREATE USER/DATABASE or MODIFY USER/DATABASE request.
If the SKEW option is not specified or is specified as DEFAULT, the skew factor is controlled by three DBS Control utility fields:
  • DefaultPermSkewLimitPercent: Controls the skew factor on permanent space
  • DefaultSpoolSkewLimitPercent: Controls the skew factor on spool space
  • DefaultTempSkewLimitPercent: Controls the skew factor on temporary space

Gives extra space to one or more AMPs when needed, up to the value of the SKEW option. The skew option can be defined as a percentage or a constant expression. You can apply this option to PERM, SPOOL, and TEMP space.

A skew limit value of 0 means that the AMP space quota is the space limit, and Vantage does not provide more space to AMPs, even when other AMPs have space available. A non-zero soft limit percent permits this quota to increase by the soft limit percent. Users receive an error if actual space use exceeds the soft limit. The default value for the default skew limit percent fields is 0.

The DBC.GlobalDBSpace table records the cumulative allocations of the AMPs and maintains database and user global-level space limits.

Global soft limit Set a non-zero value for the GlobalSpaceSoftLimitPercent field in the DBS Control utility

The percentage by which a database or user is allowed to exceed the maximum space limit. This setting applies to PERM, SPOOL, or TEMP space. The default value is 0.

The system sends alerts when processing exceeds soft limits, so the DBA can take corrective action. Processes that cannot be aborted (such as transaction recovery) may be in progress when the soft limit is exceeded or when physical storage limits are almost reached. In this case, the system continues processing but reports alerts with higher severity levels. The DBA is expected to increase affected space as soon as possible in this situation.

Teradata recommends that you change the values of the default skew limit percent fields and the GlobalSpaceSoftLimitPercent field only under the direction of Teradata Support Center personnel.

Create a User with a Skew Limit

The following example creates a user with a 10 percent skew limit for permanent space and a 20 percent skew limit for spool space:

CREATE USER Caspian AS PERM = 1e9 SKEW = 10 PERCENT, SPOOL = 2e9 SKEW = 20 PERCENT;

In the preceding example, if the system has 4 AMPs, each AMP has a limit of 250 MB of permanent space. With the permanent skew limit of 10 percent, any AMP can exceed the permanent space limit by 25 MB. An AMP may use up to 275 (250 + 25) MB of permanent space, as long as the total permanent space used by all AMPs does not go beyond the 1 GB global limit.

Similarly, each AMP has a limit of 500 MB of spool space. With the spool skew limit of 20 percent, any AMP can exceed the spool space limit by 100 MB. An AMP may use up to 600 (500 + 100) MB of spool space, as long as the total spool space used across all AMPs does not exceed the 2 GB global limit.

Considerations for Global Space Accounting

Consider the following factors about global space accounting:
  • Teradata recommends global space accounting for a database or user when actual space use is expected to be non-uniform, for example, when large, unknown data sets that may be skewed are often loaded into the underlying tables or when the database or user has stored procedures or UDFs that record very few rows with object-specific data.
  • AMP-level space accounting does not have the overhead of dynamic, need-based space allocation and is recommended when the database is uniformly distributed across all AMPs in the system or when the global limit is high enough to allow for variability in data distribution due to skew .
  • An unlimited skew limit for spool space may not be a good option, since design issues or a lack of statistics may cause a request to use excessive spool on some AMPs. A lower spool limit causes requests using excessive spool space to abort.
  • To reduce the chance of space shortages causing aborted transactions, consider setting a higher value for the global soft limit if the system has long-running transactions that consume a lot of space.
  • Skew limits cannot solve space problems, especially when actual use is close to the limits. If there is not enough space, it is best to increase the space limits instead of allowing more skew.

Interaction between Skew Factor and a Profile

  • Profile values supersede user values. For example, if a spool limit is defined in both the CREATE USER request and the profile a user is a member of, Vantage uses the profile value.
  • If a user is a member of a profile, the skew factor is controlled by the user definition.

For example, if a CREATE USER request gives user Joe 10 MB spool with 20% skew, but the profile Joe belongs to provides 5 MB spool, then Vantage applies the 5 MB spool limit with 20% skew. The same is true for temporary space specifications.

Interaction between the Skew Factor and Soft Limits

Skew factor and soft limits can be cumulative. For example, consider the following numbers:
  • Global limit: 800 MB
  • Soft limit: 10 percent
  • Per-AMP limit on a 4-AMP system: 200 MB
  • Skew factor: 25 percent
With these numbers, the global limit can exceed the 800 MB limit by 10 percent (to 880 MB). In addition, an AMP can exceed the 200 MB limit by 25% (to 250 MB) if the total across all AMPs does not exceed 880 MB. Each of the AMPs can also receive an additional 20 MB because of the global soft limit. The cumulative effects of the skew factor and global soft limit can increase the maximum possible space allotment to an AMP to 250 + 20 = 270 MB, as long as the total space used across all AMPs does not exceed 880. The user will receive an error message if either of the following limits are exceeded:
  • The global limit of 880
  • The per-AMP limit of 270

Related Topics

For more information on... See...
the SKEW option for DDL in CREATE DATABASE and CREATE USER or MODIFY DATABASE and MODIFY USER Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
DBS Control fields Teradata Vantage™ - Database Utilities , B035-1102 .