GBase 8a

运维管理

文章

GBase8a开发使用建议

发表于2024-12-31 13:37:58145次浏览1个评论

通过对GBase8a集群的运维，我们发现问题发生基本上可以归为三类：硬件问题、数据库产品本身问题、使用问题。硬件问题我们可以通过监控资源、紧急备件、维修等方式来减少影响；数据库产品本身问题我们可以通过对发生问题日志进行分析来确定是否为已知问题，来判断是通过临时处理方式或通过产品后续升级版本予以解决；除此之外，为保障我们业务长期稳定运行，使用注意事项也是一个需要我们共同注意的因素。特别是要注意：1、不经过验证或不把握的业务sql尽量避免直接在生产环境运行；2、尽量业务流程固定化，例如sql执行的并发量、并发时间、sql顺序等。3、为避免影响业务运行，如果是首次迁移到GBase集群，建议预留业务sql试运行期。

说明：本文档仅重点介绍日常使用8a开发的一些注意事项及优化建议，具体详细使用说明详见8a产品的产品手册。

开发注意事项

开发规范针对GBase 8a MPP集群数据库的特性，比如列存、分布式等特点特别列举以下几条开发注意事项。

杜绝select *不加limit的操作

由于GBase是列存储数据库，在做select查询的时候，只选择需要的列，避免使用select * 这种操作。因为如果使用select *操作如果结果集过大，返回的结果集会停留在 write to net 这样会拉低单个节点的性能，从而造成整个集群的性能瓶颈。同时增加对产生笛卡尔积SQL的检查，在测试环境中先进行检查，评估结果集大小和逻辑正确性

尽可能的各节点运算本地化

尽可能的各节点运算本地化尽可能的各节点运算本地化尽可能的各节点运算本地化尽可能的各节点运算本地化尽可能的各节点运算本地化尽可能的各节点运算本地化尽可能的各节点运算本地化尽可能的各节点运算本地化尽可能的各节点运算本地化尽可能的各节点运算本地化

在处理查询时，很多处理如关联、分组聚合等，若能够在节点本地完成，其效率将远高于跨节点（需要在各节点间交叉传输数据）的操作。这要求：

 增加对分布表倾斜程度的检查，对表的hash分布键要尽量的选择合理，避免使用不合理的分布而导致的性能问题。

 大表关联尽量选择对应的hash键进行关联。

 过程中多步运算时，中间过程表尽量不要使用随机分布表，选定后续需要运算相关的字段建成hash分布表。

 存储过程多步运算时，由于数据库的后期物化特性，可尽量减少中间过程表的生成，数据库适合处理多表关联的负责运算。

studio 负载方面的配置和使用

多人利用studio连接集群进行操作时，避免连接同一个集群节点，从而导致该节点的负载过高并产生木桶效应，建议对使用人员的连接节点进行登记和管理，为不同使用人员分配相应的集群节点进行连接，总体上保障各个节点接入数量的均衡。

频繁 insert into......values的优化方式

列存和行存在适用场景和实现架构上的不同，决定了了其使用方式的不同，列存储更适合批量插入，避免同一表进行频繁写入操作，对于频繁的单条写入同一表这类交易型操作，在列存储数据库上需要从以下方面进行优化：

1）对于同一表的频繁insert into ......Values 操作，可以化零为整，将多条insert 的value值进行拼接来实现，如insert into test1 (col1,col2) values ( 1, 'a');insert into test1 (col1,col2) values ( 2, 'b');insert into test1 (col1,col2) values ( 3, 'c');可以用insert into test1 (col1,col2) values ( 1, 'a'),(2,'b'),(3,'c');来代替。

2）对日志表的操作由于是频繁对同一张表进行，可能有大量并发写入，为取得更好处理效率，加快并发作业执行速度，建议按照如下两种方式进行：

A)对于日志的操作先落地文件，定期将日志操作以1）方式进行批量插入。

B)根据业务情况分别建立对应的临时日志表，用于分担对单一日志表的集中并发写入和等待，定期将临时日志操作插入到正式日志表。

合理规划作业调度，避免对同表进行同时写入

作业调度优化对于数据加载和跑批作业的执行效率至关重要，总体上需要结合各个业务的时间窗口和执行效果，规划各项作业的并发度以及执行的先后次序。避免大量并发作业造成资源争抢；避免并发对同一表的写入；优先处理关键作业。

相关要求如下：

1）对于单表使用上避免同时的ddl和dml操作，禁止对同一表的dml操作；

2）不要同时发起向一张表进行加载的任务，同表加载请使用串行加载；

尽量避免大数据量 full join

