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

概述

就算是配置最仔细的系统也终究会发现它仍然需要一定的性能调优，并且这时我们已经搜集了的运行监控数据，将来非常便于搜集。

保持一种系统的方法来调优和进行故障诊断对我们非常重要。当发生了一个问题，为了解决这个问题，很容易随意的进行调整。然而，当我们这么做了，事实上定位到问题的可能性非常低，甚至让问题更糟糕。性能调优的一些基本原则：

1. 有备而来，去了解系统一切正常的情况下性能怎么样。搜集运行监视信息来跟踪一段时间内系统行为的变化。
2. 了解整个场景，不要局限于你从 DB2 上看到的 – 也要搜集并分析来自于操作系统、存储、应用程序甚至来自用户的数据。了解系统本身将有助于你解释监控数据。
3. 只调整能解释你看到的症状的参数，如果连发动机都无法启动就不要更换轮胎。不要试图通过降低 CPU 来解决磁盘的瓶颈。
4. 一次只改一个参数，在更改其它参数之前先观察效果。

你可能遇到的问题类型

性能问题往往分为两大类：影响了整个系统的问题和只影响了部分系统的问题。比如某一特定应用或 SQL 语句，在研究的过程中－种类型的问题可能转化为另外一种类型的问题，或者相反。例如造成整个系统性能降低可能是一个单独的语句，或者是整个系统的问题只是在一个特定的区域被发现。

下面我们从整个系统的问题开始。

我们发现的所有导致性能降低的原因的方法就是从高层入手并逐渐提炼我们的诊断。这个“判断树”策略可以帮助我们尽可能早的排除那些不能解释我们所看到症状的因素，适用于整个系统或者更加局部的问题。我们将把瓶颈分成下面 4 种普通类型：

1. 磁盘
2. CPU
3. 内存
4. ‘懒惰系统’

在开始一个对 DB2 调查之前，首先考虑一些准备问题常常是有帮助的，比如：

1. 是否有性能降低，与什么相关？我们的‘基准’是什么？
2. 一个系统的性能看起来在随时间流逝下降了？与一个不同的系统或不同的应用比较下降了？这个问题可以对性能降低的原因展开不同的可能性。数据量增加了？所有的硬件都运行正常吗？
3. 性能下降是什么时候发生的？在另一个任务运行之前、之中、之后，性能下降或许周会期性的发生。甚至如果这个任务没有直接和数据库相关，它也可能由于消耗网络或者 CPU 资源影响数据库性能。
4. 性能下降的前后关系有什么变化吗？通常是，添加了新硬件、或应用程序被更改了、大量数据被加载、或者更多的用户访问这个系统。
5. 在据库专家和应用程序以及架构方面的专家一起工作的情况下，这些问题通常是一个综合分析方法一个很重要的部分。 DB2 服务器几乎总是硬件、其它中间件、和应用程序这样一个复杂环境的一部分，所以解决问题可能需要有多领域的技能。

磁盘瓶颈

磁盘瓶颈的基本症状是：

• 在 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 。

我们将分别考虑这几种情况。

为了判断是什么导致容器成为瓶颈的，我们需要判断都有哪些表存储在这那个表空间而且最活跃。

1. 要判断什么表在这个表空间，需要查询 syscat.tables，把 TBSPACEID 同上面的 snapcontainer.TBSP_ID 匹配
2. 要找出哪些表最活跃，需要查询 sysibmadm.snaptab，选择在我们繁忙的容器上的表的 ROW_READ 和 ROW_WRITTEN 。查看那些水平活跃程度比其它要高很多的表。注意这需要打开实例级表监控开关 DFT_MON_TABLE

进一步向下钻取，我们需要找出什么造成了这个表的高水平的活跃程度。是动态 SQL 语句造成的高度活跃？通过 sysibmadm.snapdyn_sql.TBSP_ID 查询动态 SQL 快照，来找出我们感兴趣的那些涉及这个表的语句：

select … from sysibmadm.snapdyn_sql where translate（cast（substr（stmt_text,1,32672） 
 as varchar（32672））） like ‘ %<tbname>% ’
 order by …

列返回能包含行的读和写，缓冲池活跃程度，执行时间，CPU 时间，等等。我们能在列上使用 ORDER BY 子句，比如 ROWS_READ,ROWS_WRITTEN 和 NUM_EXECUTIONS 来集中那些对表有最大影响的语句。注意我们假设这里的表名在 SQL 语句的头 32672 个字符中。这个假设虽然不完美，却在大多数情况下正确，也是需要使用 LIKE 。

select … from sysibmadm.snapdyn_sql where translate（cast（substr（stmt_text,1,32672） 
 as varchar（32672））） like ‘ %<tbname>% ’ order by …

是否是静态 SQL 语句导致的高活跃程度？在这里我们需要使用系统编目表和 db2pd 来找出哪写语句最活跃。查询 syscat.statements, 参考那些我们关注的表：

select PKGSCHEMA, PKGNAME, SECTNO, substr（TEXT,1,80） 
 from syscat.statements where translate（cast（substr（text,1,32672） 
 as varchar（32672））） like ‘ %<tbname>% ’

一旦我们有了涉及到我们感兴趣的那些表的静态 SQL 语句的包名和片段数字，我们就可以使用 db2pd – static 来找出它们中哪些是高度活跃的。 Db2pd – static 的输出都有一条从实例启动并执行过的每一个静态 SQL 语句的记录。 NumRef 计数器这条语句已经运行了多少次，并且 RefCount 计数器显示了当前有多少 DB2 代理程序正在运行这条语句。监视 db2pd – static 结果中的每一条调用记录。 NumRef 值的迅速攀升、 RefCount 的值经常超过 2 或 3，往往表明这是一个高度活跃的语句：

select PKGSCHEMA, PKGNAME, SECTNO, substr（TEXT,1,80） 
 from syscat.statements where translate（cast（substr（text,1,32672） 
 as varchar（32672））） like ‘ %<tbname>% ’

如果我们能确定并得出一个或多个 SQL 语句导致了 I/O 瓶颈，下一步我们需要确这个语句是否可以被优化以降低 I/O 。这个语句是否发起了一个不期望的表扫描？这可以通过用 db2exfmt 检查查询计划以及比较这个问题语句的 ROWS_READ 和 ROW_SELECTED 来验证。由于在表扫描中使用了过时的统计信息或有索引问题，经常在临时查询的时候不可避免会发生表扫描，但是一个导致过多 I/O 并造成瓶颈的重复查询还是应该被讨论的。另一方面，如果受到的影响的表非常小，那么增加缓冲池大小对减少 I/O 和消除瓶颈或许已经足够了。 详情参考这里对于查询优化和物理设计的最佳实践文章。

在我们讨论索引容器之前，先介绍两个数据容器磁盘瓶颈的情况：

