DB2缓冲池和索引调优的方法讲解.docx
《DB2缓冲池和索引调优的方法讲解.docx》由会员分享，可在线阅读，更多相关《DB2缓冲池和索引调优的方法讲解.docx（14页珍藏版）》请在冰豆网上搜索。
DB2缓冲池和索引调优的方法讲解
DB2缓冲池和索引调优的方法
1DB2性能问题的表现
应用系统（OA）上的表现：
一般是登录、首页、待办列表等数据量比较大的模块，响应时间长，耗时数秒到数十秒都有可能。
有时候是用户访问高峰期慢，下班时间又比较正常。
操作系统上的表现：
一般是中间件服务器（WAS）系统正常，CPU和IO占用不会持续超过50%，系统运行进程不会有持续的等待。
数据库服务器则非常繁忙，CPU占用持续在50%以上，往往会达到持续90%左右，IO占用可能不高。
从系统层面判断，性能瓶颈出在数据库上。
2调优的基本思路
DB2的性能和操作系统、锁、缓冲池、索引等参数，以及SQL的写法都有很大关系，受限于个人认识，这里主要介绍缓冲池和索引的调优方法。
缓冲池的调整比较简单，一般可以先调整缓冲池，若效果不明显，则再调整索引和SQL。
3缓冲池调优
缓冲池是内存中的一块区域，DB2会将用到数据放到缓冲池中提高性能。
缓冲池太小，每次查询仍然要到磁盘中操作，达不到缓冲的效果。
缓冲池太大，超出操作系统管理的限制，会导致数据库无法连接的错误。
缓冲池是通过表空间与数据表发生联系的，数据表存放在指定的表空间中，每个表空间又有指定的缓冲池。
因为每张数据表存储的数据量都不同，一般根据每条记录存放的最大数据量，我们会为数据表分别指定4k-32k不同的表空间来存放，以达到优化存储和性能的目的，缓冲池也是类似。
这个一般在创建数据库时就会分配好了。
在*unix下，可以使用下面的命令查看缓冲池相关信息：
切换到db2inst1账号
su–db2inst1
连接到pzbdw数据库
db2connecttopzbdw
查看缓冲池定义
db2"selectBPNAME,NPAGES,PAGESIZEfromsyscat.bufferpools"
查看表空间的定义，包含表空间名称（TableSpaceName）、使用的缓冲池名称（BufferpoolName），表空间的页大小（TBSPageSize），缓冲池的数量（BufferpoolPages），缓冲池的页大小数据（BufferpoolSize）信息。
db2"selects.TBSPACETableSpaceName,b.BPNAMEBufferpoolName,s.PAGESIZETBSPageSize,b.NPAGESBufferpoolPages,b.PAGESIZEBufferpoolSizefromSYSCAT.BUFFERPOOLSb,SYSCAT.TABLESPACESswheres.BUFFERPOOLID=b.BUFFERPOOLID"|more
查看mv_workitem表所在表空间和缓冲池信息，一般“MV_”开头的表使用的缓冲池是重点关注对象：
db2"selectTABSCHEMATableSchemaName,TABNAMETableName,t.TBSPACETableSpaceName,b.BPNAMEBufferpoolName,b.NPAGESBufferpoolPages,b.PAGESIZEBufferpoolSizefromSYSCAT.TABLESt,SYSCAT.BUFFERPOOLSb,SYSCAT.TABLESPACESswheretabname='MV_WORKITEM'ands.BUFFERPOOLID=b.BUFFERPOOLIDandt.TBSPACE=s.TBSPACE"
开启缓冲池监控器：
db2updatemonitorswitchesusingbufferpoolon
在应用系统重现问题后，检查缓冲池的快照：
db2getsnapshotforbufferpoolsonpzbdw|grep-ibuffer|more
检查相关缓冲池快照，需要重点关注的data和index的逻辑/物理读写数据，一般来说，在缓冲池足够的情况下，physicalreads值趋近于0，而logicalreads值则很大。
下面是红塔集团OA的32k缓冲池，在正常时的一个快照。
BufferpoolSnapshot
Bufferpoolname=BF32
Bufferpooldatalogicalreads=493907
Bufferpooldataphysicalreads=78
Bufferpooltemporarydatalogicalreads=129662
Bufferpooltemporarydataphysicalreads=0
Bufferpooldatawrites=1
Bufferpoolindexlogicalreads=10302
Bufferpoolindexphysicalreads=122
Bufferpooltemporaryindexlogicalreads=0
Bufferpooltemporaryindexphysicalreads=0
Totalbufferpoolreadtime（milliseconds）=671
Totalbufferpoolwritetime（milliseconds）=15
Bufferpoolindexwrites=58
Novictimbuffersavailable=635
Tablespacesusingbufferpool=2
Alterbufferpoolinformation:
如果发现物理和逻辑读的值相差不大，则使用下面的命令调整缓冲池大小，一般可以每次增加2000左右。
db2ALTERBUFFERPOOLBF32size18000
缓冲池的调整是立即生效的，不需要重启数据库。
需要注意的是，缓冲池的大小受物理内存和操作系统限制，一般32位操作系统下，总的缓冲池大小不能超过1G。
如果在这个限制下，不能满足所有缓冲池都达到物理读趋近于0，则考虑尽可能保证用户体验影响较大的（MV、UM等开头的表使用的）缓冲池大小。
理论上64位操作系统可以管理更大的内存空间，因此可以获得更好的性能。
如下所示缓冲池，总大小为1x4+4x4+3x8+2.5x16+2.5x32+1x32=226M
BPNAMENPAGESPAGESIZE
------------------------------------------------------------------
IBMDEFAULTBP10004096
BF440004096
BF830008192
BF16250016384
BF32250032768
PZBDW32100032768
由于缓冲池的监控器收集的是自启用以后的数据，为获得调整后的准确情况，应关闭后重新打开，再次收集快照信息。
db2updatemonitorswitchesusingbufferpooloff
db2updatemonitorswitchesusingbufferpoolon
db2getsnapshotforbufferpoolsonpzbdw|grep-ibuffer|more
重复以上步骤，获得比较合理的缓冲池设置。
4索引调优
索引的调优，首先应该检查OA默认的索引是否已经创建成功，如果系统已经运行了较长的一段时间，可以对所有的索引进行一次runstat，保证索引的有效性，如果还是没有效果，就需要找到有严重性能问题的SQL语句进行有针对性的调优。
对所有表进行runstat的命令：
db2-vreorgchkupdatestatisticsontableall
4.1收集DB2运行时数据：
DB使用事件监视器来收集运行时的数据，示例数据库名为pzbdw，rkmon是该示例所使用的事件监视器的名称，可以用其它任何名称来替代它。
示例中的命令是在linux上测试的，其他操作系统可能要做一些相应的调整。
1.切换到db2inst1用户，保证db2inst1用户对/tmp目录具有写的权限，且具有500M以上剩余磁盘空间。
启动监控器，创建一个名为rkmon的sql语句监控器，并启动之。
su-db2inst1
db2connecttopzbdw
db2"updatemonitorswitchesusingstatementon"
db2"createeventmonitorrkmonforstatementswritetofile'/tmp'"
db2"seteventmonitorrkmonstate=1"
2.进入应用系统执行相应的操作，重现系统问题（最好多做几次或配合压力测试）。
在/tmp目录下，应该可以看到一组扩展名为“.evt”的文件，这些文件就是您的事件监视器文件。
然后关闭监控，否则监控文件可能很快会将系统存储撑爆。
db2"seteventmonitorrkmonstate=0"
3.从事件监视文件生成详细的SQL报告，在生成的sqltrace.txt文件中，可以看到这段时间执行的所有sql语句，消耗的时间等详细信息。
db2evmon-path/tmp>/tmp/sqltrace.txt
4.如果要重新生成新的报告，需要先关闭监视器，清理监视文件，再重建，否则会和前面的事件文件混在一起不便分析。
停止监控并删除日志：
db2"dropeventmonitorrkmon"
rm-rf/tmp/*.evt
重建监控器：
db2"createeventmonitorrkmonforstatementswritetofile'/tmp'"
db2"seteventmonitorrkmonstate=1"
所有任务完成后应停止全局的监控器
$db2"updatemonitorswitchesusingstatementoff"
4.2分析数据，收集性能影响最大的SQL语句
附件提供的db2trace工具，可以分析sqltrace.txt文件，并将分析结果导入数据库DB2TRACE表中，便于查询分析。
附带源代码，可能某些版本的DB2输出格式不完全一致，可以进行相应调整。
先修改classes目录下的config.properties文件，修改数据库连接参数，db2trace.txt文件路径等信息。
然后在命令行下进入db2trace目录，运行run.sh/bat文件，*unix环境下可能需要先赋予run.sh命令执行权限：
chmod777run.sh
在具有java环境的条件下，这个工具在服务器和客户端上都可以运行。
由于sqltrace.txt文件往往比较大，在远程调优的环境下，将工具直接上传到服务器运行比较方便。
AIX操作系统自带java环境，linux也可以使用WAS自带的jdk。
Linux环境下JAVA环境的配置请自行google。
根据sqltrace文件大小不同，此命令运行可能需要较长时间，控制台没有输出，可以到数据库中查看db2trace表记录的变化，此命令每次运行都会先清空db2trace表再插入。
分析完成后，就可以直接在数据库中查询得到性能影响较大的sql语句了。
可以使用下面的四个来查询获得。
如果查询出错，请检查db2trace表的schema是否正确。
其中最耗CPU的SQL语句对于解决问题往往是最有价值的：
最耗CPU的SQL语句
db2"selectsqltxt,usrcpufromdb2tracewhereoperationnotin（'StaticCommit','StaticRollback','Prepare','Open','Describe','Compile'）orderbyusrcpudescfetchfirst10rowsonly"
执行时间最长的SQL语句
db2"selectsqltxt,exectime'ExecutionTime（sec）'fromdb2tracewhereoperationnotin（'StaticCommit','StaticRollback','Prepare','Open','Describe','Compile'）orderbydecimal（exectime）descfetchfirst10rowsonly"
执行次数最多的SQL语句
db2"selectdistinct（sqltxt）,count（*）Countfromdb2tracewhereoperationnotin（'StaticCommit','StaticRollback','Prepare','Open','Describe','Compile'）groupbysqltxtorderbycount（*）descfetchfirst10rowsonly"
排序时间最长的SQL语句
db2"selectsqltxt,totsorttime'TotalSortTime（ms）'fromdb2tracewhereoperationnotin（'StaticCommit','StaticRollback','Prepare','Open','Describe','Compile'）orderbydecimal（totsorttime）descfetchfirst10rowsonly"
收集整理上面得到的SQL语句，并将里面的?
替换为实际参数，放到DB2客户端中执行几次，验证这些SQL的查询时间确实较长（一般的查询时间在1s以上就很慢了）。
4.3使用索引顾问程序获得建议的索引
收集到有性能问题的SQL后，可以根据经验调整索引，也可以使用DB2的顾问程序获得建议的索引。
将所有收集的语句放在tune.sql文件中，并将下面这行插入到该文件中，这样可以更改工作负载中每条语句的执行频率：
--#SETFREQUENCY
这里的表示随后要执行SQL语句的次数。
最后的tune.sql文件类似于这样（最好在表名前加上schema，这样使用任何db2inst1用户连接数据库也没问题）：
--#SETFREQUENCY100
select*fromgzty.mv_object_rightwhereattribute_id=11;
selecta.*,b.descrifromgzty.mv_opinion_insta,gzty.mv_activitybwhereb.act_id=a.activity_idanda.formset_inst_id=11orderbybinding_data_name,edit_time;
selectf.*fromgzty.mv_form_data_instfwheref.formset_inst_id=11;
select*fromgzty.MV_FORM_FILEwhereOBJECT_ID=11andOBJECT_TYPE=4;
select*fromgzty.mv_form_filefwheref.object_id=11andf.object_type>4;
先创建db2执行计划的相关数据库对象（以下命令只需要执行一次）
db2-tvf~/sqllib/misc/EXPLAIN.DDL
以前面得到的tune.sql为输入参数，执行顾问程序得到建议索引，生成的索引建议文件为tuneidx.sql
db2advis-ddbname-itune.sql-t0-otuneidx.sql
可以检查一下生成的建议索引，然后执行下面的命令创建索引
db2-tftuneidx.sql-zturnidx.log
重复以上步骤，尽可能是索引最优化。
需要注意的时，并不是所有的查询都可以通过索引解决性能问题，有时可能是需要对SQL或者应用进行优化才能从根本上解决问题的，比如：
Ø未分页的大数据量查询
Ø大表间的交叉连接导致笛卡尔乘积运算的
这些问题暂不在本文讨论范围内。
5.其他调优
下面是一些DB2其他方面的调优要点，一般在初始化数据库的时候都需要调整的，从其他文档抄过来，供参考。
（1）调整db2的最大连接数MAXAPPLS和MAXAGENTS（默认是400），MAXAPPLS值要略小于MAXAGENTS（记住这两个值与硬件的配置大小有关的，不能随意增大，否则会超过物理的承受能力）
使用以下命令查看MAXAGENTS和修改其值的大小
db2getdbmcfg
db2updatedbmcfgusingMAXAGENTSN
使用以下命令修改MAXAPPLS
db2getdbcfgforDBNAME
db2updatedbcfgforDBNAMEusingMAXAPPLSN
（2）调整db2日志文件最大的大小
db2getdbcfgforDBNAME查看到LOGFILSIZ的值大小是多少，通常默认是1000，可加到10000（或更大）
db2updatedbcfgforDBNAMEusingLOGFILSIZN
（3）设置可打开最大文件数[默认64]
发现数据库该参数一直使用默认配置，系统正常运行时，不断打开和关闭文件的状态值相当高，减缓了SQL响应时间并耗费了CPU周期。
根据现场实际情况，调整该参数值，直到不断打开和关闭文件的状态停止。
数据库配置参数MAXFILOP约束DB2能够同时打开的文件最大数量。
当打开的文件数达到此数量时，DB2将开始不断地关闭和打开它的表空间文件（包括裸设备）。
不断地打开和关闭文件减缓了SQL响应时间并耗费了CPU周期。
要查明DB2是否正在关闭文件，请发出以下命令：
db2"getsnapshotfordatabaseonDBNAME"
并查找以下的行：
Databasefilesclosed=0
如果上述参数的值不为0，那么增加MAXFILOP的值直到不断打开和关闭文件的状态停止。
使用以下命令：
db2"updatedbcfgforDBNAMEusingMAXFILOPN"
（4）设置Locklist和Maxlocks
locklist--在一个数据库全局内存中用于锁存储的内存。
单位为页（4K）。
maxlocks--一个应用程序允许得到的锁占用的内存所占locklist大小的百分比。
可根据实际应用环境调整这两个值
db2getdbcfgforDBNAME
db2updatedbcfgforDBNAMEusinglocklistN
db2updatedbcfgforDBNAMEusingmaxlocksN
（5）设置超时锁
降低了原有的锁超时的参数值，防止在锁上等待过长时间会在锁上产生雪崩效应。
原有LOCKTIMEOUT=30，改为15
db2updatedbcfgforEXFLOWusingLOCKTIMEOUT15
LOCKTIMEOUT的缺省值是-1，这意味着将没有锁超时（对OLTP应用程序，这种情况可能会是灾难性的）。
尽管如此，我还是经常发现许多DB2用户用LOCKTIMEOUT=-1。
将LOCKTIMEOUT设置为很短的时间值，例如10或15秒。
在锁上等待过长时间会在锁上产生雪崩效应。
首先，用以下命令检查LOCKTIMEOUT的值：
db2"getdbcfgforDBNAME"
并查找包含以下文本的行：
Locktimeout（sec）（LOCKTIMEOUT）=-1
如果值是-1，考虑使用以下命令将它更改为15秒（一定要首先询问应用程序开发者或您的供应商以确保应用程序能够处理锁超时）：
db2"updatedbcfgforDBNAMEusingLOCKTIMEOUT15"
您同时应该监视锁等待的数量、锁等待时间和正在使用锁列表内存（locklistmemory）的量。
请发出以下命令：
db2"getsnapshotfordatabaseonDBNAME"
查找以下行：
Locksheldcurrently=0
Lockwaits=0
Timedatabasewaitedonlocks（ms）=0
Locklistmemoryinuse（Bytes）=576
Deadlocksdetected=0
Lockescalations=0
Exclusivelockescalations=0
Agentscurrentlywaitingonlocks=0
LockTimeouts=0
如果Locklistmemoryinuse（Bytes）超过所定义LOCKLIST大小的50%，那么在LOCKLIST数据库配置中增加4k页的数量。
查看锁信息及释放死锁可通过下列一系列命令
db2updatemonitorswitchesusinglockon
db2getsnapshotforlocksonDBNAME>locks.txt
把锁信息输出到locks.txt文件中
在locks.txt文件中查找某张表的相关锁
找到持有这个锁的应用程序句柄，如：
888
db2forceapplication（888）
（6）调排序堆
发现数据库该参数一直使用默认值，根据现场情况，调整后，降低在CPU、I/O和所用时间方面的成本。
db2updatedbcfgforEXFLOWusingSORTHEAP256（调大一些）
请发出以下命令：
db2"getsnapshotfordatabaseonDBNAME"
并查找以下行：
Totalsortheapallocated=0
Totalsorts=1
Totalsorttime（ms）=8
Sortoverflows=0
Activesorts=0
Commitstatementsattempted=3
Rollbackstatementsattempted=0
Lettransactions=Commitstatementsattempted+Rollback
statementsattempted
LetSortsPerTX=Totalsorts/transactions
LetPercent
升级会员 