GaussDB SMP特性调优详解

一、问题现象

某基金公司采购金融业软件开发商销售数据中心系统，该系统的主要业务为基金公司的销售终端业务数据每日汇总模块加工分析，包括交易确认、份额变动、分红、账户业务等处理。源系统业务在一家国外的数据库做数据存储与逻辑处理，现因国产化改造需要在集中式GaussDB505.1版本做数据持久与适配并进行业务接口的性能调优。

在接口压测过程中，每日汇总销售数据接口执行耗时139+分钟，另外一家国产数据库在120分钟以内执行完成。在执行多次的调优都没有达到目标值。

二、技术背景

SMP特性通过算子并行来提升性能，同时会占用更多的系统资源，包括CPU、内存、I/O等等。本质上SMP是一种以资源换取时间的方式，在合适的场景以及资源充足的情况下，能够起到较好的性能提升效果；但是如果在不合适的场景下，或者资源不足的情况下，反而可能引起性能的劣化。SMP特性适用于分析类查询场景，这类场景的特点是单个查询时间较长，业务并发度低。通过SMP并行技术能够降低查询时延，提高系统吞吐性能。然而在事务类大并发业务场景下，由于单个查询本身的时延很短，使用多线程并行技术反而会增加查询时延，降低系统吞吐性能。

• 支持并行的算子：计划中存在以下算子支持并行。
• Scan：支持行存普通表和行存分区表顺序扫描、列存普通表和列存分区表顺序扫描。支持BTREE、UBTREE、CBTREE索引上的IndexOnlyScan/IndexScan索引并行扫描。
• Join：HashJoin、NestLoop。
• Agg：HashAgg、SortAgg、PlainAgg、WindowAgg（只支持partition by，不支持order by）。
• Stream：Local Redistribute、Local Broadcast。
• 其他：Result、Subqueryscan、Unique、Material、Setop、Append、VectoRow、 Insert。
• SMP特有算子：为了实现并行，新增了并行线程间的数据交换Stream算子供SMP特性使用。这些新增的算子可以看做Stream算子的子类。
• Local Gather：实现实例内部并行线程的数据汇总。
• Local Redistribute：在实例内部各线程之间，按照分布键进行数据重分布。
• Local Broadcast：将数据广播到实例内部的每个线程。
• Local RoundRobin：在实例内部各线程之间实现数据轮询分发。

示例说明，以TPCH Q1的并行计划为例。

在这个计划中，实现了Scan以及HashAgg算子的并行，并新增了Local Gather数据交换算子。其中3号算子为Local Gather算子，上面标有的“dop: 1/4”表明该算子的发送端线程的并行度为4，而接受端线程的并行度为1，即下层的4号HashAggregate算子按照4并行度执行，而上层的1~2号算子按照串行执行，3号算子实现了实例内并行线程的数据汇总。

通过计划Stream算子上表明的dop信息即可看出各个算子的并行情况。

• 不进行反向扫描操作的游标可以通过打开SMP相关GUC参数（设置query_dop为大于1的值，如果在plsql中还要设置plsql_feature_beta = ‘enable_plsql_smp’）实现SMP执行。

三、调优环境

3.1 逻辑拓扑图

GaussDB本地盘组网拓扑图

GaussDB存算分离组网拓扑图

3.2 软硬件版本

具体硬件配置信息

软件配置信息

四、分析过程

金融业软件开发商销售数据中心系统的业务逻辑都封装在包或存储过程处理。其中每日汇总销售数据接口有一张物理表为tasset_temp_crm的数据量2000+万记录数，按照f_lastshares>0 and f_lastasset>0条件筛选以后的数据量在219+万记录数据需要处理。调用自定义函数f_get_custchnl获取正确的客户渠道号，即要处理219+万记录的客户渠道号的复杂逻辑以及按需求做汇总。在单独调用一次子存储过程需要耗时24分钟左右，极大影响了业务处理效率。

子过程执行时，CPU的使用率在5%以下，如：

五、调优过程

5.1 系统参数调整

调整GUC参数的目的是最大化使用服务器资源，开启兼容源数据库的兼容模式

与SQL SMP功能。具体如下：

5.2 SQL改写

将数据分组单独处理成一个步骤，原因是分组group by 子查询不会执行并行算子。如下：

调优化前的代码:

调优化前的代码:
begin 
INSERT INTO public.lsh_test2(
C_CHANNELNO      ,
C_AGENCYNO       ,
D_STARTDATE      ,
D_ENDDATE        ,
C_FUNDCODE       ,
C_CUSTTYPE       ,
F_NETVALUE       ,
F_TOTALNETVALUE  ,
F_LASTCUSTCOUNT  ,
F_LASTFUNDACCOCOUNT,
F_LASTSHARES     ,
F_LASTASSET      ,
C_TANO           ,
d_date ,
C_SHARETYPE)
select /*+ set (query_dop 24) */ * from
(
select
decode(TRIM(mf.c_agencyno), 'TA', '00010000',
nvl(f_get_custchnl(to_date('20230411', 'yyyymmdd'),
mf.c_fundacco,
decode(TRIM(mf.c_agencyno), 'TA', mf.c_netno, mf.c_agencyno),
mf.c_netno,
mf.c_tradeacco,
mf.c_fundcode,
mf.c_custno,
mf.c_tano),
'00010' || mf.c_agencyno)) c_channelno,
mf.c_agencyno,
to_date('20230411', 'yyyymmdd') d_startdate,
to_date('20230411', 'yyyymmdd') d_enddate,
mf.c_fundcode,
c_custtype,
-- modify 20180601
AVG(mf.f_netvalue) f_netvalue,
SUM(mf.f_managerfee) as f_totalnetvalue,
-- 20141020 该字段复用为管理费
COUNT(distinct mf.c_custno) f_lastcustcount,
COUNT(distinct mf.c_fundacco) f_lastfundaccocount,
SUM(mf.f_lastshares) f_lastshares,
SUM(mf.f_lastasset) f_lastasset,
mf.c_tano,
to_date('20230410', 'yyyymmdd') as d_date,
c_sharetype
from public.lsh_test1 mf
where(nvl(mf.f_lastshares, 0) > 0 or nvl(mf.f_lastasset, 0) > 0);	
group by
c_channelno,
mf.c_agencyno,
mf.c_fundcode,
c_custtype,
mf.c_sharetype,
mf.c_tano ;	
) t
where c_channelno like '00010%';
end;
/

