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

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

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

3、建数据库时就会分配好 在mix下，可以使用下而的命令查看缓冲池相关倍息: 切换到db2instl账号 sii- db2uistl 连接到pzbdw数据用 db2 connect to pzbdw 資看缓冲池定义 db2 select BPNAME,NPAGES,PAGESIZE from syscat.bufferpoolsH 查看表空间的定义，包含表空间(TableSpaceNaine).使用的缓冲池爼称(BufferpoolName),表空间的 页大小(TBSPageSize)t缓冲池的数fi：(Buffeq)oolPages)缓冲池的页大小数据(BufferpoolSize)信息。 db

4、2 M select s TBSP ACE T ableSpaceName ,b BPNAME BufferpoolNamesPAGESIZE TBSPageSize,b.NPAGES : | BufferpoolPages, b.PAGESIZE BufferpoolSize from SYSCAT BUFFERPOOLS bSYSCAT.TABLESPACES s ! where s BUFFERPOOLID=b BUFFERPOOLIDM|more 查看mxvorkitem表所在表空间和缓冲池信息，一般“MV_”开头的表使用的缓冲池是重点关注对象： db2 select TABSCHEM

5、A TableSchemaName, TABNAME TableName. t TBSPACE TableSpaceNaine,b.BPNAME BufferpoolName. b.NPAGES BufferpoolPages, b.PAGESIZE BufferpoolSize from SYSCAT TABLES t 5YSCAT BUFFERPOOLS bSYSCAT TABLESPACES swhere j I tabname=,MXr WORKITENf and s.BUFFERPOOLID=b BUFFERPOOLID and t TBSPACE=s TBSPACEM 开启缓冲池监

6、控器： . . * . db2 update monitor switches using bufferpool on * * * 在应用系统重现问题后，检査缓冲池的快照： db2 get snapshot for bufferpools on pzbdw|grep -i bufler|more 检査相关缓冲池快照需要重点关注的dan和mdeK的逻辑/物理读写数据 一般来说，在缓冲池足够的 情况卜，physical reads值趋近于0,而logical reads值则很大。下面是红塔集团OA的32k缓冲池,在正常时 的一个快曲。 Bufferpool Snapshot Bufferpool

7、name =BF32 Buffer pool data logical reads =493907 Buffer pool data physical reads =78| Buffer pool temporary data logical reads =129662 Buffer pool temporary data physical reads =0I Buffer pool data vntes =1 Buffer pool index logical reads =10302j Buffer pool index physical reads = 122 | Buffer pool

8、 temporary index logical reads =0 Buffer pool temporary index physical reads =0 Total buffer pool read time (milliseconds)= 671| t Total buffer pool xxTite tune (milliseconds)= 15j Buffer pool index writes =58 No victim buffers available = 635 Tablespaces using bufferpool =2i Alter bufferpool inform

9、ation: 1 如果发现物理和逻辑读的值相差不大.则使用卜俪的命令调整缓冲池大小.一般可以每次增加2000左右。 db2 ALTER BUFFERPOOL BF32 size 18000 缓冲池的调整是立即生效的，不需要重启数据库。需要注童的是，缓冲池的大小受物理内存和操作系统限 制，一般32位操作系统下，总的缓冲池大小不能超过1G。如果在这个限制下，不能满足所有缓冲池都达到物 理渎趋近于0,则考农尽可能保证用八体螫影响较大的（MV、UM等开头的农使用的）缓冲池大小。理论上64 位操作系统可以管理更大的内存空间，因此可以获得更好的性能。如下所示缓冲池，总大小为 1x4+4x4+3x8+2.5

10、x16-2.5x32+1x32=226X1 BPNAMENPAGES PAGESIZE IBMDEFAULTBP 1000 4096 BF4 4000 4096 BF8 3000 8192 BF16 2500 16384 BF32 2500 32768 PZBDW32 1000 32768 由于缓冲池的监控器收集的是自启用以后的数据，为获得调整后的准确悄况，应关闭后巫新打开，再次收 集快照信息。 db2 update monitor switches using bufferpool off : db2 update monitor switches using bufferpool on *

11、 db2 get snapshot for bufferpools on pzbdw|grep i buffer|more 重复以上步骤，获得比较合理的缓冲池设置。 4 索引调优 索引的调优，首先应该检查0A默认的索引是否已经创建成功如果系统已经运行了较长的一段时间，可 以对所有的索引进行一次ninstat,保证索引的有效性，如果还是没有效果.就需要找到有严朮性能问题的SQL 语句进行有针对性的调优。 对所有表进行nmstat的命令: db2 -v reorgchk update statistics on table all 4.1收集DB2运行时数据: DB使用韦件监视器來收集运行时的数据