1. 我们希望一个产生大量磁盘读取的表扫描通过预取器完成。如果在预取过程中有任何问题（见下面的懒惰系统），读入缓冲池操作都将被代理程序自己完成，并且－每次只读取一页。在这种情况下，会产生导产生大量闲置的“懒惰系统”，或（如我们在此讨论的）磁盘瓶颈。因此如果有磁盘瓶颈是由于表扫描造成的，但是在 iostat 中却显示的读入大小比那个表空间的预取大小要小很多的话，可能是预取不足导致的问题。
2. 通常，为了保证表空间后续读取时候有足够的可用缓冲池页面，页清理会对这个表空间产生一个稳定的写出流。然而如果在调整页清除有问题（见懒惰系统的瓶颈），代理程序将停下来自己做清理。这常常会产生页面清理的‘爆发’ - 周期性的高度活跃的写入（可能造成磁盘瓶颈），并与良好性能交替出现。

在后面懒惰系统的瓶颈章节，有更多关于如何诊断和解决这两个问题信息。

一个发生在容器中的瓶颈，更多的可能是表活跃而不是索引活跃，但是一旦我们排除了表活跃的原因，我们就应该调查是否是索引活跃造成的问题。应为我们没有索引快照可以一用，就不得不通过间接的发现问题。

1. 在表空间中索引读写是否很活跃？对表空间查询 sysibmadm.snaptbsp 的 TBSP_ID 对应上面 snapcontainer 的 TBSP_ID.

   select dbpartitionnum, tbsp_id, tbsp_name, 
    pool_index_p_reads, pool_index_writes from 
    sysibmadm.snaptbsp T 
    where T.tbsp_id = 
    <tablespace ID of hot container>

   一个很大并不断增长的 POOL_INDEX_P_READS 或 POOL_INDEX_WRITES 值表明这个表空间有一个或多个‘忙碌的索引’。

2. 这个表空间中有什么索引？

   查询 syscat.tables 和上面的 snapcontainer.TBSP_NAME 匹配 INDEX_TBSPACE 。

   select t.tabname, i.indname 
    from syscat.tables t, syscat.indexes i 
    where t.tabname = i.tabname and 
    coalesce（t.index_tbspace, t.tbspace） = 
    <name of table space with hot container>

3. 这其中哪些索引是高度活跃的？如果在我们检查的表空间上有不止一个索引，我们就需要查看索引层面的活跃程度。反复搜集 db2pd – tcbstats index – db <dbname> 。在‘ TCB index Stats ’部分列出了所有活动的索引，以及每一个的统计信息。‘ Scans ’列显示了在每个索引上已经执行了多少次索引扫描。在繁忙的表空间使用索引列表来查看一个索引上的扫描总数，虽然增长非常迅速 KeyUpdates 或 InclUpdats（包括列值的更新）却很稳定。

索引扫描通常很划算，所以基于这一点研究是否能在缓冲池中放入更多的索引而非直接从硬盘读取是很合理的。增加缓冲池的大小，或为索引指定专门的缓冲池，可能足以降低 I/O 来消除平均。注意，在数据仓库环境中的索引通常非常大，分配足够的缓冲池来消除 I/O 不大可能。在那种情况下，通过增加额外的容器以提高磁盘 I/O 带宽来消除瓶颈或许更加有效。

在我们找到了一个‘ hot table ’后，如果我们不能通过调优消除索引 I/O 瓶颈，或许我们需要更进一步的向下钻取，以找到导致索引 I/O 的 SQL 语句。不幸的是，我们并不能再一次像之前我们所做的那样只挖掘 SQL 语句文本，至少 I/O 瓶颈和具体的索引没有直接连系。

利用索引名称确定相应的表，然后用表名和上面描述的方法从活跃的表上找到可能使用这些索引的动态或静态的 SQL 语句。（这并不保证和表相关就一定会被使用。）那么我们需要使用 db2exfmt 来确定是否这些语句使用了索引。

从另一方面，如果这个繁忙的容器属于一个临时表空间，那么我们就需要考虑两个可能的原因。

1. 是否这个临时表空间的 I/O 繁忙是由于排序溢出？这是一种情况，排序从设计的内存缓冲溢出必须使用一个临时表空间来代替。如果排序时间和溢出快照监控元素很高而且不断增长，或许这就是原因。

   select dbpartitionnum, total_sorts, 
    total_sort_time, sort_overflows 
    from sysibmadm.snapdb

   STMM 会尽全力避免这种情况；然而如果你没有使用 STMM 来控制 sheapthres_shr 和 sortheap，你可能需要手动来增加这些值。

2. 是 I/O 由于庞大的中间结果导致的吗？如果是，这表明通过大量的临时数据的物理读写。我们应该在最初的时候就在表空间级的快照信息中检查这些，如果有临时数据 I/O 高的证据，那么我们就能从动态 SQL 语句的快照或者静态 SQL 事件监控数据中向下钻取。查找造成临时缓冲池中活跃的某个语句。

   select dbpartitionnum, tbsp_name, 
    pool_temp_data_p_reads, pool_data_writes 
    from sysibmadm.snaptbsp 
    where tbsp_id = <tablespace ID of hot container>

假设现在我们已经确定了一个发生以上类型瓶颈的容器 - 但是有时却会出现这种能情况，我们看不到任何明显的原因。没有繁忙的表，没有繁忙的索引，没有繁忙的 SQL 语句。可以调查几种可能的原因。

1. 在这个表空间中有太多‘ fairly active ’的表或索引吗？虽然它们中没有一个能自己活跃到足以导致瓶颈的程度，它们聚集起来仍有可能相对于底层磁盘太过活跃了。一个解决方法是把这些表和索引分布到不同的表空间。另外一个可能性是向这个表空间添加更多的容器（提供的这些容器位于不同于现有容器所在的磁盘上，因此 I/O 能力得到了提升。）
2. 太多的表空间共享了同样的磁盘吗？这一不小心就可能发生，表面上表空间占用了不同的逻辑卷，但是最终在最底层还是使用的同样的磁盘。像上面提到的，整体活跃 - 这个时候越过表，表空间或许要承担全部责任。合理的反反应是把一些表空间挪到别的磁盘上。

如果在这点上我们没有找到发生容器磁盘瓶颈具体的原因，至少我们已经排除了大多数‘可调整问题’，而且应该考虑增加而外的磁盘吞吐能力来提高这个‘问题’表空的性能。

虽然容器磁盘瓶颈很普遍，日志磁盘瓶颈却能对系统更大的影响。这是因为日志速度降低能影响系统中所有的 INSERT/UPDATE/DELETE 语句，不仅是某些表或索引。像其它类型的磁盘瓶颈最主要的症状就是 iostat/perfmon（90% 或者更高）。日志瓶颈也会造成事件监控器中的更长的提交时间，在应用程序快照中更多的代理程序处于‘ commit active ’状态。

如在这个章节提到过的对于日志配置，强烈建议在数据运行中，日志不和任何‘ active ’对像共享磁盘，比如容器，等等 。这是在发生日志瓶颈的时候需要首先反复确认的事情之一。

如果日志有它自己的磁盘，那么我们需要了解瓶颈的性质。