调优化后的代码:

begin
/*
drop table fundcrm.public.lsh_test1;
CREATE TABLE fundcrm.public.lsh_test1 (
c_channelno text,
c_agencyno varchar(20),
--d_startdate timestamp,
--d_enddate timestamp,
c_fundcode varchar(40),
c_custtype bpchar,
f_netvalue numeric,
f_managerfee numeric,
c_custno varchar(12),
c_fundacco varchar(24),
f_lastshares numeric,
f_lastasset numeric,
c_tano bpchar(2),
--d_date timestamp,
c_sharetype bpchar(1)
);
*/
execute immediate 'truncate table public.lsh_test1';
insert /*+ set (query_dop 24)  */ into public.lsh_test1
select  
decode(TRIM(mf.c_agencyno), 'TA', '00010000',
nvl(f_get_custchnl(to_date('20230411', 'yyyymmdd'),
mf.c_fundacco,
decode(TRIM(mf.c_agencyno), 'TA', mf.c_netno, mf.c_agencyno),
mf.c_netno,
mf.c_tradeacco,
mf.c_fundcode,
mf.c_custno,
mf.c_tano),
'00010' || mf.c_agencyno)) c_channelno,
mf.c_agencyno,
mf.c_fundcode,
(case
when mf.c_custtype is null then '1'
else mf.c_custtype
end) as c_custtype,
nvl(mf.f_netvalue, 0) f_netvalue,
nvl(mf.f_managerfee, 0) as f_managerfee,
mf.c_custno,
mf.c_fundacco,
nvl(mf.f_lastshares, 0) f_lastshares,
nvl(mf.f_lastasset, 0) f_lastasset,
mf.c_tano,
c_sharetype
from tasset_temp_crm mf
where(nvl(mf.f_lastshares, 0) > 0
or nvl(mf.f_lastasset, 0) > 0);
/*
DROP TABLE public.lsh_test2;
CREATE TABLE public.lsh_test2 (
c_channelno character varying(40),
c_agencyno character varying(9),
d_startdate timestamp without time zone,
d_enddate timestamp without time zone,
c_fundcode character varying(40),
c_custtype character(1),
f_netvalue numeric(7,4),
f_totalnetvalue numeric(12,4),
f_lastcustcount numeric(11,0),
f_lastshares numeric(19,2),
f_lastasset numeric(25,8),
c_tano character(2),
c_areacode character varying(100),
c_marketregion character varying(40),
f_lastfundaccocount numeric(11,0),
d_date timestamp without time zone,
f_lostcustcount numeric(11,0),
f_lostfundaccocount numeric(11,0),
c_sharetype character(1)
);
*/
execute immediate 'truncate table public.lsh_test2';
INSERT  /*+ set (query_dop 24)   */ INTO public.lsh_test2(
C_CHANNELNO      ,
C_AGENCYNO       ,
D_STARTDATE      ,
D_ENDDATE        ,
C_FUNDCODE       ,
C_CUSTTYPE       ,
F_NETVALUE       ,
F_TOTALNETVALUE  ,
F_LASTCUSTCOUNT  ,
F_LASTFUNDACCOCOUNT,
F_LASTSHARES     ,
F_LASTASSET      ,
C_TANO           ,
d_date ,
C_SHARETYPE)
select
c_channelno,
mf.c_agencyno,
to_date('20230411', 'yyyymmdd') d_startdate,
to_date('20230411', 'yyyymmdd') d_enddate,
mf.c_fundcode,
c_custtype,
-- modify 20180601
AVG(mf.f_netvalue) f_netvalue,
SUM(mf.f_managerfee) as f_totalnetvalue,
-- 20141020 该字段复用为管理费
COUNT(distinct mf.c_custno) f_lastcustcount,
COUNT(distinct mf.c_fundacco) f_lastfundaccocount,
SUM(mf.f_lastshares) f_lastshares,
SUM(mf.f_lastasset) f_lastasset,
mf.c_tano,
to_date('20230410', 'yyyymmdd') as d_date,
c_sharetype
from
public.lsh_test1 mf
where
c_channelno like '00010%'
group by
c_channelno,
mf.c_agencyno,
mf.c_fundcode,
c_custtype,
mf.c_sharetype,
mf.c_tano ;	 
end;

六、最终结果

• 优化前：从每日份额统计开始到每日份额统计结束：2024-09-19 10:59:10.000 ~ 2024-09-19 13:18:40.000   总共：139分

select * from xxxx1 where  d_date >= '2024/9/19 10:59:10'
and d_date <= '2024-10-30 19:37:49.000'
--and c_stepname  = '每日份额统计开始'
order by d_date ;

• 优化后：从每日份额统计开始到从每日份额统计结束：2024-10-11 18:29:16.000 ~ 2024-10-11 18:53:47.000 总共：24分

select * from testxxxx where  d_date >= '2024-10-11 18:28:00.000'
and d_date <= '2024-10-30 19:37:49.000'
--and c_stepname  = '每日份额统计开始'
order by d_date ;

• CPU执行情况：