Failure 2644 No more room in database

Database
Enthusiast

Failure 2644 No more room in database

How do interpert the results of this query which is shoeing me more actual sapce than wasted sapce

SyntaxEditor Code Snippet

    select TableName, sum(CurrentPerm) as ActualSpace,count(*)*(max(CurrentPerm)-avg(CurrentPerm)) as WastedSpace
from dbc.TableSize where DatabaseName='abc_data'
group by TableName order by WastedSpace desc;

SyntaxEditor Code Snippet

table                                             actual sapce                            wasted sace
VG30_RCPT_TBL                          45,379,907,072.00                 3,625,251,328.00
VG30_RCPT_BKP                       42,970,040,832.00                 3,536,107,008.00
VG62_MAKER_TBL                176,272,896.00                 321,575,424.00
VV30_INSPON_TBL                     6,136,215,040.00                 241,441,280.00
V045_RLSE_TBL                       11,438,800,896.00                206,352,384.00
VV10_VCHR_FJL                              51,134,644,224.00                 202,715,136.00
VF01_RMINI_TBL                      4,735,264,768.00                169,121,792.00
V016_MRSREL_TBL                                   22,622,766,592.00                150,891,008.00
VG52__PLT_TBL                      1,527,831,040.00                  137,500,160.00
VMA4_UPP_CHILD_TBL                 26,468,389,376.00                     82,353,664.00

 

3 REPLIES
Teradata Employee

Re: Failure 2644 No more room in database

The WastedSpace calculation above is one way to look at the impact of data skew. It's particularly useful to prioritize tuning efforts, or identify the likely cause of an "out of space" condition on one AMP (or few AMPs).

 

So reducing the data skew (e.g. by changing PI) for V630_RCPT_TBL potentially could free up 3+ GB for other tables. 

Enthusiast

Re: Failure 2644 No more room in database

Does this take BLC into consideration or does te calc work the same?

Teradata Employee

Re: Failure 2644 No more room in database

currentperm records the actual usage - thus any effect from BLC is included. (It is harder to get the non-BLC number because all the usual fields reord the actual as it is stored.)

 

I dislike the term "wasted space" in this context. The space is not "wasted" because it can be used for other things - perm storage for other tables, spool space or temp space. It is possible but rare that all tables are skewed the same way on the same AMPs. Therefore if one table is skewed on a few AMPs and another table is skewed on a few other AMPs, then the skew balances out. More tables usually means more balance. This can be seen if the aggregate is done at the database level rather than at the table level.

 

I agree with Fred however that this is a good calculation to use to prioritze work on skewed tables. I actually like looking at the percentage of the skew in combination with the absolute number and then the percentage that the table represents of the total storage in the database/system. Spending a bunch of time optimizing skew of a small table is generally wasted effort even if the table is significantly skewed since the effecct on the overall database/system is minimal. Likewise with a large table the absolute number of bytes of skew might be large but the percentage skew is small and again it is likely wasted effort to try to hyper-optimize. A badly skewed large table though is a good target on which to spend time and energy.