如何从Oracle迁移到Greenplum 第三篇

发布于 2022-5-19 17:25
浏览
0收藏

 

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

 

特殊场景分析


从Oracle迁移到Greenplum后,大部分分析类型场景都会有明显的性能提升。但在某些场景下,由于MPP内部机制的实现,很难达到性能的线性扩展。还有部分SQL要充分发挥MPP的性能优势,就要根据MPP的特点进行相应SQL的改写。

 

采用MPP后,究竟能带来多大的性能提升?这可以参考阿姆达尔定律:系统中对某一部件采用更快执行方式所能获得的系统性能改进程度,取决于这种执行方式被使用的频率,或所占总执行时间的比例。

如何从Oracle迁移到Greenplum 第三篇-开源基础软件社区

阿姆达尔定律

 

如上图所示,如果单个SQL计算过程中可并行的部分占50%,那么理论上随着cpu数的增加,并行部分的执行时间趋近于0,串行部分仍然占50%,那么最多可以达到接近2倍的性能的提升。当超过某一临界点后,增加再多的CPU也无法提升单个SQL的执行效率,所以单个SQL的性能在MPP中也是有理论上限的。另一方面,随着CPU和节点数的增加,用于集群间通信、数据的移动、锁、分布式事务等的开销会越来越大,这部分的额外开销在某一临界点会成为性能的主要瓶颈,尤其对于执行时间较短的SQL,MPP带来的额外开销反而有可能使其性能下降。所以,Oracle数据库迁移到 Greenplum的主要优化点有两个1)找出原有系统瓶颈点,尽量将其通过MPP进行并行化;2)尽量减少并行化过程中带来的其他开销。

 

1.递归查询

 

在一些行业中,经常需要通过递归查询查找历史上相关联的数据。例如,在保险行业,在本年度保单进入系统时,需要查找其历史上所有年度的保单,直至首次投保,并形成树状结构,再进行相应保费的计算。
 
以下是在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执行性能下降。具体测试过程如下图所示:

如何从Oracle迁移到Greenplum 第三篇-开源基础软件社区

在4个实例上递归查询的执行计划

 

注意:当两表关联且关联字段和分布字段不一致时,数据需要跨节点移动,优先选择移动数据量最小的执行计划,可以将小表广播,或者大表重分布。但目前在Greenplum递归查询实现中,总是将原始表进行广播,理论上还有进一步优化的空间。

 

调整gp_segments_for_planner增加广播的代价,依然将原始表进行广播,如下图所示:

如何从Oracle迁移到Greenplum 第三篇-开源基础软件社区

递归查询总是会把原始表进行广播

 

在同样的物理硬件下,执行上面的递归语句,对比PostgreSQL和Greenplum执行时间,如下表所示,随着实例数的增加,在某一临界点,当广播开销大于并行扫描以及关联的开销时,性能就会下降。

如何从Oracle迁移到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;

如何从Oracle迁移到Greenplum 第三篇-开源基础软件社区

总结一下,递归查询作为数据库高级特性,可以实现树状结构查询,如路径查找、关系图谱等,可以满足部分图计算的需求,扩展了数据库的应用场景;也可借助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之后,发现性能并没有显著提高。

 

具体执行计划如下:

如何从Oracle迁移到Greenplum 第三篇-开源基础软件社区

  1. 所有segment实例同时扫描成交表。
  2. 在各个segment实例进行排序,依据分配的内存选择具体的排序算法(快速排序或外部排序)。
  3. 通过gather motion将所有数据汇总到master,在master节点上进行全局排序。
  4. 在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

 

执行计划如下:

如何从Oracle迁移到Greenplum 第三篇-开源基础软件社区

  1. 对所有segment同时进行全表扫描。
  2. 在各个segment实例进行排序,由于有limit操作,选择使用top-N heapsort算法(能节省内存且效率高)。
  3. 通过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上,不做改写就可以达到十几倍的性能提升。
执行计划如下:

如何从Oracle迁移到Greenplum 第三篇-开源基础软件社区
从如上的执行过程可以看到 ,所有的排序和窗口函数操作都并行地在所有segment实例上完成,能充分发挥Greenplum并行计算的优势,所以得到了性能的显著提升。
 
总之,通过标准SQL借助窗口函数可以完成复杂的分析任务,在实际业务中得到广泛的应用。绝大部分SQL迁移到Greenplum之后,可带来较为明显的性能提升,也有部分SQL由于本身的写法和逻辑限制,并不能发挥出Greenplum的性能优势。针对这样的场景,我们需要了解业务真实的诉求,结合Greenplum架构的特点做相应的改写,以达到最好的业务效果。

 

文章转自公众号:Greenplum中文社区

分类
标签
已于2022-5-19 17:25:53修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