PRPD - TD 14 New spool skew tuning technique

PARTIAL REDISTRIBUTION AND PARTIAL DUPLICATION(PRPD)

http://www.dwhpro.com/teradata-partial-redistribution/

Dealing with data skew can be considered a chief activity in Teradata performance optimization. Although, skew on table level usually can be avoided by choosing a proper Primary Index, skew arising during query execution is often a major issue (so-called spool skew).

Spool skew is often emerging when join tables (or spools). On a typical Teradata System,  the join process is executed in parallel by all available AMPs at the same time.

Let’s consider the following join example:

B … Big table with 100 Million rows

S … Small table with 10.000 rows

We assume, that both tables are joined on a column which is neither the primary index of B nor the primary index of S. In such a case, the optimizer may choose to rehash and redistribute both tables. This is a good approach if the join column values if both tables are evenly distributed across all AMPs.

But we assume, that the big table B has exactly the same value in the join column in about 99% of the rows  i.e. 99 Million of rows out of 100 Million rows carry the same value in the join column.

As of now, we run into a big performance problem, caused by skew.

By rehashing the rows, 99 Million rows are distributed to only one AMP (as they carry the same hash value).

The receiving AMP will be completely overloaded (Hot AMP situation) and may run out of spool space.

Until Teradata Release 13, it was exclusively your task as performance specialist to solve suchlike dynamic skewing problems, by forcing the optimizer into a different execution plan.
Sometimes this could be solved by adding statistics, some of the time a complete rewrite of the SQL statement was the only opportunity.

Starting with Teradata 14,  a sophisticated method for resolving such data skew issues was implemented.

Each AMP receives information about skewed values (in our example this is the big table B).

Biased rows of the big table B, stay locally on their AMPs, only rows not causing skew, are redistributed by rehashing over the the join column(s).

Similarly, not biased rows from the small table S, are rehashed on their join column(s) but rows which would cause skew after redistribution, are duplicated to all AMPs.

As soon as the partial redistribution and partial duplication of the tables has been completed (PRPD), each AMP generates a first result set by joining the contents of the redistributed spools .

In a next step, each AMP generates a second result set by joining the contents of the duplicated spool of table S and the local spool of table B.

Starting with Teradata 14, PRPD relives the performance specialist from some skew related problems, but the Teradata optimizer needs to know about biased values, hence it is important to keep the statistics fresh.

No more spool space scenarios

1. System ran out of spool space

This is the most rare situation, forget about.
There are too few free space on the system, but this situation used to be avoided by defining a "SpoolReserve" database, where no objects are created, this way that area is always available for spool.
If many "big spool limit" users run high spool queries parallel, then this rare situation can yet occured.

2. Multiple session of the user are active together
This is a quite rare situation also. Check the active users from dbc.sessioninfo.

3. Volatile tables
All existing volatile tables reside in your available spool space, reducing the available.
If you create many, and even with skewed distribution, you can stuff your spool up.
Choose "primary index" carefully, when defining volatile tables also.

4. Improper execution plan
These are the >90% of cases that cause the "No more spool space" errors. Let' see how:

(a)"Duplication to all AMPs" of a non-small set of records
    The root cause is typically missing or stale statistics. Either system thinks that much less records will be duplicated than the real (sometimes billions of records get in this kind of spools), or knows this exactly, but on the other branch of the query there are more low quality estimations, and this execution seems to be cheaper.
(b) Redistribution of records by a hash that causes skewed distribution
    Check the corresponding blog post: Accelerate skewed joins
(c) Retrieve huge amount of records into spool (locally or redistributed onto the AMPs)
    Specific query structures imply this execution, like: join to a view that "union all"-s big tables.

Increasing the spool limit will not solve the problems in the most cases.

Too big task
Sometimes a given SQL query requires big spool area to be performed, even with the best execution plan.
This is the only case when raising spool limit is the solution.

Spool Space in TD

Spool space is allocated to a logon with a spool space limit parameter. This spool space parameter assigns a spool space limit to each amp. Each amp gets an equal share of the spool space limit. 

Therefore,

                                                      Logon spool space limit
Amp spoolspace limit =   ---------------------------------------------------

                                                            Number of amps

The message “Failure 2646 No more spool space in username” occurs whenever any amp exceeds its individual spoolspace limit.

For example, if the logon is assigned 150MB of spool space, and there are 50 amps, then each amp will get a 3MB spool space limit. And, the 2646 message will occur if any amp exceeds 3MB of spool space.

To check the amount of spool space that is being used:-

Here's one way of doing it:

1. zero the peak spoolspace for the user/logon (see sql below)
2. run the sql
3. view the peak spoolspace for the user/logon (see sql below)

SQL to zero peak spoolspace:

lock table dbc.dbase for access
update dbc.databasespace
set peakspoolspace = 0
where tableid = '000000000000'xband
databaseid = (sel databaseid from dbc.dbase where databasenamei = 'USERNAME')

SQL to view peak spoolspace (all amps):

lock table dbc.diskspace for access
sel databasename (format 'X(10)'),vproc,peakspool (format 'z,zzz,zzz,zz9')(char(13)),
maxspool (format 'z,zzz,zzz,zz9')(char(13)),currentspool (format 'z,zzz,zzz,zz9')(char(13))
from dbc.diskspace
where peakspool > 0 and databasename = 'USERNAME'
order by databasename,

