TD Space management

1. Capacity planning. MAXPERM = sum of following
   1. Raw Data (RD after compression)
   2. Raw Data Extension (RDE: summary tables, indexes) RD*0.25
   3. FALLBACK
   4. Working space overhead: Spool, temp tables, utility work-tables, disk fragmentation (RD+RDE)*0.40, uncompressed

Filesystem

1. A cylinder can have only one of the space type (Perm/Spool/Temp)
   1. Datablock can have rows from only one table
   2. Cylinder can have rows from multiple tables.
2. Adavanced Format Drives have native sector size of 4K. DB Size automatically adjusted to be even multiples of 4KB
3. On cylinder full condition, following alternatives are considered
   1. Cylinder migrate to adjacent cylinder: max of 10 blocks are moved
   2. Cylinder migrate to a new: Max of 10 blocks are moved
4. During cylinder migration,
   1. they are defragmented in background if they have >= 25% free space
   2. if number of free cylinders fall below 10, mini cylpack to free up a cylinder
   3. mini cylpack never happens on spool space
5. AutoCylPack runs in background when system is idle. Merge up to 8 small blocks (<MergeBlockRatio) within a single cylinder during FTS update scans (no empty INS-SEL)
   1. CREATE TABLE options [DEFAULT|NO] MERGEBLOCKRATIO [= <int> (%)]
6. freespace applies to cylinder:
   1. specified at CREATE TABLE, DBSControl FREESPACEPERCENT, FERRET PACKDISK
   2. honored during: FLOAD, MLOAD (empty), INS-SEL (empty), CREATE SI
   3. ignored during: mini cylpack, all other data load ops except above
7. online reconfig: Allows adding new nodes while system is up
   1. Uses ReconfigRedistOrderTbl and ReconfigDeleteOrderTbl system tables
8. ROT Larger (1MB) block size:
   1. Use 512KB on appliance, 256K on EDW, 1MB for spool
   2. For OLTP applications, do not use large block size
9. Rows in big blocks are aligned on 4 byte v/s 2 byte => increase in tablesize
10. Intermediate spool DB and row size can be up to 1MB; but final spool size is limited to 64K
11. Big blocks require more memory to hold block. As a consequence, when doing multicontext operations such as sliding window merge join or columnar inserts may be slower.
12. DBC tables, JI and HI can’t specify DB size but use the default value

Compression

1. Compressions: MVC, Algorithmic (ALC) and Block (BLC)
   1. ALC on MVC is allowed. ALC will compress only non-MVC-compressed values
   2. No ALC: Volatile. Yes ALC: CHAR, VARCHAR, BYTE
   3. measured in %: (uncompressed - compressed) / uncompressed * 100
   4. measured in ratio: uncompressed / compressed
   5. BLC, unlike MVC and ALC, decompresses in spool and backups.
      • Can be recompressed if spool is written to disk.
2. Base TVS (included in all versions of Teradata, paid or not), has two basic functions:
   1. Pool all cylinders within the clique and allocate to AMPs (instead of entire PDISK)
      1. Due to this, a single disk failure can affect multiple AMPs. Hence, Intelliflex systems require FALLBACK
   2. Count accesses to the cylinders to understand the usage patterns (temperature)
3. TVS
   1. Can operate in Traditional (compatibility) or Intelligent Placement modes
   2. Allocates 20% of fast storage to WAL and spool. These cannot cool off
   3. TVS designated VERY HOT data is kept in TIM by PDE
   4. VH Cache data is brought back into memory after restart
4. Storage can be plugged into IFX in units that are not multiples of the number of AMPs (eg 20 AMPs on a node does not require 20 storage units to be added). TVS carves that up and hands out the cylinders to the AMPs. This makes storage addition much more flexible.
   1. TIM. The usage pattern information is key to implementing TIM. Without it we do not know what to put/keep in TIM memory.
   2. TBBLC. Temperature based compression depends on the usage information as well. In this case we care about the low usage data versus TIM wanting to identify the high usage data.

BLC

1. Two types: Manual or Auto (Temp based)
2. DDL option BLOCKCOMPRESSION {MANUAL|AUTOTEMP|NEVER|DEFAULT} only applies to permanent (except PJ) tables. Non-perm tables use DBSControl settings
   1. If Default or unspecified uses DBSControl DefaultTableMode
3. Altering table-level attr doesn’t cause immediate compression change
4. TD14+: Manual mode independent sub-table compression: primary, fallback, clob
   1. fallback will include both data and index
   2. does not affect spool, mload work, or inex work since they don’t have fallback
5. QB
   1. Manual: BLOCKCOMPRESSION {YES/ALL|NO/NONE|FALLBACK|ONLYCLOBS|WITHOUTCLOBS|FALLBACKANDCLOBS}
   2. AutoTemp: TVSTEMPERATURE_{PRIMARY,PRIMARYCLOBS,FALLBACK,FALLBACKCLOBS}={HOT/WARM/COLD}
   3. QB overrides DBSControl Compress* settings, unless
   4. unless they are set to NEVER
6. ARC must (de)compress, but remembers compression status. Can override with QB
7. Query bands
   1. TVSTemperature force TVS Temperature
8. If all rows a deleted from table, BLC status is forgotten
9. data remains compressed in FSG Cache. Each request must perform decompression
10. can be applied independently to primary, fallback and CLOB data. Spool is off by default
11. reduces physical IO, but not logical IO
12. TBBLC continues until compressed DB reach TempBLCThresh
    1. p = ( t / comp) / ( (1-t) + t / comp) (p: amit of compressed data, t: threshod, comp: compression factor achieved
13. TD13.10: Only primary data including fallback compressed. Indexes are not compressed
14. TD14: Primary SI data is not compressed, but fallback can be
15. ROT: Uses 9x CPU for read and 25x CPU for write (Ballinger TD13.10 BLC)
16. ROT: Use Max DB size (255 sectors) because compression is at DB level
17. ROT: Use WD throttle+CPU limit for queries that use BLC tables
    1. BLC+CPU limit => higher elapsed time => AWT exhaustion if no throttle
    2. Especially needed on 5xxx platforms. Not much a concern on appliances
18. TBBLC (Temperature Based BLC)
    1. QB TVSTemperature and FERRET FORCE override default initial temp
    2. Temp is maintained at Cylinder level
19. ROT: Do not combine ALC and BLC due to higher overhead