- 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.
| 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:
|
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 Teradata Database 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. |
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
- 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, Teradata Database 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 Teradata Database 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
- Global limit: 800 MB
- Soft limit: 10 percent
- Per-AMP limit on a 4-AMP system: 200 MB
- Skew factor: 25 percent
- 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 . |