OLTP 应用程序的 DB2 调优技巧

核心提示：简介DB2 Universal Database®（UDB）是第一个支持多媒体和 Web 的关系数据库管理系统，它的功能非常强大，OLTP 应用程序的 DB2 调优技巧，足以满足大公司的需求，并且它非常灵活，通过按照这里所描述的一些简单步骤，可以设置、监控和调优 DB2 数据库系统，足以满足中小企业的要求，DB

简介

DB2 Universal Database®（UDB）是第一个支持多媒体和 Web 的关系数据库管理系统，它的功能非常强大，足以满足大公司的需求，并且它非常灵活，足以满足中小企业的要求。DB2 产品系列软件和因特网技术的结合使我们可以方便地跨不同平台访问信息、使用信息并且保证信息安全。全世界有 30 多万家公司的 6000 多万个 DB2 用户依赖于 IBM 数据管理解决方案。

DB2 UDB 为大多数需要电子商务的应用程序（比如电子商务、企业资源计划、客户关系管理、供应链管理、Web 自助服务和商业智能）提供支持。这是一种可伸缩的、工业级数据库，非常适合用作电子商务发展过程中的数据管理基础。

联机事务处理（Online transaction processing，OLTP）是一类能为面向事务应用程序提供便利的应用程序，并可用来管理面向事务的应用程序，它通常用于处理许多行业的数据输入和检索事务，这些行业包括银行、航空、邮购、超市和制造业。通常，OLTP 工作负载包括许多并发运行的短事务。如今的联机事务处理日益要求支持跨网络以及可能包括多家公司的事务。因此，新的 OLTP 软件使用了客户机／服务器处理和代理软件，这种软件允许事务在一个网络的不同计算机平台上运行。

在任何一种数据库系统中，性能是最重要的因素之一。本文根据由运行 OLTP 类型的性能基准测试程序（TPC-C、TPC-W、Trade2 等）所得到的经验，着重讨论了许多 DB2 性能调优技巧。虽然数据库应用程序的性能会受许多因素影响，但是我们着重讨论配置而不是诸如容量规划、数据库设计或应用程序设计之类的因素。

本文的组织结构如下：

有关性能的一些基本要素

更新目录统计信息，这部分强调收集和维护最新数据库统计信息的重要性，缺少这项工作常常是导致许多性能问题的源头所在。

监控和调优数据库配置参数，这部分按照重要性的顺序描述了一列数据库管理器参数和数据库参数。通常，没必要尝试列表中的所有参数以实现性能目标。可以只尝试其中位于列表顶部的那几个，以查看是否有性能方面的改进。

有了这些技巧，就可以启动自己的 OLTP 应用程序并使其拥有非常好的运行性能。

有关性能的一些基本要素

有足够的内存。

对于 32 位系统，每个 CPU 至少使用 512 MB 的 RAM，最高可达每台机器 4 GB，以支持大量并发用户所需的缓冲池、DB2 代理程序和其它共享内存对象。（请参阅 “缓冲池大小（BUFFPAGE）”一节以获取有关缓冲池的更多信息。）可能需要更多的内存来支持在本地运行或作为存储过程运行的应用程序。在 AIX® 上 JFS 文件高速缓存可以使用额外的内存来补充缓冲池。

对于 64 位系统，缓冲池实际上可以是任何大小。但是，对于使用大型数据库的大多数电子商务 OLTP 应用程序，缓冲池大小实际上不需要超过 8 GB。越大当然越好，但是在某一点，当缓冲池命中率达到 98+% 时，会随内存的增加命中率反而下降。并发用户的数目（它影响 DB2 代理程序的数量）决定需要多少内存。

每个用户连接至数据库（即 DB2 代理程序）所需的内存数量取决于应用程序所执行的 SQL 语句的性质 － 比如打开的并发游标数以及所需的排序和临时空间的数量。对于 OLTP 应用程序，所需的排序和临时空间会比较少，一次只打开少数并发游标。

经验：对于每个 DB2 代理程序，在 UNIX 中最少使用 1 MB 内存，在 Windows 中最少使用 500 KB 内存。如果使用了受防护的存储过程，那么除了运行存储过程应用程序所需的内存之外，每个用户连接还有两个 DB2 代理程序。

有足够的 I/O 处理能力。

必须有足够的磁盘设备来确保充分的 I/O 并行性，以支持大容量的并发事务。对于中等工作负载而言，每个 CPU 至少应当有 5 到 10 个磁盘，对于高 I/O OLTP 工作负载而言，至少要有 20 个磁盘。操作系统（包括调页空间）、DB2 日志和 DB2 表空间应当拥有各自的专用磁盘。应当有多个磁盘用于 DB2 日志、表和索引。

估计良好性能所需的 I/O 处理能力的正确方式，实际上是制作事务原型并找出每个事务需要多少 I/O，以及每秒需要处理多少事务。然后找出磁盘控制器和磁盘子系统的 I/O 速率以帮助确定需要多少控制器和磁盘。

有足够的网络带宽。

必须有足够大的网络带宽以支持工作负载。请确保网络或任何中间集线器都不会成为瓶颈。当支持远程访问时这一点尤为重要。例如，T1 线路支持 1.544 Mb/s，这仅为 0.193 MB/s，而通常的 10 Mb/s 以太局域网可以支持 1.25 MB/s，吞吐量为 T1 线路的 6 倍。在 UNIX 上使用诸如 netstat 这样的命令可以监控连接上的流量。

使用 DB2 控制中心（DB2 Control Center）的 DB2 性能配置向导（DB2 Performance Configuration Wizard）来设置初始的 DB2 数据库管理器（Database Manager）和数据库配置（Database Configuration）参数。

这个工具会询问您一系列有关工作负载性质的问题，以便确定配置参数值的起始设置。您可以修改这些参数以满足生产工作负载的需要。

适当地为表列建立索引。

确保查询中进行连接操作的列都有索引。

如果为 ORDER BY 和 GROUP BY 所涉及的列建立了索引，那么可以提高性能。

也可以将经常被访问的数据作为 INCLUDE 子句中的列包含在索引中。

根据所使用的表和 SQL 语句，使用索引顾问程序（Index Advisor）（也称为索引向导 (Index Wizard)，可以从 DB2 控制中心调用该程序）来帮助确定使用一组合适的索引。

确保应用程序持有锁的时间尽可能短。

当用户操作涉及多个交互作用时，每个交互作用应当提交自己的事务并且应当在将活动返回给用户之前释放所有锁。通过尽可能晚地启动事务的第一个 SQL 语句（它启动一个事务）并使事务的更新（插入、更新和删除，这些操作要用到互斥锁）尽可能接近提交阶段，从而使事务的持续时间尽可能的短。

使用 DB2 注册表参数 DB2_RR_TO_RS，通过不锁定插入或更新行的下一个键，可以改进并发性。如果对同一组表进行操作的任何程序都没有使用隔离级别 RR（可重复读，Repeatable Read），那么就可以使用上述操作。使用 DB2 快照（DB2 Snapshot）监控死锁和锁等待的数目。

使用存储过程或复合 SQL 使网络成本降到最低。

将用于 SQL 语句的网络往返通信次数降至最低，可以减少网络等待时间和上下文切换，这样可以使应用程序持锁的时间更短。通常，当 OLTP 事务有 4 个或 5 个以上语句时应当使用存储过程。

另一方面，如果应用程序逻辑中涉及了某个复杂的 CPU 密集型处理，那么将它放在运行于数据库服务器上的存储过程中会用光数据库服务器上的额外 CPU 周期，从而牺牲一些数据库操作。在这种情况下，要么不使用存储过程，要么在客户机端执行一部分逻辑，而在存储过程中执行其余的逻辑。

有效地使用 SQL。

通常，如果一条 SQL 语句能完成任务，那么就不使用多条 SQL 语句。当通过在查询中设置更多谓词来提供更详细的搜索条件时，优化器就有机会作出更好的选择。您还应该使查询具有可选择性，这样数据库就不会返回您不需要的行和列。例如，使用 SQL 来过滤您想要的行；不用返回所有行，然后要求应用程序执行过滤操作。

分析存取方案。

使用可视化说明（Visual Explain）或 db2exfmt 来分析每一条 SQL 语句。请确保使用合适的索引，从而在选择和连接（join）表时，将必须在内部访存的行数减到最少。

更新目录统计信息

背景知识

RUNSTATS 实用程序用于更新系统目录表中的统计信息，以帮助查询优化过程。如果没有这些统计信息，数据库管理器可能会做出对 SQL 语句的性能产生不利影响的决定。RUNSTATS 实用程序允许您收集表和／或索引中所包含数据的统计信息。使用 RUNSTATS 实用程序收集基于表和索引数据的统计信息，以便为下列情形中的存取方案选择过程提供精确的信息：

当向表装入数据并创建了合适的索引时。

当用 REORG 实用程序重新组织表时。

当存在大量影响表及其索引的更新、删除和插入操作时。（此处的“大量”可能意味着 10% 到 20% 的表和索引数据都受到了影响。）

在绑定性能至关重要的应用程序之前。

当您希望将新的和以前的统计信息进行比较时。定期进行统计使您能够在早期阶段发现性能问题。

当预取数量发生变化时。

当您已经使用了 REDISTRIBUTE NODEGROUP 实用程序时。

当对 SQL 查询进行优化时，SQL 编译器所做出的决定会受到优化器的数据库内容模型的重大影响。优化器使用该数据模型来估计可以用于解决某个特定查询的其它存取路径的成本。数据模型中的关键元素是一组统计信息，该统计信息收集了有关数据库中所包含的数据和系统目录表中所存储的数据的信息。这包括表、别名（nickname）、索引、列和用户定义的函数（UDF）的统计信息。数据统计信息中的变化会引起对存取方案的选择发生变化，该存取方案作为访问所期望数据的最有效方法。

下面列举了一些统计信息，这些统计信息可以帮助给优化器定义数据模型：

表中的页数和非空的页数。

从原始页移到其它（溢出）页的程度。

表中的行数。

有关单个列的统计信息，比如一列中唯一值的数量。

一个索引的群集程度；即，表中行的物理顺序与索引的符合程度。

有关索引的统计信息，比如索引级别的数量和每个索引中叶子页的数量。

经常使用的列值的出现次数。

列值在列中所有值中的分布状况。

用户定义的函数（UDF）的成本估计。

RUNSTATS 可以帮助您确定对数据库的更改与性能之间的关系。统计信息显示出表中的数据分布状况。常规使用时，RUNSTATS 提供了在一段时期内有关表和索引的数据，从而随着时间的流逝，可以确定数据模型的性能趋势。在使用 RUNSTATS 之后需要重新绑定使用静态 SQL 的应用程序，这样查询优化器就可以选择新统计信息所给出的最佳存取方案。但是，对于使用动态 SQL 的应用程序（比如大多数供应商应用程序）而言，没必要进行重新绑定，因为语句的优化是根据统计信息在运行时进行的。当有关表的统计信息不准确时，可能会造成性能问题。最糟的情况是，某个特定的 SQL 语句可能会造成 DB2 使用表扫描而不是使用索引扫描。

