Vertica数据查询优化

Vertica数据查询优化vertica是惠普公司推出的列式分布式数据库，在OLAP领域有其独到的地方，目前社区版免费，但是只能存放1T的数据。我在工作中维护的bi系统后端就是使用的vertica数据库，平时也经常需要对于数据库的查询进行一些优化。所以写下这篇博客记录一下。1.定位问题所谓的数据库调优、程序优化之类的工作，实际上是一个解决问题的过程，而解决问题，第一部就是需要定位问题。找到问题的手段多种多样，可以通过分析程序、监控生产上服务器的性能、定期生成数据库的负载报告等手段，而最不应该的就是通过生产上用户的反馈来反映问题了，因为到了那个时候，一切都已经晚了。具体到vertica来说，通过QUERY_PROFILES这个数据库本身提供的视图，可以找到耗时和执行的多的sql语句。以下三条sql语句，分别统计出执行次数topio，单次执行耗时topio，执行总耗时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.分析问题数据库调优，其实非常依赖于数据库本身提供的各种性能分析工具，例如执行计划解释器，跟着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以后，可以通过查询系统表query_plan_profiles获得语句实际的执行计划和各个阶段的执行时间，这个不同于执行计划，这是真实的执行过程。如图：kUlhllitIiITk4-CROUIWH應口(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■•数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的执行是从下往上的，在这个表里面列出了PATHID,我们可以从PATHID从大到小一步一步分析，每一步的执行耗时。注意PATHID：4这一步，查询了一张事实表，cost是2K,处理了4M的数据。这一步就是我们分析的重点，因为它排在执行步骤的较前面并且处理了较多的数据。4.解决问题通过运行analyze_wordload，可以得到对某个表具体的优化建议。我们对，PATHID：4的这个步骤查询的事实表，进行分析，可以得到如下优化建议：|■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工具，对这个事实表建立映射，此方法代价比较大，而且只能对特定的查询优化，这里进行第二条操作，对于此事实表进行分析，得到它的统计信息。ame=>selectanalyze_stafistics('pub'llc.role_logiH-online.dt')；yze_stafistics0(1row)这条命令，只会访问此表10%的数据，返回0表示成功。进行了统计信息之后，重新执行第1步和第2步，得到新的计划如下：nnni>Ktimepslbline"H3MUPBYOSHCLOC^iLKESEOMBM7OROU尚[gl;519,抽啊孰!NO3TA7ISTECS1]CTAlHIDs1)Ijrtil]1已跡亡町出：:umi血i1Bnra>Ey:TlLffTafiildik,1316737-43^T3[旳们皿加丿由伽14■>JCCNME阳印ODT创?呃駅紳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刃内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驱：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可以观察到，执行步骤被调整了，原来PATHID：4的步骤比较耗时，现在被提前到PATHID：5了，而且执行的成本和消耗资源也不一样，以下是详细对比：优化之前：|||+--Outer->STORAGEACCESSforT330143[Cost：2K,Rows：4M(NOSTATISTICS)](PATHID：4)优化之后：|||+--Outer->STORAGEACCESSforT330143[Cost：94,Rows：18K](PATHID：5)。可见，对于事实表的dt自动进行分析以后，通过dt字段获取数据，扫描行数从4M减少到了18k，cost从2k减少了94，整个sql的执行时间也从0.34秒降低到了0.17秒。至此，此次优化得到了目的（执行时间减少了50%）。分析背后的机制为什么Vertica数据库获取了统计信息以后，就可以优化查询？因为这张事实表是按照dt字段进行分区，但是在没有统计信息的时候，即使查询条件带上了分区字段，仍然没有利用分区信息，通过查询v_monitor.query_events视图可以看到sql执行过程中发生的事件。优化之前：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优化之后:■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