1. 如果 iostat 显示日志设备每秒执行 80-100 个操作，并且平均的 I/O 大小是 4k 字节，这表明日志饱和更多的发生在 I/O 操作上而非完全是数据量导致的。 
   这种情况有两种可能。
   • 首先，系统中的一些应用程序可能提交得过于频繁 - 或比需要的要频繁。高提交率的应用程序可以通过应用程序快照来确定（比较 select/insert/update/delete 语句提交率，并查看每分钟提交的数目）。在极端情况下（例如，自动提交开启，并且 SQL 语句都很短），是可能造成日志设备饱和的。在应用程序中减少提交频率对于减少日志瓶颈有直接的好处。
   • 造成频繁日志写的另外一种可能是日志缓冲区太小。当日志缓冲满了，DB2 必须把它写入磁盘，而不管其是否提交。对于日志缓冲被迅速填满的情况（如 sysibmadm.snapdb 的监视元素 num_log_buffer_full 所报告的）很可能导致这个问题。

2. System Bottleneck > Log Disk Bottleneck > Large Number of Log Writes

   过多的数据写入同样可以造成日志瓶颈。如果随着设备利用率越来越高，iostat 也会显示写入日志设备的数据超过 4k 字节很多，这表明比起高事务率，数据量对产生这个问题负有更多的责任。
   

   或许根据你的具体环境可以降低记日志记录数据的量 :

   1. 当 DB2 更新表中的一行的时候，它会记录从第一个被更改的列到最后个被更改的列之间的所有列 - 包括那些没有被更改的列。在定义表的时候，将那些经常修改的列放在一起能减少更新时日志记录的量。
   2. 大对像（CLOB,BLOB,DBCLOB）列默认是要记日志的，但是如过这些数据可以从数据库之外恢复，从减少 INSERT/UPDATE/DELETE 操作的时候日志记录的数据量，它们可以适当的标记为NOT LOGDED 。
   3. 如果大量日志数据跟大批 SQL 操作（比如，有子查询的 INSERT，有时用于数据维护和数据装载过程）有关，目标表或许能设置为 NOT LOGGED INITIALLY（NLI）。这会在当前工作单元不记日志。需要顾及到对 NLI 表的可恢复过程；然而，如果适合你的系统，NLI 可以非常明显的降低日志数据量和相应的提高性能。当然，如果这批操作是直接 INSERT，可以用 load 使用工具替代，它也能同样地避免记录日志。

无论哪种情况 - 不管是日志的高写入频率还是数据写入量高造成的日志瓶颈 - 要消除问题的原因往往是不可能或者不切实际的。一旦我们确认日志配置遵循了上面描述的最佳实践，或许就需要通过提高日志系统的能力来解决问题。要么通过添加额外的磁盘到日志 RAID 队列中，要么提供一个专门的或者升级了的磁盘缓冲控制器。

DB2 诊断日志路径― db2diag.log 存放的地方 - 繁重的磁盘写入能造成整个系统的性能下降，这很难分离，因为普通的 DB2 监控元素并不跟踪它。在多分区环境中所有分区都写入同样的诊断日志路径，它一般是通过网络共享 NFS 文件系统。类似于分区间同步，从许多分区对 db2diag.log 的并发写入可能会导致很高的网络通讯和 I/O 负载，并因此降低系统性能。正如在前面配置章节提到的，解决这个问题最简单的办法就是为每个分区指定一个单独的诊断日志路径（并指定一个专门的诊断日志文件）。

设置 DB2 的 diaglevel 数据管理配置参数为 4 会增加诊断信息的数据量，这会导致明显的性能影响 - 尤其在一个大型的多分区（DPF）环境。性能急剧下降甚至可能造成 DIAGPATH 文件系统满并最终使系统停止。为了避免这种情况，你应该确认诊断日志文件系统有足够的可用空间，通过归档诊断信息或者分配一个专门的文件系统给 DB2 存放诊断信息。

图 1. 磁盘瓶颈 - 总图

CPU 瓶颈

CPU 瓶颈表现在两个方面：

1. 所有 CPU 都饱和 - 意味着这个系统上的所有处理器都繁忙。根据 vmstat 和 perfmon 的报告，一般的标准是计算用户 CPU 时间和系统 CPU 时间。 CPU 利用率超过 95% 就被认为是饱和的
2. 单个 CPU 饱和 - 意味着系统上的在一个处理器上的负载达到了饱和，但是其它处理器却部分或完全空闲。这通常发生在系统中只有一个很忙的应用程序在运行。虽然还有可用的 CPU 能力，系统却无法消费，因此，应用程序或语句的速度取决于这一个处理器内核的繁忙程度。

同样我们也应该考虑到用户模式和系统模式上不同的 CPU 消耗。一种是处理器正在运行操作系统内核以外的软件比如 DB2 应用程序或者中间件，导致用户 CPU 时间增多。一种是在运行操作系统内核的时候系统内存增多。这两个在数量上是分开显示的，并且根据它们之间的分布情况，可以帮助我们找到 CPU 瓶颈的原因。用户和系统 CPU 时间比率在 3:1 到 4:1 之间是正常的。如果在有瓶颈的系统中，用户和系统时间比率高于这个区间，我们应该收件调查用户 CPU 时间增加的原因。

在 DB2 服务器上造成用户 CPU 瓶颈的许多原因可以通过抓取快照和语句事件监控器来诊断。我们可以使用应用程序快照或查询 sysibmadm.snapappl 管理视图来向下钻取，以找出是什么用户消耗了大多数 CPU 时间：

select appl.dbpartitionnum, appl_name, 
 agent_usr_cpu_time_s + agent_usr_cpu_time_ms / 1000000.0 as user_cpu 
 from sysibmadm.snapappl appl, 
 sysibmadm.snapappl_info appl_info 
 where appl.agent_id = appl_info.agent_id and 
 appl.dbpartitionnum = appl_info.dbpartitionnum order by user_cpu desc

同样（并且更又有用），通过动态语句快照，或查询 sysibmadm.snapdyn_sql 管理视图，我们也能确定什么 SQL 语句正在使用绝大多数 CPU 时间：

select substr（stmt_text,1,200）, 
 total_usr_cpu_time + 
 total_usr_cpu_time_ms / 1000000.0 as user_cpu 
 from sysibmadm.snapdyn_sql 
 order by user_cpu desc

通常情况下，我们查找一个或多个语句，它们消耗‘比它们平均份额更多’的 CPU 。这转化并增加 TOTAL_USR_CPU_TIME 和 TOTAL_USR_CPU_TIME_MS 的值

同时，我们应该考虑静态 SQL 语句。如上面所提到的，快照监控器并没有包含这类信息。所以我们需要使用语句事件监控器，或者 WLM 活动事件监控器，来收集它们的 CPU 使用信息。有一些推荐的步骤可以用来把语句事件监控器的开销减少到最小：

1. 事件监控器的默认 BUFFERSIZE 是 4 页，这需要增加到 512 页以获得更好的性能。
2. 在一个单独的表空间创建语句事件监控表。这避免了事件监控数据和其它表的 I/O 冲突。这样也让我们有机会选择更大的表空间，这样能最小化 SQL 语句被截断。
3. 在 CREATE EVENT MONITOR 命令的时候使用 TRUNC 选项。和存入另外的大对像列相比，这会强制把 SQL 语句文本保存在同一行，如花费的时间，CPU 时间等等…注意这可能会导致在事件监控结果中 SQL 语句被截断。根据语句事件监控表的页大小（比如，一个 4K 页能存 3500 字节），一部分 SQL 语句可以以这种方式存储。
4. 使用 WHERE 子句把监视范围集中在一部分连接或应用程序上。虽然监视连接会有额外的开销，但是使用 WHERE 子句将减少事件监控导致的总的系统开销。

