一、问题现象某基金公司采购金融业软件开发商销售数据中心系统该系统的主要业务为基金公司的销售终端业务数据每日汇总模块加工分析包括交易确认、份额变动、分红、账户业务等处理。源系统业务在一家国外的数据库做数据存储与逻辑处理现因国产化改造需要在集中式GaussDB505.1版本做数据持久与适配并进行业务接口的性能调优。在接口压测过程中每日汇总销售数据接口执行耗时139分钟另外一家国产数据库在120分钟以内执行完成。在执行多次的调优都没有达到目标值。二、技术背景SMP特性通过算子并行来提升性能同时会占用更多的系统资源包括CPU、内存、I/O等等。本质上SMP是一种以资源换取时间的方式在合适的场景以及资源充足的情况下能够起到较好的性能提升效果但是如果在不合适的场景下或者资源不足的情况下反而可能引起性能的劣化。SMP特性适用于分析类查询场景这类场景的特点是单个查询时间较长业务并发度低。通过SMP并行技术能够降低查询时延提高系统吞吐性能。然而在事务类大并发业务场景下由于单个查询本身的时延很短使用多线程并行技术反而会增加查询时延降低系统吞吐性能。支持并行的算子计划中存在以下算子支持并行。Scan支持行存普通表和行存分区表顺序扫描、列存普通表和列存分区表顺序扫描。支持BTREE、UBTREE、CBTREE索引上的IndexOnlyScan/IndexScan索引并行扫描。JoinHashJoin、NestLoop。AggHashAgg、SortAgg、PlainAgg、WindowAgg只支持partition by不支持order by。StreamLocal 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 软硬件版本数据中心分类设备类型设备名称/型号数量台Region 1计算节点BMS服务器ARM 2路服务器鲲鹏64核心3Tor-NVRCE68632管理TORCE68632存储节点存储OceanStorDorado 18500V61SAN交换机CE6860-SAN2管理交换机CE68632具体硬件配置信息硬件配置服务器型号华为Taishan200Pro (Model 2280)CPU2路HiSilicon Kunpeng 920-4826 128内存512G插卡2张四口SP570卡2张双口CX5 25GRoCE网卡存储阵列型号华为Dorado 18500 V6控制器双控 Kunpeng920 128 Cores 2.6GHz内存512G磁盘21*7.68TB SAS-SSDRoCE交换机型号华为CE6866传输模块48*25 Gbps6*100 Gbps软件配置信息软件版本说明GaussDB数据库内核505.1.0麒麟OS银河麒麟V10 sp2存储版本OceanStor Dorado 18500 V6RoCE交换机V300R022C00SPC600四、分析过程金融业软件开发商销售数据中心系统的业务逻辑都封装在包或存储过程处理。其中每日汇总销售数据接口有一张物理表为tasset_temp_crm的数据量2000万记录数按照f_lastshares0 and f_lastasset0条件筛选以后的数据量在219万记录数据需要处理。调用自定义函数f_get_custchnl获取正确的客户渠道号即要处理219万记录的客户渠道号的复杂逻辑以及按需求做汇总。在单独调用一次子存储过程需要耗时24分钟左右极大影响了业务处理效率。子过程执行时CPU的使用率在5%以下如五、调优过程5.1 系统参数调整调整GUC参数的目的是最大化使用服务器资源开启兼容源数据库的兼容模式与SQL SMP功能。具体如下参数名称调优前值调优后值参数功能简单说明numa_distribute_modenoneall启用所有的numa组功能thread_pool_attr880,4,(numabind:0-31,32-63,64-95,96-127)880,4,(numabind:0-30,32-62,64-94,96-126)用于控制线程池功能的详细属性max_process_memory160GB455GB整个实例允许使用的最大内存由于要预留一部分内存给操作系统所以这个参数需要根据RAM大小动态调整shared_buffers80GB320GB行存共享缓存区大小建议设置为max_process_memory的40%work_mem1GB4GB工作内存一般指排序、hash或join占用的内存对于复杂query增大此内存可减少临时文件的使用pagewriter_sleep3ms10ms设置用于增量检查点打开后pagewriter线程每隔pagewriter_sleep的时间刷一批脏页下盘。sql_beta_featureNonesel_semi_poisson,sel_expr_instr,rand_cost_opt,param_path_opt,page_est_opt,a_style_coerce,enable_plsql_smp开启它们可以对特定的场景进行优化但也可能会导致部分没有被测试覆盖的场景发生性能劣化。在特定的客户场景中通过此GUC参数对查询重写规则进行设置使得查询效率最优。Max_connections5002000允许和数据库连接的最大并发连接数。此参数会影响集群的并发能力。可以根据cpu核心数量和内存大小适量调大。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执行情况转载华为云论坛