大數(shù)據(jù)分析表設(shè)計與優(yōu)化

| 2022-09-07 admin

基于MPP架構(gòu)的Greenplum數(shù)據(jù)庫在面向分析的應(yīng)用中有著得天獨厚的優(yōu)勢,但使用不當你一定會遇到很多問題。

本文主要介紹了greenplum數(shù)據(jù)庫在表設(shè)計上的基礎(chǔ)原則和使用過程中的一些優(yōu)化建議。希望能給大家在使用greenplum的過程中有所收益,盡可能的降低你遇到問題的概率。

Greenplum是什么

簡單的說,Greenplum數(shù)據(jù)庫就是面向數(shù)據(jù)倉庫應(yīng)用的關(guān)系型數(shù)據(jù)庫,它基于目前流行的PosgreSQL開發(fā),通過標準的SQL就可以對Greenplum中的數(shù)據(jù)進行訪問存取。

它主要用在數(shù)據(jù)倉庫中,適用于面向分析的應(yīng)用。 例如,數(shù)據(jù)分析,統(tǒng)計報表,數(shù)據(jù)可視化以及大數(shù)據(jù)計算等場景。

本質(zhì)上講,Greenplum是一個關(guān)系型數(shù)據(jù)庫集群. 它實際上是由數(shù)個獨立的數(shù)據(jù)庫服務(wù)組合成的邏輯數(shù)據(jù)庫。

與 ORACLE RAC的Shared-Storage架構(gòu)不同,Greenplum采用的是Shared-Nothing架構(gòu),它根據(jù)表的分發(fā)列(distribute column),將一個表中的數(shù)據(jù)平均分布到每個節(jié)點上。

整個集群由多個數(shù)據(jù)節(jié)點(segment節(jié)點)和控制節(jié)點(master節(jié)點)組成,控制節(jié)點只存儲一些數(shù)據(jù)庫的元數(shù)據(jù),不負責運算,它僅僅負責應(yīng)用的連接,生成拆分執(zhí)行計劃,并把執(zhí)行計劃分配給數(shù)據(jù)節(jié)點去執(zhí)行。

這種基于Shared-Nothing的分布式存儲架構(gòu)和MPP(Massive parallel processing)的大規(guī)模并行處理架構(gòu),使得Greenplum數(shù)據(jù)庫能夠極大地提高I/O吞吐和并發(fā)計算能力,并且隨著數(shù)據(jù)節(jié)點的增加,可線性提高系統(tǒng)的存儲容量,I/O吞吐和處理能力。

Greenplum表設(shè)計以及優(yōu)化

本文的優(yōu)化主要針對select 語句,以下的案例均為真實案例

表的類型

在gp設(shè)計中,還是遵循其他關(guān)系數(shù)據(jù)庫的原則,字段需要合理設(shè)計。例如:如果md5值,我們就沒有必要為了簡單存儲到text里。

另外,如果是join 聯(lián)合幾個表,那么一定要確保聯(lián)合字段的類型一樣

表的分布列設(shè)計

這個是gp表設(shè)計的重中之重,如下一些標準,供參考

1)確定表里有沒有一個字段的數(shù)據(jù)是離散的,例如:類似mysql里的主鍵、唯一健,這個字段可以做為表的分布列;

2)如果滿足第1條,并且如果這個字段在join關(guān)系里,這個字段可以做為表的分布列。

【真實案例,這個提升性能50%,原因:每個segment都能在自己的segment上join,避免數(shù)據(jù)motion】

3)如果不滿足1和2,那么我們就設(shè)置DISTRIBUTED RANDOMLY,讓表的數(shù)據(jù)完全隨機離散開,也能提供不錯的性能。

【真實案例:有人選擇一個業(yè)務(wù)id做為分布列,但是因為個別業(yè)務(wù)的訪問量巨大,而其他又特別小,那就出現(xiàn)了數(shù)據(jù)傾斜現(xiàn)象,造成性能特別的差】 

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

關(guān)于索引

官方文檔一直建議:gp盡量避免添加索引,官方解釋是:MPP架構(gòu),會讓所有segment同時執(zhí)行掃描數(shù)據(jù)操作,都是順序IO,特別快。

其實不然,如果我們用線上關(guān)系數(shù)據(jù)庫的方式去查詢(例如:組合等值查詢 where  name='張三' and status=1),我們還是需要添加 (name,status) 的索引。

create index idx_name on user(name,status)