大数据量下full join，尤其是没有任何过滤条件，将会严重耗费系统资源和处理性能，类似操作应该从业务上权衡实现的必要性并尽量避免，同时做好上线前验证并尽量避开业务高峰期执行。

能用varchar不用char

Char的空格可能影响性能

char和varchar字段做关联，由于char是定长字符串类型，如果字段内容没有填满的话，会使用空格补齐，而varchar是变长字符串类型，如果内容没有填满的话，不使用空格补齐，在这种情况下，就会出现关联不上的情况。

尽量避免使用longtext或longblob类型

varchar最大存储长度为32K，longtext最大存储长度为64M，对于不超过32K长度的字段建议使用varchar类型，其可以存储最多10992个中文汉字

原因说明：

longtext为大对象类型，其设计和其它数据库一样是为了满足普通类型无法存储的大字段需求。
其与普通字段varchar在底层存储是不一致的，由于字段长度比较长，longtext列值为独立存储文件，不压缩，占有存储会多，进行数据计算时也会增加IO负载压力。
longtext列值为独立存储文件，随着数据量增多，磁盘上文件个数也会增加，增加系统维护压力，对于数据备份、清理、集群扩容性能都会有影响。
longtext字段计算也可能会导致计算使用内存高，增加机器负载，影响系统性能。
1. 查询条件尽量不要使用函数

提倡where条件中的列能不加函数运算就不加函数，因加函数会造成智能索引失效，sql性能降低。

例如：某现场原始sql为：where substr(product_no, 2, 1) in ('3', '4', '5', '8')，智能索引失效，性能非常低；

改写为： where (product_no like '13%' or product_no like '14%' or product_no like '15%' or product_no like '18%')'，智能索引还能用的上（智能索引支持对字符串类型数据前8个字符的索引，再多了就不支持了）。

避免操作字段表达式后进行比较

1、保证使用智能索引且最有效的方法是字段与常量表达式直接操作的形式rownumtag>=100*10，改成rownumtag+1>=100*10+1就无法使用智能索引。

2、保证一边是字段，另一边是常量表达式（常量当然也可以），常量表达式无论多么复杂都没有问题，因为它只需要计算一遍。

表达式与常量进行比较的条件不能用智能索引。

如：

select ... from ... where ceil(rownumtag / ceil(to_number('100'))) ='10' ;

改为：select ... from ... where rownumtag>100*9 and rownumtag<=100*10;

拷贝表结构的几种方式：

create table t2 like t1;

create t2 as select * from t1;（如果t1有hash健，t2创建后会丢失hash属性）

create table t2 distributed by (‘id’) as select * from t1;（t2）

create table t2 replicated as select * from t1;（拷贝为复制表）

只选择必要的投影列

在编写SELECT语句时应遵循只选择有效投影列,发挥列存数据的优势降低IO。

尽量不用游标

游标是逐条遍历，数据量大时效率低下，建议优先使用SQL批量处理

能用union all尽量不用union

由于union操作需要进行一次去重，去重对于性能影响很大，尽量保证相同数据只入库一次，不同表间无重复数据，进行union all性能会很大提升

删除表中全部的数据时，如果可以使用TRUNCATE，不使用DELETE

性能优化建议
1. 表设计优化

表设计时根据表的数据量和用途选择创建分布表还是复制表。

对于数据量非常大的事实表，建议创建为hash分布表，并建议按照以下原则选择hash分布列：

在多表JOIN查询时，表中某列经常用于JOIN等值关联；
表中该列通常是等值查询的列，并且使用的频率很高；
做group by操作时，分组字段；
表中重复值较少的列，尽量让数据均匀分布。
1. 数据分布

一般来说，小表（维度表）可以被创建成复制表；一些表频繁参与JOIN查询且数据量不大情况下，也可以被创建成复制表。

GBase8a集群性能取决于各个节点整体的性能，每个节点存储的数据量对于集群性能有很大影响，为了尽可能达到最好的性能，所有的数据节点应该尽量存储等量的数据，因此在数据库表规划定义阶段要考虑表是复制表还是分布表，以及对分布表上的某一些列设置为分布列进行hash分布。

例如根据数据的分布特性设计，可以把字典表或者维度表建成复制表的方式将数据存储到各个节点上，不须对其数据进行分片存储，因为字典表的数据量相对较小，虽然在各个节点进行存储有一定的数据冗余，但和事实表的JOIN 运算就可在本地进行，避免节点间搬动数据。对于事实表（大表）可将数据分片到不同的节点上存储，分片方法可采用（round robin, hash）等不同方法，SQL执行的查询条件满足只在其中部分节点时，查询优化可决定SQL的执行仅在这些节点执行即可。

