All Rows on One AMP – By Design

9
1175

AMPGuest Post Probably the first thing that anyone learns about designing tables for Teradata is to spread the rows evenly across all AMPs in the system. So if you have 100000 rows on a 100-AMP system the aim is to have approximately 1000 rows on each AMP.

For a lot of tables in a Teradata system this is a design guideline that I completely agree with. However there are a group of tables that I think can usefully use a different design approach, namely that we ensure that all rows are stored on one AMP. Yes I know, this is completely counter-intuitive in a Teradata environment but please read on.

The group of tables where I think this design approach applies are the small, reference tables (sometimes called ‘lookup’ tables) that have very few rows – often only a few tens or hundreds rows.

What is the new design?

For tables in this group, add an additional column (called PICOL in this article) which is used as the non-unique Primary Index (NUPI) for the table. On every row in the table, this column contains the same data value, forcing all rows on to the same AMP and typically into the same data block.

How does this help?

Firstly, this design approach is NOT going to make SQL requests run noticeably faster.

However it will make them run more efficiently because they use fewer CPU and IO resources to achieve the same result.

What is going to change?

With this design change implemented, any SQL that references this table needs to include an additional selection criterion against column PICOL to select the specific data value. This would usually be added into the base view which references the table, so there are minimal changes to application code; the ETL code that loads or updates this table will need to ensure that the required value is inserted into column PICOL.

Having made those changes the plan will use a single-AMP read of the table, instead of an all-AMP read, thereby saving AWTs, CPU and IO. The query provides the same functional result but uses fewer resources, hence making it more efficient.

The following two (partial) plans show the differences.

This first plan is using a conventional table design (table ‘dept’) whereby the rows are distributed as evenly as possible across the AMPs.

1) First, we lock a distinct WORKDB.”pseudo table” for read on a
RowHash to prevent global deadlock for WORKDB.dept.

2) Next, we lock a distinct WORKDB.”pseudo table” for read on a
RowHash to prevent global deadlock for WORKDB.emp.

3) We lock WORKDB.dept for read, and we lock WORKDB.emp for read.

4) We do an all-AMPs RETRIEVE step from WORKDB.dept by way of an
all-rows scan with no residual conditions into Spool 2 (all_amps),
which is duplicated on all AMPs.  The size of Spool 2 is estimated
with high confidence to be 70 rows (7,280 bytes).  The estimated
time for this step is 0.04 seconds.

 

This second plan is using the alternative table design (still on table ‘dept’) whereby all rows are on a single AMP.

1) First, we lock a distinct WORKDB.”pseudo table” for read on a
RowHash to prevent global deadlock for WORKDB.emp.

2) Next, we lock WORKDB.emp for read.

3) We do a single-AMP RETRIEVE step from WORKDB.dept by way of the
primary index “WORKDB.dept.PICOL = 1” with no residual conditions
into Spool 2 (all_amps), which is duplicated on all AMPs.  The
size of Spool 2 is estimated with high confidence to be 70 rows (
7,280 bytes).  The estimated time for this step is 0.03 seconds.

 

A few differences to note between these two plans:

  • The second plan accesses the table “by way of the primary index”. This is as a result of the selection criteria added to the SQL request. This is one of the changes noted above.
  • The second plan does not require a table level, all-AMP lock on the table with the new design (it is now locked using a row hash lock).
  • Although not shown, the step that reads the table and duplicates it to all AMPs will use fewer AWTs. A duplication step requires two AWTs on each AMP (a total of 2 * #AMPs on the system), one to read the data rows and send them to all AMPs and the second one to receive the distributed rows. In the second plan, because only one AMP is used to read the rows the number of AWTs used is 1 + #AMPs on the system.
    On a 100-AMP system the number of AWTs used for this step will be reduced from 200 to 101.
  • (Again not shown) is that where the ‘lookup’ table fits into a single data block, the original plan would typically require a single I/O on each AMP, but the new plan will be a single I/O on one AMP.

When Can I (And When Can I Not) Use This Design?

As with a lot of performance design tips you have to use this in specific places. Here are some guidelines to help you:

  • Small tables that you know will not grow.
  • This design can cater for a ‘few thousand’ rows (127.5KB block can handle @5200 250-byte rows, row length is after compression).
  • Add the appropriate WHERE clause into the base view that references the table.
  • The aim is to reduce the number of AWTs used for processing; this will not change performance although you may find that a frequently referenced table will simply sit in memory on the Node.
  • If you decide to use this approach for a number of tables, do not use the same data value for column PICOL on all tables. Doing so would potentially overload one AMP. Use different values (perhaps the table name?) to ensure that the table rows are assigned to different AMPs.
Our Reader Score
[Total: 12    Average: 3.7/5]
All Rows on One AMP – By Design written by David Wellman on July 1, 2015 average rating 3.7/5 - 12 user ratings

9 COMMENTS

  1. Little things make a big difference. I think using this technique wisely (as explained) is definitely a benefit. Good approach Dave!

  2. Hi Naveed, You’re welcome. If you get to try this please post any comments or suggestions back here.
    regards, Dave

LEAVE A REPLY

Please enter your comment!
Please enter your name here