如何更新统计信息

只有当进行显式的请求时，对象的统计信息才会在系统目录表中被更新。有几种方法可以更新部分或全部统计信息：

使用 RUNSTATS（运行统计信息，run statistics）实用程序。

使用带有指定的统计信息收集选项的 LOAD。

对针对一组预先定义的目录视图进行操作的 SQL UPDATE 语句进行编码。

使用“reorgchk update statistics”命令。

当您不完全知道所有表名或表名实在太多时，进行 RUNSTATS 的最简单方法就是使用“db2 reorgchk update statistics”命令。正确的脚本如下：

db2 -v connect to DB_NAME db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes" db2 -v reorgchk update statistics on table all db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes" db2 -v terminate

我们上面所选的示例不需要表名。这一命令对所有表执行 RUNSTATS。

记住： 在填充数据库之后再运行 RUNSTATS 实用程序。

如果您知道表名并且想避免对大量表运行 RUNSTATS 实用程序（因为这样做可能要花很长时间），那么一次对一张表进行 RUNSTATS 更为可取。命令如下：

db2 -v runstats on table 　　　　　TAB_NAME and indexes all

这个命令将收集该表及其所有索引（基本级别）的统计信息。

查看是否运行了 RUNSTATS

要查看是否对数据库执行了 RUNSTATS，一种快捷方法便是查询一些系统目录表。例如，如上面的脚本所示，可以运行下面这条命令：

db2 -v "select tbname, nleaf, nlevels, stats_time from sysibm.sysindexes"

如果还未运行 RUNSTATS，您会看到 nleaf 和 nlevels 列为“-1”且 stats_time 列为“-”。如果已经运行了 RUNSTATS，则这些列包含实际的数字，并且如果运行过 RUNSTATS，则 stats_time 列将包含时间戳记。如果您认为 stats_time 中所示时间离现在已有很长一段时间，那就该再次运行 RUNSTATS。

监控和调优数据库配置参数

下面这些有关数据库配置调优的技巧将使您在 OLTP 环境中取得非常好的性能，同时使您能够避免显而易见的“陷阱”。在配置参数中，数据库管理器配置参数需要重新启动数据库管理器，而为了使更改生效，大多数数据库配置参数都要求应用程序重新连接到数据库。

这里描述的配置参数包括：

缓冲池大小

日志缓冲区大小

应用程序堆大小

排序堆大小和排序堆阈值

代理程序的数目

锁

活动应用程序的最大数目

异步页清除程序的数目

I/O 服务器的数目

编入组中的提交数目

缓冲池大小

背景知识

缓冲池是内存中的一块存储区域，用于临时读入和更改数据库页（包含表行或索引项）。缓冲池的用途是为了提高数据库系统的性能。从内存访问数据要比从磁盘访问数据快得多。因此，数据库管理器需要从磁盘读取或写入磁盘的次数越少，性能就越好。对一个或多个缓冲池进行配置之所以是调优的最重要方面，是因为连接至数据库的应用程序的大多数数据（不包括大对象和长字段数据）操作都在缓冲池中进行。

缺省情况下，应用程序使用缓冲池 IBMDEFAULTBP，它是在创建数据库时创建的。当 SYSCAT.BUFFERPOOLS 目录表中该缓冲池的 NPAGES 值为 -1 时，DB2 数据库配置参数 BUFFPAGE 控制着缓冲池的大小。否则会忽略 BUFFPAGE 参数，并且用 NPAGES 参数所指定的页数创建缓冲池。

建议

对于仅使用一个缓冲池的应用程序，将 NPAGES 更改成 -1，这样 BUFFPAGE 就可以控制该缓冲池的大小。这使得更新和报告缓冲池大小以及其它 DB2 数据库配置参数变得更加方便。

确保可以使用数据库配置中的 BUFFPAGE 参数来控制缓冲池大小之后，将该参数设置成合适的值。根据数据库的大小和应用程序的性质将该参数设置成一个合理的大值，这种做法很安全。通常，该参数的缺省值非常小，可能满足不了要求。请考虑下列情况：

一开始，如果您的机器上有足够大的内存，请将 BUFFPAGE 设置成 40000 个页（160 MB），或者等于机器总内存的 10%。

对于大型 OLTP 数据库，在保持系统稳定的同时为缓冲池留出尽可能多的内存。一开始，先尝试使用 1.6 GB 的内存，然后尝试用更多内存。

如何更改该参数

运行下面这个脚本，以便：

验证目录值

启用数据库配置参数 BUFFPAGE

更新所有数据库的 BUFFPAGE 值。

db2 -v connect to DB_NAME db2 -v select * from syscat.bufferpools db2 -v alter bufferpool IBMDEFAULTBP size -1 db2 -v connect reset db2 -v update db cfg for dbname using BUFFPAGE bigger_value db2 -v terminate

研究步骤

要确定数据库的缓冲池大小是否由 BUFFPAGE 参数所决定，请运行：

db2 -v connect to DB_NAME db2 -v SELECT * from SYSCAT.BUFFERPOOLS db2 -v connect reset db2 -v terminate

检查结果。如果每个缓冲池都有一个为“-1”的 NPAGES 值，那么缓冲池大小是由数据库配置中的 BUFFPAGE 参数控制的。

要确定缓冲池大小是否足够大，请在运行应用程序时收集数据库和／或缓冲池的快照。类似于下面的脚本为您提供这些所需的信息：

