db2数据库性能参数优化笔记整理

1、Application Support Layer Heap Size (ASLHEAPSZ)
它是app和agent通信的 buffer，占用实例共享内存空间。
监控:
get snapshot for all on | grep –i “Rejected Block Remote Cursor requests”
Rejected Block Remote Cursor requests = 2283
如果Rejected Block Remote Cursor requests值比较高，增大ASLHEAPSZ值，直到该值为0
配 置：
update dbm cfg using aslheapsz 20

2、Maximum Requester I/O Block Size (RQRIOBLK)
它是client和通 信的buffer，占用每个agent的私有内存空间。
监控:无法监控
配置：建议设置为最大值64K，缺省32767bytes，（设到 最大值不会影响其它性能）
update dbm cfg using rqrioblk 65536

3、Sort Heap Threshold (SHEAPTHRES)
私有模式排序空间最大阀值，值＝并发数×SORTHEAP
监控：
需要打开sort 监控开关－ update monitor switches using sort on
get snapshot for dbm | grep –i “sort”
如果Post threshold sorts值比较大，增加SORTHEAP 、SHEAPTHRES参数值
如果(Piped sorts accepted/Piped sorts requested)值比较低，增加SORTHEAP 、SHEAPTHRES参数值
配置：
update dbm cfg using sheapthres 80000

4、Enable Intra-Partition Parallelism (INTRA_PARALLEL)
在SMP环境中打开该选项，提高表和索引扫描速度
监控：
list applications
看application对应的Agents（# of Agents）数目是否大于1
配置：
update dbm cfg using intra_parallel yes

5、Maximum Query Degree of Parallelism (MAX_QUERYDEGREE)
指定一个语 句的最大subagent数目，当INTRA_PARALLEL值为yes时该参数起作用。如果该值为 ANY (-1)，那么优化器将使用服务器的最大cpu数目。
监控：
list applications
看application对应的 Agents（# of Agents）数目是否大于1
配置：
update dbm cfg using MAX_QUERYDEGREE 4 IMMEDIATE

6、Query Heap Size (QUERY_HEAP_SZ)
占 用agent的私有内存空间，存储每个agent运行时所有的sql文，包括the input SQLDA，the output SQLDA，the statement text，the SQLCA，the package name，the package creator，the section number，a consistency token，the cursor control block for any blocking cursors。
监控：
无法监控
配置：
一般不需要修改，如果访问大的LOB，可能需要增加该值
update dbm cfg using query_heap_sz 10000

7、Number of FCM Buffers (FCM_NUM_BUFFERS)
在multi-partitioned database（partition之间）和intra-partition parallelism enabled（subagent之间）环境中通信缓存。
在AIX上，如果DBM有充足的空间，每个partition依照FCM配置拥有独立的 空间，如果不够，所有partition依照FCM配置共享空间；
在其它操作系统上，所有partition依照FCM配置共享空间；
如 果DB2_FORCE_FCM_BP注册变量设置为YES，所有partition将一直共享空间，但大小将受32bit的OS限制
监控：
get snapshot for FCM for all dbpartitionnums
配置：
update dbm cfg using fcm_num_buffers 4096 immediate

8、Connection、Agent配置
监控：
db2 get snapshot for dbm | grep -i agent
High water mark for agents registered = 2
High water mark for agents waiting for a token = 0
Agents registered = 2
Agents waiting for a token = 0
Idle agents = 1
Agents assigned from pool = 146
Agents created from empty pool = 3
Agents stolen from another application = 0
High water mark for coordinating agents = 2
Max agents verflow = 0
Gateway connection pool agents stolen = 0

9、Keep Fenced Process (KEEPFENCED)
UDF和SP按照运行模式分为两 种：fenced和unfenced，fenced模式是一种c/s的通信方式，存储过程为客户端请求server的一个 agent为其执行业务逻辑。unfenced模式是一种直接调用db2进程并在进程的地址空间内执行，有不，但 该模式可以读取运行的PID，而 fenced模式做不到。
如果KEEPFENCED设置为YES，可以使UDF或SP所调用fenced进程或 线程一直保持并被重复使用，一直到实例关闭才销毁，但这将占用一定资源（如内存）。例如，使用写 的sp，sp运行完成后不会结束JVM，下次运行sp将省去启动JVM的时间。
配置：
update dbm cfg using keepfenced YES

10、Maximum Total of Files Open (MAXFILOP)
服务器打开 文件的最大数目，如果使用SMS容器，要求该值比较高，也需要检查操作系统对该值的限制。
配置：
update db cfg using maxfilop 2000
监控：（需要bufferpool的monitor：db2 update monitor switches using bufferpool on）
db2 get snapshot for db on testdb |grep -i ‘close’

