DB2数据库性能监控和调优实践

DB2监控和性能调优实践徐明伟DB2独立咨询顾问大纲•性能调优概述•监控和问题定位•性能调优•案例•Q&A性能调优概述•性能问题的症状–响应时间慢–吞吐量低–资源占用高(CPU、Memory、I/O等)•调优是个系统工程–存储–系统–中间件–数据库–应用程序•数据库角度–数据库逻辑设计–数据库物理设计(存储规划)–SQL语句数据库调优关键•I/O最关键–减少I/O–最大化I/O效率–存储规划，物理设计•CPU2大杀手–表扫描–排序•Memory命中率可能会骗人•SQL是一切问题的根源性能调优步骤•调优是个迭代过程•明确问题-收集数据-分析数据-细化、定位问题-优化•明确问题–真正了解问题是什么–只相信自己看到的•收集数据–根据不同症状收集相应数据–结合系统监控工具和数据库监控工具收集数据–收集频度•分析数据、定位问题•“找到问题就相当于解决了一半”性能监控和问题定位DB2数据库监控工具•即时监控工具–Snapshot–db2pd–db2top•事件监控工具–Eventmonitor–新事件监控工具(9.7)•对数据的访问是通过SQL语句执行的，绝大数性能问题都是SQL本身问题–动态语句：JDBC、DB2命令行–静态语句：存储过程、嵌入式SQL程序通过脚本实现数据收集自动化为什么要使用脚本–DBA可以将主要精力花费在更关键的任务上–降低对图形界面的依赖–自动化脚本相对于人工，犯错误的几率更小•通过脚本实现自动监控和管理–Shell,sed,awk…–Cron等工具实现脚本的定时调度•Windows平台实现自动管理–拷贝Unix工具–通过Unix/Linux远程客户端–At/schtasks调度–DB2任务中心有很多限制数据库快照分析•数据库快照包含一些统计信息、状态信息等，通过getsnapshotfordatabaseondb或sysibmadm.snapdb–数据库连接和代理信息–锁相关信息：死锁、锁等、锁超时、锁升级信息–排序信息：排序溢出、排序时间–缓冲池信息：同步、异步逻辑/物理读/写、命中率–事务信息：提交/回滚的事务数–SQL语句信息：动态、静态SQL语句；Select/UDISQL数量；增删改查的行数；–日志信息：日志空间、最早的事务–Packagecache命令率–Catalogcache命令率其他重要快照信息•应用快照：getsnapshotforapplicationsondb或（sysibmadm.snapappl和sysibmadm.snapappl_info）–获取应用执行情况，找到异常应用•表快照：getsnapshotfortablesondb或sysibmadm.snaptab–获取表的读写行数，跟踪热表•动态SQL快照：getsnapshotfordynamicsqlondb或sysibmadm.snapdyn_sql–获取动态SQL语句的执行情况，如执行次数、执行时间、CPU时间、排序、锁等信息关键性能指标(KPI)•读有效性：rows_read/rows_selected•同步读百分比：数据/索引读的效率•表的读写效率：•Bufferpool命中率：•……读有效性：RowsRead/RowsSelected•当读有效性值较高时，表示可能在发生表扫描，可通过索引等手段进行优化•读有效性表示每获取一条记录需要访问的行数对于OLTP，最好小于20对于OLAP，最好小于100同步读百分比：•数据从存储加载到bufferpool有同步和异步两种方式–同步(synchronousI/O)：当有合适的索引时，db2会使用同步I/O访问需要的索引和数据页–异步(AsynchronousI/O)：当没有合适的索引，或SQL语句不够优化时，db2使用异步预取(prefetch)I/O扫描数据或索引同步读百分比=100–100*(Asynchronouspooldatapagereads+Asynchronouspoolindexpagereads)/(Bufferpooldataphysicalreads+Bufferpoolindexphysicalreads+1)对于交易系统，同步I/O的百分比越高越好，反之则需要优化同步读举例表db.test1:(idprimarykey,name,code)10万行数据select*fromdb.test1wherename=‘aaaaaaa1’Bufferpooldatalogicalreads=5843Bufferpooldataphysicalreads=5602Bufferpooltemporarydatalogicalreads=0Bufferpooltemporarydataphysicalreads=0Asynchronouspooldatapagereads=5381Bufferpooldatawrites=0Asynchronouspooldatapagewrites=0Bufferpoolindexlogicalreads=52Bufferpoolindexphysicalreads=38在name字段增加索引后，数据库snapshot快照结果Bufferpooldatalogicalreads=415Bufferpooldataphysicalreads=186Bufferpooltemporarydatalogicalreads=0Bufferpooltemporarydataphysicalreads=0Asynchronouspooldatapagereads=0Bufferpooldatawrites=0Asynchronouspooldatapagewrites=0Bufferpoolindexlogicalreads=318Bufferpoolindexphysicalreads=220Bufferpooltemporaryindexlogicalreads=0Bufferpooltemporaryindexphysicalreads=0Asynchronouspoolindexpagereads=0同步读比例：100%*(1–5381/5602)=4%同步读比例：100%*(1–0/186)=100%找到I/O读写频繁的表•Sysibmadm.snaptab•db2pd–tcbstat•查找表扫描较多的表找到最差的dynamicSQL语句–通过Sysibmadm.snapdyn_sql视图，根据如下字段进行排序，找到最差的SQL语句•ROWS_READ•ROWS_WRITTEN•NUM_EXECUTIONS•NUM_COMPILATIONS•STMT_SORTS•TOTAL_EXEC_TIME•TOTAL_SYS_CPU_TIME•TOTAL_USR_CPU_TIME•STMT_TEXT静态SQL活动•通过databasesnapshot，staticstatementattempted表示有静态语句执行。静态语句$db2pd–dsample-static每个存储过程或嵌入式c在内部都是一个package，每条语句都是Package里的section，可通过syscat.statement查找9.7动态、静态语句抓取•MON_GET_PKG_CACHE_STMT存储过程返回当前packagecache里的动态语句和静态语句锁的诊断•锁的几种现象–死锁–锁超时–锁等待–锁升级•锁是症状，不是根源•对锁的诊断最主要是找到引起锁的SQL语句•锁问题诊断工具deadlockeventmonitorwithdetailshistorydb2pd(8.2后)db2pdcfg(9.1)db2_capture_locktimeout(9.5)NewLockingeventmonitor(9.7)9.7新的锁事件监控Locktimeoutevents(MON_LOCKTIMEOUT)=hist_and_valuesDeadlockevents(MON_DEADLOCK)=hist_and_valuesLockwaitevents(MON_LOCKWAIT)=hist_and_valuesLockwaiteventthreshold(MON_LW_THRESH)=10000createeventmonitorlockevmonforlockingwritetounformattedeventtable(tablelocks)callsysproc.EVMON_FORMAT_UE_TO_TABLES('LOCKING',NULL,NULL,NULL,NULL,NULL,'RECREATE_FORCE',-1,'SELECT*FROMlocksORDERBYevent_timestamp')只需用一个事件监控器就可捕获死锁、锁超时、锁等的语句锁的调优•写优良的SQL语句•创建合适的索引避免表扫描•选择合适的隔离级别:UR,CS,RS,RR•事务(transaction)尽可能频繁的提交•在事务结尾执行insert/update/delete•调优locklist和maxlocks数据库参数•锁延迟设计考虑–db2setDB2_EVALUNCOMMITTED=ON–db2setDB2_SKIPDELETED=ON–db2setDB2_SKIPINSERTED=ON•9.7当前已提交读特性(CurrentlyCommitted)排序•排序是CPU杀手，过多的排序和排序溢出对性能造成很大影响•Database、Applications、Dynamicsql和表快照都有排序信息TotalPrivateSortheapallocated=0TotalSharedSortheapallocated=0SharedSortheaphighwatermark=36Postthresholdsorts(sharedmemory)=0Totalsorts=77Totalsorttime(ms)=390Sortoverflows=17Activesorts=0数据库快照排序信息找到排序溢出的SQL语句•getsnapshotfortablesondbTableSchema=17DB2INST1TableName=TEMP(00001,00002)TableType=TemporaryDataObjectPages=1590RowsRead=1972RowsWritten=30000Overflows=0PageReorgs=0•getsnapshotforapplicationagentid17DynamicSQLstatementtext:select*fromdb.test1orderbyname观察到排序溢出时，可通过表快照找到临时表，找到agentid，然后通过getsnapshotforapplicationagentidagentid，查找正在执行的SQL语句。然后对引起排序的SQL语句进行调优表快照临时表信息性能优化性能优化•写好的SQL语句•更新Runstats信息•Reorg•Rebindpackages•通过db2exfmt或db2expln查看SQL语句执行计划•海量数据库物理设计–索引–多维索引–物化视图–分区表–压缩–数据库分区写好的SQL语句•使用参数化查询，wherecol1=?，减少编译时间–案例分析•避免对查询条件计算，wheresalary*2xx改为salaryxx/2–无法利用索引•只返回需要的行，避免用select*fromt1•尽量使用exists而不是用in•避免笛卡尔乘积，select*froma,b•函数的效率很高，充分利用日常运维工具•Runstats：运行时统计信息，为db2优化器提供最佳路径选择。•Reorgchk:重组检查，根据8个公式判断是否需要reorg•Reorg:类似磁盘碎片整理，对表和索引做物理重组•Rebind：针对存储过程或嵌入