db2 -v update monitor switches using bufferpool on db2 -v get monitor switches db2 -v reset monitor all 　　　　　 -- run your application -- db2 -v get snapshot for all databases > snap.out db2 -v get snapshot for dbm >> snap.out db2 -v get snapshot for all bufferpools >> snap.out db2 -v reset monitor all db2 -v terminate

请确保您在断开数据库连接之前发出“db2 -v get snapshot”。当最后一个应用程序与数据库断开连接时，该数据库停止运行，同时所有快照统计信息将会丢失。要确保一直存在使数据库处于正常运行状态的连接，请使用下列方法之一：

在收集快照的窗口中保持一个单独的连接。

使用 DB2 ACTIVATE DATABASE 命令。

在数据库快照或缓冲池快照的快照输出中，查找下列“logical reads”和“physical reads”，这样就可以计算出缓冲池命中率，它可以帮助您调优缓冲池：

-- Related lines from a sample of bufferpool snapshots -- Buffer pool data logical reads = 702033 Buffer pool data physical reads = 0 Buffer pool data writes = 414 Buffer pool index logical reads = 168255 Buffer pool index physical reads = 0

缓冲池命中率表明数据库管理器不需要从磁盘装入页（即该页已经在缓冲池中）就能处理页请求的时间百分比。缓冲池的命中率越高，使用磁盘 I/O 的频率就越低。按如下计算缓冲池命中率：

(1 - ((buffer pool data physical reads + buffer pool index physical reads) / (buffer pool data logical reads + pool index logical reads)) ) * 100%

这个计算考虑了缓冲池高速缓存的所有页（索引和数据）。理想情况下，该比率应当超过 95%，并尽可能接近 100%。要提高缓冲池命中率，请尝试下面这些方法：

增加缓冲池大小。

考虑分配多个缓冲池，如果可能的话，为每个经常被访问的大表所属的表空间分配一个缓冲池，为一组小表分配一个缓冲池，然后尝试一下使用不同大小的缓冲池以查看哪种组合会提供最佳性能。

如果已分配的内存不能帮助提高性能，那么请避免给缓冲池分配过多的内存。应当根据取自测试环境的快照信息来决定缓冲池的大小。

日志缓冲区大小（LOGBUFSZ）

背景知识

LOGBUFSZ 是一个数据库配置参数。它是用于日志缓冲区的参数。它允许您指定数据库共享内存的大小以用作在将日志记录写到磁盘之前这些记录的缓冲区。当下列事件之一发生时会将日志记录写到磁盘：

事务提交。

日志缓冲区已满。

其它某个内部数据库管理器事件发生时。

将日志记录存在缓冲区将产生更加有效的日志文件 I/O，这是因为这样一来可以降低将日志记录写到磁盘的频率，同时每次可写更多的日志记录。如果对专用的日志磁盘有相当多的读操作，或者希望有较高的磁盘利用率，那么可以增加这个缓冲区的大小。当增加这个参数的值时，也要考虑 DBHEAP 参数，因为日志缓冲区使用的空间由 DBHEAP 参数所控制。

如何更改该参数

我们发现该参数的缺省值为 8（4KB 页），这对于 OLTP 数据库而言通常不够大。LOGBUFSZ 的最佳值为 128 个或 256 个 4KB 页。例如，可以使用下面这个命令来更改该参数值：

db2 -v update database cfg for DB_NAME using LOGBUFSZ 256 db2 -v terminate

研究步骤

通过查看下面这个示例中所示各行，使用数据库快照来确定 LOGBUFSZ 参数的值是否为最佳值：

Log pages read = 0 Log pages written = 12644

一般而言，“log pages read”和“log pages written”之比应当尽可能小。理想情况下，“log pages read”的值应为 0，而“log pages written”的值应很大。当 log pages read 太多时，意味着需要一个较大的 LOGBUFSZ。

应用程序堆大小（APPHEAPSZ）

背景知识

APPHEAPSZ 是一个数据库配置参数，它定义了代表某个特定代理程序或子代理程序的数据库管理器可以使用的私有内存页数。在为应用程序初始化代理程序或子代理程序时分配堆。分配的堆大小是处理给予代理程序或子代理程序的请求所需的最小值。当代理程序或子代理程序需要更多的堆空间以处理较大的 SQL 语句时，数据库管理器将按照需要分配内存，所分配的内存大小最大可达到该参数所指定的最大值。

如何更改该参数

下面这条命令可以将缺省值（DB2 EE 为 128 个 4KB 页，DB2 EEE 为 64 个 4KB 页）更改成最佳值：

db2 -v update db cfg for DB_NAME using applheapsz 256 db2 -v terminate

研究步骤

当应用程序接收到一个表明应用程序堆中存储空间不够的错误时，应该增加 APPHEAPSZ 的值。

排序堆大小（SORTHEAP）和排序堆阈值（SHEAPTHRES）

背景知识

SORTHEAP 是一个数据库配置参数，它定义了私有排序所使用的私有内存页的最大数目，或共享排序所使用的共享内存页的最大数目。如果排序是私有排序，那么该参数影响代理程序私有内存。如果排序是共享排序，那么该参数影响数据库的共享内存。每个排序都有单独的由数据库管理器按需分配的排序堆。在排序堆中对数据进行排序。如果由优化器来指导排序堆大小的分配，那么用优化器提供的信息来分配的排序堆的大小要小于由该参数所指定的排序堆大小。

SHEAPTHRES 是一个数据库管理器配置参数。私有和共享排序所使用内存的来源不一样。共享排序内存区的大小是在第一次连接到数据库时根据 SHEAPTHRES 值以静态方式预先确定的。私有排序内存区的大小是不受限制的。对于私有排序和共享排序，应用 SHEAPTHRES 参数的方式不同：

对于私有排序，SHEAPTHRES 是对私有排序在任何给定的时间可以消耗的全部内存的实例级“软”限制。当实例的总私有排序内存消耗量达到这一限制时，为其它进入的私有排序请求而分配的内存会大大减少。

对于共享排序，SHEAPTHRES 是对共享排序在任何给定的时间可以消耗的全部内存的数据库级“硬”限制。当达到这一限制时，不允许有其它共享排序内存请求，直到总的共享内存消耗量回落到 SHEAPTHRES 所指定的限制以下。

使用排序堆的操作示例包括内存中表的散列连接和操作。阈值的显式定义防止数据库管理器将过多数量的内存用于大量排序。

建议

使用数据库系统监视器来跟踪排序活动。

使用合适的索引使排序堆的使用降到最低。

当需要频繁进行大型排序时，增加 SORTHEAP 的值。

如果增加 SORTHEAP，请确定是否还需要调整数据库管理器配置文件中的 SHEAPTHRES 参数。

优化器用排序堆大小来确定存取路径。在更改该参数后请考虑重新绑定应用程序（使用 REBIND PACKAGE 命令）。

理想情况下，应当将排序堆阈值（SHEAPTHRES）参数合理地设置为在数据库管理器实例中设置的 SORTHEAP 参数最大值的倍数。该参数至少应当是实例中任何数据库所定义的最大 SORTHEAP 的两倍。

如何更改这些参数

要更改 SORTHEAP 和 SHEAPTHRES 的值，请运行以下命令：

-- SORTHEAP should be changed for individual database -- db2 -v update db cfg for DB_NAME using SORTHEAP a_value -- SHEAPTHRES is a database manager parameter -- db2 -v update dbm cfg using SHEAPTHRES b_value db2 -v terminate

研究步骤

OLTP 应用程序不应该执行大型排序。大型排序在 CPU 和 I/O 资源方面的成本太高了。通常，SORTHEAP 大小的缺省值（256 个 4KB 页）就足够了。事实上，对于高并发性 OLTP，您可能希望降低这个缺省值。当需要进一步研究时，可以发出下面这条命令：

db2 -v update monitor switches using sort on

然后，让您的应用程序运行一会，然后输入：

db2 -v get snapshot for database on DBNAME

看一下下面这个示例中的输出：

Total sort heap allocated = 0 Total sorts = 1 Total sort time (ms) = 0 Sort overflows = 0 Active sorts = 0 Commit statements attempted = 1 Rollback statements attempted = 0 Dynamic statements attempted = 4 Static statements attempted = 1 Binds/precompiles attempted = 0

根据该输出，可以计算每个事务的排序数目，并可以计算溢出了可用于排序的内存的那部分排序的百分比。

SortsPerTransaction = (Total Sorts) / (Commit statements attempted + Rollback statements attempted) PercentSortOverflow = (Sort overflows * 100 ) / (Total sorts)

经验：如果 SortsPerTransaction 大于 5，它可能表明每个事务的排序太多。如果 PercentSortOverflow 大于 3%，那么可能发生了严重的、未曾预料到的大型排序。发生这种情况时，增加 SORTHEAP 只会隐藏性能问题 － 却无法修正它。这个问题的正确解决方案是通过添加正确的索引改进有问题的 SQL 语句的存取方案。

代理程序的数目（MAXAGENTS、NUM_POOLAGENTS 和 NUM_INITAGENTS）

背景知识

这些是数据库管理器配置参数。

MAXAGENTS 参数表明在任何给定时间接受应用程序请求的数据库管理器代理程序的最大数目。MAXAGENTS 的值应当至少是每个被并发地访问的数据库中的 MAXAPPLS（并发应用程序最大数目）值的总和。如果数据库的数量大于 NUMDB 参数，那么最安全的方案就是使用 NUMDB 和 MAXAPPLS 最大值的乘积。每个额外的代理程序都需要一些资源开销，这些开销在启动数据库管理器时会分配给代理程序。

NUM_POOLAGENTS 参数是用于评定您希望代理程序池增加到多大的准则。如果所创建的代理程序多于该参数值所指明的数目，那么当代理程序执行完自己当前的请求后将终止运行而不是返回给代理程序池。如果该参数的值为 0，将按照需要创建代理程序，在代理程序执行完自己当前的请求后终止运行。

要避免因在并发连接许多应用程序的 OLTP 环境中频繁创建和终止代理程序而产生的成本，请将 NUM_POOLAGENTS 的值增加到接近 MAXAGENTS 值。

NUM_INITAGENTS 参数决定空闲代理程序的初始数量，这些代理程序是在 DB2START 时在代理程序池中创建的。指定初始代理程序数目要合适（尽管并非必要条件），这可以缩短“热身”时间。

建议

在大多数情况下，将 MAXAGENTS 和 NUM_POOLAGENTS 的值设置成略微大于并发应用程序连接的最大预计数目。

让 NUM_INITAGENTS 保留为缺省值会比较好。

如何更改该参数

为了更改这些参数，请运行以下命令：

db2 -v update dbm cfg using MAXAGENTS a_value db2 -v update dbm cfg using NUM_POOLAGENTS b_value db2 -v update dbm cfg using NUM_INITAGENTS c_value db2 -v terminate