11、Default Buffer Pool Size (BUFFPAGE)
调整缓冲池的大小办法：
1、 alter bufferpool IBMDEFAULTBP size -1，修改所有bufferpool大小为-1，然后依赖BUFFPAGE参数控制，缓冲池的数量：4（隐藏的）＋创建的缓冲池（含 IBMDEFAULTBP），每个创建的缓冲池大小＝pagesize×buffpage×（1＋5％）
2、直接修改bufferpool大小， 建议使用该方法，可以控制pagesize大小不同缓冲池的大小。
配置：
update db cfg for using BUFFPAGE bigger_value
alter bufferpool IBMDEFAULTBP size -1
监控：
get snapshot for db on db_name

12、Log Buffer Size (LOGBUFSZ)
从 logbuff写到磁盘的激活条件：
1）A transaction commits (or MINCOMMIT transactions commit). （最小提交事务数时flush）
2）The log buffer is full（日志缓冲满时flush）
3）One second has elapsed since the last log buffer flush.（间隔1秒时flush）
配置：
update database cfg for using LOGBUFSZ 256
监控：
get snapshot for database on | grep –i “Log space”
Log space available to the database (Bytes) = 4549916
Log space used by the database (Bytes) = 550084
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 550084
CLSA(current amount of log space available ) = Log space available to the database - Log space used by the database, CLSA就是LOGBUFSZ参数可以配置的最大值。
get snapshot for database on | grep –i “Log pages”
Log pages read = 0
Log pages written = 12644
日志页面读（Log pages read）是日志记录器（logger）从磁盘读取的日志页面的数目，而日志页面写（Log pages written）是日志记录器（logger）写入磁盘的日志页面的数目。理想状态，Log pages read为0，如果该值比较大，考虑增加LOGBUFSZ值。

13、Application Heap Size (APPLHEAPSZ)
存放agent或subagent当前sql文处理的所需内存，大小决定于sql文的复杂度及宿主变量大小。如果是分区，这 部分内存使用APP_CTL_HEAP_SZ堆，而不在应用程序堆。在运行时按需要分配内存，这个值仅是上限值。
配置：
update database cfg for using applheapsz 1024
监控：
无法监控，如果应用报错，加倍该值，看应用错误是 否消失

14、Sorting (SORTHEAP, SHEAPTHRES_SHR)
只有 INTRA_PARALLEL 数据库器配置参数是 ON 或启用集中器（concentrator）时（即当 MAX_CONNECTIONS 大于 MAX_COORDAGENTS 时），才可以使用共享排序。
对私有排序，SHEAPTHRES 在实例级是一个软限制，如果超过这个值，系统将分配很小的排序堆，性能会下降
对 共享排序，SHEAPTHRES_SHR在数据库级是一个硬限制。
当并发用户为10个，sortheap为100，如果是私有排 序，SHEAPTHRES ＝10×sortheap，如果是共享排序，SHEAPTHRES_SHR＝5×sortheap

15、 Locking (LOCKLIST, MAXLOCKS, LOCKTIMEOUT, DLCHKTIME)
LOCKLIST 表明分配给锁列表的存储容量。每个数据库都有一个锁列表，锁列表包含了并发连接到该数据库的所有应用程序所持有的锁。锁定是数据库管理器用来控制多个应用 程序并发访问数据库中数据的机制。行和表都可以被锁定。根据对象是否还持有其它锁，每把锁需要 32 个或 64 个字节的锁列表：
* 需要 64 个字节来持有某个对象上的锁，在这个对象上，没有持有其它锁。64bit的是112字节
* 需要 32 个字节来记录某个对象上的锁，在这个对象上，已经持有一个锁。32bit的是56字节
MAXLOCKS 定义了应用程序持有的锁列表的百分比，当任何一个应用程序所持有的锁数量达到整个锁列表大小的这个百分比时，对该应用程序所持有的锁进行锁升级。如果锁列 表用完了空间，那么也会发生锁升级。经验：MAXLOCKS 参数乘以 MAXAPPLS 参数不能小于 100。
如果发生死锁，将数据库诊断级 别改为4，数据库日志将记录死锁发生的原因（与谁发生了死锁，锁类型，是否行级锁）
db2 update dbm cfg using diaglevel 3
0 为不记录信息
1 为仅记录错误
2 记录服务和非服务错误
缺省是3，记录db2的错误和警告
4 是记录全部信息，包括成功执行的信息
监控：
get snapshot for database on | grep -i ‘Lock’
Locks held currently = 12
Lock waits = 0
Time database waited on locks (ms) = 0
Lock list memory in use (Bytes) = 2080
Deadlocks detected = 1
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 0
Lock Timeouts = 0
Internal rollbacks due to deadlock = 1
如果诊断级别改为4，Lock Timeouts的详细原因就可以记录：
Request for lock “TAB: (2, 13)” in mode “.IX” timed out
Application caused the lock wait is “*LOCAL.DB2.007340152709″
Statement: 7570 6461 7465 2074 3120 7365 7420 6331 update t1 set c1
3d63 312b 3531 3231 30 =c1+51210
可以看到LOCAL.DB2.007340152709应用占着t1表的锁没有释放

