DB2数据库性能参数优化注释.doc

1、Application Support Layer Heap Size (ASLHEAPSZ)它是app和agent通信的buffer，占用实例共享内存空间。监控:db2 get snapshot for all on dbname | grep -i Rejected Block Remote Cursor requestsRejected Block Remote Cursor requests = 2283如果Rejected Block Remote Cursor requests值比较高，增大ASLHEAPSZ值，直到该值为0配置：update dbm cfg using aslheapsz 202、Maximum Requester I/O Block Size (RQRIOBLK)它是client和server通信的buffer，占用每个agent的私有内存空间。监控:无法监控配置：建议设置为最大值64K，缺省32767bytes，（设到最大值不会影响其它性能）update dbm cfg using RQRIOBLK 655363、Sort Heap Threshold (SHEAPTHRES)私有模式排序空间最大阀值，值并发数SORTHEAP监控：需要打开sort监控开关db2 update monitor switches using sort ondb2 get snapshot for dbm | grep sort如果Post threshold sorts值比较大，增加SORTHEAP 、SHEAPTHRES参数值如果(Piped sorts accepted/Piped sorts requested)值比较低，增加SORTHEAP 、SHEAPTHRES参数值配置：update dbm cfg using sheapthres 800004、Enable Intra-Partition Parallelism (INTRA_PARALLEL)在SMP环境中打开该选项，提高表和索引扫描速度监控：db2 list applications看application对应的Agents（# of Agents）数目是否大于1配置：db2 update dbm cfg using intra_parallel yes5、Maximum Query Degree of Parallelism (MAX_QUERYDEGREE)指定一个SQL语句的最大subagent数目，当INTRA_PARALLEL值为yes时该参数起作用。如果该值为 ANY (-1)，那么优化器将使用服务器的最大cpu数目。监控：db2 list applications看application对应的Agents（# of Agents）数目是否大于1配置：db2 update dbm cfg using MAX_QUERYDEGREE 4 IMMEDIATE6、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，可能需要增加该值db2 update dbm cfg using query_heap_sz 100007、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限制监控：db2 get snapshot for FCM for all dbpartitionnums配置：db2 update dbm cfg using fcm_num_buffers 4096 immediate8、Connection、Agent配置监控：db2 get snapshot for dbm | grep agentHigh water mark for agents registered = 2High water mark for agents waiting for a token = 0Agents registered = 2Agents waiting for a token = 0Idle agents = 1Agents assigned from pool = 146Agents created from empty pool = 3Agents stolen from another application = 0High water mark for coordinating agents = 2Max agents verflow = 0Gateway connection pool agents stolen = 09、Keep Fenced Process (KEEPFENCED)UDF和SP按照运行模式分为两种：fenced和unfenced，fenced模式是一种c/s的通信方式，存储过程为客户端请求server的一个 agent为其执行业务逻辑。unfenced模式是一种直接调用db2进程并在进程的地址空间内执行，有不安全性，但该模式可以读取运行的PID，而 fenced模式做不到。如果KEEPFENCED设置为YES，可以使UDF或SP所调用fenced进程或线程一直保持并被重复使用，一直到实例关闭才销毁，但这将占用一定资源（如内存）。例如，使用java写的sp，sp运行完成后不会结束JVM，下次运行sp将省去启动JVM的时间。配置：db2 update dbm cfg using keepfenced YES10、Maximum Total of Files Open (MAXFILOP)服务器打开文件的最大数目，如果使用SMS容器，要求该值比较高，也需要检查操作系统对该值的限制。配置：db2 update db cfg using maxfilop 2000监控：（需要bufferpool的monitor：db2 update monitor switches using bufferpool on）db2 get snapshot for db on dbname |grep close 11、Default Buffer Pool Size (BUFFPAGE)调整缓冲池的大小办法：1、 alter bufferpool IBMDEFAULTBP size -1，修改所有bufferpool大小为-1，然后依赖BUFFPAGE参数控制，缓冲池的数量：4（隐藏的）创建的缓冲池（含 IBMDEFAULTBP），每个创建的缓冲池大小pagesizebuffpage（15）2、直接修改bufferpool大小，建议使用该方法，可以控制pagesize大小不同缓冲池的大小。配置：db2 update db cfg for using BUFFPAGE bigger_valuedb2 alter bufferpool IBMDEFAULTBP size -1监控：db2 get snapshot for db on db_name12、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）配置：db2 update database cfg for using LOGBUFSZ 256监控：db2 get snapshot for database on topicis |grep -i Log spaceLog space available to the database (Bytes) = 4549916Log space used by the database (Bytes) = 550084Maximum secondary log space used (Bytes) = 0Maximum total log space used (Bytes) = 550084CLSA(current amount of log space available ) = Log space available to the database - Log space used by the database, CLSA就是LOGBUFSZ参数可以配置的最大值。db2 get snapshot for database on dbname |grep -i Log pagesLog pages read = 0Log 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堆，而不在应用程序堆。在运行时按需要分配内存，这个值仅是上限值。配置：db2 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 10sortheap，如果是共享排序，SHEAPTHRES_SHR5sortheap15、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 30 为不记录信息1 为仅记录错误2 记录服务和非服务错误缺省是3，记录db2的错误和警告4 是记录全部信息，包括成功执行的信息监控：db2 get snapshot for database on dbname | grep -i LockLocks held currently = 12Lock waits = 0Time database waited on locks (ms) = 0Lock list memory in use (Bytes) = 2080Deadlocks detected = 1Lock escalations = 0Exclusive lock escalations = 0Agents currently waiting on locks = 0Lock Timeouts = 0Internal rollbacks due to deadlock = 1如果诊断级别改为4，Lock Timeouts的详细原因就可以记录：Request for lock “TAB: (2, 13)” in mode “.IX” timed outApplication caused the lock wait is “*LOCAL.DB2.007340152709Statement: 7570 6461 7465 2074 3120 7365 7420 6331 update t1 set c13d63 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，当写入日志文件的数据量达到5M10M50时，启动清理进程）3）当发生Dirty page steals情况时，配置：经验值（A rule of thumb）小于等于CPU数目db2 update db cfg for using NUM_IOCLEANERS 16监控：db2 update monitor switches using bufferpool on dbnamedb2 get snapshot for db on topicis|grep -i writesBuffer pool data writes = 0Asynchronous pool data page writes = 167660Buffer pool index writes = 0Asynchronous pool index page writes = 178944PADW = (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_IOCLEANERSdb2 get snapshot for db on topicis|grep -i cleaner triggerLSN Gap cleaner triggers = 142Dirty page steal cleaner triggers = 2Dirty 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写6414k2.5M数据到硬盘，对于1G的buffer来讲，启动次数太多，但对100M的buffer来讲，是合适的。17、Number of I/O Servers (NUM_IOSERVERS)该参数值就是预取器的数量。一般该值等于数据库数据所在的磁盘数目。db2 get db cfg for dbname|grep NUM_IOSERVERS18、Number of Commits to Group (MINCOMMIT)如果1秒内有6个事务，该参数最后调到6。并且也要调logbufsize=MINCOMMIT平均每个事务消耗的空间配置：db2 update db cfg for using MINCOMMIT 5监控：db2 get snapshot for database on dbnameLast reset timestamp = 09-12-2002 14:51:43.786876Snapshot timestamp = 09-12-2002 14:56:27.787088Commit statements attempted = 1011Rollback statements attempted = 10Log space used by the database (Bytes) = 3990Log pages written = 23每秒执行的事务数(Commit sta