研究步骤

在运行期间的任何时候，您都可以使用下面这个命令来获取数据库管理器的快照数据：

db2 -v get snapshot for database manager

看一下下列输出行：

High water mark for agents registered = 4 High water mark for agents waiting for a token = 0 Agents registered = 4 Agents waiting for a token = 0 Idle agents = 0 Agents assigned from pool = 5 Agents created from empty pool = 4 Agents stolen from another application = 0 High water mark for coordinating agents = 4 Max agents overflow = 0

如果您发现“Agents waiting for a token”或“Agents stolen from another application”不等于 0，则可能需要增加 MAXAGENTS 以允许数据库管理器可以使用更多的代理程序。

锁（LOCKLIST、MAXLOCKS 和 LOCKTIMEOUT）

背景知识

这些与锁相关的控制都是数据库配置参数：

LOCKLIST 表明分配给锁列表的存储容量。每个数据库都有一个锁列表，锁列表包含了并发连接到该数据库的所有应用程序所持有的锁。锁定是数据库管理器用来控制多个应用程序并发访问数据库中数据的机制。行和表都可以被锁定。根据对象是否还持有其它锁，每把锁需要 32 个或 64 个字节的锁列表：

需要 64 个字节来持有某个对象上的锁，在这个对象上，没有持有其它锁。

需要 32 个字节来记录某个对象上的锁，在这个对象上，已经持有一个锁。

MAXLOCKS 定义了应用程序持有的锁列表的百分比，在数据库管理器执行锁升级之前必须填充该锁列表。当一个应用程序所使用的锁列表百分比达到 MAXLOCKS 时，数据库管理器会升级这些锁，这意味着用表锁代替行锁，从而减少列表中锁的数量。当任何一个应用程序所持有的锁数量达到整个锁列表大小的这个百分比时，对该应用程序所持有的锁进行锁升级。如果锁列表用完了空间，那么也会发生锁升级。数据库管理器通过查看应用程序的锁列表并查找行锁最多的表，来决定对哪些锁进行升级。如果用一个表锁替换这些行锁，将不再会超出 MAXLOCKS 值，那么锁升级就会停止。否则，锁升级就会一直进行，直到所持有的锁列表百分比低于 MAXLOCKS。MAXLOCKS 参数乘以 MAXAPPLS 参数不能小于 100。

虽然升级过程本身并不用花很多时间，但是锁定整个表（相对于锁定个别行）降低了并发性，而且数据库的整体性能可能会由于对受锁升级影响的表的后续访问而降低。

下面是一些控制锁列表大小的建议：

经常进行提交以释放锁。

当执行大量更新时，更新之前，在整个事务期间锁定整个表（使用 SQL LOCK TABLE 语句）。这只使用了一把锁从而防止其它事务妨碍这些更新，但是对于其他用户它的确减少了数据并发性。

使用 altER TABLE 语句的 LOCKSIZE 参数控制如何在持久基础上对某个特定表进行锁定。

查看应用程序使用的隔离级别。使用可重复读隔离级别在某些情况下可能会导致自动执行表锁定。当有可能减少所持有共享锁的数量时，可以使用游标稳定性（Cursor Stability）隔离级别。如果没有损害应用程序完整性需求，那么可以使用未提交的读隔离级别而不是游标稳定性隔离级别，以进一步减少锁的数量。

使用下列步骤确定锁列表所需的页数：

计算锁列表大小的下限：(512 * 32 * MAXAPPLS) / 4096，其中 512 是每个应用程序平均所含锁数量的估计值，32 是对象（已有一把锁）上每把锁所需的字节数。

计算锁列表大小的上限：(512 * 64 * MAXAPPLS) / 4096，其中 64 是某个对象上第一把锁所需的字节数。

对于您的数据，估计可能具有的并发数，并根据您的预计为锁列表选择一个初始值，该值位于您计算出的上限和下限之间。

使用数据库系统监视器调优 MAXLOCKS 值。

设置 MAXLOCKS 时，请考虑锁列表的大小（LOCKLIST）：

MAXLOCKS = 100 * (512 锁／应用程序 * 32 字节／锁 * 2) / (LOCKLIST * 4096 字节)

该样本公式允许任何应用程序持有的锁是平均数的两倍。如果只有几个应用程序并发地运行，则可以增大 MAXLOCKS，因为在这些条件下锁列表空间中不会有太多争用。

LOCKTIMEOUT 指定了应用程序为获取锁所等待的秒数。这有助于应用程序避免全局死锁。

如果将该参数设置成 0，那么应用程序将不等待获取锁。在这种情形中，如果请求时没有可用的锁，那么应用程序立刻会接收到 -911。

如果将该参数设置成 -1，那么将关闭锁超时检测。在这种情形中，应用程序将等待获取锁（如果请求时没有可用的锁），一直到被授予了锁或出现死锁为止。

建议

设置 LOCKTIMEOUT 以快速检测由于异常情形而出现的等待，比如事务被延迟了（可能是由于用户离开了他们的工作站）。将它设置得足够高，这样有效的锁请求就不会因为高峰时的工作负载而超时，在高峰时等待获取锁的时间将延长。

在联机事务处理（OLTP）环境中，这个值从 30 秒开始。在只进行查询的环境中可以从一个更大的值开始。无论哪种情况，都可使用基准测试技术来调优该参数。

如何更改这些参数

要更改锁参数，请运行以下命令：

db2 -v update db cfg for DB_NAME using LOCKLIST a_number db2 -v update db cfg for DB_NAME using MAXLOCKS b_number db2 -v update db cfg for DB_NAME using LOCKTIMEOUT c_number db2 -v terminate

