db缓冲池和索引调优的方法

1、DB2缓冲池和索引调优的方法1 DB2性能问题的表现应用系统（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 "sel

4、ect s.TBSPACE TableSpaceName,b.BPNAME BufferpoolName,s.PAGESIZE TBSPageSize,b.NPAGES BufferpoolPages, b.PAGESIZE BufferpoolSize from SYSCAT.BUFFERPOOLSb,SYSCAT.TABLESPACESswheres.BUFFERPOOLID=b.BUFFERPOOLID"|more查看mv_workitem 表所在表空间和缓冲池信息，一般" MV_ "开头的表使用的缓冲池 是重点关注对象：db2 "select T

5、ABSCHEMA TableSchemaName, TABNAME TableName, t.TBSPACE TableSpaceName,b.BPNAME BufferpoolName, b.NPAGES BufferpoolPages, b.PAGESIZE BufferpoolSize from SYSCAT.TABLES t .SYSCAT.BUFFERPOOLS b,SYSCAT.TABLESPACES swhere tabname='MV_WORKITEM'ands.BUFFERPOOLID=b.BUFFERPOOLID and t.TBSPACE=s.TBSPAC

6、E"开启缓冲池监控器：db2 update monitor switches 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

7、= BF32Buffer pool data logical reads= 493907Buffer 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

8、logical reads= 0Buffer pool temporary 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:如果发现物理和逻辑读的值相差不大，则使用下面的命

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

10、SIZEIBMDEFAULTBP10004096BF440004096BF830008192BF16250016384BF32250032768PZBDW32100032768由于缓冲池的监控器收集的是自启用以后的数据，为获得调整后的准确情况， 应关闭后重新打开，再次收集快照信息。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 on"db2 "create event monitor rkmon for statements write to file '/tmp'"db2 "

13、;set event monitor rkmon state=1"2 .进入应用系统执行相应的操作，重现系统问题（最好多做几次或配合压力测试）。在/tmp 目录下，应该可以看到一组扩展名为“ .evt ”的文件，这些文件就是您的事件监视器 文件。然后关闭监控，否则监控文件可能很快会将系统存储撑爆。db2 "set event monitor rkmon state=0"3 .从事件监视文件生成详细的SQL报告，在生成的sqltrace.txt文件中，可以看到这段时间执行的所有sql语句，消耗的时间等详细信息。db2evmon -path /tmp > /tm

