大数据分析利器-Greenplum 表设计与优化
女主宣言 基于MPP架构的Greenplum数据库在面向分析的应用中有着得天独厚的优势,但使用不当你一定会遇到很多问题。 本文主要介绍了greenplum数据库在表设计上的基础原则和使用过程中的一些优化建议。希望能给大家在使用greenplum的过程中有所收益,尽可能的降低你遇到问题的概率。 PS:丰富的一线技术、多元化的表现形式,尽在“HULK一线技术杂谈”,点关注哦! 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所支持的数据类型的详细信息请参考:http://www.php100.com/manual/PostgreSQL8/datatype.html 表的分布列设计 这个是gp表设计的重中之重,如下一些标准,供参考 1)确定表里有没有一个字段的数据是离散的,例如:类似mysql里的主键、唯一健,这个字段可以做为表的分布列; 2)如果满足第1条,并且如果这个字段在join关系里,这个字段可以做为表的分布列。 【真实案例,这个提升性能50%,原因:每个segment都能在自己的segment上join,避免数据motion】 3)如果不满足1和2,那么我们就设置DISTRIBUTED?RANDOMLY,让表的数据完全随机离散开,也能提供不错的性能。 注:?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语句的优化 不能在一颗树上吊死 在日常的运维中,我们遇到过业务反馈如下报错: 报错原因一般是由于业务执行了类似下面的SQL: 我们一般会建议业务对SQL语句进行如下修改: 能过滤的一定要过滤 根据需求,能在单个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; 针对join的优化 我们需要按照如下几个原则:
尽管有时候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等工具的使用。欢迎大家继续关注。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |