Vertica數(shù)據(jù)查詢優(yōu)化

Vertica數(shù)據(jù)查詢優(yōu)化vertica是惠普公司推出的列式分布式數(shù)據(jù)庫，在OLAP領(lǐng)域有其獨到的地方，目前社區(qū)版免費，但是只能存放1T的數(shù)據(jù)。我在工作中維護(hù)的bi系統(tǒng)后端就是使用的vertica數(shù)據(jù)庫，平時也經(jīng)常需要對于數(shù)據(jù)庫的查詢進(jìn)行一些優(yōu)化。所以寫下這篇博客記錄一下。1.定位問題所謂的數(shù)據(jù)庫調(diào)優(yōu)、程序優(yōu)化之類的工作，實際上是一個解決問題的過程，而解決問題，第一部就是需要定位問題。找到問題的手段多種多樣，可以通過分析程序、監(jiān)控生產(chǎn)上服務(wù)器的性能、定期生成數(shù)據(jù)庫的負(fù)載報告等手段，而最不應(yīng)該的就是通過生產(chǎn)上用戶的反饋來反映問題了，因為到了那個時候，一切都已經(jīng)晚了。具體到vertica來說，通過QUERY_PROFILES這個數(shù)據(jù)庫本身提供的視圖，可以找到耗時和執(zhí)行的多的sql語句。以下三條sql語句，分別統(tǒng)計出執(zhí)行次數(shù)topio，單次執(zhí)行耗時topio，執(zhí)行總耗時topio的sql語句。SELECTquery,count(*)astimesFROMQUERY_PROFILESWHEREquery_type='QUERY'andquery_start>='2015-02-13'groupbyqueryORDERBYtimesDESClimit10;SELECTquery,avg(query_duration_us)asavg_costFROMQUERY_PROFILESWHEREquery_type='QUERY'andquery_start>='2015-02-13'groupbyqueryORDERBYavg_costDESClimit10;SELECTquery,

sum(query_duration_us)astotal_costFROMQUERY_PROFILESWHEREquery_type='QUERY'andquery_start>='2015-02-13'groupbyqueryORDERBYtotal_costdesclimit10;2.3.分析問題數(shù)據(jù)庫調(diào)優(yōu)，其實非常依賴于數(shù)據(jù)庫本身提供的各種性能分析工具，例如執(zhí)行計劃解釋器，跟著profile工具。在vertica中，可以通過profile，分析一條具體的sql語句。我們分析一條第一步中獲取到的sql語句：■JI■Jiy111—— d^yyqame=>nrofliesplectj;um(T3-J01z.3.p,loq1n_id_ratal")msrl,surn(T330143."majt-jonlTne-TOTal"^asc2,SLmCT33O143."roleia_coial,r)ascm.i^16736,1,idaceT'asc4?T516797."gamename1'asT516797.'game"asc6from"dinugame_&erverr,T316797,"d-inL_cateffT316786,YqIe_log-in_orline"T3301^3where<T316786."tdate11-T33OL43.irdt,randT316797.,rnameir-T33O143.,rgame"andT2167B6."tdate,r-'201斗-DH-02"andT316757.,,qame_￡erver"-T230143."gaiiic.server'andT33O143."dt"-'201^1-03-02')groupbyTJ16786. TJ167&7.,Pgine"?TJ167&7."game_njnne,r;noticea/8a：statemenr1sbedngproflledhint：^ele匸t*J'-oniv_mon1cor-.execuTlon_eng1re_profl1h-^wherei:ransacc1on_fd=45035&962S2563976and5Latenent_-id=Z;NOTICE3557;InitiatormemDryforquery:「onpoolgeneral;2d5697KB,rndninium;2015697KB]HINT：EgIc匚t*亠“oinv_m0nii0r.o)LQCiJt_iDn_Qrig_int_prafilQ￡wheretransacilan_id-45O35&&628256M76andstateinentz_-id^-1;NOTICE35S7：inltiatormEmoryfarqupry:[onpoolgensrml:20CL5697KB,mlnlmum:Z015697KB]NOTICE〔。亍了：Tutklm亡iioi~￥「亡口ui「^dhycuErv：[2012百爭了€呂]獲取到這個語句的transcation_id和statement_id以后，可以通過查詢系統(tǒng)表query_plan_profiles獲得語句實際的執(zhí)行計劃和各個階段的執(zhí)行時間，這個不同于執(zhí)行計劃，這是真實的執(zhí)行過程。如圖：kUlhllitIiITk4-CROUIWH應(yīng)口(LOCAL11SU3MGKTGIKJUIftIX耐,曲存L慚:叱口ATI和CSJI(FrtTHfCh])luJJI訕

iixiJi