把所有这些放在一起，并且从 LIST APPLICATIONS 得到我们关注的连接对应的应用程序 ID，我们将得到跟下面一样的结果：

create event monitor stmt_evt for statements 
 where appl_id = '*LOCAL.DB2.075D83033106' 
 write to table 
 connheader（table stmt_evt_ch, in tbs_evmon）, 
 stmt（table stmt_evt_stmt, in tbs_evmon, trunc）, 
 control（table stmt_evt_ctrl, in tbs_evmon）
 buffersize 512

对 WLM 活动监视器也有类似的原则。一个大的 BUFFERSIZE，单独的表空间和 TRUNC 选项都是减少开销的好思路。 WLM 活动监视器不支持 WHERE 子句，不过在活动监视器被定义的时候，在工作量或者服务类型里已经隐含了一个范围。和 WHERE 子句相同，这也能非常有效的降低性能开销和收集的数据量。注意如果在活动监视器使用了 WITHOUT DETAILS 字句 , 将提供我们所需要的 CPU 消耗的基本信息。提高到 WITH DETAILS 或者 WITH DETAILS AND VALUES 会提供其它的有用信息，但是如果监视开销堆你的环境是一个问题，那最好还是从 WITHOUT DETAILS 开始，然后在需要的情况下启用 DETAILS 或 VALUES 。

我们并不总是能够减少一个 SQL 语句请求的 CPU 量，但是在某些情况下我们可以影响它。

1. 一个频繁运行的对缓冲池中的表扫描能消耗令人吃惊的 CPU 时间。这可以发生在一个很小的表上，它没有合适的索引而它却会在一个连接中被查询到。我们能看到的症状包括
   • （i）相关地语句运行很短地时间
   • （ii）用户 CPU 消耗大致等于执行时间
   • （iii）在查询计划中又一个关系查询
   • （iv）db2pd – tcbstats 中查询数目不在增加
   • （v）对于这个语句只发生非常少地物理读。
   
   虽然这类语句不属于我们通常考虑的瓶颈，频繁执行和 CPU 的高消耗却能让它成为问题。通常我们的响应是创建一个索引给优化器在表查询的时候多一个选项。一个正确的索引定义可以在查询计划中明显的被看到，如果没有，在这里设计顾问程序可以提供协助。
2. 如果应用程序执行一个 SQL 语句却只会用到一小部分产生的数据，使用 OPTIMIZE FOR n ROWS（OFnR）和 FETCH FIRST n ROWS ONLY（FFnRO）子句能帮助减少所有类型的资源消耗，也包括 CPU 。详细说来，比起优化返回结果集中所有的行给应用程序，OFnR 和 FFnRO 能帮助优化 SQL 查询计划来返回结果集的初始行更加有效。如果只用了 OFnR，n 可以在运行时被超过；然而 FFnRO 将阻止超过 n 的行数被返回，即使应用程序也尝试这么做。
3. 正如之前配置章节我们提到的，使用了文化正确性校验序列的 Unicode 代码页会产生大量的开销，尤其在 CPU 消耗上。因为开销的总量和 SQL 语句产生的字符串数据比较量直接相关（在谓词中，或者在 ORDER BY 子句引起的排序，等等），如果我们能减少一个语句产生的数据比较量，我们将减少 CPU 消耗。这常常能通过鼓励在预先评估和结果集排序时都使用索引来实现。设计顾问程序会设计正确的索引来将表扫描和排序减到最少。
4. 锁的问题通常只存在于冲突和等待时间这两个方面。然而就算有很少或者根本没有冲突，获取和释放锁的过程也会消耗大量的 CPU 时间。考虑一个要检查表里很多行的应用程序或语句，由于只有它自己在运行就只产生了少量的锁冲突，因为对它涉及的表的访问是排他访问，或是因为所有并发的应用程序都使用的是只读模式。在这样的情况下为了降低 CPU，可以使用表级别的锁定来达到被要求的隔离级别

如果没有 SQL 语句看起来消耗了大量的 CPU，也有很多潜在问题会导致整体 CPU 消耗增加。

1. 比起使和用参数标记，许多应用程序通过连接语句片段和字符串值来构建它们的 SQL 语句。（注意，使用分发统计信息的复杂的 SQL 语句、以及嵌入的文字信息可以帮助 SQL 优化器选择一个更好的查询计划。作为替代，我们专注于嵌入文字信息没有任何好处的轻量级语句）

   String procNameVariable = "foo"; 
    String query = 
    "SELECT language FROM " 
    + "syscat.procedures " 
    + "WHERE procname =" 
    + " ‘ " 
    + procNameVariable// inject literal value 
    + " ‘ ";

   System Bottleneck > CPU Bottleneck > User CPU Bottleneck > Utilities Running?

2. 为了尽快完成工作，DB2 实用工具被设计成扩展良好并充分利用系统资源。那就是说在一个实用工具正在运行时，可能 CPU 使用会有显著的增加。 Load 和 runstats 是一个实用程序的很好的例子，它们经常导致高 CPU 使用，但是在正常情况下，其它的实用程序也能这样。正在运行的实用程序可以通过 LIST UTILITYES SHOW DETAIL 命令看到。

   如果实用程序正在执行，我们可以向下钻取来判断它的 CPU 使用情况。在 Unix 和 Linux，在 DB2 9.5 引入线程引擎之前只需通过一个简单的ps命令，就可以看见实用程序许多的工作进程（伴随这 CPU 的使用，等）到 DB2 9.5 db2pd – edus 命令显示了 DB2 引擎中的所有线程（见DB2 Process Model），包括用户和系统的 CPU 使用。这对决定是否某个线程是 CPU 瓶颈的时候非常有用。

   设置 UTL_IMPACT_PRIORITY 可以有助于限制 BACKUP 和 RUNSTATS 对系统的影响。另外 RUNSTATS 的开销和运行时间可以通过两个方法降低（1）收集统计信息时仅对那些会作为谓词的列（因此它需要统计信息）如果知道的话。（2）使用样本统计信息，也是很好的建议，在后面的“写和调优查询以优化性能”提到。

   默认情况下，在 LOAD 命令将对每个 CPU 都创建一个格式化程序线程（db2lfrm），但是通过使用 CPU PARALLELISM n 选项，我们可以把格式化程序减少到 n，来为剩下的系统留出更多的 CPU 能力。注意，通常情况下，通过 UTL_IMPACT_PRIORITY 或 CPU PARALLELISM 等来限制一个实用程序会成比例的延长使用程序的运行时间。

   System Bottleneck > CPU Bottleneck > User CPU Bottleneck > Temporary Object Cleanup Overhead

