GP优化总结

总结了一些常用的GP优化经验，分享给大家，欢迎拍砖哈！

一、从技术角度

1 模型优化

1）分散键不合理：在初建表的时候，可能对业务理解不够或者后续需求不太明确，所建表的分散键设置不合理，导致后续使用的时候表要重分布，sql性能比较差；可以重建表来修改分散键或者使用alter table tablename set distributed by(column);语句修改分散键，如果数据量比较大的话还是重建表比较好。

2）分区设置不合理：如数据量不大的表建了分区，每次使用的时候要扫描多个分区，其实每个分区的数据量不大，性能还不如不分区的好；这类情况可以将分区表改为非分区表，或者根据需求改为按周或月分区以减少分区数。

2 查看执行计划

1）join顺序是否正确

我们看下下面的sql语句：

SELECT t.*

FROM (

SELECT a.* FROM tdl.cn_aclm_yy_serv_imprv_tmpt0 a

LEFT OUTER JOIN

tdl.cn_aclm_yy_map_top_mem_tmpt0 b

ON (a.member_id=b.member_id AND a.effect_id=b.effect_id)

WHERE b.member_id IS NULL

) t,

tdl.cn_aclm_yy_high_mem_tmpt0 m

WHERE t.capital=m.capital

AND (t.category_id=m.category_id OR (t.keyword_1 IN (m.keyword_1,m.keyword_2)) OR (t.keyword_2 IN (m.keyword_1,m.keyword_2)))

