Hash Collision 和 Skew

1.计算每个hash值的记录个数

SELECT                 HASHROW (CHARACTER_COLUMN) 
                        AS "Hash Value"
                        ,COUNT(*)
FROM                DB.TABLE
GROUP BY        1
ORDER BY        2 DESC;

2.计算每个AMP上的记录个数

SELECT                 HASHAMP (HASHBUCKET
                        (HASHROW (CHARACTER_COLUMN))) 
                        AS "AMP #"
                        ,COUNT(*)
FROM                DB.TABLE
GROUP BY        1
ORDER BY        2 DESC; 
3.查找出skew偏移严重的表

SELECT 
 DATABASE,TABLENAME,SUM(CURRENTPERM) AS TableSize,
 MAX(CURRENTPERM) * NUM_OF_AMP  AS VirtualSize,
 100 - AVG(CURRENTPERM)/MAX(CURRENTPERM) * 100 AS TableSkew,
 MAX(CURRENTPERM) * NUM_OF_AMP - SUM(CURRENTPERM) AS SkewSavings
 FROM DBC.TABLESIZE
 WHERE CURRENTPERM > 0
 GROUP BY 1,2,3
 HAVING(SkewSavings > 1000000000 and TableSkew > 10)
 OR TableSkew > 50;

http://blog.chinaunix.net/u3/103331/showart.php?id=2028068

Tweet