【用線上關(guān)系型數(shù)據(jù)庫思維去設(shè)計SQL,那么就需要用線上數(shù)據(jù)庫思維來解決問題】

空洞回收

主要來源于一業(yè)務(wù):一個SQL早期速度很快,但是后期速度特別特別的慢,表的行數(shù)整體并沒有太多變化。

經(jīng)過溝通了解到,該業(yè)務(wù)經(jīng)常delete數(shù)據(jù),而gp并沒有回收delete的數(shù)據(jù)空間。

這個時候,我們需要重建表,MPP架構(gòu)下的數(shù)據(jù)庫表重建速度特別快,測試表明:9T數(shù)據(jù),都只需要分鐘級別(vacuum 并不靠譜)。

重建表方式:

(以user表為例)

create table user_new as select * from user;

rename table user to user_bak;

renmae table user_new to user;

針對SQL語句的優(yōu)化

不能在一顆樹上吊死

在日常的運維中,我們遇到過業(yè)務(wù)反饋如下報錯:

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

報錯原因一般是由于業(yè)務(wù)執(zhí)行了類似下面的SQL:

select count(distinct m3) from up_old_full ; 中間所需要的內(nèi)存過大,出現(xiàn)錯誤。

我們一般會建議業(yè)務(wù)對SQL語句進行如下修改:

select count(*) from (select m3 from up_old_full group by m3)a ; 分兩步走,避免上述問題。

能過濾的一定要過濾

根據(jù)需求,能在單個segment過濾數(shù)據(jù)的,一定要過濾。

例如:如下兩條SQL,性能差別幾十倍,唯一區(qū)別就是分析業(yè)務(wù)加了一個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的優(yōu)化

我們需要按照如下幾個原則:

join的表的字段務(wù)必相同類型

盡量讓join的字段是 DISTRIBUTED鍵值

盡管有時候key分布不太均衡,我們也需要這樣做,這樣保證join的時候在通過一個segment,而不是全局motion redirect。

針對insert的優(yōu)化

常情況下,標準的insert語句,只涉及到語法分析以及語句執(zhí)行,理論不會執(zhí)行慢,但是當訪問量過多的時候,達到了resoure queue,insert就出現(xiàn)等待,造成堵塞。

這里我們設(shè)置resource_select_only=1,resoure queue的限制只限于select語句,達到了在實時寫入系統(tǒng)的快速insert效果。

另外如果大量insert,但是又不太方便轉(zhuǎn)換成load,那就只能使用HULK DBA團隊與基礎(chǔ)架構(gòu)組聯(lián)合開發(fā)的gpstall工具,加速insert。

后續(xù)我們會開源一套gpdb的工具集,包括,加速寫入gpstall,集群之間的遷移gptransfer、mysql到gpdb的實時同步gpmysqlload、gp快速備份gpbakcup。

關(guān)于update/delete

超級慢,慢的你受不了。要盡量避免數(shù)據(jù)的更新刪除。

關(guān)于prepare語句

Server prepare會特別的慢。在PHP PDO中,務(wù)必把PDO::ATTR_EMULATE_PREPARES 設(shè)置為TRUE。

網(wǎng)卡選型的建議

在機器選型中,盡量要用萬M網(wǎng)卡,如果沒有萬M網(wǎng)卡,至少也要選擇多網(wǎng)卡聯(lián)合工作,否則segment節(jié)點間的大量數(shù)據(jù)傳輸和聚合,網(wǎng)卡會成為瓶頸。

segment 均衡策略

gpdb的mirror策略分為group mirror 和spread mirror。默認創(chuàng)建集群的mirror的策略為group mirror。

使用spread mirror策略優(yōu)勢是,如果A服務(wù)器宕機,壓力不會全部集中到B服務(wù)器上,降低因為故障導致的集群性能瓶頸,甚至因為B機器壓力過大,接連發(fā)生宕機導致集群雪崩。

它的劣勢是A機器宕機后,A機器的segment節(jié)點的mirror節(jié)點所在的機器不能再宕機,否則會有segment和mirror節(jié)點同時宕機的情況,造成集群不可用。

在后續(xù)的文章中,我們會繼續(xù)給大家更進一步的介紹gpdb的使用經(jīng)驗,以及加速寫入中間層gpstall,mysql到gpdb的實時同步gpmysqlload,gp快速備份gpbakcup等工具的使用。歡迎大家繼續(xù)關(guān)注。