如何从Oracle迁移到Greenplum 第三篇

Pivotal研发中心 2020-05-14

1022

在过去的两周里，我们的《如何从Oracle迁移到Greenplum》系列文章分别介绍了《Greenplum和Oracle的产品对比》，《迁移效果的几个关键因素》，以及《迁移场景和迁移过程》。今天将继续和大家分享如何Oracle迁移到Greenplum的特殊场景。

特殊场景分析

从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）尽量减少并行化过程中带来的其他开销。

递归查询

在一些行业中，经常需要通过递归查询查找历史上相关联的数据。例如，在保险行业，在本年度保单进入系统时，需要查找其历史上所有年度的保单，直至首次投保，并形成树状结构，再进行相应保费的计算。

以下是在Oracle中实现递归查询的一个用例。

--其中PROPOSALNO列为本年度保单的投保单号，POLICYNO为本年度保单的保单号，OLDPOLICYNO为上一年度保单号。

--该程序逻辑为本年度保单数据进入系统时，按照上述关系找到该保单之前所有年度的保单，直至首次投保保单，形成一个树状结构。

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.etlsourceflag
FROM   cxc_prpcrenewal R
START WITH R.oldpolicyno IN (SELECT DISTINCT Nvl(oldpolicyno, policyno)
                             FROM   cxc_prpcrenewal_inc)
           AND NOT EXISTS (SELECT 1
                           FROM   cxc_prpcrenewal_inc I
                           WHERE  R.oldpolicyno = I.policyno
                                  AND 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;
BEGIN
FOR r IN SELECT generate_series(2002,2018) i
LOOP
raise 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 as 
with recursive policyinfo_graph(id,oldpolicyno, policyno, depth,path) as (
select id,oldpolicyno,policyno,1,array[oldpolicyno,policyno] from policyinfo e where oldpolicyno is null
union all
select e.id,e.oldpolicyno, e.policyno,g.depth+1,path||e.policyno from policyinfo e, policyinfo_graph g
where 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执行时间，如下表所示，随着实例数的增加，在某一临界点，当广播开销大于并行扫描以及关联的开销时，性能就会下降。

递归查询语句执行时间

在数据库中也可以用递归查询查找内部对象的依赖关系，示例如下：

--可以通过如下SQL获取表上oid之间的依赖关系。

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_class
  WHERE oid = 'public.hello'::regclass
  UNION ALL
  SELECT pg_depend.classid, objid,level+1,path||pg_depend.objid
  FROM 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,path
FROM dep
ORDER BY level,obj;
复制

总结一下，递归查询作为数据库高级特性，可以实现树状结构查询，如路径查找、关系图谱等，可以满足部分图计算的需求，扩展了数据库的应用场景；也可借助MPP的优势，提升性能。但由于底层实现上的一些限制，无法达到完全的线性扩展，对于类似的一些特殊应用，要理解原有性能的瓶颈点，并针对MPP做相应的优化，以充分利用硬件，发挥MPP的性能优势。

窗口函数

窗口函数允许应用程序开发人员使用标准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产生的结果再次做排序和窗口函数操作。

从执行计划可以看出，除了第1步和第2步可以用到MPP并行的优势外，第3步和第4步其实和单机数据库并没有差异。由于数据汇总到master之后，只有一个进程做后续的排序和窗口函数操作，所以受限于第3步和第4步，导致性能无较明显的提升。

改写后SQL如下：

(select * from match order by mrje desc limit 10)--买入top10
union 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_rank
from match)
select * from foo where mtje_rank <=10 or mrje_rank<=10 order by mrje_rank,mtje_rank;
复制

结果，原有SQL迁移到Greenplum上，不做改写就可以达到十几倍的性能提升。

执行计划如下：

从如上的执行过程可以看到，所有的排序和窗口函数操作都并行地在所有segment实例上完成，能充分发挥Greenplum并行计算的优势，所以得到了性能的显著提升。

总之，通过标准SQL借助窗口函数可以完成复杂的分析任务，在实际业务中得到广泛的应用。绝大部分SQL迁移到Greenplum之后，可带来较为明显的性能提升，也有部分SQL由于本身的写法和逻辑限制，并不能发挥出Greenplum的性能优势。针对这样的场景，我们需要了解业务真实的诉求，结合Greenplum架构的特点做相应的改写，以达到最好的业务效果。

本文摘录自Greenplum官方教材《Greenplum：从大数据战略到实现》，点击下面商品链接或文章底部“阅读原文”购买。

我知道你在看哟

oracle

最后修改时间：2020-05-14 15:06:25

文章转载自Pivotal研发中心，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

如何从Oracle迁移到Greenplum 第三篇

评论

相关阅读