3. 当一个系统临时表不再需要然后可以删除了的时候，DB2 必须从缓冲池中移除它不在需要的页面。如果这经常发生，而且如果临时表和普通用户数据共享缓冲池，会导致耗费额外的 CPU 时钟周期来解决冲突和处理页面。比起复杂查询系统，这个问题在事务处理系统更常见。如果表快照显示有很多临时表被创建和销毁，最佳实践是把临时表放到它们自己的的缓冲池里面。这会消除多于的冲突和处理开销，并对降低 CPU 实用有好处。

在大多数 CPU 受限的环境中用户 CPU 往往是决定因素，不过系统 CPU 有时也能成为决定因素。虽然我们能诊断出很多原因，但是能最终解决的只有极少数。

系统 CPU 时间高的一个原因是与之相关的 DB2 在操作系统（OS）中上下文切换率过高。一个上下文切换是 OS 用来切换它需要处理的不同任务。上下文开关被系统中不同的规则触发。然而当上下文切换得太频繁，它们自己最终可能导致消耗大量 CPU 时间。在 Unix，上下文切换在 vmstat 中在‘ CS ’列有记录。每秒超过 75,000 到 100,000 次的上下文切速度被认为是非常高了。

在 DB2 系统中导致上下文切换率高的原因是大量的数据库连接。每个连接都有一个或多个的数据库代理进程为它工作，所以如果连很活跃－尤其对于很短的事务－会导致高上下文切换率何高 CPU 消耗的结果。避免这种情况的一种方法是开启 DB2 连接集中器。它允许多个连接共享一个代理程序，因此降低了代理数目（节约了内存占用），并降低了上下文切换率。

设备中断也会导致 CPU 时间高。一个设备（如网卡）发生一个中断就需要操作系统的‘注意’。一次中断的成本并不高，然而如果中断的频率太高，系统的总的负载会非常高。幸运的是，现在的网卡何磁盘转适配器已经从 OS 中高度独立出来了，比起前几年的产品，现在只产生了非常少的中断。来自磁盘的显著中断开销很少，不过在网络密集的客户端 / 服务器系统（像许多 SAP R/3）网络中断造成的开销可能会非常高。有些步骤可以用来降低网络在服务器上造成的开销，不过这类调试超过了本文讨论的范围。在这样的场合你最好还是让你的网络管理员来定位并解决问题。

如果应用逻辑（尤其是较长的事务）能被写入一个 SQL 存储过程，这会有助于减少上下文切换和网络通信量。这么做不仅是把应用程序逻辑存入服务器，在上下文切换问题范畴，它也直接把逻辑推入 DB2 代理程序。这就消除了代理程序和客户应用程序之间的 SQL 调用和结果的进出流 - 从而减少了上下文切换。

在一个 DB2 系统中，我们通常尽可能的让系统有很少的空闲内存并努力开发系统内存。不幸的是，如果我们过多的分配了内存 - 就像错误配置 DB2 或者其它软件一样，使用了超过系统物理内存的总量 - 页清除操作将造成系统 CPU 开销（磁盘开销也有可能）。这种情况在 UNIX 上通过低空闲内存和 vmstat 动态输出众较高的页面换入 / 换出，来确定。修正的办法就是把内存分配降到触发页清除操作起点之下。

文件缓存消耗了内存是稍微有点棘手的情况。为了避免 I/O，OS 通常使用空闲内存来缓存磁盘数据。虽然文件缓存使用的内存在需要的时候 DB2 可以使用，我们总是希望避免 DB2 和文件系统在内存上发生‘拔河’的情况。由于文件系统缓存处理它自己是发生在用户模式（它不消耗系统 CPU）而虚拟内存管理又能增加系统 CPU 使用，在这篇文章前面的‘ DB2 配置’章节中建议了要避免系统缓存影响 DB2 。在 AIX 上，使用 vmo 参数 LRU_FILE_REPAGE=0（同样在上面讨论过）能有助于确保 DB2 外的系统缓存开销在可控的范围。

拥有庞大内存总量的系统 - 100G 或者更多 - 如果系统没有配置使用大内存页就会产生额外的 CPU 开销。操作系统是以页为最小粒度来管理内存的（注意这和 DB2 的页有所不同）。一个典型的页大小是 4KB －这意味着操作系统必须管理 100G 内存的 250,000,000 个页表项。大多数操作系统都支持更大的页大小，这有助于减少虚拟内存管理的开销。以 AIX 为例，支持的最大页大小达到了 16G 。这虽然在实际中几乎用不到，不过有其它的页大小可供手动选择。在大多数拥有庞大内存的系统的最佳实践是确保在启用了 64-KB 页，在 AIX 中 DB2 可以使用它们。这是在良好性能和最小化使用大页面带来的副作用之间的折衷。在 Linux 中，DB2 必须通过DB2_LARGE_PAGE_MEM来手动启用对大数据页的支持。

在 AIX 上，vmstat -P ALL 显示了系统提供并使用了的页大小是多少。如果系统使用了 64KB 的页面并且数据库正在运行，通过 vmstat – P ALL 你可以看到非常多的 64KB 页被分配。如果系统有很大的物理内存却没有使用 64KB 页，这会导致高于正常水平的系统内存消耗。

系统 CPU 瓶颈 – 总图

图 2. 系统 CPU 瓶颈 – 总图

内存瓶颈

系统有充足的内存并得到正确的配置是良好性能的关键。否则，如果没有恰当的内存访问，数据在填满缓冲后就将转向 I/O - 在这个过程中常常会造成磁盘瓶颈。同样，虽然总量小一些却很重要的内存也被用来存储元数据以及运算结果，比如 SQL 查询计划和锁。如果它们缺少内存，系统不得不取消或销毁那些重要信息，并重新运算要不就以额外的处理来补偿－增加了 CPU 的开销。因此，一个内存瓶颈经常能把自己伪装成磁盘或 CPU 问题。

下表把我们在前面讨论过的可能由于底层内存的原因造成的磁盘和 CPU 瓶颈再回顾一下。

这是最常见的有 CPU 或磁盘瓶颈症状的内存瓶颈，并且是在分析这种问题时内存问题的主要可能性。然而在内存短缺的时候也是经常发生的（像 vmstat 报告的）－伴随着低性能让我们把这放到第一的位置－是明显的症状。如果进一步调查数据（vmstat）显示持续活跃的页面调度（有可能伴随系统 CPU 使用的升高），这表示系统内存压力过大。

DB2 的内存分配分成两大类：

1. 数据库和实例级分配，分配了像共享数据库内存，从缓冲池、排序堆、锁列表到包缓存等等。对于定位内存过度分配的原因，共享内存非常容易处理，因为分配是和数据库连接数没有关系。这些分配都受到 DATABASE_MEMORY 配置参数的限制。
2. 连接层面或应用程序分配，像应用程序堆和语句堆。连接层总的内存消耗明显依赖与连接数，这可能会使得在系统初始化配置时候更难预估。在 DB2 9.5 每个应用程序内存都在 INSTANCE_MEMORY 保护伞下，所以由于突然连接导致的逃逸分配变得不大可能。

无论是直接指定堆大小还是启用 STMM，判断 DB2 内存实际用量的最好办法就是查看 database,database manager 和应用程序快照的内存使用部分（或 sysibmadm.snapdb_memory_pool snapdbm_memory_pool, 和 snapagent_memory_pool 管理视图），它们提供了数据库，实例和应用层面各自的配置堆大小和当前堆大小。这些让你检查配置的和当前堆和应用程序的分配，以及检查当前总的分配。

