Database-Level Capacity Planning Considerations

Capacity planning is integral to the optimum performance of diversely accessed data. While numerous critical considerations for capacity planning are developed in this chapter, the principal emphasis is on multi-value compression, particularly for warm and hot data.

Warm and hot data typically constitute what is often called the operational data store.

The BlockCompression reserved query band controls the compression of data loaded into a primary data table, join index, or hash index at the data block level using the following data loading methods:

The BlockCompression reserved query band indicates whether data being loaded into an empty table should be compressed at the data block level or not.

The TVSTemperature reserved query band controls the desired TVS-managed temperature level for the cylinders that store the newly loaded table data, which can be specified to be Cold, Warm, or Hot

The BlockCompression and TVSTemperature reserved query bands enable you to control various aspects of loading new data into tables.

Compressible. Includes both value-compressed and algorithmically-compressed data and can be stored in any order

Multi-value compression

Algorithmic compression

Reserved Query Bands for Managing the Block Level Compression and Storage Temperature of Newly Loaded Data

When describing the compression of hash and join indexes, compression generally refers to row compression.

while algorithmic compression can be either lossless or lossy, depending on the algorithm used.

Compression has two principal applications: • Reducing storage costs • Enhancing system performance

The multi-value compression of column values provides the following capacity and performance benefits. • Enhanced storage capacity • Improved response time for table scans • Reduced disk I/O traffic • Moderate to little CPU savings

Block level compression applies only to primary data tables, join indexes, and hash indexes.

Block level compression is controlled by the Ferret utility, by several DBS Control flags (

This chapter planning the storage capacity needs of your data warehouse.

• You cannot compress values in the following types of columns: • Component of the primary index • Identity columns • Volatile table columns • Derived table columns • Referenced primary key columns • Referencing foreign key columns for Standard referential integrity relationships. You can compress values in referencing foreign key columns for Batch and Referential Constraint referential integrity relationships. • Columns defined with a UDT, Period, Geospatial, BLOB, or CLOB data type. • You can compress columns that are a component of a secondary index with the foll

Guidelines for Allocating Spool Space Unless you reserve a pool of spool space, available space tends to disappear quickly.42 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. 3 End of procedure.

Consider the following factors to perform finer tuning of user 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 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 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. • 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.

examines capacity planning for system and table space. The majority of the material focuses on system space planning, including planning for disk space. Database sizing issues such as allocating permanent space and estimating database size requirements are also described

Database Size Considerations

The sizes of the Teradata systems managing relational databases range up to 2,048 CPUs and 2,048 GB of memory supporting 128 TB or larger databases. The BYNET interconnect supports up to 512 nodes. Database sizing considerations must take the following issues into account: • Contents of system disks • Contents of data disks • Data disk space allocation • Determination of usable data space

“System-Level Capacity Planning Considerations,” describes the more file systemand hardware-oriented considerations undertaken for capacity planning.

queries access only a few AMPs, more of them can run concurrently. Number of Nodes Total Work Accomplished 1101A078 , 6 )@ 6 )@ , * * > ( Full table scan or indexed access to all AMPs Read one row 1101A079

IF you cannot provide the value for the partitioning key in the WHERE clause, the PPI is a NUSI, and its values are … THEN consider creating this type of index on the primary index columns of the PPI … unique USI. not unique global join index.

Recommended Monitoring Activities A quick list of monitoring activities that offer a foundation for workload management follows: • Use Teradata Viewpoint to monitor and manage the workload. • Collect user resource usage detail data. The DBC.Acctg table is the underlying table for the AMPUsage view, and captures data about user usage of CPU and I/O for each AMP. Heavy resource consumers over time, skewed work, and application usage trends can be identified. • Collect ResUsage data. The ResUsage tables report on the aggregate effect of all user requests on various system components over time, and can identify bottlenecks and capacity issues. See Resource Usage Macros and Tables for details on all ResUsage data and macros. • Use the Locking Logger utility. Locking logger is essential for identifying locking conflicts. See Utilities: Volume 2 (L-Z) for information about the Locking Logger utility. • Use Database query logging. The Database Query Log records details on queries run, including arrival rates, response times, objects accessed, and SQL statements performed. See Database Administration and SQL Data Definition Language for more information about Query Logging. • Use the Priority Scheduler utility. Priority Scheduler monitor output information shows comparative CPU usage across Priority Scheduler groups. This monitor output should be collected daily, minimally at a 5 or 10-minute interval, then summarized or charted. See Utilities: Volume 2 (L-Z) for more information about the Priority Scheduler utility. • Enable canary queries. You should use canary queri

System-Level Capacity Planning Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1029 Database Size Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1030 Contents of System Disks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1031 Contents of Data Disks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1032 Data Disk Space Allocation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1034 Permanent Space Allocations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1035 Estimating Database Size Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1037 Determining Available User Table Data Space. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1040 Designing for Backups

In general, the following guidelines apply. • Reserve 25% to 35% of total space for spool space and spool growth buffer. When you create user SysAdmin, you can leave the SPOOL parameter unspecified, so it defaults to the maximum allocation of the owning user, user DBC. • Allow an extra 5% of PERM space in user DBC. • Each time a new user or database is created, you can specify the maximum amount of spool space that a query submitted by that user can consume.