DB2缓冲池和索引调优的方法讲解.docx

1、DB2缓冲池和索引调优的方法讲解DB2缓冲池和索引调优的方法1DB2性能问题的表现应用系统(OA)上的表现：一般是登录、首页、待办列表等数据量比较大的模块，响应时间长，耗时数秒到数十秒都有可能。有时候是用户访问高峰期慢，下班时间又比较正常。操作系统上的表现：一般是中间件服务器(WAS)系统正常，CPU和IO占用不会持续超过50%，系统运行进程不会有持续的等待。数据库服务器则非常繁忙，CPU占用持续在50%以上，往往会达到持续90%左右，IO占用可能不高。从系统层面判断，性能瓶颈出在数据库上。2调优的基本思路DB2的性能和操作系统、锁、缓冲池、索引等参数，以及SQL的写法都有很大关系，受限于个人

2、认识，这里主要介绍缓冲池和索引的调优方法。缓冲池的调整比较简单，一般可以先调整缓冲池，若效果不明显，则再调整索引和SQL。3缓冲池调优缓冲池是内存中的一块区域，DB2会将用到数据放到缓冲池中提高性能。缓冲池太小，每次查询仍然要到磁盘中操作，达不到缓冲的效果。缓冲池太大，超出操作系统管理的限制，会导致数据库无法连接的错误。缓冲池是通过表空间与数据表发生联系的，数据表存放在指定的表空间中，每个表空间又有指定的缓冲池。因为每张数据表存储的数据量都不同，一般根据每条记录存放的最大数据量，我们会为数据表分别指定4k-32k不同的表空间来存放，以达到优化存储和性能的目的，缓冲池也是类似。这个一般在创建数据

3、库时就会分配好了。在*unix下，可以使用下面的命令查看缓冲池相关信息：切换到db2inst1账号su db2inst1连接到pzbdw数据库db2 connect to pzbdw查看缓冲池定义db2 select BPNAME,NPAGES,PAGESIZE from syscat.bufferpools查看表空间的定义，包含表空间名称(TableSpaceName)、使用的缓冲池名称(BufferpoolName)，表空间的页大小(TBSPageSize)，缓冲池的数量(BufferpoolPages)，缓冲池的页大小数据(BufferpoolSize)信息。db2 select s.T

4、BSPACE TableSpaceName,b.BPNAME BufferpoolName,s.PAGESIZE TBSPageSize,b.NPAGES BufferpoolPages, b.PAGESIZE BufferpoolSize from SYSCAT.BUFFERPOOLS b,SYSCAT.TABLESPACES s where s.BUFFERPOOLID=b.BUFFERPOOLID|more查看mv_workitem表所在表空间和缓冲池信息，一般“MV_”开头的表使用的缓冲池是重点关注对象：db2 select TABSCHEMA TableSchemaName, TAB

5、NAME TableName, t.TBSPACE TableSpaceName,b.BPNAME BufferpoolName, b.NPAGES BufferpoolPages, b.PAGESIZE BufferpoolSize from SYSCAT.TABLES t ,SYSCAT.BUFFERPOOLS b,SYSCAT.TABLESPACES s where tabname=MV_WORKITEM and s.BUFFERPOOLID=b.BUFFERPOOLID and t.TBSPACE=s.TBSPACE开启缓冲池监控器：db2 update monitor switche

6、s using bufferpool on在应用系统重现问题后，检查缓冲池的快照：db2 get snapshot for bufferpools on pzbdw|grep -i buffer|more 检查相关缓冲池快照，需要重点关注的data和index的逻辑/物理读写数据，一般来说，在缓冲池足够的情况下，physical reads值趋近于0，而logical reads值则很大。下面是红塔集团OA的32k缓冲池，在正常时的一个快照。 Bufferpool SnapshotBufferpool name = BF32Buffer pool data logical reads = 49

7、3907Buffer pool data physical reads = 78Buffer pool temporary data logical reads = 129662Buffer pool temporary data physical reads = 0Buffer pool data writes = 1Buffer pool index logical reads = 10302Buffer pool index physical reads = 122Buffer pool temporary index logical reads = 0Buffer pool tempo