建Hash分布列的原则基本如下：

尽量选择count(distinct)值大的列做Hash分布列，让数据均匀分布。
优先考虑大表间的JOIN，尽量让大表JOIN条件的列为Hash分布列，以使得大表间的JOIN可以直接分布式执行。
其次考虑GROUP BY，尽量让GROUP BY带有Hash分布列，让分组聚合一步完成。
通常是等值查询的列，并且使用的频率很高的应考虑建立为hash分布列。
选择某数据列随机性很大的字段，避免部分节点的热查询。
1. 数据排序

数据在按某查询列进行排序后，则相同数据取值会集中存放在有限的数据包中，因此在以该列进行过滤时，利用智能索引命中的数据包会很少，不仅能降低IO量而且会提高压缩比。其最大好处是可以将智能索引的过滤效果发挥到最优，从而使整体查询性能大幅提升。建议在实际应用场景许可的前提下，将数据按照查询常用条件列进行排序。如在电信行业中，通常按照手机号码进行查询，因此可按一定的时间间隔对数据按照手机号码进行排序，则在此时间范围内的手机号码有序，在进行查询时，便可通过智能索引特性提高查询性能。

排序方式

外部排序：使用排序工具（psort）对数据文件进行排序，排序后使用加载工具加载至表内
库内排序：创建临时表，将未排序的数据先存储进临时表，再通过insert into select * … order by XXX方式将临时表内数据排序后插入正式表

排序方式适应场景

外部排序适合非实时加载的业务
库内排序适合实时加载业务
1. 投影列

GBase8a是一款列存数据库，在编写select语句时应遵循只选择有效投影列，对于无关的投影列应避免写入到select语句中，尤其要避免执行select * 这样的SQL语句，这样可以使得需要物化的列有效缩减，进而降低io成本，因此能有效提升查询性能。

SQL优化案例

任何数据库的优化器都不是完美的，当通过性能分析发现GBase 8a优化器执行方式存在问题时，很多场合下可以通过人为改写SQL的方式避免一些性能问题（相当于人工干预，帮助优化器按最有效率的方式来执行SQL）。

以下是几个SQL优化的案例，请参考！

大表关联join占用大量临时磁盘空间

描述：

执行某条sql，涉及到大表关联且join列重复值太多，这样会导致占用大量临时磁盘空间。

解决办法：

建议将join方式修改为union all 方式，并且将where条件进行切分处理，例如 where条件查询一个月数据，将其切分为3-4个时间段进行unin all关联查询。

update语法改写

描述：

大部分数据库支持批量字段的update，我们需要改写

如：update ods_product_high_mobile_sn a

set (brand_id,userstatus_id,arpu_08,call_duratiton_08)=

( select b.brand_id,b.userstatus_id, nvl(b.fact_fee,0) - nvl(b.INSTEAD_FEE,0), nvl(b.CALL_DURATION_M,0)

from tmp_high_mobile b where a.user_id=b.user_id)

解决办法：

改写为：

update ods_product_high_mobile_sn a inner join tmp_high_mobile b on a.user_id=b.user_id

set a.brand_id = b.brand_id ,

a.userstatus_id = b.userstatus_id,

a.arpu_08= nvl(b.fact_fee,0) - nvl(b.INSTEAD_FEE,0),

a.call_duratiton_08 = nvl(b.CALL_DURATION_M,0)。

执行sql中对一字段使用函数性能会变慢

描述：

集群执行sql中对一字段使用函数性能会变慢，例如如下sql

SELECT * FROM

access_cache

WHERE

to_char(RecordDate,'YYYYMMDD') = to_char(adddate(sysdate(),interval -1 day),'YYYYMMDD')

解决办法：

可以通过如下方式提高查询性能

SELECT * FROM access_cache WHERE RecordDate

between (to_char(adddate(sysdate(), INTERVAL -1 DAY), 'YYYY-MM-DD')|| ' 00:00:00')

and (to_char(adddate(sysdate(), INTERVAL -1 DAY), 'YYYY-MM-DD')|| ' 23:59:59')。

三张表实时查询并union后进行order排序，查询时间不理想

描述：

由于实时查询需要对三张表同时查询，应用开发时将查询sql对三张表查询并union后进行order排序，查询时间不理想。

解决办法：

主要分两个部分解决。第一个是union的问题，由于通过有效标志位解决了数据查询结果可能重复的问题，直接将union改写为union all。第二个是需要对查询出来的全部结果进行order排序，对资源消耗较多。改为对每个查询结果分别排序，同时在union all的时候注意以月表、昨天表、当天表的顺序排列，解决了整体上排序的问题。

