分会场一-Topic3-数据库性能调优经验分享

•数据库优化•杨刚IBM系统工程师议程•CPU监控和调整•内存监控和调整•IO监控和调整•网络监控和调整•其他系统调优流程ActionsActionsYesIsSystemCPU-Bound?IsSystemMemory-Bound?IsSystemDisk-Bound?IsSystemNetwork-Bound?ActionsYesActionsYesYesNoNoNoActionsAdditionalTests案例：IdentifytheusingofCPUResourceandTopSQL•ResponseTime=ServiceTime+WaitTimeServiceTimeistimespentontheCPUWaitTimeisthesumoftimespentonWaitEventsi.e.non-idletimespentwaitingforaneventtocompleteorforaresourcetobecomeavailable.•ServiceTime=CPUParse+CPURecursive+CPUOtherServiceTimeiscomprisedoftimespentontheCPUforParsing,RecursiveCPUusage(forPLSQLandrecursiveSQL)andCPUusedforexecutionofSQLstatements(CPUOther).案例：IdentifytheUsingofCPUResouceTop5TimedEventsConcurrency1.84674116,074latch:librarycachelockConcurrency3.3181,37778,300latch:cachebufferschainsConcurrency9.2763,86950,923latch:librarycacheOther31.36113,182217,033latchfree47.620,066CPUtimeWaitClass%TotalCallTimeAvgWait(ms)Time(s)WaitsEventCPUTimeisasignificantcomponentofResponseTime,soapossiblenextstepistolookattheSQLorderedbyGetssection.案例：FindingSQLwithHighCPUOtherTimeinStatspack•HereareafewexamplesofSQLorderedbyGetssectionsfromStatspack.SQLorderedbyGetsforDB:PRODInstance:prodSnaps:8-9-EndBufferGetsThreshold:10000-NotethatresourcesreportedforPL/SQLincludestheresourcesusedbyallSQLstatementscalledwithinthePL/SQLcode.AsindividualSQLstatementsarealsoreported,itispossibleandvalidforthesummedtotal%toexceed100BufferGetsExecutionsGetsperExec%TotalHashValue------------------------------------------------------------91,938,6714,24921,637.724.13503723562SELECTTEKSTI_IK,U_VERSION,ASIAKIR_IK,KONTAKT_IK,LOMAKE_IK,PVM,TIEDON_LKA,TEKSTI_VER,TEKST_TILA,VALK_AUKKO,SUOR_PA_IK,SUOR_PA_ID,RESURSS_IK,RESURSS_I39,196,4834,2579,207.510.3576408779SELECTKONTAKT_IK,ASIAKAS_IK,KAYNTIPVM,KLO_JNRO,KT_PIKASEL,RESURSS_ID,SUOR_PA_IK,SUOR_PA_ID,TEKSTI_IK,KT_TEKSTI,KONT_LAJIFROMTEI1000_VIEWWHERE(kontakt_i31,870,1134,2627,477.78.33583640853SELECTLAAKE_T_IK,U_VERSION,TAPAHTU_IK,ASIAKAS_IK,TOT_DATIM,LAAKE_IK,KAUPPANIMI,VALM_MUOTO,VAHVUUS,PAKK_KOKO,ANNOS,INDIKAATIO,PYSYVAIS,VOIM_OLEVA,S30,567,4494,2597,177.18.01901268379SELECTLAB_TUL_IK,U_VERSION,TAPAHTU_IK,ASIAKAS_IK,TOT_DATE,TOT_TIME,PALVELU_IK,PALV_LYHEN,SL_KOODI,OSAVAS_NRO,OSAVAS_HOP,VAST_TYYPP,VAST_ARVO,VAST_SIJ•HerethefirstSQLstatement(withhashvalue3503723562)aloneaccountsfor24.1%ofallbuffergetsintheinstance.Thenext3statementsaccountfor10.3%,8.3%and8.0%.All4statementsareexecutedapproximatelythesamenumberoftimes(around4,250executions).ThefirststatementhasmoretotalGetsbecauseitfetchesmorebufferseachtimeitruns(Gets/Execis21,637.7comparedto9,207.5,7,477.7and7,177.1).SoitisafirstcandidatefortuningasithasgreaterimpactonCPUOthertimethantheother3statements.AbetterexecutionplanforthisstatementresultinginfewerGets/ExecwillreduceitsCPUconsumption.注：本页来源于：HowtoIdentifyResourceIntensiveSQLforTuningmetalinkDocID:232443.1CPUaffinity(举例)halpar1:[/home/oracle]$lsdev-Ccprocessorproc0Available00-00Processorproc2Available00-02Processorproc4Available00-04Processorproc6Available00-06Processorhalpar1:[/home/oracle]$bindprocessor-qTheavailableprocessorsare:0123halpar1:[/home/oracle]$ps-ef|greplistenerdemooracle6759981015:42:44pts/30:00/home/oracle/orasys/bin/tnslsnrlistenerdemo-inheritoracle9094541011724215:46:53pts/30:00greplistenerdemohalpar1:[/home/oracle]$bindprocessor3770562halpar1:[/home/oracle]$ps-emoTHREAD|greplistenerdemooracle6759981-A0603*240001pts/32/home/oracle/orasys/bin/tnslsnrlistenerdemo-inheritroot1151026397400-A1601-200001pts/3-greplistenerdemohalpar1:[/home/oracle]$bindprocessor–u377056halpar1:[/usr/lpp/mmfs/bin]#ps-emoTHREAD|greplistenerdemooracle6759981-A0603*240001pts/3-/home/oracle/orasys/bin/tnslsnrlistenerdemo-inheritroot909434397400-A1601f100060018a60f88200001pts/3-greplistenerdemo确认有多个CPU选择需要进行CPU绑定的进程CPU绑定进程到2号CPU解除与2号CPU的绑定CPUaffinity(续)•比bindprocessor更灵活的方式，ResourceSet#mkrset–c0-3test/oracle#attachrsettest/oraclepid#detachrsetpidhalpar1:[/home/oracle]$ps-ef|greplistenerdemooracle6759981015:42:44pts/30:00/home/oracle/orasys/bin/tnslsnrlistenerdemo-inheritoracle9094541011724215:46:53pts/30:00greplistenerdemo#attachrsettest/oracle675998创建CPU0到CPU3,4颗CPU的一个Rset案例：如何判断数据库的内存占用（以Oracle为例）#ps-lfp13288FSUIDPIDPPIDCPRINIADDRSZWCHANSTIMETTYTIMECMD240001Aoracle132881060201ba2f34032Nov03-0:06ora_pmon_DEV#psv13288PIDTTYSTATTIMEPGINSIZERSSLIMTSIZTRS%CPU%MEMCOMMAND13288-A0:082255616139043276828420135120.01.0ora_pmon_DEV•ps–v的输出需要关注RSS和TRSRSS=working-segmentpages*4K+code-segmentpages*4KTRS=code-segmentpages*4K•Intheexampleabove,theoraclepmonbackgroundprocessisusing13904(RSS)-13512(TRS)=392392*1024=401408bytes•Thecorrectamountofmemoryusedbythepmonbackgroundprocessis392k(401408bytes),not34MBasreportedbytheps-lfcommand.注：本页来源于：DeterminingOraclememoryusageonAIXmetalinkDocID:123754.1ƒAIX的vmo命令提供显示和调整内存参数的功能例如：–vmo–a–vmo–ominfree=1440–vmo–p–ominfree=1440(-p命令表示重启之后设置仍然生效)调整内存Anumberofthedefault“vmo”settingsarenotoptimizedfordatabaseworkloadsandshouldbemodifiedforOracleenvironmentsVMM参数调整MINPERM%–minimum%realmemoryforfilesystembuffercache15-