测试数据为:
#drop table lianxi1;
#create table lianxi1 as select a as id,round(random()) as flag,repeat('a',1024) as value from generate_series(1,5000000) a distributed by(id);
1 数据倾斜对性能的影响
查看下数据在节点上的分布情况,以id作为分布健,数据可
均匀分散到所有数据节点:
#select gp_segment_id,count(*) from lianxi1 group by 1;
数据
倾斜情况下,由于flag只有两个值0,1,所以数据分散到两个segment上:
#drop table lianxi1;
#create table lianxi1 as select a as id,round(random()) as flag,repeat('a',1024) as value from generate_series(1,5000000) a distributed by(flag);
总结:数据均匀情况下可以利用更多机器工作,性能也比较高。
2 数据倾斜对查询速度的影响
数据倾斜时查询:
#
数据均匀时的查询速度:
总结:数据均匀时查询速度快了好多。
3 数据压缩后对查询速度的影响:
普通表时查询速度:
#create table lianxi1 as select a as id,round(random()) as flag,repeat('a',1024) as value from generate_series(1,5000000) a distributed by(flag);
压缩表对查询速度的影响:
#create table lianxi1_compress with(appendonly=true,compresslevel=5) as select * from lianxi1 distributed by(flag);
总结:压缩表查询很快