Teradata系统性能监控介绍

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,Teradata Confidential,49,Teradata,系统性能监控介绍,Teradata,系统中的性能组件,Pdisks,CPUs,Memory,Channel,Connection,LAN,Connection,System,Disk,AMP,PE,Channel Driver,Gateway,Teradata DBS,PDE,OS,Vdisks,User,Application,性能监控工具,Performance,Monitor,ResUsage,AWS,Database,Console,RSS,RSS,RSS,RSS,RSS,RSS,PMON,O,S,Teradata,Manager,gtwglobal/xgtwglobal,ResUsage,数据,两种,ResUsage,数据搜集方式,:,Data collection,Data logging,搜集,ResUsage,数据的系统组件,:,Parallel Database Extension (PDE),Resource Sampling Subsystem (RSS),Data collection,PDE,和,RSS,搜集操作系统和,RDBMS,系统的性能信息,.,Data logging,搜集到的性能信息记录到,ResUsage,的相关表,.,设置,Monitor,和,Log,频度,设置方法,:,PM/API,命令：,Set Session Rate,和,Set Resource Rate.,xctl,或,ctl,命令,DBW,超级用户命令,PMON,工具,使用,DBW,进行设置,使用,XCTL,或,CTL,登录,ResUsage,相关表,ResUsageSpma,：节点性能信息,ResUsageIpma,：节点性能信息,ResUsageScpu,：,CPU,性能信息,ResUsageSvpr,：,Vproc,性能信息,ResUsageIvpr,：,Vproc,性能信息,ResUsageS,ldv,：,IO,性能信息,ResUsageShst,：,Channel,或,LAN,性能信息,ResUsage Views,原始数据,引用数据,Calculations,ResGeneralInfoView,ResUsage,表引用的内容,:,资源利用百分比,每秒,Event,个数,Event,平均大小,常用视图：,ResGeneral View,ResCPUUsageByAMPView,ResCPUUsageByPEView,ResUsage,相关的,Macro,宏,相关表,描述,ResCPUByAMP,SVPR,为每个,AMP,提供明细的,CPU,使用情况,.,ResCPUByPE,SVPR,为每个,PE,提供明细的,CPU,使用情况,.,ResCPUByNode,SPMA,为每个节点提供明细的,CPU,使用情况,.,ResHostByLink,SHST,为单独的网路链路提供主机冲突的情况,.,ResLDVByNode,SLDV,为每个节点提供,IO,相关信息,.,ResMemMgmtBy,Node,SPMA,为每个节点提供内存管理相关信息,.,ResNETByNode,SPMA,提供节点间的网络冲突信息,.,ResNode,ResNodebyNode,SPMA,提供节点的,ResUsage,汇总信息,.,包含以下内容,:, CPU,使用, Logical device,接口, Memory,接口, Host,接口, Net,接口,主节点调度,锁冲突,ResNodebyGroup,为节点组提供,ResUsage,汇总信息,.,SPMA,SPMA,多代共存的,ResUsage,的,Macro,如果系统多代共存，会额外增加,8,个宏,.,这,8,个宏的名称都是以,ByGroup,结尾,.,输入参数,: FromDate, ToDate, FromTime, ToTime,.,ResPeCpuByGroup,ResAmpCpuByGroup,ResMemByGroup,ResNodeByGroup,ResNetByGroup,ResCpuByGroup,ResLdvByGroup,ResHostByGroup,ResUsage,数据,系统汇总信息,Performance Monitor,PM/API,接口,Teradata,软件的一部分,.,低代价,实时监控,可以进行,Abort SQL,的操作,不会产生报告,.,PM/API,从,AMP,中搜集相关信息,.,节点、,vproc,和,session,级的数据均从缓存中直接读取,.,COLLECTED,DATA,SESSION,级数据,累积,系统级数据,不累积,RSS,从缓存中直接搜集,PM/API,按周期定时取最近一次的系统级性能数据信息,.,系统级数据不累积,.,搜集,session,级的性能数据,.,Session,数据累积,.,系统级数据,Session,级数据,系统性能总体情况,监控,Vproc,和节点状态,Vprocmanager,工具,Options (1 of 2),Session Options,General Options,Options (2 of 2),Session History Options,Start Options,Chart,监控节点的性能状况,监控,Vproc,性能情况,(1 of 2),监控,Vproc,性能情况,(2 of 2),Session,信息,按,Logon Partition,来查看,SQL,执行计划,Access Logging,Access Logging,能记录所有对,Teradata,数据库的操作行为,.,以下内容可以被,Access Logging,记录,:,访问类型,SQL,文本,访问频率,请求操作,使用用户,相关对象,Access Logging,用来监控对象或用户的使用行为,.,通过,Teradata Administrator,启动,Access Logging,操作动作,任何操作对象均可定义,(,例如：,CREATE, USER, MACRO, GRANT,等,).,使用对象,不指定代表所有用户,.,操作对象,不指定代表所有对象,Access Logging,相关视图,VIEW NAME,DESCRIPTION,DBC.AccessLog,DBC.AccLogRules,数据字典中相关表,DBC.AccLogRuleTbl,DBC.AccLogTbl,通过,Begin Logging,和,End Logging,生成的,Access Logging,相关规则,.,根据规则搜集的,Access Logging,相关数据,.,Access Log,规则,Teradata Administrator,Tools Menu Query Logging,Query Logging,是针对用户进行,SQL,分析的工具,DBQL,相关表和视图,以下表和相关的视图均与,DBQL,相关,DBC.DBQLogTbl,DD/D Tables,DD/D Views,DBC.QryLogV,DBC.DBQLStepTbl,DBC.QryLogStepsV,DBC.DBQLObjTbl,DBC.QryLogObjectsV,DBC.DBQLSqlTbl,DBC.QryLogSQLV,DBC.DBQLSummaryTbl,DBC.QryLogSummaryV,DBC.DBQLExplainTbl,DBC.QryLogExplainV,保存默认记录,每步一条记录,查询中每个对象一条记录,保存详细,SQL,信息,汇总信息,保存,EXPLAIN,信息,DBQL Purpose,提供,TDWM,相关信息,:,DBC.QryLogEventsV TDWM,事件信息,DBC.QryLogExceptionsV Workload,中定义的查询,exception,DBC.QryLogTDWMV ,查询中所包含的,TDWM,信息,DBC.QryLogTDWMSumV ,查询中所包含的,TDWM,汇总信息,.,Teradata Manager,用户必须需要有,dbcmngr,的,select,权限,Teradata Manager,可以有,Server,和,Client,两种模式,PMON,是,Teradata Manager,的部分功能,Sessions,All Sessions,Session,明细信息,创建,Alert Policy,以下三步建立,Alert Policy.,1.,创建,policy,名称,.,2.,创建,action (,例如：给,DBA,发,email).,3.,定义,action,动作,.,选择,File - New,去创建新的,Alert Policy.,为,Alert Policy,命名,.,Alert Policy Editor,Alert Policy Editor,Tools- Alert Policy Editor-Vprocs,Alert Policy Editor,RDBMS Events/System,Tools- Alert Policy Editor-System,Tools-Alert Policy Editor-RDBMS Events,Alert Policy Editor,DB Space/Rates,Tools- Alert Policy Editor-DB Space,Tools- Alert Policy Editor-Rates,Alert Policy Editor,Sessions,Tools- Alert Policy Editor-Sessions,Alert Policy,应用规则到数据库,Tools- Alert Policy Editor,Tools- Apply to Database,Action,也只能有一种方式,.,保存,policy,内容,.,Alert policy,应用到数据库中,.,每个数据库只能允许一个,policy.,Teradata,的并行度,Notes:,每个,PE,可以支持,120,个,sessions.,每个,Session,可以支持多个,REQUESTS.,Message Passing Layer,并行处理各个事务,.,每个,AMP,可并行支持,80,个,AWT.,所有,AMPs,可并行处理各自,REQUEST.,每个,AMP,可以并行处理多个,REQUEST.,并行无处不在,!,Session A,Session B,Session C,Session D,Session E,Session F,PE,PE,PE,Task 1Task 2Task 3,Task 7Task 8Task 9,Task 4Task 5Task 6,Task 10Task 11Task 12,AMP 1,AMP 4,AMP 3,AMP 2,Message Passing Layer,SQL Parser,过程,DATA parcel,STATISTICS,REQUEST Parcel,CACHED?,No,Yes,DD,Dbase,AccessRights,RoleGrants (V2R5),TVM,TVFields,Indexes,SYNTAXER,RESOLVER,SECURITY,OPTIMIZER,GENERATOR,DISPATCHER,AMP,DD Cache,Plastic STEPS,EXPLAIN,APPLY,R-T-S Cache,Concrete STEPS,AMP Work Task,AWT,类型,常用的,AWT,类型,:,MSGWORKNEW:,普通,Request,MSGWORKONE: redistribution,或,index update,等操作,MSGWORKTWO: updating fallback for an index,MSGWORKTHREE:,特定类型,MSGWORKABORT:,系统内部处理,MSGWORKSPAWN:,系统内部处理,MSGWORKNORMAL:,系统内部处理,MSGWORKCONTROL:,系统内部处理,显示,AWT,的命令,命令,puma c,显示当前,AWT,使用状况,:,VPROC = 4,WorkType Min Max Inuse Peak,MSGWORKNEW 3 50 10 11,MSGWORKONE 3 999 0 10,MSGWORKTWO 3 999 0 1,MSGWORKTHREE 3 999 0 0,MSGWORKFOUR 0 999 0 0,MSGWORKFIVE 0 999 0 1,MSGWORKSIX 0 999 0 1,MSGWORKSEVEN 0 999 0 0,MSGWORKEIGHT 0 999 0 0,MSGWORKNINE 0 999 0 0,MSGWORKTEN 0 999 0 0,MSGWORKELEVEN 0 999 0 0,MSGWORKABORT 3 999 0 4,MSGWORKSPAWN 3 999 0 2,MSGWORKNORMAL 3 999 0 3,MSGWORKCONTROL 3 999 0 2,命令,schmon m,显示队列长度,Avg queue Avg queue Avg serviceAG #requests wait(msec) length time(msec)= = = = =2 186 3 0 234 2983 1 0 112 57740 9 8 2113 12771 3 0 1,15 554 3 0 54,Average queue wait,在,0-5,较为合理,与,V1,保持一致,特定类型,Inuse ,当前在用,Peak ,数据库启动后达到的最高值,Active,AGs,Need,AWTs,AWT,平均占用时间,非,CPU,消耗时间,awtmon,命令,awtmon s,：查看当前系统每个,AMP,上,AWT,使用情况,awtmon 1 10 :,使用方法类似,sar,，只监控本节点,AWT,使用情况,谢谢！,Q&A,