OLTP类应用系统之DB2数据库优化最佳实践

本文所涉及的优化技巧均建立在您的数据库物理架构已经设计完成后而为了保证您的应用有最佳表现所必须做的后续优化工作。下面这些有关数据库配置调优的技巧将使您在 OLTP 环境中取得非常好的性能，同时使您能够避免显而易见的“陷阱”。在配置参数中，数据库管理器配置参数需要重新启动数据库管理器，而为了使更改生效，大多数数据库配置参数都要求应用程序重新连接到数据库。

这里要优化的配置参数如下所示：
一、      配置缓冲池大小

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

db2pd -d dbname -bufferpools

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

1)     增加缓冲池大小

#db2 "alter bufferpool bpname immediate size 40000"

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

#db2 "create bufferpool bpname SIZE 200 PAGESIZE 8K"

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

二、      配置日志缓冲区大小（LOGBUFSZ）

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

1)     事务提交；

2)     日志缓冲区已满；

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

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

1.    如何更改该参数

可以使用下面这个命令来更改该参数值：

db2 -v update database cfg for DB_NAME using LOGBUFSZ 256

2.    调整依据

通过查看下面这个示例中所示各行，使用数据库快照来确定 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 语句时，数据库管理器将按照需要分配内存，所分配的内存大小最大可达到该参数所指定的最大值。

1.    如何更改该参数

下面这条命令可以将缺省值更改成最佳值：

db2 -v update db cfg for DB_NAME using applheapsz 256

2.    调整依据

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

四、      排序堆大小（SORTHEAP）和排序堆阈值（SHEAPTHRES_SHR）

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

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

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

1.     最佳实践：

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

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

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

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

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

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

2.     如何更改这些参数

要更改 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

3.     调整依据

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 和 NUM_POOLAGENTS 的值设置成略微大于并发应用程序连接的最大预计数目。

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

1.     如何更改该参数

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

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

2.     调整依据

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

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）

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

1)     经常进行提交以释放锁。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5.     如何更改这些参数

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

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

6.     调整依据

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

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

db2 -v update monitor switches using lock on

2)     然后收集数据库快照：

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 是一个数据库配置参数。它指定了可以连接到数据库的并发应用程序（本地和远程）的最大数量。该参数值必须大于等于已连接应用程序的数量，加上这些相同的应用程序中完成两阶段提交或回滚过程中可能并发存在的数量的总和。

1.     建议

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

2.     如何更改该参数

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

db2 -v update db cfg for DB_NAME using MAXAPPLS a_number

db2 -v terminate

3.     调整依据

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

SQL1040N The maximum number of applications is already connected to the

database. SQLSTATE=57030

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

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

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

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

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

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

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

2.     如何更改该参数

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

db2 -v update db cfg for DB_NAME using NUM_IOCLEANERS a_number

db2 -v terminate

3.     调整标准

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

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

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

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

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

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

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

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

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

4)     “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 约束。

1.     建议

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

2.     如何更改该参数

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

db2 -v update db cfg for DB_NAME using NUM_IOSERVERS a_number

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

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

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

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

计算每秒钟的峰值事务数通过采用典型一天中的监视器样本，可以确定重负载时期。它的一种实现方法是：

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

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

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

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

db2 -v reset monitor for database db_name.

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

db2 -v get snapshot for database on db2_name.

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

LogSpaceUsedPerTrans = log_space_used / totalTransactions

2.     如何更改该参数

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

db2 -v update db cfg for  DB_NAME using MINCOMMIT a_number

十一、         总结

本文描述了一些 DB2 性能方面的基本要素、调优技巧和技术以及可能影响 OLTP 性能的一些主要的 DB2 配置参数。通过按照这里所描述的一些简单步骤，可以设置、监控和调优 DB2 数据库系统。