DB2 9.5 内存使用的最高限制是通过 INSTANCE_MEMORY 数据库管理配置参数，和 DATABASE_MEMORY 数据库配置参数。在大多数系统中，它们默认为 AUTOMATIC, 这意味著实例和数据库的内存的使用可以上下浮动。（它们的当前值通过 get database manager configuration 和 get database configuration 命令的 SHOW DETIAL 选项）因此，DB2 的内存管理系统是设计来避免我们在这里讨论的内存瓶颈问题。为什么仍然发生内存瓶颈？

1. 如果 INSTANCE_MEMORY 被设置为一个确定的数值，并且 STMM 启动了，那么 STMM 将调整 DB2 内存消耗只达到 INSTANCE_MEMORY 的值。在这种情况下，STMM 不会对系统释放内存的压力做出反应（因为 INSTANCE_MEMORY 没有设置成 AUTOMATIC）因此，可能 DB2 和其它像应用程序服务器这样的内存使用大户等一起，会把系统范围的总的内存用量推到过高位置。 Unix 的 ps 命令或者 Windows 的任务管理器将显示进程的内存使用，是一个无价的工具用来跟踪数据库外的“memory hogs ”。
   

   如前面提到的，当文件系统缓存对于 DB2 这样的消费者是技术上存在的，DB2 也能成为大量文件缓存的原因（尤其对于在内存能释放给其它内存用户之前，修改的数据必须存到磁盘）如果产生了额外的内存需求，这可能导致发生页清除。

   如果在这个服务器上存在很强的非 DB2 的内存需求，INSTANCE_MEMORY 参数应该被设置成 AUTOMATIC，或降低到 DB2 在系统中能使用的内存范围。

2. 一个确定的 INSTANCE_MEMORY 值对系统而言太高的话可在另外的数据库启动之前可能不会出现问题，把 DB2 的整个分配高于系统可以调整不过仍然在 INSTANCE_MEMORY 之内。
   

   
   因为 STMM 被设计来处理内存压力，在需要的时候释放内存给操作系统，如果没有启动 STMM 这种情况会经常发生，或者涉及的系统（如 Solaris）不支持内存释放回操作系统，或当数据库内存需求非常动态（如，很快的创建 / 销毁数据库连接，或者数据库活跃很短周期，等。）

3. 如果数据库有非常大的连接需求，这可能导致实例的很大一部分内存被代理程序消耗。如果总量过多 - 留给数据库全局内存太少，无论有没有启动 STMM- 它都可以通过使用连接集中器来减少内存消耗。

‘懒惰系统’瓶颈

第四，也是最有意思的一类瓶颈，我们接下来一起来看一下‘懒惰系统’瓶颈。这表现为在前面几个瓶颈可能都不存在。没有（明显的）CPU，磁盘或者内存（或者网络，或…）瓶颈，系统也不忙。

在‘懒惰系统中’一个最常见的原因是锁争抢。幸运的是，锁争抢是很容易在快照数据中被检查到的。快照元素 lock_wait_time 和 locks_waiting 通过 sysibmadm.snapdb 分别显示总的锁等待时间和当前在等待锁的代理进程数，活动代理等待锁的百分比很高（如，20% 或者更高）与 / 或 锁等待的时间增加是发生瓶颈的明显迹象。

虽然不能判断每个语句的锁等待时间，但是通过动态 SQL 语句快照和语句事件监控器中被延长的执行时间、相关的 CPU 消耗以及 IO 活动，通常还是可以合理的判断出语句收到锁等待影响的时间。也就是说，应用程序快照同样报告了应用程序的锁等待时间，这对缩小在系统范围内引起锁等待的可能性的范围来说非常有用。我们可以从 sysibmadm.snaplockwait 视图中得到更多的锁等待信息。它显示：

• 锁类型 - 共享锁或排它锁
• 对像类型 – 行，表，等。
• 持有者和请求者的代理进程 ID

注意，不像其它许多 DB2 快照监控数据 , 锁的信息的存在周期是非常短暂的。除了 lock_wait_time 是总共的，其它大多数锁的信息在锁释放后也释放了。因此，在一段时间内周期性搜集锁和锁等待快照非常很重要，连续的场景也更容易被理解。像前面所介绍的，分析大量快照数据的最佳实践是通过管理视图并把它存入 DB2 。这尤其对于来自 snaplockwait 的数据是这样，它由应用程序数据，数据库和锁快照组成，并且不能通过快照命令得到。

虽然不像其它快照类型，与仅通过锁监控开关启用快照功能相比，锁快照的总开销是抓取快照本身。所以，虽然抓取快照并存入 DB2 很平常，但是过于频繁的快照会对它自己造成瓶颈。

有很多方法有助于降低锁争抢和锁等待时间

1. 如果可能，尽可能的避免运行时间过长的事务和 WITH HOLD 游标。持有锁的时间越长，与其它应用程序发生锁争抢的可能性就越大。
2. 要避免取得比实际需要更大的结果集，尤其是 REPEATABLE READ 隔离级别。简单的说，涉及的行数越多意味着持有更多的锁，而且有更多的机会运行到别人持有的锁。在实际情况下，这常常意味着降低 SELECT 的 WHERE 子句对行的选择标准，而不是返回更多的行却在应用程序里过滤它。

   图 3. 例子

   
3. 避免使用比实际需要更高的隔离级别。可重复读或许在应用程序中保护结果集完整性时是必需的，然而它也会在持有锁以及潜在的锁冲突方面造成额外的开销。
4. 如果应用程序的商业逻辑合理，考虑到通过db2_evaluncommitted,db2_skipdeleted, anddb2_skipinserted来更改锁的行为。这些设置让 DB2 可以推迟或避免在某些情况下产生锁，因而降低争抢并潜在的提高吞吐量。

锁升级也会成为发生争抢的主要原因。反之，经过良好设计的应用程序持有的个别行锁或许不会发生冲突，锁升级导致的块或表级别的锁常常会导致对象串行化以及严重的性能问题。数据库快照报告了一个全局的锁升级的总和（sysibmadm.snapdb.lock_escals）。通过检查写入 db2diag.log 中的消息（DIAGLEVEL 3），当锁升级发生的时候这很容易确定是哪个表上的升级 。

当一个应用程序消耗了他被允许的那一部分锁列表（取决于数据库配置参数 MAXLOCKS，它表现为锁列表大小的百分比），锁升级就被触发。因此增加 MAXLOCKS 和 / 或 锁列表可以降低锁升级的频率。同样，如上面提到的，降低应用程序持有的锁数目（通过增加提交频率，降低隔离级别，等）将有助于减少锁升级。

当锁等待的时间成为十分微妙的瓶颈的时候，死锁和锁超问题时就很难被忽略了，因为它们俩都对应用程序返回负的 SQL 码。虽然许多应用程序会简单的重试失败事务没有报出死锁并最终取得成功。在这种情况下能够最直接反映潜在死锁问题的是在 sysibmadm.snapdb 管理视图中的死锁监控元素。如上面提到的，我们建议把这作为搜集日常操作监控数据的一部分。