研究步骤

一旦锁列表满了，由于锁升级生成更多的表锁和更少的行锁，因此减少了数据库中共享对象的并发性，从而降低了性能。另外，应用程序间可能会发生更多死锁（因为它们都等待数量有限的表锁），这会导致事务被回滚。当数据库的锁请求达到最大值时，应用程序将接收到值为 -912 的 SQLCODE。如果锁升级造成性能方面的问题，则可能需要增大 LOCKLIST 参数或 MAXLOCKS 参数的值。可以使用数据库系统监视器来确定是否发生锁升级，跟踪应用程序（连接）遭遇锁超时的次数，或者数据库检测到的所有已连接应用程序的超时情形。

首先，运行下面这个命令以打开针对锁的 DB2 监视器：

db2 -v update monitor switches using lock on db2 -v terminate

然后收集数据库快照：

db2 -v get snapshot for database on DB_NAME

在快照输出中，检查下列各项：

Locks held currently = 0 Lock waits = 0 Time database waited on locks (ms) = 0 Lock list memory in use (Bytes) = 504 Deadlocks detected = 0 Lock escalations = 0 Exclusive lock escalations = 0 Agents currently waiting on locks = 0 Lock Timeouts = 0 Internal rollbacks due to deadlock = 0

如果“Lock list memory in use (Bytes)”超过定义的 LOCKLIST 大小的 50%，那么就增加 LOCKLIST 数据库配置参数中的 4KB 页的数量。锁升级、锁超时和死锁将表明系统或应用程序中存在某些潜在问题。锁定问题通常表明应用程序中存在一些相当严重的并发性问题，在增大锁列表参数的值之前应当解决这些问题。

活动应用程序的最大数目（MAXAPPLS）

背景知识

MAXAPPLS 是一个数据库配置参数。它指定了可以连接到数据库的并发应用程序（本地和远程）的最大数量。由于需要给连接到数据库的每个应用程序分配一些私有内存，因此允许有更多并发应用程序将意味着用去更多内存。该参数值必须大于等于已连接应用程序的数量，加上这些相同的应用程序中完成两阶段提交或回滚过程中可能并发存在的数量的总和。

建议

要运行 OLTP 应用程序，请确保将 MAXAPPLS 的值设置正确（足够大但不能是没必要的大）以容纳最多的并发用户／连接。对于那些使用连接池的应用程序，我们建议将 MAXAPPLS 的值设置成比连接池的大小大 1 或 2（这样做只是为了以防需要调用命令行连接来同时做一些事情）。

如何更改该参数

要更改 MAXAPPLS 的值，请运行下面的命令：

db2 -v update db cfg for DB_NAME using MAXAPPLS a_number db2 -v terminate

研究步骤

当应用程序尝试连接数据库，但是连接到数据库的应用程序数已经达到了 MAXAPPLS 的值时，会向应用程序返回下面这个错误，表明连接到该数据库的应用程序数已达到了最大值。

SQL1040N The maximum number of applications is already connected to the database. SQLSTATE=57030

异步页清除程序的数目（NUM_IOCLEANERS）

背景知识

NUM_IOCLEANERS 是一个数据库配置参数，它可以让您指定数据库的异步页清除程序的数目。在数据库代理程序需要缓冲池中的空间之前，这些页清除程序将缓冲池中已更改的页写到磁盘。这允许代理程序不必等待已更改页被写到磁盘就可以读取新页。因此，这会加快应用程序事务的运行。

如果将该参数设置成 0，则不启动页清除程序，结果，数据库代理程序将缓冲池中的所有页写到磁盘。该参数会对存储在多个物理存储设备上的单个数据库的性能产生显著影响，这是因为在这种情况下其中某个设备极有可能处于空闲状态。如果没有配置页清除程序，则应用程序可能会遇到不时发生的“日志已满”情况。

如果连接到数据库的应用程序主要执行更新数据的事务，那么增加清除程序的数目会提高性能。增加页清除程序的数量还会减少“软”故障（比如断电）的恢复时间，因为磁盘上数据库的内容在任何给定时候都是比较新的。

当设置该参数的值时要考虑下面这些因素：

如果有多个事务针对数据库运行，则将该参数的值设置在 1 到该数据库所使用的物理存储器的数量之间。有一个建议：至少将该参数的值设置成您系统上 CPU 的数量。

在具有高更新事务率的环境下，可能需要配置较多的页清除程序。

在具有大缓冲池的环境下，也可能需要配置较多的页清除程序。

如何更改该参数

可以用下面的命令来为该参数设置一个新值：

db2 -v update db cfg for DB_NAME using NUM_IOCLEANERS a_number db2 -v terminate

研究步骤

使用数据库系统监视器，利用有关从缓冲池进行写操作的快照数据（或事件监视器）信息来帮助您调优该配置参数。

当使用快照和收集缓冲池的快照数据时，监控下列计数器：

Buffer pool data writes = 0 Asynchronous pool data page writes = 0 Buffer pool index writes = 0 Asynchronous pool index page writes = 0 LSN Gap cleaner triggers 　　= 0 Dirty page steal cleaner triggers　= 0 Dirty page threshold cleaner triggers = 0

如何决定该减少还是该增加 NUM_IOCLEANERS？

如果下面这两个条件成立，则 减少NUM_IOCLEANERS：

“Buffer pool data writes”约等于“Asynchronous pool data page writes”。

“Buffer pool index writes”约等于“Asynchronous pool index page writes”。

