【DWS】MPP架构下数据倾斜率分析
MPP架构下数据倾斜率分析
MPP架构下处理海量数据，需要各位参与者时刻关注并行处理能力，若无法发挥并行能力，再大的集群也无法发挥其处理能力，可能还会出现用户质疑集群处理能力。作为数据库使用者，特别是高阶使用人员、运维监控人员，需要即时发现系统隐患。
本文是从数据表的数据分布角度探讨，发现有害倾斜，优化系统表数据分布。
1. 涉及到的检索手段有：
a) 表空间大小占用角度出发分析：table_distribution()
b) 表数据记录行数角度出发分析：table_skewness('schema名.table名')
2. 倾斜率计算方法说明：
a) 方法一： (最大值-最小值)/合计值
b) 方法二：1-(平均值/最大值)
这两种计算方式，倾斜率最大值不超过1，即小于等于100%，特别是方法二永远达不到100%值。
c) 两种方法跟踪比对：
--跟踪倾斜率 select schemaname,tablename ,(100-100*avg(dnsize)/nullif(max(dnsize),0))::numeric(5,2) "New_Skew(%)" ,(max(dnsize)-min(dnsize)) skewsize ,(((max(dnsize)-min(dnsize))/sum(dnsize))*100)::numeric(6,3) skewratio ,stddev(dnsize)::numeric(1000,0) skewstddev from table_distribution('public','wzy_rows') a group by 1,2 order by 3 desc;
|
跟踪如下，
d) 对比个人倾向结论
即使用方法二“1-(平均值/最大值) “跟踪倾斜率；
3. 倾斜引起空间浪费计算说明：
a) 理念
鉴于MPP架构下希望所有数据表尽量均衡分布，即整个系统是处于分布平均的情况下，跟踪当前表对系统均衡性的影响分析。
b) 计算方式
最大值*DN总数-当前合计值
Select schemaname,tablename, (100-100*avg(dnsize)/nullif(max(dnsize),0))::numeric(5,2) "New_Skew(%)" ,max(dnsize)*count(1)-sum(dnsize) "WastedPerm(B)" From table_distribution('public','wzy_rows') a group by 1,2 order by 3 desc; |
4. 倾斜率选择使用空间占用还是记录数占用计算问题
a) 空间问题
当前涉及page页8KB预占问题，即并不是每新增一条记录就带来空间线性增长；即空间占用大小差不多，但可能记录数存在较大较别，当该数据进行关联运算时，会存在计算资源占用不一致，发生倾斜场景；
b) 记录数问题
若只关注记录数，无法准确反映在数据重分布过程中的IO变化，存储工作空间变化；
c) 结论
由于空间测算成本较记录数测算成本低，常规情况下先看空间倾斜率，再进一步关注记录倾斜情况。
5. 最后，生产环境数据倾斜率分析与调整
a) 全系统的偷懒方式查询
select * from ( select schemaname,tablename,(100-100*avg(dnsize)/nullif(max(dnsize),0))::numeric(5,2) Skew ,max(dnsize)*count(1)-sum(dnsize) WastedPerm ,Table_RowSkew(schemaname||'.'||tablename) as Row_Skew From table_distribution() a group by 1,2 having Skew>30 ) where Row_Skew>30 order by WastedPerm desc |
即从浪费空间（WastedPerm）最多的表开始排查问题。
b) 精细排查
--提取表名 /* （可选） 空间分布： select * from table_distribution('db1','tb1'); 记录分布: select * from table_skewness('db1.tb1'); */ --1.查看表字段信息 Select pg_get_tabledef('db1.tb1'); --或 select tbl.relname,col.attname,col.attnum from pg_attribute col inner JOIN pg_class tbl ON col.attrelid = tbl.oid where col.attnum>0 and tbl.oid='db1.tb1'::regclass ; --2.重新选择分布列，查看记录倾斜情况 with tmp as ( select * from table_skewness('db1.tb1','colA,ColB') ) select tmp.*,b.skew::text||'%' skew from tmp, (select (100-100*(avg(num::bigint)/max(num::bigint))) ::dec(5,2) skew from tmp ) b ; --3.重建表 create table db1.tmp_tb1 DISTRIBUTE BY HASH (colA,colB) as table db1.tb1 with data; --4.验证空间倾斜情况 with tmp as ( select * from table_distribution('db1','tb1') ) select tmp.*,b.skew::text||'%' skew from tmp, (select (100-100*(avg(dnsize)/max(dnsize))) ::dec(5,2) skew from tmp ) b ; --5.对换表名，旧名事后或人工或程序化删除 alter table db1.tb1 rename to tmp_tb1_20200612; alter table db1.tmp_tb1 rename to tb1;
|
一般重新选择的分布列，除了关注倾斜率越小越好，还要从数据关联、使用角度统合考虑，不能一味追求分布的完全均匀；
6. 附
a) 上文使用到的记录数倾斜率自定义函数table_skew(’表名‘)
CREATE OR REPLACE FUNCTION Table_RowSkew(_table_name text )
RETURNS numeric
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $function$
DECLARE
row_data record;
row_name record;
query_str text;
query_str_nodes text;
cnt bigint;
total_rows bigint;
total_cnt bigint := 0;
max_row bigint := 0;
BEGIN
-- total records
EXECUTE 'SELECT count(1) FROM ' || _table_name
INTO total_rows;
IF total_rows = 0 THEN
RETURN 0;
END IF;
--Get the node names
query_str_nodes := 'SELECT node_name FROM pgxc_node where node_type IN (''C'', ''D'') AND oid::text in ' ||
'(SELECT regexp_split_to_table(nodeoids::text, '' '') FROM pgxc_class WHERE pcrelid=''' || _table_name || '''::regclass::oid)';
FOR row_name IN EXECUTE(query_str_nodes) LOOP
query_str := 'EXECUTE DIRECT ON (' || row_name.node_name || ') ''select count(1) as count1 from ' || $1 || '''';
EXECUTE(query_str) INTO cnt;
IF max_row < cnt THEN
max_row := cnt;
END IF;
total_cnt := total_cnt + 1;
END LOOP;
RETURN 100 - 100 * (total_rows/total_cnt)/max_row;
END; $function$ |
- 点赞
- 收藏
- 关注作者
评论（0）