死锁的成本有所不同，它的成本直接和回滚的事务成正比。同样，每 1000 笔事务发生死锁次数超过一次通常意味着有问题。

死锁的频率有时可以很容易降低，通过保证所有应用程序以相同的顺序访问它们的数据 - 例如，访问（并且也锁定）行在表 Table A 中 , 接着是 Table B，接着是 Table C，等。如果两个应用程序以不同的顺序在相同对像上持有互斥的锁，它们发生死锁的风险会很大。

默认的死锁事件监控器db2detaildeadlock 将记录所有死锁信息并存在 deftdbpath（数据库管理器默认数据库路径），例如在 <dftdbpath>/NODE0000/SQL00001/db2event/db2detaildeadlock.

像所有事件监控器一样，它会带来少量的额外开销，但是能跟踪死锁带来的好处超过了这小小的性能降低。

锁超时对系统而言具有同死锁相同的破坏力。因为普通的死锁事件监控器不会跟踪锁超时，我们需要另外一个机制。 DB2 9.5 提供了一个技术用来产生一个基于文本关于锁超时的报告，它基于死锁事件监控器，在引擎中增加了其它基础构造。比起之前的版本，这极大的简化了锁超时的诊断。产生锁超时报告的流程在 developerworks：“New options for analyzing lock timeouts in DB2 9.5 ”中有描述。

比起代理程序自己读取数据，DB2 预取程序读取数据查询大量的数据对磁盘进行连续读执行的效率要高很多。这有多种原因 ，

1. 比如预取程序每次读取多个页面，读取的大小是数据库或表空间预取值，而代理程序一次只读取一个数据页。
2. 代理程序可以在预取程序工作时执行一部分查询，减少了计算和 I/O 的串行化。
3. 多个预取程序可以每个都分配读取一部分页面，达到 I/O 并行性。

当代理程序将需要一个页面范围的数据的时候，它会排队提出预取的请求。当代理程序需要要使用一个页面的时候，如果预取程序还没有开始它的 I/O（就是说，如果他还没有最终被预取程序取到，或如果这个请求还在预取队列），代理程序将自己读取那个页面。这会减少代理程序等待预取程序的频率（它将之等待实际进行中的 I/O）。然而，如果我们像这样回到使用代理程序 I/O，预取的所有好处都不复存在。

这种问题的症状包括：

• 一个大的查询语句低于 100% 的‘预取率’。（根据在数据库或者缓冲池层面，目标值会降低，基于它的那些活动不是基于扫描的。）我们设置这个值的度量类似于缓冲池命中率，不过在这里是以预取完成的物理读和总的物理读想比来计算的。

  100% * （pool_data_p_reads – async_data_reads） / pool_data_p_reads

  这样可以在数据库层面通过对 sysibmadm.snapdb、或者通过缓冲池层面的 sysibmadm.snapbp、在动态 SQL 语句层面的 namic SQL statement level with 计算

• 在 sysibmadm.snapdb 和 sysibmadm.snapappl 中的 prefetch_wait_time 显示较高并且不断攀升的‘花费在预取等待的时间’。如在上面所提到的，代理进程只等待实际上‘正在运行’的预取 I/O 。
• 类似于其它的“懒惰系统”问题，我们通常总会从 vmstat&perfmon 看见很高的空闲。不过 I/O 等待时间可能增加，因为代理进程读取单个页面的效率要远远少于预取进行大块的读取。但是即使如此，I/O 等待也不大可能爬升到足以造成瓶颈。

这个问题的最大可能性是预取器的数目（数据库配置参数 NUM_IOSERVERS）太少。在 DB2 v9 及以后可以有 AUTOMATIC 设置，而且后来使用像表空间并行这样的元素等，来计算预取器的个数，一般情况下不再需要调整。不过，在低预取率的时候调整显得还是必要的，流程如下：

1. 判断是否所有的预取器花费大致等于 CPU 时间。这在 DB2 v9 或之前的版本上可以通过的 ps 命令，或者在 db2 v9.5 及以后版本使用 db2pd – edu 来实现。如果有一些预取器消耗了比其它预取器少很多的 CPU，那么说明已经有足够多（可能过多）的预取器。（如果有超过一对‘空闲’预取器，你可以慢慢减少 NUM_IOSERVERS，不过有多余的预取器并不是问题。）
2. 通过 10% 增加 NUM_IOSERVERS 。让系统在有大量预取器的情况下可以正常运行。如果不能提高繁重查询的预取率，那么我对我这个问题的判断不正确，而且 NUM_IOSERVERS 应该被设回原始设置。
3. 重复这个流程直到你找到预取器在这个系统上合适的级别。

如果预取仍然低于同等操作水平，就非常需要验证 prefetchsize 是否被正确设置。DB2 Information Center对 于对这个验证流程已经进行了详细的讨论，我们就不再这里重复了。

类似于预取的另一个关于缓冲池页面清除的问题，如果强制代理进程终止它们的正常处理来执行 I/O，则通常应该有一个 DB2 的‘工作线程’来处理。然而在这种情况下，代理进程不得不进行写操作（更改页面）而非读取。这常常会涉及到类似‘页清除操作’。

应该说这个症状和上面老套的‘预取缺乏’不大一样。在一个有很多并发 DB2 代理进程的线事务处理（OLTP）环境中，缺少页面清除会导致更多的问题。如果它们不能找到干净的缓冲页就不得不自己进行页清除，这可能潜在的造成很多额外的单个页面写入容器。这意味着在这种情况下，我们可能看到一个 I/O 瓶颈，而不是一个通常意义上的‘懒惰系统’。发生的次数取决于连接的数目，页面清除程序的性能，等等。

一个相关症状是‘爆炸性的’系统活跃，如在 vmstat 中看到的。系统可能在短时间内运行很好，代理进程工作正常，在接下来的一段时间大多数代理进程会被阻塞，并自己刷新一个脏页面到磁盘。这将在 vmstat 中显示为高 I/O 等待和代理进程运行队列段时间空闲。等代理进程完成了页清除，性能又恢复如常 - 这将周期性重复。

在 DB2 ’ s 监控数据中，页清除的次数（在 sysibmadm.snapdb 中 pool_drty_pg_steal_clns）是是否出现问题最佳指示器。我们通常希望在一个流畅运行的系统中很少发生页清除操作，因此页面清除次数的任何异常的增长都由于某些原因导致的。

如果页清除下降并且发生换页操作，要检查的第一件事就是页面清除程序的数目（数据库配置参数 NUM_IOCLEANERS.）DB2 9 和之后的版本支持 AUTOMATIC 设置，这是遵循当前分区中每颗处理器一个页面清除程序的最佳实践。注意，在 DB2 9.5 中超过推荐值得多余的页面清楚程序会最终有损于性能。

在 DB2 8.2 中 DB2 支持两类页清除程序 - ‘典型’被动页清除程序（默认），和主动页清除程序。

• 典型的页面清除程序是受到两个 DB2 配置参数控制的

