讲解DB2数据库性能调整的十个实用技巧

de the number of rows read by the number of transactions (RowsPerTX).OLTP一般每次交易从一个table里面读出20 row,如果发现一个交易能读出成百上千行数据，表扫描就可能出现，可能需要看看index是否需要。简单情况下是运行runstats收集信息。

 

Sample output from "get snapshot for tables on DBNAME" follows:

Snapshot timestamp = 09-25-2000 4:47:09.970811

Database name= DGIDB

Database path= /fs/inst1/inst1/NODE0000/SQL00001/

Input database alias= DGIDB

Number of accessed tables= 8

Table List

Table Schema= INST1

Table Name= DGI_SALES_ LOGS_TB

Table Type= User

Rows Written= 0

Rows Read= 98857

Overflows= 0

Page Reorgs= 0

有很高的Overflows ，就需要re-org table。当一行宽度改变，可能DB2就会把一行放到不同的页中。

5. SORT MEMORY

OLTP应该没有大规模的sort，因为sort会消耗大量的CPU, I/O和时间。

缺省的SORTHEAP = 256*4K=1M，一般是足够了。应该知道sort overflows 的数目和每个交易的sort number。

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 超过3%，可能说明应用中有比较严重的sort SQL。因为大量的overflows说明有大量的sort出现，为零或者小于1时比较理想的。

 

如果有大量的overflow出现，权宜之计是增加SORTHEAP，但是这么做只是隐藏了问题。根本解决是：要定位SQL，通过调整SQL，INDEX,clustering 来减少sort 代价。

 

如果SortsPerTX 大于5，说明每个交易的sort数目过多，某些应用可能执行了大量的小复合查询，不会overflow,但是有很小的时间段。但是会消耗大量的CPU。同样是要调整SQL，INDEX,clustering来解决问题。

6. Temporary Tablespaces

临时表空间一般要有3个containers在不同的disk上，可以实现并行I/O，提高sorts, hash joins,或者其他在TEMPSPACE上的动作的性能。

 

db2 "list tablespaces show detail"，可查看临时表空间的container：

 

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

这里表示有3个container，Prefetch size是Extent size的3倍。为了最好的并行性能，最好Prefetch size是Extent size的倍数。一般倍数是container的数目。

 

db2 "list tablespace containers for 1 show detail"

可以看到containers的定义。

 

7. Locks

缺省的LOCKTIMEOUT=-1，就是说不设置lock的timeout，在OLTP中这可能是一个灾难。我们要设置比较小的数值，比如设置LOCKTIMEOUT=10或者15秒。

查看命令:

db2 "get db cfg for DBNAME",

继续查看下面的信息：

Lock timeout (sec) (LOCKTIMEOUT) = -1

 

要和应用人员将明白，他们是否已经在程序中可以处理timeout的情况。然后设置：

db2 "update db cfg for DBNAME using LOCKTIMEOUT 15"

 

可以在系统中察看lock wait的数目，lock wait time, lock list 使用的内存量。

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的内存量(bytes)超过LOCKLIST 的50%，那么需要增加LOCKLIST的量，LOCKLIST是按4k计算。

 

8. Maximum Open Files

最大的打开文件数目

DB2限制同时打开的文件数目，数据库参数"MAXFILOP"限定了并发打开的文件数目。如达到这个数目，DB2就会开始关闭和打开Tablespace文件，包括raw device，这样会降低SQL反映时间和占用CPU。

使用命令来查看是否有文件关闭情况:

db2 "get snapshot for database on DBNAME"

看看其中的 "Database files closed = 0"

 

如果值不是零,就需要修改MAXFILOP,

db2 "update db cfg for DBNAME using MAXFILOP N"

 

9. Agents

需要保证有足够的agent应付系统负载。

命令：db2 "get snapshot for database manager"

此时需要观察“Agents waiting for a token” 或者“ Agents stolen from another application”，假如有值，就需要增加DB manager的agent值，也就是修改MAXAGENTS 和/或者 MAX_COORDAGENTS的值。

 

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

 

10. Monitor Switches

打开Monitor Switch后才可以获得性能方面的信息,详细命令如下:

db2 "update monitor switches using lock ON sort ON bufferpool ON uow ON table ON statement ON"