大数据分析表设计与优化

uwb定位| 2022-09-07

基于MPP架构的Greenplum数据库在面向分析的应用中有着得天独厚的优势,但使用不当你一定会遇到很多问题。

本文主要介绍了greenplum数据库在表设计上的基础原则和使用过程中的一些优化建议。希望能给大家在使用greenplum的过程中有所收益,尽可能的降低你遇到问题的概率。

Greenplum是什么

简单的说,Greenplum数据库就是面向数据仓库应用的关系型数据库,它基于目前流行的PosgreSQL开发,通过标准的SQL就可以对Greenplum中的数据进行访问存取。

它主要用在数据仓库中,适用于面向分析的应用。 例如,数据分析,统计报表,数据可视化以及大数据计算等场景。

本质上讲,Greenplum是一个关系型数据库集群. 它实际上是由数个独立的数据库服务组合成的逻辑数据库。

与 ORACLE RAC的Shared-Storage架构不同,Greenplum采用的是Shared-Nothing架构,它根据表的分发列(distribute column),将一个表中的数据平均分布到每个节点上。

整个集群由多个数据节点(segment节点)和控制节点(master节点)组成,控制节点只存储一些数据库的元数据,不负责运算,它仅仅负责应用的连接,生成拆分执行计划,并把执行计划分配给数据节点去执行。

这种基于Shared-Nothing的分布式存储架构和MPP(Massive parallel processing)的大规模并行处理架构,使得Greenplum数据库能够极大地提高I/O吞吐和并发计算能力,并且随着数据节点的增加,可线性提高系统的存储容量,I/O吞吐和处理能力。

Greenplum表设计以及优化

本文的优化主要针对select 语句,以下的案例均为真实案例

表的类型

在gp设计中,还是遵循其他关系数据库的原则,字段需要合理设计。例如:如果md5值,我们就没有必要为了简单存储到text里。

另外,如果是join 联合几个表,那么一定要确保联合字段的类型一样

表的分布列设计

这个是gp表设计的重中之重,如下一些标准,供参考

1)确定表里有没有一个字段的数据是离散的,例如:类似mysql里的主键、唯一健,这个字段可以做为表的分布列;

2)如果满足第1条,并且如果这个字段在join关系里,这个字段可以做为表的分布列。

【真实案例,这个提升性能50%,原因:每个segment都能在自己的segment上join,避免数据motion】

3)如果不满足1和2,那么我们就设置DISTRIBUTED RANDOMLY,让表的数据完全随机离散开,也能提供不错的性能。

【真实案例:有人选择一个业务id做为分布列,但是因为个别业务的访问量巨大,而其他又特别小,那就出现了数据倾斜现象,造成性能特别的差】 

注: select gp_segment_id,count(*) from table_name group by 1 order by 1; #查看表在各个segment的分布情况

关于索引

官方文档一直建议:gp尽量避免添加索引,官方解释是:MPP架构,会让所有segment同时执行扫描数据操作,都是顺序IO,特别快。

其实不然,如果我们用线上关系数据库的方式去查询(例如:组合等值查询 where  name='张三' and status=1),我们还是需要添加 (name,status) 的索引。

create index idx_name on user(name,status)

【用线上关系型数据库思维去设计SQL,那么就需要用线上数据库思维来解决问题】

空洞回收

主要来源于一业务:一个SQL早期速度很快,但是后期速度特别特别的慢,表的行数整体并没有太多变化。

经过沟通了解到,该业务经常delete数据,而gp并没有回收delete的数据空间。

这个时候,我们需要重建表,MPP架构下的数据库表重建速度特别快,测试表明:9T数据,都只需要分钟级别(vacuum 并不靠谱)。

重建表方式:

(以user表为例)

create table user_new as select * from user;

rename table user to user_bak;

renmae table user_new to user;

针对SQL语句的优化

不能在一颗树上吊死

在日常的运维中,我们遇到过业务反馈如下报错:

ERROR:  insufficient memory reserved for statement (tuplesort_mk.h:115)

报错原因一般是由于业务执行了类似下面的SQL:

select count(distinct m3) from up_old_full ; 中间所需要的内存过大,出现错误。

我们一般会建议业务对SQL语句进行如下修改:

select count(*) from (select m3 from up_old_full group by m3)a ; 分两步走,避免上述问题。

能过滤的一定要过滤

根据需求,能在单个segment过滤数据的,一定要过滤。

例如:如下两条SQL,性能差别几十倍,唯一区别就是分析业务加了一个device字段。

select count(*) from info_push_realtime where act in('arrived','show','click') and device in('0','1') group by sign,pushid,style;

select count(*) from info_push_realtime where act in('arrived','show','click')  group by sign,pushid,style;

针对join的优化

我们需要按照如下几个原则:

join的表的字段务必相同类型

尽量让join的字段是 DISTRIBUTED键值

尽管有时候key分布不太均衡,我们也需要这样做,这样保证join的时候在通过一个segment,而不是全局motion redirect。

针对insert的优化

常情况下,标准的insert语句,只涉及到语法分析以及语句执行,理论不会执行慢,但是当访问量过多的时候,达到了resoure queue,insert就出现等待,造成堵塞。

这里我们设置resource_select_only=1,resoure queue的限制只限于select语句,达到了在实时写入系统的快速insert效果。

另外如果大量insert,但是又不太方便转换成load,那就只能使用HULK DBA团队与基础架构组联合开发的gpstall工具,加速insert。

后续我们会开源一套gpdb的工具集,包括,加速写入gpstall,集群之间的迁移gptransfer、mysql到gpdb的实时同步gpmysqlload、gp快速备份gpbakcup。

关于update/delete

超级慢,慢的你受不了。要尽量避免数据的更新删除。

关于prepare语句

Server prepare会特别的慢。在PHP PDO中,务必把PDO::ATTR_EMULATE_PREPARES 设置为TRUE。

网卡选型的建议

在机器选型中,尽量要用万M网卡,如果没有万M网卡,至少也要选择多网卡联合工作,否则segment节点间的大量数据传输和聚合,网卡会成为瓶颈。

segment 均衡策略

gpdb的mirror策略分为group mirror 和spread mirror。默认创建集群的mirror的策略为group mirror。

使用spread mirror策略优势是,如果A服务器宕机,压力不会全部集中到B服务器上,降低因为故障导致的集群性能瓶颈,甚至因为B机器压力过大,接连发生宕机导致集群雪崩。

它的劣势是A机器宕机后,A机器的segment节点的mirror节点所在的机器不能再宕机,否则会有segment和mirror节点同时宕机的情况,造成集群不可用。

在后续的文章中,我们会继续给大家更进一步的介绍gpdb的使用经验,以及加速写入中间层gpstall,mysql到gpdb的实时同步gpmysqlload,gp快速备份gpbakcup等工具的使用。欢迎大家继续关注。