14、p/sqltrace.txt4 .如果要重新生成新的报告，需要先关闭监视器，清理监视文件，再重建，否则会和前面 的事件文件混在一起不便分析。停止监控并删除日志：db2 "drop event monitor rkmon"rm -rf /tmp/*.evt重建监控器：db2 "create event monitor rkmon for statements write to file '/tmp'"db2 "set event monitor rkmon state=1"所有任务完成后应停止全局的监控器$ db2 &q

15、uot;update monitor switches using statement off"4.2 分析数据，收集性能影响最大的SQL语句附件提供的db2trace工具，可以分析sqltrace.txt文件，并将分析结果导入数据库 DB2TRACE表中，便于查询分析。附带源代码，可能某些版本的 DB2输出格式不完全一致， 可以进行相应调整。先修改classes目录下的perties文件，修改数据库连接参数，db2trace.txt文件路径等信息。然后在命令彳T下进入db2trace目录，运行run.sh/bat文件，*unix环境下可能需要先赋予run.sh命

16、令执行权限：chmod 777 run.sh在具有java环境的条件下，这个工具在服务器和客户端上都可以运行。由于sqltrace.txt文件往往比较大，在远程调优的环境下，将工具直接上传到服务器运行比较方便。AIX操作系统自带java环境，linux也可以使用 WAS自带的jdk。Linux环境下JAVA 环境的配置请自行 google。根据sqltrace文件大小不同，此命令运行可能需要较长时间，控制台没有输出，可以到数据库中查看db2trace表记录的变化，此命令每次运行都会先清空db2trace表再插入。分析完成后，就可以直接在数据库中查询得到性能影响较大的sql语句了。可以使用下面的

17、四个来查询获得。如果查询出错，请检查db2trace表的schema是否正确。其中最耗CPU的SQL语句对于解决问题往往是最有价值的：最耗CPU的SQL语句db2 "select sqltxt ,usrcpu from db2trace where operation not in ('Static Commit','Static Rollback', 'Prepare', 'Open', 'Describe', 'Compile') order by usrcpu desc fetch

18、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 fetc

19、h 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 firs

20、t 10 rows only"排序时间最长的 SQL语句db2 "select sqltxt ,totsorttime ' TotalSortTime(ms)' from db2trace where operation not in ('Static Commit', 'Static Rollback', 'Prepare', 'Open', 'Describe', 'Compile') order by decimal(totsorttime) desc fe

21、tch first 10 rows only"收集整理上面得到的SQL语句，并将里面的？替换为实际参数，放到 DB2客户端中执行几次，验证这些 SQL的查询时间确实较长(一般的查询时间在1s以上就很慢了)。4.3 使用索引顾问程序获得建议的索引收集到有性能问题的SQL后，可以根据经验调整索引，也可以使用 DB2的顾问程序获得建议的索引。将所有收集的语句放在tune.sql文件中，并将下面这行插入到该文件中，这样可以更改工作负载中每条语句的执行频率：-#SET FREQUENCY <x>这里的<x>表示随后要执行SQL语句的次数。最后的tune.sql文件类似于

22、这样（最好在表名前加上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.mv_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 gzt

23、y.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 f.object_type > 4;先创建db2执行计划的相关数据库对象（以下命令只需要执行一次）db2 -tvf /sqllib/misc/EXPLAIN.DDL以前面得到的tune.sql为输入参数，执行顾问程序得到建议索引，生成的索引

24、建议文件为 tuneidx.sqldb2advis -d dbname -i tune.sql -t 0 -o tuneidx.sql可以检查一下生成的建议索引，然后执行下面的命令创建索引db2 -tf tuneidx.sql -z turnidx.log重复以上步骤，尽可能是索引最优化。需要注意的时，并不是所有的查询都可以通过索引解决性能问题，有时可能是需要对SQL或者应用进行优化才能从根本上解决问题的，比如：未分页的大数据量查询大表间的交叉连接导致笛卡尔乘积运算的这些问题暂不在本文讨论范围内。5. 其他调优下面是一些DB2 其他方面的调优要点，一般在初始化数据库的时候都需要调整的，从其他文

25、档抄过来，供参考。(1) 调整 db2 的最大连接数 MAXAPPLS 和 MAXAGENTS （默认是400） ， MAXAPPLS值要略小于 MAXAGENTS （记住这两个值与硬件的配置大小有关的，不能随意增大，否则会超过物理的承受能力）使用以下命令查看MAXAGENTS 和修改其值的大小db2 get dbm cfgdb2 update dbm cfg using MAXAGENTS N使用以下命令修改 MAXAPPLSdb2 get db cfg for DBNAMEdb2 update db cfg for DBNAME using MAXAPPLS N(2) 调整 db2 日志文

26、件最大的大小db2 get db cfg for DBNAME 查看到 LOGFILSIZ 的值大小是多少， 通常默认是1000 ， 可加到10000（或更大）db2 update db cfg for DBNAME using LOGFILSIZ N(3) 设置可打开最大文件数默认64发现数据库该参数一直使用默认配置， 系统正常运行时， 不断打开和关闭文件的状态值相当高，减缓了 SQL 响应时间并耗费了 CPU 周期。根据现场实际情况，调整该参数值，直到不断打开和关闭文件的状态停止。数据库配置参数 MAXFILOP 约束 DB2 能够同时打开的文件最大数量。 当打开的文件数达到此数量时， D

27、B2 将开始不断地关闭和打开它的表空间文件（包括裸设备） 。 不断地打开和关闭文件减缓了 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"(4) 设置 Locklist 和

28、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 N(5) 设置超时锁降低了原有的锁超时的参数值，防止在锁上等待过长时间会在锁上产生雪崩效应。原有 LOCKTIMEOUT = 30 ，改为 15db

29、2 update db cfg for EXFLOW using LOCKTIMEOUT 15LOCKTIMEOUT 的缺省值是-1，这意味着将没有锁超时(对OLTP 应用程序，这种情况可能会是灾难性的) 。 尽管如此， 我还是经常发现许多 DB2 用户用 LOCKTIMEOUT =-1。 将 LOCKTIMEOUT 设置为很短的时间值， 例如 10 或 15 秒。 在锁上等待过长时间会 在锁上产生雪崩效应。首先，用以下命令检查LOCKTIMEOUT 的值：db2 "get db cfg for DBNAME"并查找包含以下文本的行：Lock timeout (sec) (

30、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= 0Lock waits= 0Tim

31、e 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 数据库配置中增加4k 页的数量。查看锁信息及释放死锁可通过下列一系列命令db2

32、 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 using SORTHEAP 256 (调大一些)请发出以下命

33、令：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 attempte

34、dLet SortsPerTX= Total sorts / transactionsLet PercentSortOverflows = Sort overflows * 100 / Total sorts 100/ 14392681如果 PercentSortOverflows （Sort overflows * 100） / Total sorts ） 大于 3 个百分点，那么在应用程序SQL 中会出现严重的或意外的排序问题。 因为正是溢出的存在表明发生了大的排序，所以理想的情况是发现没有排序溢出或至少其百分比小于一个百分点。如果出现过多的排序溢出， 那么 “应急 ”解决方案是增加 SO

35、RTHEAP 的大小。 然而， 这样做只是掩盖了真实的性能问题。相反，您应该确定引起排序的 SQL 并更改该 SQL 、索引或群集来避免或减少排序开销。如果 SortsPerTX 大于 5 （作为一种经验之谈） ，那么每个事务的排序数可能很大。虽然某些应用程序事务执行许多小的组合排序（它们不会溢出并且执行时间很短） ，但是它消耗了过多的 CPU。当SortsPerTX很大时，按我的经验，这些机器通常会受到CPU的限制。确定引起排序的 SQL 并改进存取方案（通过索引、群集或更改SQL ）对提高事务吞吐率是极为重要的。dbm:ASLHEAPSZ 256 ,query_heap_sz N 查询堆的

36、大小必须大于或等于 ASLHEAPSZ , 最好 5倍以上 db:APPLHEAPSZ 4096应用程序堆是供数据库管理器代表某个特定代理使用的私有内存。 当代理或子代理要为应用程序而初始化时， 就要从这个堆中分配内存， 并且所分配的内存数量是处理请求时所需的最小内存量， 如果需要更多的内存， 则最多可以从堆中分配由该参数指定的一个最大值那么多的内存。按256 逐次增加，直到错误消失。APP_CTL_HEAP_SZ 1024了解了各参数的功能后， 当需要对这些参数进行调整时， 应同时考虑是否需要对其它几个相关参数进行调整。例如当 SQL0973N 错误提示需要增大应用程序控制堆的大小时，则可直接增大 APP_CTL_HEAP_SZ 参数的值， 用户希望维持应用程序组中应用程序数