8、rary index physical reads = 0Total buffer pool read time (milliseconds) = 671Total buffer pool write time (milliseconds)= 15Buffer pool index writes = 58No victim buffers available = 635Tablespaces using bufferpool = 2Alter bufferpool information:如果发现物理和逻辑读的值相差不大，则使用下面的命令调整缓冲池大小，一般可以每次增加2000左右。db2 A

9、LTER BUFFERPOOL BF32 size 18000缓冲池的调整是立即生效的，不需要重启数据库。需要注意的是，缓冲池的大小受物理内存和操作系统限制，一般32位操作系统下，总的缓冲池大小不能超过1G。如果在这个限制下，不能满足所有缓冲池都达到物理读趋近于0，则考虑尽可能保证用户体验影响较大的(MV、UM等开头的表使用的)缓冲池大小。理论上64位操作系统可以管理更大的内存空间，因此可以获得更好的性能。如下所示缓冲池，总大小为 1x4+4x4+3x8+2.5x16+2.5x32+1x32=226MBPNAME NPAGES PAGESIZE- - -IBMDEFAULTBP 1000 40

10、96BF4 4000 4096BF8 3000 8192BF16 2500 16384BF32 2500 32768PZBDW32 1000 32768由于缓冲池的监控器收集的是自启用以后的数据，为获得调整后的准确情况，应关闭后重新打开，再次收集快照信息。db2 update monitor switches using bufferpool offdb2 update monitor switches using bufferpool ondb2 get snapshot for bufferpools on pzbdw|grep -i buffer|more重复以上步骤，获得比较合理的缓冲

11、池设置。4索引调优索引的调优，首先应该检查OA默认的索引是否已经创建成功，如果系统已经运行了较长的一段时间，可以对所有的索引进行一次runstat，保证索引的有效性，如果还是没有效果，就需要找到有严重性能问题的SQL语句进行有针对性的调优。对所有表进行runstat的命令：db2 -v reorgchk update statistics on table all4.1收集DB2运行时数据：DB使用事件监视器来收集运行时的数据，示例数据库名为pzbdw，rkmon是该示例所使用的事件监视器的名称，可以用其它任何名称来替代它。示例中的命令是在linux上测试的，其他操作系统可能要做一些相应的调整

12、。1.切换到db2inst1用户，保证db2inst1用户对/tmp目录具有写的权限，且具有500M以上剩余磁盘空间。启动监控器，创建一个名为rkmon的sql语句监控器，并启动之。su - db2inst1db2 connect to pzbdwdb2 update monitor switches using statement ondb2 create event monitor rkmon for statements write to file /tmpdb2 set event monitor rkmon state=12.进入应用系统执行相应的操作，重现系统问题（最好多做几次或配

13、合压力测试）。在/tmp 目录下，应该可以看到一组扩展名为“ .evt ”的文件，这些文件就是您的事件监视器文件。然后关闭监控，否则监控文件可能很快会将系统存储撑爆。db2 set event monitor rkmon state=03.从事件监视文件生成详细的SQL报告，在生成的sqltrace.txt文件中，可以看到这段时间执行的所有sql语句，消耗的时间等详细信息。db2evmon -path /tmp /tmp/sqltrace.txt4.如果要重新生成新的报告，需要先关闭监视器，清理监视文件，再重建，否则会和前面的事件文件混在一起不便分析。停止监控并删除日志：db2 drop ev

14、ent monitor rkmonrm -rf /tmp/*.evt重建监控器：db2 create event monitor rkmon for statements write to file /tmpdb2 set event monitor rkmon state=1所有任务完成后应停止全局的监控器$ db2 update monitor switches using statement off4.2分析数据，收集性能影响最大的SQL语句附件提供的db2trace工具，可以分析sqltrace.txt文件，并将分析结果导入数据库DB2TRACE表中，便于查询分析。附带源代码，可能某些

15、版本的DB2输出格式不完全一致，可以进行相应调整。先修改classes目录下的config.properties文件，修改数据库连接参数，db2trace.txt文件路径等信息。然后在命令行下进入db2trace目录，运行run.sh/bat文件，*unix环境下可能需要先赋予run.sh命令执行权限：chmod 777 run.sh在具有java环境的条件下，这个工具在服务器和客户端上都可以运行。由于sqltrace.txt文件往往比较大，在远程调优的环境下，将工具直接上传到服务器运行比较方便。AIX操作系统自带java环境，linux也可以使用WAS自带的jdk。Linux环境下JAVA环

16、境的配置请自行google。根据sqltrace文件大小不同，此命令运行可能需要较长时间，控制台没有输出，可以到数据库中查看db2trace表记录的变化，此命令每次运行都会先清空db2trace表再插入。分析完成后，就可以直接在数据库中查询得到性能影响较大的sql语句了。可以使用下面的四个来查询获得。如果查询出错，请检查db2trace表的schema是否正确。其中最耗CPU的SQL语句对于解决问题往往是最有价值的：最耗 CPU 的 SQL 语句db2 select sqltxt ,usrcpu from db2trace where operation not in (Static Comm

17、it,Static Rollback, Prepare, Open, Describe, Compile) order by usrcpu desc fetch first 10 rows only执行时间最长的SQL语句db2 select sqltxt, exectime ExecutionTime(sec) from db2trace where operation not in (Static Commit, Static Rollback, Prepare, Open, Describe, Compile) order by decimal (exectime) desc fetch

18、 first 10 rows only执行次数最多的 SQL 语句db2 select distinct(sqltxt),count(*) Count from db2trace where operation not in (Static Commit, Static Rollback,Prepare, Open, Describe, Compile) group by sqltxt order by count(*) desc fetch first 10 rows only排序时间最长的 SQL 语句db2 select sqltxt ,totsorttime TotalSortTime

19、(ms) from db2trace where operation not in (Static Commit, Static Rollback, Prepare, Open, Describe, Compile) order by decimal(totsorttime) desc fetch first 10 rows only收集整理上面得到的SQL语句，并将里面的?替换为实际参数，放到DB2客户端中执行几次，验证这些SQL的查询时间确实较长(一般的查询时间在1s以上就很慢了)。4.3使用索引顾问程序获得建议的索引收集到有性能问题的SQL后，可以根据经验调整索引，也可以使用DB2的顾问

20、程序获得建议的索引。将所有收集的语句放在 tune.sql 文件中，并将下面这行插入到该文件中，这样可以更改工作负载中每条语句的执行频率：-#SET FREQUENCY 这里的 表示随后要执行 SQL 语句的次数。最后的 tune.sql 文件类似于这样(最好在表名前加上schema，这样使用任何db2inst1用户连接数据库也没问题)：-#SET FREQUENCY 100select * from gzty.mv_object_right where attribute_id=11;select a.*,b.descri from gzty.mv_opinion_inst a,gzty.m

21、v_activity b where b.act_id =a.activity_id and a.formset_inst_id=11 order by binding_data_name,edit_time;select f.* from gzty.mv_form_data_inst f where f.formset_inst_id=11;select * from gzty.MV_FORM_FILE where OBJECT_ID=11 and OBJECT_TYPE=4;select * from gzty.mv_form_file f where f.object_id=11 and

22、 f.object_type 4;先创建db2执行计划的相关数据库对象(以下命令只需要执行一次)db2 -tvf /sqllib/misc/EXPLAIN.DDL以前面得到的tune.sql为输入参数，执行顾问程序得到建议索引，生成的索引建议文件为tuneidx.sqldb2advis -d dbname -i tune.sql -t 0 -o tuneidx.sql可以检查一下生成的建议索引，然后执行下面的命令创建索引db2 -tf tuneidx.sql -z turnidx.log重复以上步骤，尽可能是索引最优化。需要注意的时，并不是所有的查询都可以通过索引解决性能问题，有时可能是需要对

23、SQL或者应用进行优化才能从根本上解决问题的，比如：未分页的大数据量查询大表间的交叉连接导致笛卡尔乘积运算的这些问题暂不在本文讨论范围内。5.其他调优下面是一些DB2其他方面的调优要点，一般在初始化数据库的时候都需要调整的，从其他文档抄过来，供参考。(1)调整db2 的最大连接数 MAXAPPLS 和 MAXAGENTS（默认是400） ，MAXAPPLS值要略小于 MAXAGENTS （记住这两个值与硬件的配置大小有关的，不能随意增大，否则会超过物理的承受能力）使用以下命令查看 MAXAGENTS 和修改其值的大小db2 get dbm cfg db2 update dbm cfg usin

24、g MAXAGENTS N使用以下命令修改 MAXAPPLSdb2 get db cfg for DBNAMEdb2 update db cfg for DBNAME using MAXAPPLS N(2)调整 db2日志文件最大的大小 db2 get db cfg for DBNAME 查看到 LOGFILSIZ的值大小是多少，通常默认是1000，可加到10000(或更大)db2 update db cfg for DBNAME using LOGFILSIZ N(3)设置可打开最大文件数默认64 发现数据库该参数一直使用默认配置，系统正常运行时，不断打开和关闭文件的状态值相当高，减缓了 S

25、QL 响应时间并耗费了 CPU 周期。根据现场实际情况，调整该参数值，直到不断打开和关闭文件的状态停止。数据库配置参数 MAXFILOP 约束 DB2 能够同时打开的文件最大数量。当打开的文件数达到此数量时，DB2 将开始不断地关闭和打开它的表空间文件（包括裸设备）。不断地打开和关闭文件减缓了 SQL 响应时间并耗费了 CPU 周期。要查明 DB2 是否正在关闭文件，请发出以下命令：db2 get snapshot for database on DBNAME并查找以下的行：Database files closed = 0如果上述参数的值不为 0，那么增加 MAXFILOP 的值直到不断打开

26、和关闭文件的状态停止。使用以下命令：db2 update db cfg for DBNAME using MAXFILOP N(4)设置Locklist 和 Maxlockslocklist-在一个数据库全局内存中用于锁存储的内存。单位为页（4K）。maxlocks-一个应用程序允许得到的锁占用的内存所占locklist大小的百分比。 可根据实际应用环境调整这两个值 db2 get db cfg for DBNAME db2 update db cfg for DBNAME using locklist N db2 update db cfg for DBNAME using maxlocks

27、 N(5)设置超时锁 降低了原有的锁超时的参数值，防止在锁上等待过长时间会在锁上产生雪崩效应。原有LOCKTIMEOUT = 30，改为15db2 update db cfg for EXFLOW using LOCKTIMEOUT 15LOCKTIMEOUT 的缺省值是 -1，这意味着将没有锁超时（对 OLTP 应用程序，这种情况可能会是灾难性的）。尽管如此，我还是经常发现许多 DB2 用户用 LOCKTIMEOUT = -1。将 LOCKTIMEOUT 设置为很短的时间值，例如 10 或 15 秒。在锁上等待过长时间会在锁上产生雪崩效应。首先，用以下命令检查 LOCKTIMEOUT 的值：

28、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

29、currently= 0Lock waits= 0Time database waited on locks (ms)= 0Lock list memory in use (Bytes)= 576Deadlocks detected= 0Lock escalations= 0Exclusive lock escalations= 0Agents currently waiting on locks= 0Lock Timeouts= 0如果 Lock list memory in use (Bytes) 超过所定义 LOCKLIST 大小的 50%，那么在 LOCKLIST 数据库配置中增加 4

30、k 页的数量。 查看锁信息及释放死锁可通过下列一系列命令 db2 update monitor switches using lock ondb2 get snapshot for locks on DBNAME locks.txt 把锁信息输出到 locks.txt 文件中在 locks.txt 文件中查找某张表的相关锁找到持有这个锁的应用程序句柄，如：888db2 force application(888) (6)调排序堆发现数据库该参数一直使用默认值，根据现场情况，调整后，降低在 CPU、I/O 和所用时间方面的成本。 db2 update db cfg for EXFLOW usin

31、g SORTHEAP 256（调大一些）请发出以下命令：db2 get snapshot for database on DBNAME并查找以下行：Total sort heap allocated= 0Total sorts = 1Total sort time (ms)= 8Sort overflows = 0Active sorts = 0Commit statements attempted = 3Rollback statements attempted = 0Let transactions = Commit statements attempted + Rollbackstatements attemptedLet SortsPerTX= Total sorts / transactionsLet Percent