• CHAGPGS_THRESH – 判断用于激活页面清除程序的已更改缓冲池页面的百分比。
• SOFTMAX – 限制在缓冲池中最新更改页面与最老的已更改页面的 LSN 差距，以控制恢复时间。

• 减少它们中的任意一个通常都会使页面清除更有侵略性，不过要影响在缓冲池中干净页面的数目，CHNGPGS_THRESH 是首选的方法。降低 CHNGPGS_THRESH 或许可以帮助减少换页的次数，并且稳定起伏的页面清除。注意，这个参数设置过低会导致过多的磁盘写入，因此应该被设置为刚好能避免换页的程度。
• 主动页面清除（也就是熟知的轮流换页，或 APC）通过使用注册表变量 DB2_USE_ALTERNATE_PAGE_CLEANING 来启用。这不同于‘典型’的页面清除程序，在典型页清除中它调整它的清除比例来维持期望的 LSN 差距。比起清除被‘开’或‘关’、触发或不触发，APC 可以阻止它被激活以避免典型页面清除在某些时候的‘爆炸性’行为。和典型页面清除类似，减少 SOFTMAX 也有效的增加了页面清除的频率，并减少了换页。注意，如果系统之前是基于命中 CHNGPGS_THRESH 来清除页面的话，APC 则只受 SOFTMAX 控制。（例如，脏页阀值触发器。）

客户端应用程序和 DB2 服务器之间的请求以及前端与后端的响应和同步流，都意味著它们都是整个系统性能的角色之一。例如，在一批应用程序的运行时中的增加请求数，可能会导致系统缓慢，不过这也可能是由于应用程序对 DB2 的请求成功率下降的原故。这类问题的症状和 ‘懒惰系统’非常接近的模式。

对 DB2 的请求成功率减少的症状包括：

• 在应用程序中‘ UOW wait ’状态的代理进程数目增加（在 sysibmadm.snapappl_info 中的 appl_status ）- 意味着它们等待的时间多于工作时间。
• 请求到达代理进程的时间增加（sysibmadm.snapappl_info 中的 status_change_time）
• 客户端发起请求的时间间隔增加，比如在语句事件监控器中，或 CLI 以及 JDBC trace 中看到的。 CLI 和 JDBC 跟踪抓取客户端的 API 请求，以及记录请求发起的时间戳。虽然客户端 trace 的开销很大，不过它们有对包括网络响应时间计时的好处，以及搜集其它 DB2 引擎外部的因素。
• 如果有应用程序端度量标准，比如商业层事务吞吐量或响应时间可能表现为变慢。

如果应用程序端变慢则显示有问题，可能的原因包括

• 部署了一个新版的应用程序
• 一个在客户端和服务器之间的网络瓶颈
• 客户端系统负载过大– 例如太多用户或太多应用程序的副本在运行。

对 DB2 的请求成功率减少的症状包括：

• 在应用程序中‘ UOW wait ’状态的代理进程数目增加（在 sysibmadm.snapappl_info 中的 appl_status ）- 意味着它们等待的时间多于工作时间。
• 请求到达代理进程的时间增加（sysibmadm.snapappl_info 中的 status_change_time）
• 客户端发起请求的时间间隔增加，比如在语句事件监控器中，或 CLI 以及 JDBC trace 中看到的。 CLI 和 JDBC 跟踪抓取客户端的 API 请求，以及记录请求发起的时间戳。虽然客户端 trace 的开销很大，不过它们有对包括网络响应时间计时的好处，以及搜集其它 DB2 引擎外部的因素。

如果有应用程序端度量标准，比如商业层事务吞吐量或响应时间可能表现为变慢。

如果应用程序端变慢则显示有问题，可能的原因包括

• 部署了一个新版的应用程序
• 一个在客户端和服务器之间的网络瓶颈
• 客户端系统负载过大– 例如太多用户或太多应用程序的副本在运行。

图 4. 系统瓶颈 - 总图

点击查看大图

点击看大图

回页首

局部 vs. 系统范围的问题诊断

到现在为止，我们已经处理了系统中的整体性能问题 – 高层磁盘、CPU、内存和懒惰系统问题。但是性能问题并不总是以这样的形式出现。通常，整个系统运行正常，不过有一个用户或应用程序，或存储过程，或一个 SQL 语句 – 出现了问题。处理小范围的问题和系统范围的性能问题有什么不同？

幸运的是，在本文中提出的诊断性能问题方法论用在应用程序上也一样，不管是普遍的还是特定问题。我们所需要做的就是抽取系统可以提供的监控数据的相关部分。

假设我们有一个应用程序，它运行在我们期望的级别之下。在我们可以开始诊断问题之前，需要在系统上定位这个应用程序的范围

1 ．了解应用程序名字和应用程序使用的授权 ID，LIST APPLICATIONS 命令告诉我们‘ appl ID ’（例如，LOCAL.srees.0804250311139），这是定位这个应用程序详细监控数据的关键。

2 ．对于特定应用程序监控数据，应用程序快照是一个极好的资源，而且通过指定刚才得到的 appl ID，我们可以专注于 我们感兴趣的连接上。

db2 get snapshot for application applid  '*LOCAL.srees.080425031139'

在这里（或从 sysibmadm.snapappl 和 sysibmadm.snapapplinfo），我们可以判断很多关于应用程序的重要信息，比如在抓取快照时的正在执行的语句、缓冲池命中率、排序总时间、选择的行和读取的行的比例，以及 CPU 和花费的时间。在排序中，我们可以得到了很多和我们用在诊断系统级别问题相同的信息，然而在这种情况下只侧重我们关注的信息。

3 ．为了深入研究，我们也可以使用在语句事件监控器中的应用程序 ID 作为一个 WHERE 子句的子句，只专注于我们工作的这个应用程序搜集的事件监控信息。这将向我们提供应用程序的每条语句执行时间、缓冲池和 CUP 消耗信息。

虽然我们对改变全局 CPU 消耗或磁盘活动（不要忘记，整体很好）并不关注，但是理解状况是否发生在运行中的哪个应用程序却仍然十分重要。如果系统是 CPU 受限，而且我们的应用程序是 CPU 饥饿状态，则它的性能将会受到影响。类似，磁盘活跃情况也一样。

在收集了很多应用程序快照、事件监控的跟踪后，我们现在几乎拥有和系统范围问题一样的监控数据。我们的基本目标就是判断我们应该把时间花费在应用程序的什么地方 – 这也是瓶颈所在。应用程序中的哪些 SQL 语句运行时间最长？哪些语句消耗了绝大多数 CPU，或导致了绝大部分的磁盘 I/O ？模仿我们在系统范围的判断树来回答这些问题。

总结和结论

本文考虑了 3 个关键范围，它们对于在理解尝试避免你的系统性能降低时候非常重要：配置、监控和性能诊断。

我们的建议包括硬件和软件配置，它们可以帮助你确保良好的系统性能。我们讨论了很多监控技术这将帮助你在操作方面和问题诊断方面理解系统性能。同样为了有步骤的，有条不紊的处理问题，我们展示了一批 DB2 性能诊断的最佳实践。

如果你的系统配置恰当而且监控良好，你就可以有效的解决可能出现的性能问题，因此减少总的拥有成本并提高你的业务的投资回报。