12、.示例数据库名为pzbdw. rkinon是该示例所使用的察件监视器 的名称，可以用其它任何名称來替代它。示例屮的命令是在lmux上测试的，其他操作系统可能要做一些相应 的调整 1. 切换到db2instl用户，保证db2instl用户对tmp冃录具有写的权限，且具有500M以上剩余磁盘空间。启 动监控器，创建一个冬为rkmoii的sql语句监控器，并启动Z。 su - db2instl db2 connect to pzbdw db2 update monitor switches using statement onH db2 create event monitor rknion for

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

14、-oath /tino . tmo saltrace.txt 4.如果要巫新生成新的报吿，需要先关闭监视器，淸理监视文件再重建，否则会和前而的事件文件混在 起不便分析。 停止监控并删除日志： I db2 drop event monitor Tkrnon nn -rf ztnqj/*.eM 审建监控器: db2 Mcreate event monitor rknion for statements write to file /trnp”. db2 set event monitor rkiuon state=l M 所有任务完成后应停止全局的监控器 Sdb2 update monitor s

15、witches using statement off* : 4.2分析数据，收集性能影响最大的SQL语句 附件提供的db2trace T具，可以分析sqltrace.txt文件，并将分析结果导入数据库DB2TRACE表中，便于 査询分析。附带源代码，可能某些版本的DB2输出格式不完全一致，可以进行相应调整。 先修改classes 录下的config properties文件，修改数据库连接参数，db2trace.txt文件路径等信息.然后 在命令行卜进入db2trace 口录，运行nin shAat文件，*imix环境下可能需要先賦予nrn sh命令执行权限： i chmod 777 nm

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

17、査询出错，请检査db2trace表的schema是否正确。其中最耗CPU的SQL语句对于解决问题往往是 最有价值的： 最耗CPU的SQL语句 : db2 select sqltxt ,usrcpu fironi db2trace where operation not in (Static Conimif/Static Rollback*. Prepare,: *Open Describe, Compile? order by usrcpu desc fetch first 10 rows only* * 执行时间最长的SQL语句 db2Mselectsqltxt.exectimeTxecut

18、ionTlme(sec)*from db2trace whereoperationnotin (Static ConuiutStatic； ! : ! Rollback*. Prepare*, Open1, describe*, Compile*) order by decimal (exectime) desc fetch first 10 rows only* 执行次数最多的SQL语句 : db2 Hselect distinct(sqltxt),count(*) Coiuit from db2trace where operation not in (Static Commit1, St

19、atic RollbackTPreparel Open, Describe*, Compile*) group by sqltxt order by count(*) desc fetch first 10 rows only* 排序时间垠长的SQL语句 * db2 Mselect sqltxt jotsorttime TotalSortTlme(ms)1 belcct a.*,b.de5Cii Hom gzty .ui _upMnou_iubt a,gzty.m _acli ity b wheie b.act_id a.actix ity_id aud a. formset_inst_id=

20、 11 order by bmding_data_name ,edit_time; select * from gzty.mfomi_data_iiist f where f.fbrmset_inst_id= 11; select a from gzty.MV_FORM_FILE where OBJECT_ID=11 and OBJECT_TYPE=4: select * from gzty.mv_fbnn_file f where f.object_id=l 1 and f object_type 4; 先创建db2执行计划的相关数据库对象（以下命令只需要执行一次） db2 tvf /sql

21、lib/misc/EXPLAIN DDL 以前而得到的nme sql为输入参数，执行顾问程序得到建议索引，生成的索引建议文件为nrneidx sql db2ad is -d dbname -1 tiuie.sql -t 0 -o tuneidx.sql 可以检查一下生成的建议索引，然后执行下而的命令创建索引 db2 tf nuieidx.sql -z tuniidx log 重复以上步骤，尽可能是索引最优化。 需要注意的时，井不是所有的查询都可以通过索引解决性能问题，有时可能是需耍对SQL或者应用进行 优化才能从根本上解决问题的，比如： 未分页的大数据量査询 大表间的交叉连接导致笛匸尔乘积运算

22、的 这些问题何不在本文讨论范围内. 5.英他调优 下而是-些DB2其他方面的调优耍点，-般在初始化数据库的时候都需要调整的，从其他文档抄过來， 供参考. （1）调整db2的披大连接数 MAXAPPLS和 MAXAGENTS （默认是400）, MAXAPPLS值婆略小于 MAXAGENTS （记住这两个值与硬件的配置大小有关的，不能随意增大，否则会超过物理的承受能力） 使用以下命令査看MAXAGENTS和修改英值的大小 db2 get dbm cfg db2 update dbm cfg using MAXAGENTS N 使用以卜命令修改MAXAPPLS db2 get db cfg for

23、 DBNAME db2 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) 设It可打开最大文件数默认64 发现数据库该参数一直使用默认配豐，系统正常运行时，不断打开和关闭文件的状态值柑当高，减缓了 SQL响应时间并耗费了 CPU周期。根据现场实际情况，调整该参数值，直到不断打开和关闭文件

24、的状态停 止. 数据J4：配宜参数N1AXFIL0P约束DB2能够同时打开的文件故人数量。当打开的文件数达到此数量时， DB2将开始不断地关闭和打开它的表空间文件(包括裸设备)。不断地打开和关闭文件减缓了 SQL响应时 间并耗费f CPU周期。 要査明DB2是否正在关闭文件，请发出以下命令： db2 get snapshot for database on DBNAME 并査找以下的行： Database files closed = 0 如果卜.述参数的值不为0.那么增加MAXFILOP的值直到不断打开和关闭文件的状态停止。使用以卜命 令： db2 update db cfg for DBN

25、AME using MAXFILOP NM (4) 设宜 Locklist 和 Maxlocks locklist-在一个数据廂全局内存中用于锁存储的内存。单位为页(4K) o maxlocks-一个应用程序允许得到的锁占用的内存所占locklist大小的百分比。 可根拥实际应用环境调整这两个值 db2 get db cfg for DBNAME db2 update db cfg for DBNAME usmg locklist N db2 update db cfg for DBNAME using maxlocks N (5) 设置超时锁 降低了原有的锁超时的参数值，防止在锁上等待过长时

26、间会在锁上产生雪崩效应。 原有 LOCKTIMEOUT = 30,改为 15 db2 update db cfg for EXFLOW using LOCKTIMEOUT 15 LOCKTIMEOUT的缺省值是-1,这盘味着将没有锁超时(对OLTP应用程序，这种悄况可能会是灾难 性的)尽管如此，我还是经常发现许多DB2用户用LOCKTIMEOUT = -1 locks txt 把锁信息输出到locks.txt文件中 在locks txt文件中查找某张表的相关锁 找到持有这个锁的应用程序句柄，如：888 db2 force application88) (6) 调排序堆 发现数据库该参数一直便用

27、默认值，根据现场情况，调整肓，降低在CPU. I/O和所用时间方面的成本。 db? update db rfg far FXFT.OW using SORTHFAP 256 (调大一些) 请发出以下命令： db2 get snapshot for database on DBNAME 并査找以下行: Total sort heap allocated= 0 Total sorts = 1 Total sort tmie （ms）= 8 Sort overflows = 0 Acthe sorts = 0 Commit statements attempted = 3 Rollback stat

28、ements attempted = 0 Let transactions = Conmut statements attempted * Rollback statements attempted Let SortsPerTX= Total sorts / transactions Let PercentSortOA erflows = Sort overflows * 100 / Total sorts 100. 如果 PercentSortO erflows （Sort overflows * 100） / Total sorts ）人 丁 3 个白分点，那么在应用程序 SQL 中会出现

29、严重的或意外的排序问题。因为正是溢出的存在表明发生了大的排序，所以理想的情况是发现没有排 序溢出或至少其百分比小于一个百分点。 如果出现过多的排序溢出，那么“应急解决方案是増加SORTHEAP的大小。然而，这样做只是掩盖了氏实的 性能问题。相反，您应该确定引起排序的SQL并更改该SQL、索引或群集来避免或减少排序开销。 如果SortsPerTX大于5 （作为一种经脸之谈），那么每个事务的排序数可能很大。虽然某些应用程序 审务执行许多小的组合排序（它们不会溢出并且执行时间很短），但是它消耗了过多的CPU当SortsPerTX 很大时，按我的经齡，这些机器通常会受到CPU的限制。确定引起排序的SQ

30、L并改进存取方案（通过索引、 群集或更改SQL）对提离事务吞吐率是极为重要的。 dbm: ASLHEAPSZ 256,? query_heap_szN査询堆的大小必须大于或等于ASLHEAPSZ,最好5倍以上 db: APPLHEAPSZ 4096 应用程序堆是供数据库管理器代表某个特定代理使用的私有内存。肖代理或子代理要为应用程序而初始化时， 就要从这个堆中分配内存，并且所分配的内存数量是处理请求时所需的最小内存量，如果需要更多的内存，则 辰名可以从堆中分配山该参数指宜的一个屐大值那么多的内存.按256逐次增加.直到错误消失， APP CTL HEAP SZ 1024 了解了参数的功能后，卅需要对这些参数进行调整时，应同时考虑是否需要对英它几个相关参数进行调整。 例如当SQL0973N错误提示需婆増大应用程序控制堆的大小时，则可直接增大APP_CTL_HEAP_SZ参数的 值，用八希望维持应用程序组中应用程序数口不变，山于该值为APPGROUP_MEM_SZ/APP_CTL_HEAP_SZ 所决定，就要考虑同时增大APPGROUP_NIEM_SZ的值，即增大应用程序组共享内存：或由于应用程序控制 堆为公式(100 - GROUPHEAP_R