16、Number of Asynchronous Page Cleaners (NUM_IOCLEANERS)
异步清理缓冲池中脏页的进程数.
清理进程启 动的条件：
1)脏页阀值CHNGPGS_THRESH：表示脏页占缓冲池的百分数
2)达到SOFTMAX（soft checkpoint）值时，（如果softmax值是50，logfilesize值是10M，当写入日志文件的数据量达到5M＝10M×50％时，启 动清理进程）
3）当发生Dirty page steals情况时，
配置：经验值（A rule of thumb）－小于等于CPU数目
update db cfg for using NUM_IOCLEANERS 16
监控：
update monitor switches using bufferpool on
get snapshot for db on testdb|grep -i “writes”
Buffer pool data writes = 0
Asynchronous pool data page writes = 167660
Buffer pool index writes = 0
Asynchronous pool index page writes = 178944
PADW = (Asynchronous pool data page writes / Buffer pool data writes) * 100%
PAIX = (Asynchronous pool index page writes / Buffer pool index writes) * 100%
如果PADW 、PAIX 接近100％，应该减少NUM_IOCLEANERS
get snapshot for db on testdb|grep -i “cleaner trigger”
LSN Gap cleaner triggers = 142
Dirty page steal cleaner triggers = 2
Dirty page threshold cleaner triggers = 396
如 果Dirty page steal cleaner triggers 值非常小，其它两个大，说明配置恰当。
如果Dirty page steal cleaner triggers 值非常大，LSN Gap cleaner triggers比较小，说明softmax值比较高，需要调小。
计算每次cleaner启动后，写多少page？
APPAW = (167660 + 178944) / (142 + 2 + 396) ＝ 641 Pages
相当于每次cleaner写 641×4k＝2.5M数据到硬盘，对于1G的buffer来讲，启动次数太多，但对100M的buffer来讲，是合适的。

17、 Number of I/O Servers (NUM_IOSERVERS)
该参数值就是预取器的数量。一般该值等于数据库数据所在的磁盘数 目。

18、Number of Commits to Group (MINCOMMIT)
如果1秒内有6个事务，该参数最后调 到6。
并且也要调logbufsize>=MINCOMMIT×平均每个事务消耗的空间
配置：
update db cfg for using MINCOMMIT 5
监控：
get snapshot for database on
Last reset timestamp = 09-12-2002 14:51:43.786876
Snapshot timestamp = 09-12-2002 14:56:27.787088
Commit statements attempted = 1011
Rollback statements attempted = 10
Log space used by the database (Bytes) = 3990
Log pages written = 23
每秒执行的事务数＝(Commit statements attempted + Rollback statements attempted) /
(Last reset timestamp - Snapshot timestamp)

19、Catalog Cache Size (CATALOGCACHE_SZ)
配置：
update db cfg for using CATALOGCACHE_SZ 32
监控：
db2 get snapshot for db on testdb|grep -i “catalog”
Catalog database partition number = 0
Catalog network node name =
Catalog cache lookups = 17
Catalog cache inserts = 7
Catalog cache verflows = 0
Catalog cache high water mark = 0
如果命中率(1 – (Catalog cache inserts / Catalog cache lookups)) * 100 < 95％,增加该参数值。
如果Catalog cache overflows 不为0， 也需要增加该参数值，一般同时会增加dbheap参数值。

20、Average Number of Active Applications (AVG_APPLS)
DB2优化器根据这个参数来评估资源的使用策略，特别是缓冲池空间。
配置：
update db cfg for using AVG_APPLS 16
监控：
db2 get snapshot for db on testdb|grep -i “appls”
Appls. executing in db manager currently = 0

修 改注册变量：
DB2_SKIPINSERTED=on,DB2_SKIPDELETED=on,DB2_EVALUNCOMMITTED=on(V.8.2 以上)
可以提高数据库并发量，但是查询的结果不含正在插入或删除的记录。