Where 条件中需要日期判断的sql优化

所有语句的Where 条件中，需要将日期判断的值加上to_date函数，即：

to_date(optime)='2013-10-03'

修改为：

optime = to_date('2013-10-03','YYYY-MM-DD')。

两个并列的查询中聚集函数的优化

两个并列的查询中都使用了聚集函数，如distinct a,b，然后又对这两个查询进行了union去重操作，如果distinct不能聚掉很多数据的话（例如对 product_no这种离散度很高的列），可以考虑把distinct直接去掉，仅用union做一次数据去重即可。

关联时过滤条件的优化

现场测试中有类似于示例中的SQL，a表与b表进行内关联，并且有 a.c2 between b.c3 and b.c4条件，所以可以推出b.c3的取值范围应该是b.c3<=10，b.c4的取值范围是b.c4>=5，所以现在改写时加上了这两个条件，减少了b表参与运算的数据量提高了SQL性能。

例：

select a.c1 , b.c2

from a join b

on a.c1 = b.c1

where a.c2 between b.c3

and b.c4

and a.c2 >= 5

and a.c2 <= 10

优化后需要添加：

And b.c3<=10

And b.c4>=5

关联字段重复值过高的优化

在多表关联然后做分组统计的sql中，经常会碰到关联字段有重复值的情况，当两边的关联字段都存在相同重复值时，会导致关联结果集指数级的增长，此时sql执行效率往往会非常低，数据库对此类sql无法做到自动优化（数据库只能是先关联再对关联结果集进行分组统计），此时就只能是人工分析后手动改写sql，进行对重复值较多的表先分组再关联，这样就不会出现在关联时结果集指数增长的情况，如此次测试就有如下两条sql改写优化示例。

例1：

select a.trd_dt,

b.inv_cd,

(case when b.sec_cd between '000001' and '001999' then '1主板'

when b.sec_cd like '002%' then '2中小板' else '3创业板' end) sec_typ ,

sum((b.SHR_QTY +b.CHG_QTY)*ac.LCLOS_PRC) shr_val

from dt2 a, wwtnishc b, QUOTAT c

where a.trd_dt between rec_fdt and rec_edt

and a.next_dt = c.trd_dt

and b.sec_cd = c.sec_cd

and (b.sec_cd like '00%' or b.sec_cd like '30%')

group by ac.trd_dt, b.inv_cd ,

(case when b.sec_cd between '000001' and '001999' then '1主板' when b.sec_cd like '002%' then '2中小板' else '3创业板' end);

改写后如下：

select ac.trd_dt, b.inv_cd ,

(case when b.sec_cd between '000001' and '001999' then '1主板'

when b.sec_cd like '002%' then '2中小板' else '3创业板' end) sec_typ ,

sum((b.SHR_QTY +b.CHG_QTY)*ac.LCLOS_PRC) shr_val

from

wwtnishc b ,

(select a.trd_dt,c.sec_cd,sum(c.LCLOS_PRC) LCLOS_PRC

from dt2 a,QUOTAT c

where a.next_dt=c.trd_dt

group by c.sec_cd,a.trd_dt)ac

where ac.trd_dt between rec_fdt and rec_edt and b.sec_cd=ac.sec_cd

and (b.sec_cd like '00%' or b.sec_cd like '30%' )

and rec_fdt<=(select max(trd_dt) from dt2) -- 增加

