GP分布式数据库数据倾斜调查
本人正在使用的一种数据倾斜方法,交给大家如何使用
--创建两张用户统计的表
CREATE TABLE chkuser.table_segment_statistics ( table_name varchar(200) DEFAULT NULL, segment_count int4 DEFAULT NULL, table_rows int8 DEFAULT NULL );
CREATE TABLE chkuser.table_segment_statistics_balance ( table_name varchar(200) DEFAULT NULL, segment_id int4 DEFAULT NULL, segment_count int8 DEFAULT NULL );
--创建统计模式的函数
CREATE OR REPLACE FUNCTION chkuser.analyze_table_dk_balance(v_schemaname varchar) RETURNS "pg_catalog"."int4" AS $BODY$ DECLARE v_tb varchar(200); v_cur_tb cursor for select schemaname||'.'||tablename from pg_tables where schemaname<>'information_schema' and schemaname<>'pg_catalog' and schemaname<>'gp_toolkit' and tablename not like '%prt%' and schemaname=v_schemaname; BEGIN truncate table chkuser.table_segment_statistics; truncate table chkuser.table_segment_statistics_balance; open v_cur_tb; loop fetch v_cur_tb into v_tb; if not found THEN exit; end if; execute 'insert into chkuser.table_segment_statistics select '''||v_tb||''' as table_name,count(*) as segment_id,sum(num) as table_rows from (select gp_segment_id,count(*) num from '||v_tb||' group by gp_segment_id) t'; execute 'insert into chkuser.table_segment_statistics_balance select '''||v_tb||''' as table_name,gp_segment_id,count(*) as cnt from '||v_tb||' group by gp_segment_id order by gp_segment_id'; end loop; RETURN 0; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
分析的语句如下:
--36指的是greenplum的节点,如果数据量比较大,可适当调整table_rows的值即可
select * from chkuser.table_segment_statistics where table_rows is not null and segment_count<36 and table_rows>10000 order by table_rows desc;
--找出比平均值超出10%的节点,这个阀值可以自行调整,另:只统计超过1万行的表,小表没有太大的分析意义
select a.table_name,b.segment_id,a.table_rows/a.segment_count as reldk,b.segment_count from chkuser.table_segment_statistics a inner join chkuser.table_segment_statistics_balance b on a.table_name = b.table_name where a.table_name is not null and a.table_rows > 10000 and abs(a.table_rows/a.segment_count-b.segment_count)/(a.table_rows/a.segment_count)>0.1
继续阅读
评论