Skip to content

General

  • Objects that require Permspace: TB, IX, SP, UDFs
  • HASHAMP/HASHBAKAMP(HASHBUCKET(HASHROW(value)))
  • TPA Node memory: AMP memory: Master Index, PE memory: RTS cache, D/D cache, PDE memory: hash map, and config maps
  • WAL. Write Ahead Log
    • Can batch changes from multiple transactions for improved throughput
    • WAL Depot: Fixed # of cylinders to each AMP. Two areas: Large slots and small slots. Large slots are used by aging routines to write multiple blocks per IO. Small slots are used by foreground tasks
    • WAL log: Dynamic # of cylinders, used for UNDO/REDO images
    • WAL data is not stored on data (perm/spool/temp/PJ) cylinders and not part of DBC perm space TDV2R6.2+
    • Although disk space allocated for TJ and WAL records is charged against dbc.TrasientJournal, no actual TJ or WAL records are found in any subtables
  • DBC Users
    • SysAdmin contains restart table for fastload
    • TDPUser is used for two-phase commit
  • After canceling recovery, only DML allowed is DELETE ALL.
  • TotalIOCount from DBQL doesn’t include parsing/Bynet/swap IOs or IO from aborted queries. AMPUsage provides more accurate count, but at a higher level.
  • FirstStepTime from DBQL is after table locks are acquired, but if AMP level lock (rowhash) wait occurs, that wait time will be included in FirstStepTime
  • Persistent HSN: HSN remains active when failed node is repaired. Avoids costly MI rebuilds
  • Enabling FALLBACK may increase CPU 20% to 40%
  • Rollback Performance: Tables with, Complex JI > NUSI > USI > no indexes (~ 2x)
  • Rollback of tables with no NUSIs are optimized to use block-at-a-time
  • ProcID: Unique Processor ID of the dispatcher
  • DDLP: DDL Processor utility to convert and sends ANSI Schema to TD
  • TD14+: Active Fallback. TD uses fallback data for query and to repair the primary data block in case primary data block has an error. (mainly for SSD/without RAID capability)
    • available for data and USI. Not available for NUSI
  • OUC is recorded only for DML. Not recorded for EXPLAIN or dictionary tables
    • If archiving DBC tables and arc is blocked by OUC, system will disable OUC
    • ROT: ObjectUseCountCollectRate < 10 min. => severe performance impact
  • Any level of swapping is bad for performance. Try to bring it to near zero (Todd Walters)
  • 2800 load rate: 35 MB/Sec/Node
  • Saturated I/O bandwidth on Intelliflex: 350 MB/sec/core
  • Systems with same # of AMPs will have same hash map
  • set session subscriber on to override identity generated always
  • Host Group ID HGID define collection of Gateway GTW (non-movable) and PE (movable, except FICON PE) VPROCs
    • GTW VPROCs provide socket interfact to Teradata and are tied to network interface
    • By default all TCP/IP connections are in HGID 1
    • Multiple HGIDs can be defined to, say, have different encryption option
    • A node can have multiple GTW provided they belong to different HGID
  • Teradata Intelligent Memory (TIM) uses Most Frequently Used algorithm whereas FSG Cache uses MRU
  • REDRIVE let transactions continue through DBS crash/restart
  • Java Hybrid Server is a protected mode server running on each that executes Java UDFs using tdatuser OS ID
    • cutconfig utility allows changing various settings associated with JUDF

Dual System

  • TD13.10 Unity Directory: TD 13.10 supported two modes
    • Read-override (Query directory compatibility mode) sessions are read-only
    • Read-write: Writes sent to all systems
  • Unity components:
    • Regions: Generally same as geographical location
    • Sequencer: Maintains locks. Max 2, only one active. ROT 1/Region - consists of recovery log, repository (DD+Config)
    • Dispatcher: dispatches requests to Teradata systems. 1/system in all regions
    • End Point: Where sessions connect to.
  • TD14+ provides routing Options and removes TD13.10 modes
    • Read options: Named, Preferred, Automatic
    • Write options: default, Create Preferred, Create Balanced - Writes sent to all systems that the object exists on
  • Unity Table states:
    • Active: tables available
    • Interrupted: Write failed on the affected system
    • Unrecoverable: Number of rows affected differed. (First system assumed to be correct)
    • Read-only: no writes (action of freeze command)
    • StandBy: No reads (action of demote)
    • Restore: action of Recover
    • Out-of-Service: Action of of Halt. Connections closed off cleanly.
    • Disconnected (System): System level state
  • Unit 14 uses table level locks for objects accessed by a stored procedure
  • Routing rules based on UserName, AcctStr, Role/Profile
  • If Unity+TMSM integration used, TMSM server must hold Unity repository as well.
  • Unity Loader lands data one per region. Each region loads data into Teradata systems
  • End-point sets date, time or random for deterministic results.
  • Identity tables are blocked
  • ROT: Unity load balances to least loaded system (not fastest system nor geo-closest system)

Replication

  • Replication supports both active-active and active-passive modes
  • A set of tables to be replicated is called Replication Group
  • A replication ruleset provides a list of rules for replicating objects and associated with repl group
  • Need REPLCONTROL privilege to set up replication group

Cloud (AWS)

  • Unfold to increase CPU+memory by redistributing number of AMPs and EBS Volumes to more nodes.
    • Total database capacity and AMP count do not change
    • Cannot fold if the volume count increase more than 20
    • Must have min 1 storage subpool/node. 5 AMPs/subpool. For 20 AMPs/node, max unfold is 4 times.
    • Commands: tdc-unfold 4x tdc-fold 4x, check if system is folded tdc-fold -d
  • Increase EBS volume sizes to up storage without CPU+memory increase
    • System cannot be in an unfolded state when increasing EBS volume size
    • must have 20 pdisk EBS volumes per node
    • must shutdown and run tdc-expand-storage --size <value> (value = additional TB to be added)

Tricks+Tips

  • MaxViewFoldTreeSize 0 => fold all views, 1=> spool all views, x => spool views > x
    • Try this only after MaxParseSegSize is set high and query still fails
    • To find the actual limit,
      • diagnostic "maxvftreesize 0" on for session;
      • run the query
      • check dbsio output (cnsterm 5)
      • mod int 137=<max value from dbsio>
  • Use ARC DBSIZEFILE parameter to set new database sizes while restoring on smaller systems