DB2数据库优化10佳性能技巧

为了帮助 DB2  DBA 避免性能灾难并获得高性能，我为我们的客户、用户和 DB2 专家同行总结了一套故障诊断流程。以下详细说明在 Unix、Windows 和 OS/2 环境下使用 DB2 UDB 的电子商务 OLTP 应用程序的 10 条最重要的性能改善技巧 - 并在本文的结束部分作出 总结。

　　每隔大约几个星期，我们就会接到苦恼的 DBA 们的电话，抱怨有关性能的问题。“我们 Web 站点速度慢得像蜗牛一样”，他们叫苦道，“我们正在失去客户，情况严重。你能帮忙吗?”为了回答这些问题，我为我的咨询公司开发了一个分析流程，它能让我们很快找到性能问题的原因，开发出补救措施并提出调整意见。这些打电话的人极少询问费用和成本 - 他们只关心制止损失。当 DB2 或电子商务应用程序的运行不能达到预期的性能时，组织和财务的收益将遭受极大的损失。

　　10. 监视开关

　　确保已经打开监视开关。如果它们没有打开，您将无法获取您需要的性能信息。要打开该监视开关，请发出以下命令：

　　db2 "update monitor switches using

　　lock ON sort ON bufferpool ON uow ON

　　table ON statement ON"

　　9. 代理程序

　　确保有足够的 DB2 代理程序来处理工作负载。要找出代理程序的信息，请发出命令：

　　db2 "get snapshot for database manager"

　　并查找以下行：

　　High water mark for agents registered = 7

　　High water mark for agents waiting for a token = 0

　　Agents registered= 7

　　Agents waiting for a token= 0

　　Idle agents= 5

　　Agents assigned from pool= 158

　　Agents created from empty Pool = 7

　　Agents stolen from another application= 0

　　High water mark for coordinating agents= 7

　　Max agents overflow= 0

　　如果您发现Agents waiting for a token或Agents stolen from another application不为 0，那么请增加对数据库管理器可用的代理程序数(MAXAGENTS 和/或 MAX_COORDAGENTS取适用者)。

　　8. 最大打开的文件数

　　DB2 在操作系统资源的约束下尽量做一个“优秀公民”。它的一个“优秀公民”的行动就是给在任何时刻打开文件的最大数设置一个上限。数据库配置参数MAXFILOP约束 DB2 能够同时打开的文件最大数量。当打开的文件数达到此数量时，DB2 将开始不断地关闭和打开它的表空间文件(包括裸设备)。不断地打开和关闭文件减缓了 SQL 响应时间并耗费了 CPU 周期。要查明 DB2 是否正在关闭文件，请发出以下命令：

　　db2 "get snapshot for database on DBNAME"

　　并查找以下的行：

　　Database files closed = 0

　　如果上述参数的值不为 0，那么增加MAXFILOP的值直到不断打开和关闭文件的状态停止。使用以下命令：

　　db2 "update db cfg for DBNAME using MAXFILOP N"

　　7. 锁

　　LOCKTIMEOUT的缺省值是 -1，这意味着将没有锁超时(对 OLTP 应用程序，这种情况可能会是灾难性的)。尽管如此，我还是经常发现许多 DB2 用户用LOCKTIMEOUT= -1。将LOCKTIMEOUT设置为很短的时间值，例如 10 或 15 秒。在锁上等待过长时间会在锁上产生雪崩效应。

　　首先，用以下命令检查LOCKTIMEOUT的值：

　　db2 "get db cfg for DBNAME"

　　并查找包含以下文本的行：

　　Lock timeout (sec) (LOCKTIMEOUT) = -1

　　如果值是 -1，考虑使用以下命令将它更改为 15 秒(一定要首先询问应用程序开发者或您的供应商以确保应用程序能够处理锁超时)：

　　db2 "update db cfg for DBNAME using LOCKTIMEOUT 15"