AND (t.follow_reason IS NULL OR t.follow_reason IN (’deadline’,'out_of_touch_too_long’))

AND t.yyyymmdd = cast(’20110115′ AS DATE)

AND (CASE WHEN t.effect_id=1 AND t.effect_value < m.feedback_contact THEN 1

WHEN t.effect_id=2 AND t.effect_value < m.feedback_im THEN 1

WHEN t.effect_id=3 AND t.effect_value < m.athena_cookie THEN 1

ELSE 0 END)>0;

—————————————————————————————————————

Gather Motion 104:1 (slice4) (cost=30.70..3385.81 rows=470 width=191)

-> Hash Left Join (cost=30.70..3385.81 rows=470 width=191)

Hash Cond: a.member_id::text = b.member_id::text AND a.effect_id = b.effect_id

Filter: b.member_id IS NULL

-> Redistribute Motion 104:104 (slice3) (cost=27.80..3358.21 rows=470 width=191)

Hash Key: a.member_id::text

-> Hash Join (cost=27.80..3348.80 rows=470 width=191)

Hash Cond: a.capital::text = m.capital::text

Join Filter: (a.category_id = m.category_id OR (a.keyword_1::text = ANY (ARRAY[m.keyword_1, m.keyword_2]::text[])) OR (a.keyword_2::text = ANY (ARRAY[m.keyword_1, m.keyword_2]::text[]))) AND CASE WHEN a.effect_id = 1::numeric AND a.effect_value < m.feedback_contact THEN 1 WHEN a.effect_id = 2::numeric AND a.effect_value < m.feedback_im THEN 1 WHEN a.effect_id = 3::numeric AND a.effect_value < m.athena_cookie THEN 1 ELSE 0 END > 0

-> Redistribute Motion 104:104 (slice1) (cost=0.00..684.81 rows=11894 width=65)

Hash Key: a.capital::text

-> Seq Scan on cn_aclm_yy_serv_imprv_tmpt0 a (cost=0.00..446.93 rows=11894 width=65)

Filter: (follow_reason IS NULL OR (follow_reason::text = ANY (’{deadline,out_of_touch_too_long}’::character varying[]::text[]))) AND yyyymmdd = ‘2011-01-15′::date

-> Hash (cost=20.21..20.21 rows=607 width=146)

-> Redistribute Motion 104:104 (slice2) (cost=0.00..20.21 rows=607 width=146)

Hash Key: m.capital::text

-> Append-only Scan on cn_aclm_yy_high_mem_tmpt0 m (cost=0.00..8.07 rows=607 width=146)

-> Hash (cost=1.76..1.76 rows=76 width=16)

-> Seq Scan on cn_aclm_yy_map_top_mem_tmpt0 b (cost=0.00..1.76 rows=76 width=16)

(19 rows)

其中表tdl.cn_aclm_yy_map_top_mem_tmpt0，tdl.cn_aclm_yy_serv_imprv_tmpt0都是已member_id作为分散键，如果先做这两表关联就只需要重分布一次数据，而执行计划中join的顺序并不是按我们所想的去做，而是先做的a表和m表关联，此时需要将数据重分布一次，结果集再和内层的b表关联，此时数据再次重分布；对于此类问题，目前我的解决方案是将内层子查询的join那出来单独做一张临时表，然后再用临时表和m表关联。

2）redistribute或broadcast是否正确

i）union操作导致数据重分布

create table aquery.t01 as select * from pg_tables distributed by(tablename);

create table aquery.t02 as select * from pg_tables distributed by(tablename);

create table aquery.t03 as select * from pg_tables distributed by(tablename);

explain select a.* from (select tablename,schemaname,tableowner,tablespace,hasindexes,hasrules,hastriggers from aquery.t01 union all select * from aquery.t02) a left join aquery.t03 b on a.tablename = b.tablename;

QUERY PLAN

——————————————————————————————-

Gather Motion 104:1 (slice2) (cost=22.92..131.75 rows=1504 width=259)

-> Hash Left Join (cost=22.92..131.75 rows=1504 width=259)

Hash Cond: a.tablename = b.tablename

-> Redistribute Motion 104:104 (slice1) (cost=0.00..87.20 rows=1504 width=259)

Hash Key: a.tablename

-> Append (cost=0.00..42.08 rows=1504 width=259)

-> Seq Scan on t01 (cost=0.00..13.52 rows=752 width=259)

-> Seq Scan on t02 (cost=0.00..13.52 rows=752 width=259)

-> Hash (cost=13.52..13.52 rows=752 width=64)

-> Seq Scan on t03 b (cost=0.00..13.52 rows=752 width=64)

这种union操作做子查询，结果集再和其他表做join，即使所有表的分散键都一直，从执行计划来看都会重分布数据，因此对于类似情况，目前的解决方案是将union操作结果集固化为表，再和其他表做join操作。

ii）将大表做了重分布或广播

一个非常大的表和非常小的表做关联非分散键时，比较理想的执行计划是将小表广播，但由于truncate表会同时丢失表的统计信息，导致sql的执行计划错误，将较大的表做了重分布或者广播，对于此类情况，使用固化统计信息的方法，在脚本中增加语句：$rv=alisql::ali_analyze(__LINE__,$script_step++,$dbCon_gp,60000000,"pub","cn_sekw_cate_h_1_prt_p30001231");(此语句并非GP自带的，而是我们自己封装的)

3）分区裁剪是否正确：在对分区字段做限制的时候使用了如stat_date=date_trunc(’month’,cast(’20110115′ as date))类似的不稳定函数，是无法使用到分区裁剪，会扫描所有分区，导致性能降低；解决方法是将把函数写在外面赋值给一个变量，然后用此变量再对分区字段做限制。

二、从业务角度

1 全量刷新改增量刷新：全量改增量有两种，一种是将每天进行全量刷新的逻辑改为每天按增量刷新，每天刷新全量数据会由于数据量大计算复杂导致性能较差，那么可以考虑能否根据业务逻辑将全量刷新改为增量刷新；第二种仍然是每天进行全量刷新，但不全部使用当天的全量数据进行计算，利用前一天的全量数据和当天变化的增量数据进行比对计算出当天的全量数据，这样会减少一部分计算时的数据量。

2 充分利用已计算的指标：如业务部门的需求为最近一个自然月的数据，而我们的idl层有已经算好的最近30天数据，可以和业务部门讨论将最近一个自然月指标改为最近30天数据，尽可能的使用数据库中已经存在的指标数据。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22556479/viewspace-1044942/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22556479/viewspace-1044942/