DB2 最佳实践 性能调优和问题诊断最佳实践

DB2最佳实践:性能调优和问题诊断最佳实践本系列介绍了DB2系统性能的最优方法，分两部分。第1部分首先介绍为了达到良好性能，我们如何从软硬件配置方面来保障，紧接着讨论了在多种在操作和故障诊断的情况下，有助于我们了解系统性能的监控方法。第2部分我们介绍在出现性能问题时如何逐步地、有条不紊地去处理它们。概述就算是配置最仔细的系统也终究会发现它仍然需要一定的性能调优，并且这时我们已经搜集了的运行监控数据，将来非常便于搜集。保持一种系统的方法来调优和进行故障诊断对我们非常重要。当发生了一个问题，为了解决这个问题，很容易随意的进行调整。然而，当我们这么做了，事实上定位到问题的可能性非常低，甚至让问题更糟糕。性能调优的一些基本原则：1.有备而来，去了解系统一切正常的情况下性能怎么样。搜集运行监视信息来跟踪一段时间内系统行为的变化。2.了解整个场景，不要局限于你从DB2上看到的–也要搜集并分析来自于操作系统、存储、应用程序甚至来自用户的数据。了解系统本身将有助于你解释监控数据。3.只调整能解释你看到的症状的参数，如果连发动机都无法启动就不要更换轮胎。不要试图通过降低CPU来解决磁盘的瓶颈。4.一次只改一个参数，在更改其它参数之前先观察效果。你可能遇到的问题类型性能问题往往分为两大类：影响了整个系统的问题和只影响了部分系统的问题。比如某一特定应用或SQL语句，在研究的过程中－种类型的问题可能转化为另外一种类型的问题，或者相反。例如造成整个系统性能降低可能是一个单独的语句，或者是整个系统的问题只是在一个特定的区域被发现。下面我们从整个系统的问题开始。我们发现的所有导致性能降低的原因的方法就是从高层入手并逐渐提炼我们的诊断。这个“判断树”策略可以帮助我们尽可能早的排除那些不能解释我们所看到症状的因素，适用于整个系统或者更加局部的问题。我们将把瓶颈分成下面4种普通类型：1.磁盘2.CPU3.内存4.‘懒惰系统’在开始一个对DB2调查之前，首先考虑一些准备问题常常是有帮助的，比如：1.是否有性能降低，与什么相关？我们的‘基准’是什么？2.一个系统的性能看起来在随时间流逝下降了？与一个不同的系统或不同的应用比较下降了？这个问题可以对性能降低的原因展开不同的可能性。数据量增加了？所有的硬件都运行正常吗？3.性能下降是什么时候发生的？在另一个任务运行之前、之中、之后，性能下降或许周会期性的发生。甚至如果这个任务没有直接和数据库相关，它也可能由于消耗网络或者CPU资源影响数据库性能。4.性能下降的前后关系有什么变化吗？通常是，添加了新硬件、或应用程序被更改了、大量数据被加载、或者更多的用户访问这个系统。5.在据库专家和应用程序以及架构方面的专家一起工作的情况下，这些问题通常是一个综合分析方法一个很重要的部分。DB2服务器几乎总是硬件、其它中间件、和应用程序这样一个复杂环境的一部分，所以解决问题可能需要有多领域的技能。磁盘瓶颈SystemBottleneckDiskBottleneck?磁盘瓶颈的基本症状是：在vmstat或iostat结果中出现较高的I/O等待时间。这显示系统会花费一小段时间等待磁盘I/O完成请求。等待时间达到20%或25%是很少见的。如果CPU时间非常低，那么高I/O等待时间是一个很好的预示了瓶颈所在。从iostat或perfmon显示磁盘高达80%的繁忙程度。从vmstat输出中看到较低的CPU利用率（25%-50%）可能最终我们可能需要添加磁盘，但现在我们将检查我们是否能通过调优DB2系统消除这个瓶颈。如果存在一个磁盘瓶颈，系统管理员可以帮忙映射一个繁忙设备镜像的文件系统路径。从这里你可以决定DB2如何使用这些受到影响的路径:瓶颈是表空间容器？这取决于在sysibmadm.snapcontainer中查询TBSP_NAME,TBSP_ID和CONTAINER_NAME的结果，查看造成瓶颈的路径是否在CONTAINER_NAME结果中。瓶颈是事务日志路径？这取决于检查数据库配置参数的结果，查看造成瓶颈的路径是否是“日志文件路径”。作为诊断日志路径？这取决于检查数据库管理配置参数的结果，查看造成瓶颈的路径是否是DIAG_PATH。我们将分别考虑这几种情况。SystemBottleneckContainerDiskBottleneckHotDataContainerHotTable?为了判断是什么导致容器成为瓶颈的，我们需要判断都有哪些表存储在这那个表空间而且最活跃。1.要判断什么表在这个表空间，需要查询syscat.tables，把TBSPACEID同上面的snapcontainer.TBSP_ID匹配回页首2.要找出哪些表最活跃，需要查询sysibmadm.snaptab，选择在我们繁忙的容器上的表的ROW_READ和ROW_WRITTEN。查看那些水平活跃程度比其它要高很多的表。注意这需要打开实例级表监控开关DFT_MON_TABLESystemBottleneckContainerDiskBottleneckHotDataContainerHotTableDynamicSQLstmt?进一步向下钻取，我们需要找出什么造成了这个表的高水平的活跃程度。是动态SQL语句造成的高度活跃？通过sysibmadm.snapdyn_sql.TBSP_ID查询动态SQL快照，来找出我们感兴趣的那些涉及这个表的语句：select…fromsysibmadm.snapdyn_sqlwheretranslate（cast（substr（stmt_text,1,32672）asvarchar（32672）））like‘%tbname%’orderby…列返回能包含行的读和写，缓冲池活跃程度，执行时间，CPU时间，等等。我们能在列上使用ORDERBY子句，比如ROWS_READ,ROWS_WRITTEN和NUM_EXECUTIONS来集中那些对表有最大影响的语句。注意我们假设这里的表名在SQL语句的头32672个字符中。这个假设虽然不完美，却在大多数情况下正确，也是需要使用LIKE。select…fromsysibmadm.snapdyn_sqlwheretranslate（cast（substr（stmt_text,1,32672）asvarchar（32672）））like‘%tbname%’orderby…SystemBottleneckContainerDiskBottleneckHotDataContainerHotTableStaticSQLstmt?是否是静态SQL语句导致的高活跃程度？在这里我们需要使用系统编目表和db2pd来找出哪写语句最活跃。查询syscat.statements,参考那些我们关注的表：selectPKGSCHEMA,PKGNAME,SECTNO,substr（TEXT,1,80）fromsyscat.statementswheretranslate（cast（substr（text,1,32672）asvarchar（32672）））like‘%tbname%’一旦我们有了涉及到我们感兴趣的那些表的静态SQL语句的包名和片段数字，我们就可以使用db2pd–static来找出它们中哪些是高度活跃的。Db2pd–static的输出都有一条从实例启动并执行过的每一个静态SQL语句的记录。NumRef计数器这条语句已经运行了多少次，并且RefCount计数器显示了当前有多少DB2代理程序正在运行这条语句。监视db2pd–static结果中的每一条调用记录。NumRef值的迅速攀升、RefCount的值经常超过2或3，往往表明这是一个高度活跃的语句：selectPKGSCHEMA,PKGNAME,SECTNO,substr（TEXT,1,80）fromsyscat.statementswheretranslate（cast（substr（text,1,32672）asvarchar（32672）））like‘%tbname%’SystemBottleneckContainerDiskBottleneckHotDataContainerHotTableHotSQLstatement如果我们能确定并得出一个或多个SQL语句导致了I/O瓶颈，下一步我们需要确这个语句是否可以被优化以降低I/O。这个语句是否发起了一个不期望的表扫描？这可以通过用db2exfmt检查查询计划以及比较这个问题语句的ROWS_READ和ROW_SELECTED来验证。由于在表扫描中使用了过时的统计信息或有索引问题，经常在临时查询的时候不可避免会发生表扫描，但是一个导致过多I/O并造成瓶颈的重复查询还是应该被讨论的。另一方面，如果受到的影响的表非常小，那么增加缓冲池大小对减少I/O和消除瓶颈或许已经足够了。详情参考这里对于查询优化和物理设计的最佳实践文章。在我们讨论索引容器之前，先介绍两个数据容器磁盘瓶颈的情况：1.我们希望一个产生大量磁盘读取的表扫描通过预取器完成。如果在预取过程中有任何问题（见下面的懒惰系统），读入缓冲池操作都将被代理程序自己完成，并且－每次只读取一页。在这种情况下，会产生导产生大量闲置的“懒惰系统”，或（如我们在此讨论的）磁盘瓶颈。因此如果有磁盘瓶颈是由于表扫描造成的，但是在iostat中却显示的读入大小比那个表空间的预取大小要小很多的话，可能是预取不足导致的问题。2.通常，为了保证表空间后续读取时候有足够的可用缓冲池页面，页清理会对这个表空间产生一个稳定的写出流。然而如果在调整页清除有问题（见懒惰系统的瓶颈），代理程序将停下来自己做清理。这常常会产生页面清理的‘爆发’-周期性的高度活跃的写入（可能造成磁盘瓶颈），并与良好性能交替出现。在后面懒惰系统的瓶颈章节，有更多关于如何诊断和解决这两个问题信息。SystemBottleneckContainerDiskBottleneckHotIndexContainerHotIndex?一个发生在容器中的瓶颈，更多的可能是表活跃而不是索引活跃，但是一旦我们排除了表活跃的原因，我们就应该调查是否是索引活跃造成的问题。应为我们没有索引快照可以一用，就不得不通过间接的发现问题。1.在表空间中索引读写是否很活跃？对表空间查询sysibmadm.snaptbsp的TBSP_ID对应上面snapcontainer的TBSP_ID.selectdbpartitionnum,tbsp_id,tbsp_name,pool_index_p_reads,pool_index_writesfromsysibmadm.snaptbspTwhereT.tbsp_id=tablespaceIDofhotcontainer2.3.一个很大并不断增长的POOL_INDEX_P_READS或POOL_INDEX_WRITES值表明这个表空间有一个或多个‘忙碌的索引’。4.这个表空间中有什么索引？查询syscat.tables和上面的snapcontainer.TBSP_NAME匹配INDEX_TBSPACE。selectt.tabname,i.indnamefromsyscat.tablest,syscat.indexesiwheret.tabname=i.tabnameandcoalesce（t.index_tbspace,t.tbspace）=nameoftablespacewithhotcontainer5.这其中哪些索引是高度活跃的？如果在我们检查的表空间上有不止一个索引，我们就需要查看索引层面的活跃程度。反复搜集db2pd–tcbstatsindex–dbdbname。在‘TCBindexStats’部分列出了所有活动的索引，以及每一个的统计信息。‘Scans’列显示了在每个索引上已经执行了多少次索引扫描。在繁忙的表空间使用索引列表来查看一个索引上的扫描总数，虽然增长非常迅速KeyUpdates或InclUpdats（包括列值的更新）