　　您同时应该监视锁等待的数量、锁等待时间和正在使用锁列表内存(lock list memory)的量。请发出以下命令：

　　db2 "get snapshot for database on DBNAME"

　　查找以下行：

　　Locks held currently= 0

　　Lock waits= 0

　　Time database waited on locks (ms)= 0

　　Lock list memory in use (Bytes)= 576

　　Deadlocks detected= 0

　　Lock escalations= 0

　　Exclusive lock escalations= 0

　　Agents currently waiting on locks= 0

　　Lock Timeouts= 0

　　如果Lock list memory in use (Bytes)超过所定义LOCKLIST大小的 50%，那么在LOCKLIST数据库配置中增加 4k 页的数量。

　　6. 临时表空间

　　为了改善 DB2 执行并行 I/O 和提高使用TEMPSPACE的排序、散列连接(hash join)和其它数据库操作的性能，临时表空间至少应该在三个不同的磁盘驱动器上拥有三个容器。

　　要想知道您的临时表空间具有多少容器，请发出以下命令：

　　db2 "list tablespaces show detail"

　　查找与以下示例类似的TEMPSPACE表空间定义：

　　Tablespace ID= 1

　　Name= TEMPSPACE1

　　Type= System managed space

　　Contents= Temporary data

　　State= 0x0000

　　Detailed explanation: Normal

　　Total pages= 1

　　Useable pages= 1

　　Used pages= 1

　　Free pages= Not applicable

　　High water mark (pages)= Not applicable

　　Page size (bytes)= 4096

　　Extent size (pages)= 32

　　Prefetch size (pages)= 96

　　Number of containers= 3

　　注意Number of containers的值是 3，而且Prefetch size是Extent size的三倍。为了得到最佳的并行 I/O 性能，重要的是Prefetch size为Extent size的倍数。这个倍数应该等于容器的个数。

　　要查找容器的定义，请发出以下命令：

　　db2 "list tablespace containers for 1 show detail"

　　1 指的是tablespace ID #1，它是刚才所给出的示例中的TEMPSPACE1。

　　5. 内存排序

　　OLTP 应用程序不应该执行大的排序。它们在 CPU、I/O 和所用时间方面的成本极高，而且将使任何 OLTP 应用程序慢下来。因此，256 个 4K 页(1MB)的缺省SORTHEAP大小(1MB)应该是足够了。您也应该知道排序溢出的数量和每个事务的排序数。

　　请发出以下命令：

　　Db2 "get snapshot for database on DBNAME"

　　并查找以下行：

　　Total sort heap allocated= 0

　　Total sorts = 1

　　Total sort time (ms)= 8

　　Sort overflows = 0

　　Active sorts = 0

　　Commit statements attempted = 3

　　Rollback statements attempted = 0

　　Let transactions = Commit statements attempted + Rollback

　　statements attempted

　　Let SortsPerTX= Total sorts / transactions

　　Let PercentSortOverflows = Sort overflows * 100 / Total sorts

　　如果PercentSortOverflows ((Sort overflows * 100) / Total sorts )大于 3 个百分点，那么在应用程序 SQL 中会出现严重的或意外的排序问题。因为正是溢出的存在表明发生了大的排序，所以理想的情况是发现没有排序溢出或至少其百分比小于一个百分点。

　　如果出现过多的排序溢出，那么“应急”解决方案是增加SORTHEAP的大小。然而，这样做只是掩盖了真实的性能问题。相反，您应该确定引起排序的 SQL 并更改该 SQL、索引或群集来避免或减少排序开销。

　　如果SortsPerTX大于 5 (作为一种经验之谈)，那么每个事务的排序数可能很大。虽然某些应用程序事务执行许多小的组合排序(它们不会溢出并且执行时间很短)，但是它消耗了过多的 CPU。当SortsPerTX很大时，按我的经验，这些机器通常会受到 CPU 的限制。确定引起排序的 SQL 并改进存取方案(通过索引、群集或更改 SQL)对提高事务吞吐率是极为重要的。