and rec_edt>=(select min(trd_dt from dt2) -- 增加

group by ac.trd_dt, b.inv_cd ,

(case when b.sec_cd between '000001' and '001999' then '1主板' when b.sec_cd like '002%' then '2中小板' else '3创业板' end);

此sql做了两种优化，一个是将ac先关联分组后再与b表关联，另一个是将根据比较条件传值过滤减少数据量，使得sql耗时从原来的1小时零几分钟优化到几分钟，性能整整提升1小时。

例2：

SELECT a.zqdh,

a.bgddm,

min(a.cjxh) AS cjxh,

sum(a.cjgs) AS cjgs,

b.inv_cd,

xw.seat_nm,

zh.inv_nm,

zh.inv_kind

FROM dwcjk a

JOIN wwtnishc b

ON a.zqdh = b.sec_cd

AND a.bgddm = b.inv_cd

AND a.cjrq BETWEEN b.rec_fdt AND b.rec_edt

JOIN dwzqxx c

ON b.sec_cd = c.zqdh

LEFT JOIN wwtnseat xw

ON a.BXWDH = xw.seat_cd

AND xw.seat_edt = @ed_seat

LEFT JOIN wwtnmmbr hy

ON xw.mbr_cd = hy.mbr_cd

AND hy.mbr_edt = @ed_mbr

LEFT JOIN wwtnbrch yz

ON yz.mbr_cd = xw.mbr_cd

LEFT JOIN swtnialk zh

ON zh.inv_cd = a.bgddm

WHERE a.cjsj >= @st_cjsj

AND a.cjsj <= @ed_cjsj

AND a.cjgs >= @cjgs

AND a.zqdh = @zqdh

AND a.cjrq >= @st_cjrq

AND a.cjrq <= @ed_cjrq

GROUP BY a.zqdh, a.bgddm, b.inv_cd, zh.inv_nm, zh.inv_kind, xw.seat_nm

ORDER BY sum(a.cjgs) DESC LIMIT 10000;

改写后：

SELECT a.zqdh,

a.bgddm,

min(a.cjxh) AS cjxh,

sum(a.cjgs * yz.ct) AS cjgs, -- 修改

b.inv_cd,

xw.seat_nm,

zh.inv_nm,

zh.inv_kind

FROM dwcjk a

JOIN wwtnishc b

ON a.zqdh = b.sec_cd

AND a.bgddm = b.inv_cd

AND a.cjrq BETWEEN b.rec_fdt AND b.rec_edt

JOIN dwzqxx c

ON b.sec_cd = c.zqdh

LEFT JOIN wwtnseat xw

ON a.BXWDH = xw.seat_cd

AND xw.seat_edt = @ed_seat

LEFT JOIN wwtnmmbr hy

ON xw.mbr_cd = hy.mbr_cd

AND hy.mbr_edt = @ed_mbr

LEFT JOIN (select mbr_cd, count(*) ct from wwtnbrch group by mbr_cd) yz

ON yz.mbr_cd = xw.mbr_cd -- 修改

LEFT JOIN swtnialk zh

ON zh.inv_cd = a.bgddm

WHERE a.cjsj >= @st_cjsj

AND a.cjsj <= @ed_cjsj

AND a.cjgs >= @cjgs

AND a.zqdh = @zqdh

AND a.cjrq >= @st_cjrq

AND a.cjrq <= @ed_cjrq

and b.rec_edt >= @st_cjrq -- 增加

and b.rec_fdt <= @ed_cjrq -- 增加

GROUP BY a.zqdh, a.bgddm, b.inv_cd, zh.inv_nm, zh.inv_kind, xw.seat_nm

ORDER BY sum(a.cjgs * yz.ct) DESC -- 修改

LIMIT 10000;

此sql也进行了两种改写优化，一种是yz表先分组统计后再进行关联，极大减少了关联结果（每个节点数据从几百亿减少到几亿），另一个是将根据比较条件传值过滤减少数据量，此sql的改写使得测试整体耗时减少约10小时。

投影列带子查询时的优化

现场遇到如下类型的sql：

SELECT khh,(select mc from jgdy where jgm=jg.sjjgm) c3,n.jgm jgm,

SUM(jyje) jyje,SUM(jybs) jybs,

SUM(sxf) sxf,SUM(dlcs) dlcs,SUM(FEE1) FEE1,SUM(FEE2) FEE2

FROM nb_qymxb n inner join jgdy jg on jg.jgm=n.jgm ....

此类sql的投影列带查询，需现场根据结果集数据量来对sql进行修改，如果结果集较小的情况下，可以不用修改sql，性能不会受到影响，如果结果集较大的情况下，就需要将投引列中的查询修改放到下面的表关联中，如下：

SELECT khh, jy.mc c3,n.jgm jgm,

SUM(jyje) jyje,SUM(jybs) jybs,

SUM(sxf) sxf,SUM(dlcs) dlcs,SUM(FEE1) FEE1,SUM(FEE2) FEE2

FROM nb_qymxb n inner join jgdy jg on jg.jgm=n.jgm

Left join jgdy jy on jgm=jg.sjjgm

而至于具体什么数据量下那种更快，需要根据不同sql、不同关联数据量、不同结果集现场测试才能确定。

修改group by列的顺序提升性能

现场sql：insert into table select FROM tab WHERE CITY_ID = 430 GROUP BY CITY_ID,...

group by第一列为常量，第一步数据重分布后集中到一个节点；重分布时进行了除第一列CITY_ID外的group by；第二步聚集运算，再次进行了包含CITY_ID的group by已经没必要，结果集不会发生变化。

方案：使用distinct值多的列作为group by第一列，现场sql从过去6小时降低为现在的1小时。提高6倍性能；

登录后才可以发表评论

levvel发表于 8个月前

感谢分享

GBase8a开发使用建议

评论

热门帖子