特殊场景分析
从Oracle迁移到Greenplum后，大部分分析类型场景都会有明显的性能提升。但在某些场景下，由于MPP内部机制的实现，很难达到性能的线性扩展。还有部分SQL要充分发挥MPP的性能优势，就要根据MPP的特点进行相应SQL的改写。
采用MPP后，究竟能带来多大的性能提升？这可以参考阿姆达尔定律：系统中对某一部件采用更快执行方式所能获得的系统性能改进程度，取决于这种执行方式被使用的频率，或所占总执行时间的比例。
阿姆达尔定律
如上图所示，如果单个SQL计算过程中可并行的部分占50%，那么理论上随着cpu数的增加，并行部分的执行时间趋近于0，串行部分仍然占50%，那么最多可以达到接近2倍的性能的提升。当超过某一临界点后，增加再多的CPU也无法提升单个SQL的执行效率，所以单个SQL的性能在MPP中也是有理论上限的。另一方面，随着CPU和节点数的增加，用于集群间通信、数据的移动、锁、分布式事务等的开销会越来越大，这部分的额外开销在某一临界点会成为性能的主要瓶颈，尤其对于执行时间较短的SQL，MPP带来的额外开销反而有可能使其性能下降。所以，Oracle数据库迁移到 Greenplum的主要优化点有两个：1）找出原有系统瓶颈点，尽量将其通过MPP进行并行化；2）尽量减少并行化过程中带来的其他开销。
1.
递归查询
INSERT INTO temp_root_new(root,policyno,oldpolicyno,proposalno,inserttimeforhis,etlsourceflag)SELECT /*+PARALLEL(32)*/ DISTINCT connect_by_root( R.oldpolicyno ) ROOT,R.policyno,R.oldpolicyno,R.proposalno,R.inserttimeforhis,R.etlsourceflagFROM cxc_prpcrenewal RSTART WITH R.oldpolicyno IN (SELECT DISTINCT Nvl(oldpolicyno, policyno)FROM cxc_prpcrenewal_inc)AND NOT EXISTS (SELECT 1FROM cxc_prpcrenewal_inc IWHERE R.oldpolicyno = I.policynoAND I.oldpolicyno IS NOT NULL)CONNECT BY NOCYCLE PRIOR R.policyno = R.oldpolicyno;COMMIT;
我们将其业务表简化为包含保险人id，本年度保单号policyno和上一年保单号oldpolicyno。如果为第一次购买保险，则上一年保单号为空。假设从2001年开始发展保险业务，每年新增保单200万，有75%的用户会在下年继续购买保险，那么直到2018年，总数据量为1.2亿。
数据准备过程如下：
DROPdrop TABLEtable if exists policyinfo;CREATEcreate TABLEtable policyinfo (id int ,oldpolicyno text,policyno text) distributed randomly;INSERTinsert INTOinto policyinfo select i::int, null,'2001-'||i from generate_series(1,2000000) i;--插入2001年初始200万数据insert into policyinfo select substr(policyno,6)::int,policyinfo.policyno,'2002-'||substr(policyno,6) from policyinfo where policyno like '2001-%' and random()<0.75;--2002年有75%的用户继续购买保单insert into policyinfo select i::int,null,'2002-'||i from generate_series(2000001,4000000) i;--2002年新增保单200万
使用匿名块实现数据准备：
drop table policyinfo_analyze;truncate table policyinfo;insert into policyinfo select i::int, null,'2001-'||i from generate_series(1,2000000) i; --插入2001年初始200万数据DO $$DECLARE record r;BEGINFOR r IN SELECT generate_series(2002,2018) iLOOPraise info '%','insert into policyinfo select substr(policyno,6)::int,policyinfo.policyno,'''||r.i||'-''||substr(policyno,6) from policyinfo where policyno like '''||r.i-1||'-%'' and random()<0.75';EXECUTE 'insert into policyinfo select substr(policyno,6)::int,policyinfo.policyno,'''||r.i||'-''||substr(policyno,6) from policyinfo where policyno like '''||r.i-1||'-%'' and random()<0.75';raise info '%','insert into policyinfo select i::int,null,'''||r.i||'-''||i from generate_series('||(r.i-2001)*2000000+1||','||(r.i-2000)*2000000||') i';EXECUTE 'insert into policyinfo select i::int,null,'''||r.i||'-''||i from generate_series('||(r.i-2001)*2000000+1||','||(r.i-2000)*2000000||') i';END LOOP;END$$;analyze policyinfo;
生成递归查询的树状结构，主要包括层级结构和路径关系，
create table policyinfo_analyze aswith recursive policyinfo_graph(id,oldpolicyno, policyno, depth,path) as (select id,oldpolicyno,policyno,1,array[oldpolicyno,policyno] from policyinfo e where oldpolicyno is nullunion allselect e.id,e.oldpolicyno, e.policyno,g.depth+1,path||e.policyno from policyinfo e, policyinfo_graph gwhere e.oldpolicyno=g.policyno)select * from policyinfo_graph;
接下来，我们重点分析一下递归查询在Greenplum里的执行计划。原始执行计划如下所示：
QUERY PLAN----------------------------------------------------------------------------------------------------------------------Redistribute Motion 4:4 (slice2; segments: 4) (cost=0.00..366724049.15 rows=639909999 width=66)Hash Key: e.id-> Recursive Union (cost=0.00..366724049.15 rows=639909999 width=66)-> Append-only Scan on policyinfo e (cost=0.00..1238469.48 rows=9015072 width=30)Filter: oldpolicyno IS NULL-> Hash Join (cost=14072655.28..31429277.98 rows=63089493 width=66)Hash Cond: g.policyno = e.oldpolicyno-> WorkTable Scan on policyinfo_graph g (cost=0.00..7212057.27 rows=90150716 width=68)-> Hash (cost=7244961.88..7244961.88 rows=120129848 width=30)-> Broadcast Motion 4:4 (slice1; segments: 4) (cost=0.00..7244961.88 rows=120129848 width=30)-> Append-only Scan on policyinfo e (cost=0.00..1238469.48 rows=30032462 width=30)Settings: gp_recursive_cte_prototype=on; optimizer=off
如执行计划所示，首先将policyinfo表做广播后计算哈希，再递归扫描生成的worktable，进行hashjoin。由于worktable的数据被打散在所有节点，可以利用MPP并行的优势提升hashjoin的性能；另一方面，为了保证结果的正确性，需要将policyinfo做广播。当表数据量非常大、实例数比较多的时候，广播带来的开销会越来越大，在某一临界点其开销会超过并行带来的性能提升，导致整体SQL执行性能下降。具体测试过程如下图所示：
在4个实例上递归查询的执行计划
注意：当两表关联且关联字段和分布字段不一致时，数据需要跨节点移动，优先选择移动数据量最小的执行计划，可以将小表广播，或者大表重分布。但目前在Greenplum递归查询实现中，总是将原始表进行广播，理论上还有进一步优化的空间。
调整gp_segments_for_planner增加广播的代价，依然将原始表进行广播，如下图所示：
递归查询总是会把原始表进行广播
在同样的物理硬件下，执行上面的递归语句，对比PostgreSQL和Greenplum执行时间，如下表所示，随着实例数的增加，在某一临界点，当广播开销大于并行扫描以及关联的开销时，性能就会下降。
递归查询语句执行时间
在数据库中也可以用递归查询查找内部对象的依赖关系，示例如下：
create table hello(id int check(id>100),name text) with (appendonly=true, compresslevel=5,compresstype=zlib, orientation=column);create view v1 as select * from hello;create index bidx_hello_id on hello(id);WITH RECURSIVE dep (classid, obj,level,path) AS (SELECT (SELECT oid FROM pg_class WHERE relname = 'pg_class'),oid,0,array[oid]FROM pg_classWHERE oid = 'public.hello'::regclassUNION ALLSELECT pg_depend.classid, objid,level+1,path||pg_depend.objidFROM pg_depend JOIN dep ON (refobjid = dep.obj))SELECT distinct obj,(SELECT relname FROM pg_class WHERE oid = classid) AS class,(SELECT typname FROM pg_type WHERE oid = obj) AS typname,(SELECT relname FROM pg_class WHERE oid = obj) AS relname,(SELECT relkind FROM pg_class where oid = obj::regclass) AS kind,(SELECT adsrc FROM pg_attrdef WHERE oid = obj) AS attrdef,(SELECT conname FROM pg_constraint WHERE oid = obj) AS constraint,(select ev_class::regclass from pg_rewrite where oid=obj) as view,level,pathFROM depORDER BY level,obj;
总结一下，递归查询作为数据库高级特性，可以实现树状结构查询，如路径查找、关系图谱等，可以满足部分图计算的需求，扩展了数据库的应用场景；也可借助MPP的优势，提升性能。但由于底层实现上的一些限制，无法达到完全的线性扩展，对于类似的一些特殊应用，要理解原有性能的瓶颈点，并针对MPP做相应的优化，以充分利用硬件，发挥MPP的性能优势。
2.
窗口函数
窗口函数允许应用程序开发人员使用标准SQL命令轻松地组成复杂的联机分析处理（OLAP）查询。例如，使用窗口表达式，用户可以计算移动平均值或各种时间间隔的总和，在各个分组内重置聚合和排序，具体窗口分区在OVER（）子句中定义。窗口分区是一组被组合在一起以应用窗口函数的行。与聚合函数不同，聚合函数为每组返回一个结果值，窗口函数为每一行返回一个结果值，但该值是针对特定窗口分区中的行计算的。如果未指定分区，则将在整个中间结果集上计算窗口函数。
例如，在证券行业中，经常要根据股票交易的情况，实时统计全市场买入或者卖出股票数量最多的股东，或者按照股票代号进行分组，统计每只股票买入和卖出最多的股东，再做复杂的业务规则关联，看是否有违规交易等。
我们将其业务简化，在成交表中存放股东的账号、股票代码、买入金额、卖出金额，然后分组统计全市场交易金融最大的10个股东和每只股票交易量最大的10个股东。
create table match(id int,gpdm int ,mrje numeric,mtje numeric) distributed randomly;
insert into match select i,random()*2000,random()*1000000,random()*1000000 from generate_series(1,100000000) i;--插入1亿数据，其中包含2000证券代号的1亿笔交易信息
查询全市场买入和卖出金额最大的10笔记录（原系统SQL写法）：
with foo as(
select id,mrje,row_number() over (order by mrje desc) as mtje_rank,
mtje,row_number() over(order by mtje desc) as mrje_rank
from match)
select * from foo where mtje_rank <=10 or mrje_rank<=10 order by mrje_rank,mtje_rank;
直接迁移到Greenplum之后，发现性能并没有显著提高。
具体执行计划如下：
- 所有segment实例同时扫描成交表。
- 在各个segment实例进行排序，依据分配的内存选择具体的排序算法（快速排序或外部排序）。
- 通过gather motion将所有数据汇总到master，在master节点上进行全局排序。
- 在master节点，对步骤3产生的结果再次做排序和窗口函数操作。
改写后SQL如下：
(select * from match order by mrje desc limit 10)--买入top10union all(select * from match order by mtje desc limit 10);--卖出top10
执行计划如下：
对所有segment同时进行全表扫描。
在各个segment实例进行排序，由于有limit操作，选择使用top-N heapsort算法（能节省内存且效率高）。
通过gather motion，将各个segment实例的排序后的前10条记录返回master，在master节点进行全局排序。
通过如上SQL改写，性能比原有系统提升了将近20倍。
查询每只股票买入和卖出金额最大的10笔记录，命令如下：
with foo as(select id,mrje,row_number() over (partition by gpdm order by mrje desc) as mtje_rank,mtje,row_number() over(partition by gpdm order by mtje desc) as mrje_rankfrom match)select * from foo where mtje_rank <=10 or mrje_rank<=10 order by mrje_rank,mtje_rank;
从如上的执行过程可以看到 ，所有的排序和窗口函数操作都并行地在所有segment实例上完成，能充分发挥Greenplum并行计算的优势，所以得到了性能的显著提升。 总之， 通过标准SQL借助窗口函数可以完成复杂的分析任务，在实际业务中得到广泛的应用。绝大部分SQL迁移到Greenplum之后，可带来较为明显的性能提升，也有部分SQL由于本身的写法和逻辑限制，并不能发挥出Greenplum的性能优势。针对这样的场景，我们需要了解业务真实的诉求，结合Greenplum架构的特点做相应的改写，以达到最好的业务效果。
本文摘录自Greenplum官方教材《Greenplum：从大数据战略到实现》，点击下面商品链接或文章底部“阅读原文”购买。
我知道你在看哟
本文分享自微信公众号 - Greenplum中文社区（GreenplumCommunity）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
扫一扫
3096
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