vprocSQL to view peak spoolspace (totals):

lock table dbc.diskspace for access
sel max(peakspool) (format 'z,zzz,zzz,zz9')(char(13)),
min(peakspool) (format 'z,zzz,zzz,zz9')(char(13)),
((min(peakspool)*100)/nullifzero(max(peakspool)))(format 'zz9%')(char(4)),
sum(peakspool) (format 'z,zzz,zzz,zz9')(char(13)),databasename (format 'X(10)')
from dbc.diskspace
where peakspool > 0 and databasename = 'USERNAME'
group by databasename
order by max(peakspool) desc, databasename;

To increase user’s spool space:

MODIFY USER  AS SPOOL = xxxxx BYTES;

To increase profile’s spool space:

MODIFY PROFILE  AS SPOOL = xxxxx BYTES;

To check what the user's spool limit is:

SELECT SUM(MaxSpool)
FROM
DBC.DiskSpace
WHERE DatabaseName='';

Run the following query to check whether the user has spool space limited through a profile:

SELECT vproc,maxspool,maxprofilespool,
currentspool,peakspool
FROM dbc.diskspace
WHERE databasename = ''
WITH sum(maxspool), sum(maxprofilespool),
sum(currentspool), sum(peakspool)
ORDER BY 1;

Co-Related Subqueries in TD

Example : SELECT Requests With a Correlated Subquery



The following SELECT request uses a correlated subquery to return the names of the employees who have the highest salary in each department.

SELECT name

FROM personnel p

WHERE salary = (SELECT MAX(salary)

FROM personnel sp

WHERE p.department=sp.department);

The following SELECT request uses a correlated subquery to return the names of publishers with no books in the library.

SELECT pubname

FROM publisher

WHERE 0 = (SELECT COUNT(*)

FROM book

WHERE book.pubnum=publisher.pubnum);

Skewness in Teradata

Definition

Skewness is the statistical term, which refers to the row distribution on AMPs. 
If the data is highly skewed, it means some AMPs are having more rows and some very less i.e. data is not properly/evenly distributed. 
This affects the performance/Teradata's parallelism. 
The data distribution or skewness can be controlled by choosing indexes.

The data distribution of table among AMPs is called Skew Factor.....
Generally For Non-Unique PI we get duplicate values so the more duplicate vales we get more the data have same rowhash, so all the same data will come to same amp, it makes data distribution inequality, One amp will store more data and other amp stores less
amount of data, when we are accessing full table, The amp which is having more data will take longer time and makes other amps waiting which leads processing wastage.
A skew factor of 0 indicates that the data is perfectly distributed across all the AMPS.
In this situation (unequal distribution of data)we get Skew Factor High........

Suggestions

For this type of tables we should avoid full table scans Skew factor is distribution of rows of a table among the available no.of AMP's.
If your table has a chance of using unique primary index,it is always better to use UPI which ensures the skew factor around 0%.
If there is no chance of having unique values column in a table choose a column as PI(primary index) which has less duplicate values which inturn results in less skew factor.
That is the data will be distributed almost(not exactly equal percentage) equally to all AMP's.....
ex:
AMP0 AMP1
10000(10%) 9000000(90%)
in this situation skew factor is very high 90%.


Query to find out Skewness in tables in the system 

SELECT  TSIZE.DatabaseName, TSIZE.TableName,
TDEF.CreateTimeStamp AS Created,
TDEF.LastAlterTimeStamp AS LastAltered ,
TDEF.AccessCount,
TDEF.LastAccessTimeStamp AS LastAccess,
SUM(TSIZE.CurrentPerm) AS SCurrentPerm,
SUM(TSIZE.CurrentPerm)/1024/1024 AS SCurrentPerm_MB,
SUM(TSIZE.PeakPerm) AS SPeakPerm,
SUM(TSIZE.PeakPerm)/1024/1024 AS SPeakPerm_MB,
(100 - (AVG(TSIZE.CurrentPerm)/MAX(TSIZE.CurrentPerm)*100)) AS SkewFactor
FROM DBC.TableSize TSIZE JOIN DBC.Tables TDEF
ON TSIZE.DatabaseName = TDEF.DatabaseName
AND TSIZE.TableName = TDEF.TableName
GROUP BY 1,2,3,4,5,6
--ORDER BY SCurrentPerm_GB DESC, skewFactor
HAVING SCurrentPerm_MB > 100 AND skewFactor > 50
ORDER BY SCurrentPerm_MB DESC, skewFactor;

Space in Teradata

24 Jan 2016

Space Queries

SELECT

DatabaseName

,SUM(CurrentPerm)/1024/1024 AS USEDSPACE_IN_MB

,SUM(MaxPerm)/1024/1024 AS MAXSPACE_IN_MB

,SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used

,MAXSPACE_IN_MB- USEDSPACE_IN_MB AS REMAININGSPACE_IN_MB

FROM DBC.DiskSpace

WHERE DatabaseName = <<DatabaseName>>

GROUP BY DatabaseName;