Teradata Database Administration – Teradata Internals

Tom Coffing, Todd Wilson

• Publisher: Coffing Publishing
• Year: 2013
• eBook ISBN: 9781940540184
• Topic: Computer Science
• Subtopic: Data Warehousing
• Index: Computer Science

This DBA book takes the reader deep inside the guts of Teradata, down to the row level, block level, and cylinder level. Readers have an opportunity to understand Teradata at a level that 1% of the world understands Teradata. This book will bring you into the top 1% of knowledge of Teradata.

Chapter 1 - The Cold, Hard Teradata Facts

“Get your facts first, and then you can distort them as you please.”

- Mark Twain

What is Parallel Processing?

“After enlightenment, the laundry”

- Zen Proverb

“After parallel processing the laundry, enlightenment!”

-Teradata Zen Proverb

Two guys were having fun on a Saturday night when one said, “I've got to go and do my laundry.” The other said, “What?!” The man explained that if he went to the laundry mat the next morning, he would be lucky to get one machine and be there all day. But, if he went on Saturday night, he could get all the machines. Then, he could do all his wash and dry in two hours. Now that's parallel processing mixed in with a little dry humor!

The Basics of a Single Computer

Data on disk does absolutely nothing. When data is requested, the computer moves the data one block at a time from disk into memory. Once the data is in memory it is processed by the CPU at lightning speed. All computers work this way. The “Achilles Heel” of every computer is the slow process of moving data from disk to memory. That is all you need to know to be a computer expert!

Teradata Parallel Processes Data

Teradata has been the pioneer in parallel processing since 1988, when Wells Fargo bought the first Teradata system. In the picture above you see that we have 16 orders, with four orders placed on each disk. It appears to be four separate computers, but this is one system. Teradata systems work just like a basic computer as they still need to move data from disk into memory, but Teradata divides and conquers.

Parallel Architecture

The rows of a Teradata table are spread across the AMPs, so each AMP can then process an equal amount of the rows when a USER queries the table.

The Teradata Architecture

The Parsing Engine (PE) takes the User's SQL and builds a Plan for each AMP to follow to retrieve the data. Parallel Processing is all about each AMP doing an equal amount of the work. If they start at the same time and end the same time, they are performing true Parallel Processing. All communication is done over the BYNET.

All Teradata Tables are spread across ALL AMPS

Each table dreams of spreading their rows equally across the AMPs. Above, are three tables with each table holding 9 rows (3-rows per AMP).

Teradata Systems can Add AMPs for Linear Scalability

If you double your AMPs, the system is twice as fast! System number one has only 4-AMPs, but system two has 8-AMPs and is twice as fast. When a customer buys more hardware, they are adding AMPs to the system. Once the hardware is configured, the AMPs will redistribute the data to include the new AMPs.

Understand that Teradata can scale to incredible size

“If you do what you've always done, you'll get what you've always got.”

-Anonymous

The largest systems in the world have used Teradata for market dominance for the past 20 years. Its Massively Parallel Processing (MPP) technology analyzes on such a large scale that companies can run queries they have never been able to run before. Recognize that you now have something very powerful and that has the ability to analyze every aspect of your business. So do what you've never done, and get something that you've never got.

AMPs and Parsing Engines (PEs) live inside SMP Nodes

AMPs and PEs are called Virtual Processors because each is a process that lives inside a node's memory. Think of a node as a very powerful personal computer. SMP stands for symmetric multi-processing which means each CPU processor performs equally, and all CPUs share a pool of memory and operate under one operating system. Each node is designed to operate at maximum performance.

Each Node is attached via a Network to a Disk Farm

A Teradata AMP will be assigned a Virtual disk to store its tables and the rows assigned to it. Only the AMP assigned to the virtual disk can read or write to that disk. A node holds many AMPs. In the early days, each node held around 8-10 AMPs, but with more power in a node due to CPU advances, 64-bit architecture, and a ton more memory, many nodes today will hold up to 40-50 AMPs. Each AMP is still attached to its virtual disk. Th...

Table of contents

• Cover Page
• Title Page
• Copyright Page
• About Tom Coffing
• About Todd Wilson
• Contents
• Chapter 1 - The Cold, Hard Teradata Facts
• Chapter 2 - How Teradata Tracks Objects
• Chapter 3 – AMP Worker Tasks
• Chapter 4 - Deep Dive Overhead for Each Row
• Chapter 5 - Compression
• Chapter 6 - Data Stored in the Row
• Chapter 7 - How Data Rows are Stored in Blocks
• Chapter 8 - Disk Cylinders and the Master Index
• Chapter 9 - Teradata Virtual Storage (TVS)
• Chapter 10 - Teradata Writes and Blocks
• Chapter 11 – Access Logging
• Chapter 12 – DBQL Query Logging
• Chapter 13 – ResUsage
• Chapter 14 – Top SQL Commands Cheat Sheet
• Back Cover