[基础运维]GP分布式集群数据倾斜调查—运维篇

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