只要下面这两个条件有一个成立，则 增加NUM_IOCLEANERS：

“Buffer pool data writes”远远大于“Asynchronous pool data page writes”。

“Buffer pool index writes”远远大于“Asynchronous pool index page writes”。

Dirty page steal cleaner triggers 指出调用页清除程序的次数，因为在数据库“受损”缓冲区替换期间需要同步写操作。为了有更好的响应时间，该数值应当尽可能低。利用上面所示的计数器，可以使用下面的公式计算用该元素表示的所有清除程序调用的百分比：

Dirty page steal cleaner triggers / (Dirty page steal cleaner triggers + 　　　　　　　　　 　　　　　　 Dirty page threshold cleaner triggers + 　　　　　　　　　　 LSN Gap cleaner triggers)

如果该比率很高，则它可能表明您所定义的页清除程序太少了。页清除程序太少会使故障恢复时间变长。

I/O 服务器的数目（NUM_IOSERVERS）

背景知识

诸如备份和恢复之类的实用程序使用 I/O 服务器代表数据库代理程序执行预取 I/O 和异步 I/O。该参数是一个数据库配置参数，用于指定数据库的 I/O 服务器的数目。超过这个数量的预取和实用程序 I/O 在任何时候都不能在数据库中运行。在启动 I/O 操作时，I/O 服务器处于等待状态。由于从数据库代理程序直接调度非预取 I/O，因此非预取 I/O 不受 NUM_IOSERVERS 约束。

建议

在 OLTP 环境中，请使用缺省值。

如何更改该参数

使用下面的命令为 NUM_IOSERVERS 设置新值：

db2 -v update db cfg for DB_NAME using NUM_IOSERVERS a_number db2 -v terminate

编入组中的提交数目（MINCOMMIT）

背景知识

MINCOMMIT 是数据库配置参数，它让您把将日志记录写到磁盘的工作一直延迟到执行了最小数量的提交为止。该延迟可以有助于减少与写日志记录相关的数据库管理器开销。这意味着当您针对数据库运行多个应用程序并且在非常短的时间范围内应用程序请求大量提交时可以提高性能。只有当该参数值大于 1 并且当连接到数据库的应用程序数量大于或等于该参数值时，才会发生这个提交分组。当执行提交分组时，应用程序提交请求会被挂起，直到时间过去 1 秒或提交请求的数量等于该参数值。

建议

MINCOMMIT 的缺省值为 1。如果多个读／写应用程序通常请求并发数据库提交，则从缺省值开始递增该参数值。这将产生更有效率的日志文件 I/O，因为使用日志文件 I/O 的次数比较少，而每次使用日志文件 I/O 时所写的日志记录比较多。如果您认为缺省值不够大，那么建议您从 3 开始进行调整，在 3 的附近尝试以查看性能对工作负载的影响。您还可以对每秒钟的事务量进行采样，并调整该参数以适应每秒钟的峰值事务量（或者采用它的某个较大的百分比）。适应峰值活动使得在重负载期间写日志记录的开销减到了最低。

如果增大 MINCOMMIT，可能还需要增大 LOGBUFSZ 参数以避免在这些重负载期间强制将已满的日志缓冲区写入磁盘。在这种情况下，LOGBUFSZ 应该等于：

MINCOMMIT * (log space used, on average, by a transaction)

下面介绍了如何使用数据库系统监视器帮助您调优该参数的一些方法：

计算每秒钟的峰值事务数：

通过采用典型一天中的监视器样本，可以确定重负载时期。它的一种实现方法是：

1. 在测量开始时，发出下面这个命令：

db2 -v reset monitor for database db_name

（这不会使高水位的计数器复位。）

2. 在测量完毕后，发出下面这个命令：

db2 -v get snapshot for database on db_name

3. 使用以下输出来计算事务的峰值：

Last reset timestamp = 06-12-2001 14:51:43.786876 Snapshot timestamp = 06-12-2001 14:56:27.787088 Commit statements attempted = 1011 Rollback statements attempted = 10 Log space used by the database (Bytes) = 3990

让 totalTransactions等于“commit statements attempted”和“rollback statements attempted”的总和。

让 totalElapsedTime（单位为秒）等于“Last reset timestamp”和“Snapshot timestamp”的差。如下计算每秒事务数：

NumOfTransPerSecond = totalTransactions / totalElapsedTime

计算每个事务所使用的日志空间：

用类似的方式，通过在一段时间内对一些事务使用抽样技术，可以通过下面这个监视器元素： log_space_used（所使用的工作日志空间单元）计算出使用的日志空间的平均值。

1. 在测量开始时使用下面这个命令将感兴趣的数据库的监视器复位：

db2 -v reset monitor for database db_name.

2. 在测量完毕后使用下面这个命令获取快照：

db2 -v get snapshot for database on db2_name.

3. 产生如上所示的输出。

4. 可以使用下面这个公式计算出每个事务所使用的日志空间：

LogSpaceUsedPerTrans = log_space_used / totalTransactions

如何更改该参数

使用下面的命令更改 MINCOMMIT 值：

db2 -v update db cfg for 　　　　　DB_NAME using MINCOMMIT 　　　　　a_number db2 -v terminate

结束语

本文描述了一些 DB2 性能方面的基本要素、调优技巧和技术以及可能影响 OLTP 性能的一些主要的 DB2 配置参数。通过按照这里所描述的一些简单步骤，可以设置、监控和调优 DB2 数据库系统。我们希望本文所提供的指导能帮助您实现最优化 DB2 应用程序性能的目标。