(iiJIlllJIIluJJI訕

iixiJi

(iiJIlllJIIlidll

Hullli wJ*m；T$ _i-i_wtai■>sumIT 2<jhe_oiiw_x?J；\iui>TiSflL斗$Md問Ii Ev汀對何SidiH：.Til阿?jpnjh丁iL刪?翻+—> WFEGElaiNMrpjItpieKiltdl[Gwl：25K,.&:■?：ll^￥.mJTftTETICS]IFftTHI氐釘loinCwrf!EIB7盯即1址■H3QJU5-MM-)AHD>7131(-797^nnt.WiW-T3UJ<3；!II hhK■訛心dilr^riLT剽山乂日世片口JCvHE■?數(shù)ci<：&nwII XiKii皿川3中口1330屈血貝阿T3iOL4U^ifi_iLieUI133R4■如也?沁jtdII+-芒比1T『口陽kJIROLIDTHGif^ef-beiIkIJIChIz'iK,Rtm=J(M?TATTfi7TCS：l|<tATHTO:-QHIloini>nd;CB1阿丿啊二T3弟HMDHIt-CJUd ftDCf?haT5?J+3(M2L.Rm+HiNtUMTIfTICSiltFWTH1比町II11 hc+Khccc呎]knil 弗股”加iII11 rTMDLildr-2DLMJJX1」Illi Kui皿Flbn：剛畤kUtTUDMZdH,坤皿lit W：T33UL414^u：_fiunf^&IFXHU問fa”T3JQL相卻血Ti30143.RlM_BtfM]li：ii?￡CUMJ)i：ft5L-：iIIIiLuu<闕出HE±E血J3L頌&|C-.l7.皿■:SK■陽Q3TATt=TIC^]ff.ATHIS5J巾訊圉巾訊圉ll■+..taiflHDJ:ADEACZESEfcfTJL6W|O?tM.F.0WT：活KCHO5IATI5TICD]IFATHItrfeIII Fk?.kfcfi：jtii11:Jim_|ine^MTiiei?3JIII Hkkilab茁rMl商如暑ik.T3]bW-ipritjtTKte”I an*sql的執(zhí)行是從下往上的，在這個表里面列出了PATHID,我們可以從PATHID從大到小一步一步分析，每一步的執(zhí)行耗時。注意PATHID：4這一步，查詢了一張事實表，cost是2K,處理了4M的數(shù)據(jù)。這一步就是我們分析的重點，因為它排在執(zhí)行步驟的較前面并且處理了較多的數(shù)據(jù)。4.解決問題通過運行analyze_wordload，可以得到對某個表具體的優(yōu)化建議。我們對，PATHID：4的這個步驟查詢的事實表，進(jìn)行分析，可以得到如下優(yōu)化建議：|■u*a*T?ix? ecu.papy-Bc-3jrnmrHinp-xc："XE-'y-a'-'-■sBru'-cajrn?■ |H■丁?好：L|i-iZDIJlK|trnrwi au4rvpj?tnp-Tte'xaf*yEWIrirnu丁孑ltvjHlCfi■F*nu*nu曠 h=21ft口ufxiUTiid-==acmr^|4rJiir3iid.-'cMp其中第一條指的是，運行vertica提供的databasedesigner工具，對這個事實表建立映射，此方法代價比較大，而且只能對特定的查詢優(yōu)化，這里進(jìn)行第二條操作，對于此事實表進(jìn)行分析，得到它的統(tǒng)計信息。ame=>selectanalyze_stafistics('pub'llc.role_logiH-online.dt')；yze_stafistics0(1row)這條命令，只會訪問此表10%的數(shù)據(jù)，返回0表示成功。進(jìn)行了統(tǒng)計信息之后，重新執(zhí)行第1步和第2步，得到新的計劃如下：nnni>Ktimepslbline"H3MUPBYOSHCLOC^iLKESEOMBM7OROU尚[gl;519,抽啊孰!NO3TA7ISTECS1]CTAlHIDs1)Ijrtil]1已跡亡町出：:umi血i1Bnra>Ey:TlLffTafiildik,1316737-43^T3[旳們皿加丿由伽14■>JCCNME陽印ODT創(chuàng)?呃駅紳1紳ICctC-?：9.險陽旳1WJTATC5TK31]ffATHB②Wl)IIJainCer止i7316797^]mt=T妙]】4玉￡iine：i/iKDCB】EC硏劇me_server=1330J.41esme 1Oral〕HlUi4Tidli^ikipui:TSSOLiSd,1"羽0141^幅三11§&1站沁仙」6佯殂 J4_toisl,T2SD143.hfh>J L4S.mfiH：_-snli_toill晌II函Hiiili肉刨0H|利l；T31C79T序UUiMiEllf-■on?*5ICIRMEA0ZESJftirT31OT?ICccl:?.Zu33K(MOSTA7ISTKS](MTHED:3)Oral)iiifix:如ikflm閃上*汕血jp加亠了聞」iiptfr伽即II!隔槪謝曲：T3」CW魯加pTJlWg?翻綽陽(millIllRurrtinitFiFlwsr(STFJ(tfTEekin上T3167??.paraie^(SnTlMfrreJoin):T3167?742mit_wri￥i)!.fSllh30^iB|!,,orii)：1316797腳血.T3L6797_g3rwjserw1価曲加炳安111-hubFOINME：F：GElC(IFaiMyn丙口對弍i[口Mi:[迄船彌：LHE.i>3刃內(nèi)T侶TJWj](FUEHEDCRDUFINgJ(FftTH：l：D-4?(milIllJoinOXKl：刪阿厲如二n?143dJJ0MflM0j?7579III4-Od￥i仝STORAGEACCESSfee1330143KhfcM.Jiii■:1E8CJ(PftTHFfc5)(nul)Illi[^□kdxxn:pTstliud*_l￡iiI4itc^sujia血IlliIjralJIlliPda;IT330丄陽泄=■腳WJM驅(qū)：iifluliHliRinurriAFiljw:(XlF4<ktaatlaiAi：T130143dOQ她>00頗5711114-mier->5TC^A0&ACCESS[IXJilCi?I0ME7.ROWE3KINDSTATJST[C5l)fTA7HID6l^jul)IlliPiojcctknr[ra'Nk.din3_diijjwpjj^(nul)Illi仙小]血:TSifiT&fijjdMiIlliEdtffl!113167^.^=^140.^1可以觀察到，執(zhí)行步驟被調(diào)整了，原來PATHID：4的步驟比較耗時，現(xiàn)在被提前到PATHID：5了，而且執(zhí)行的成本和消耗資源也不一樣，以下是詳細(xì)對比：優(yōu)化之前：|||+--Outer->STORAGEACCESSforT330143[Cost：2K,Rows：4M(NOSTATISTICS)](PATHID：4)優(yōu)化之后：|||+--Outer->STORAGEACCESSforT330143[Cost：94,Rows：18K](PATHID：5)?？梢?，對于事實表的dt自動進(jìn)行分析以后，通過dt字段獲取數(shù)據(jù)，掃描行數(shù)從4M減少到了18k，cost從2k減少了94，整個sql的執(zhí)行時間也從0.34秒降低到了0.17秒。至此，此次優(yōu)化得到了目的（執(zhí)行時間減少了50%）。分析背后的機(jī)制為什么Vertica數(shù)據(jù)庫獲取了統(tǒng)計信息以后，就可以優(yōu)化查詢？因為這張事實表是按照dt字段進(jìn)行分區(qū)，但是在沒有統(tǒng)計信息的時候，即使查詢條件帶上了分區(qū)字段，仍然沒有利用分區(qū)信息，通過查詢v_monitor.query_events視圖可以看到sql執(zhí)行過程中發(fā)生的事件。優(yōu)化之前：evenl_lj?pe CTeni_d<fSC[lpiionNDH^TOGKAMThe-oplimizw apr&iicateonacolumnforwhkhi1dce^notliaTealiiffto^TamCrROUPEYPUSHDCiTFNTheoplimize]pushedGtoupEupastaloinNDGROUFBYfUSHDDWNTh亡QpliinizejcDuldLn'lpushGioupBypastJoinPAkTiriCiNS.ELlMJNftTEDSo倔skuaseix-ntaiiicrswillnot誕pwcKsedbemiseihe^contaijinordevam<iat3BMERLjE_CaKVERTED_rQ_UN[DNM；eTge-con7frrledtounioti,followedby的汁.SMALL.WERGE.EEPLACEDSinaLlSlocaieMetfiereplacedwithSlcaaieUDionfarefficiericy優(yōu)化之后:■F尸wenl」yp亡NOHIFTQGE陽dwait-dcKripWoiiTfieoximiseieiicoim冊edapr-sdiasona-D&luiniifoiprhfchil n?兀ahlaiooajnNOH[班CG&AMUeojtiiriizs:enmi.mleredipredicateoni.oolumnFoxwJifcbildocsjiQl sthisloEr-amNOGROUFBYPUSTHD0W1-1He■:'ptiiiizeicould口*1pusiG[oupBypastJoiHPAE7JT[ON￡_EL[td：l：NArED&ijn.eEtoTasso^raineiiswILno1beprocessedfesausethes1coHaJ口mrelevmldata.gUALL_MEROE_BEPLACEDSfoal]他g