数据库监控与优化
1数据库性能分析
1.1、CPU瓶颈
在AIX系统中，使用vmstat用来监控系统CPU使用情况，例如：vmstat 1 3
参数1代表采样的时间间隔，单位为秒；参数3代表采样次数。
在上面的显示中，说明该系统有16颗逻辑CPU，内存为32G，而授权CPU为4颗，也就是说，LPAR管理程序会保证该LPAR有最少4颗CPU的运算能力。
r：运行队列，是平均等待执行的内核线程数量，如果该列高(一般数值在10以下)，则可以认为有很多的内核线程在请求执行，但无法得到CPU。
b：等待队列
最后6列分别代表的意义如下：
Us:用户空间占用CPU百分比
Sy:内核空间占用CPU百分比
Id:空闲CPU百分比
Wa:等待输入/输出的CPU时间百分比
Pc:当前LPAR所用到的实际CPU运算量
Ec:值等于pc/ent，当前LPAR的运算量所占百分比。
结论：对于AIX系统，如果pc列高于ent，说名cpu资源紧张；如果pc列低于ent,us+sy保持在90%左右也说明cpu资源紧张。
如果CPU使用率较高，那就使用ps命令获得进程的CPU使用情况，
ps -elf|sort +5 -rn|more
第6列为C列，用来表示当前进程所消耗的CPU，如果C列很高，说明该进程消耗大量的CPU资源
如果发现db2进程消耗过多的CPU，则可以通过如下的方法进一步定位。
案例：高CPU占用率
第一步：判断CPU使用率高的进程是不是DB2进程
第二步：找到那些占用高CPU时间的DB2代理线程
第三步：查看代理线程对应的应用程序快照及该应用程序对应的SQL快照，对SQL进行调整或优化
db2pd -edus interval=10 top=5 stacks
(如果明确知道是哪个应用程序消耗了大量的CPU，也可以直接db2pd -stack eduid拿到堆栈信息,EDUID对应应用程序快照中的coordinator agent process or thread ID = 11139)
在10秒内，消耗CPU前5的EDU，并输出这些EDU的stack
只关注EDU Name为db2agent(dbname)的EDU ID，EDU ID为11139，根据该EDU ID在db2pd –agents的结果中去筛选，找到AppHandl，命令为db2pd -agents|grep -i 11139，如下：
找到AppHandl后，就可以通过获取该应用程序的快照，查看该应用程序的执行细节，命令为：db2 get snapshot for application agentid 1309 > 1309_appSnap.txt
分析1309_appSnap.txt（附件共享链接详见末尾），能看到正在执行语句：select * from db2obits.wf_task。
1.2、IO瓶颈
在vmstat输出结果中，wa列表示有多少CPU时间用于IO等待。
也可以使用iostat -D 1 2来收集磁盘IO信息，参数1代表采样的时间间隔，单位为秒；参数2代表采样次数，-D收集磁盘扩展属性。其中tm_act指的是该驱动器占用率的百分比，对于读写操作来说，avgserv列代表着平均单个读写的时间。对于通常的存储设备来说，如果单个IO平均时间超过10ms就意味着IO性能相对较低。
如果vmstat中wait列超过20%或iostat中某个磁盘的tm_act维持在80%左右，则说明IO异常，可能引起了性能问题或即将发生性能问题。
结论：vmstat中wait列超过20%或iostat中某个磁盘的tm_act维持在80%左右，则说明IO异常，可能引起了性能问题或即将发生性能问题。但即使两者没有显示高wait，也不能够排除IO瓶颈的可能性。
案例一：IO瓶颈
现象：vmstat中wait列超过20%或iostat中某个磁盘的tm_act维持在80%左右。
解决方法：确定该磁盘存放的内容：是存放表空间还是存放日志目录。
1)、如果是表空间：就找到引起IO瓶颈的表(一般是热大表)，即表扫描次数很多的大表
方法如下：
使用db2pd
命令为：db2pd -d dbname –tcbstats
从输出结果中”TCB Table Stats”部分的”Scans”列，可以看到数据库dbname中发生的表扫描次数，输出结果示意如下：
Address TableName SchemaNm Scans
0x07700000EF8A2558 AP_CLONE_CUSTOM_TR DB2OBITS 140798
从输出可以看到，表AP_CLONE_CUSTOM_TR上出现了大量的表扫描。
使用表函数
例如要查找表空间TRX_DATA_DMS_16K中表扫描次数最多的表
select tabname,table_scans from table(mon_get_table(null,null,null)) where tabname in (SELECT TABNAME FROM SYSCAT.TABLES WHERE TBSPACE='TRX_DATA_DMS_16K') order by table_scans desc
找到热表之后，继续通过如下查询查到引用过表的SQL语句：
select stmt_text from table(mon_get_pkg_cache_stmt(null,null,null,-2)) as T where stmt_text like '%AP_CL%'
然后通过查看这些SQL语句的查询访问计划，确定具体哪个语句引起了表扫描，最后根据实际情况看是否需要通过创建或调整索引、更新统计信息或者SQL语句编写方式等措施消除表扫描。
案例二：IO wait不高，但问题仍可以归类为IO存在瓶颈
数据库服务器配置：4颗CPU，POWER 7 3808 MHz，64位，物理内存16G，操作系统是AIX7.1、DB2版本是V10.1 Fixpack 3，不运行任何其他应用程序，只对一个表做UPDATE操作，该表包含1.5亿条数据，数据量大概10G，使用的缓冲池为2G，期望UPDATE的性能为5000笔/秒。但实际性能为不到400笔每秒，最好情况下也不过1000笔每秒。UPDATE语句类似：
UPDATE TABNAME SET COL1=?,COL2=?,COL3=? WHERE COL4=? AND COL5=? AND COL6=?
解决方法如下：
1)、通过vmstat监控，发现IO wait在10%左右，CPU使用率20%左右，pi、po等于0，能得出不存在CPU瓶颈、不存在内存瓶颈、IO问题也不大。无法得出明显的瓶颈
串行操作每秒钟5000行的数据更新，也就是说每条数据的更新不能高于0.2毫秒((1*1000ms)/5000行=0.2毫秒/行)，平均每一个物理IO大概需要1到10毫秒完成，取中间值5毫秒，也就是说假设内存访问不占用任何时间(纳秒级)，需要至少确保25次数据访问中最多一次进行物理IO
2)、获取SQL快照
Number of executions = 206419
Number of compilations = 1
Worst preparation time (ms) = 1801
Best preparation time (ms) = 1801
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 206415
Internal rows updated = 0
Rows written = 206415
Statement sorts = 0
Statement sort overflows = 0
Total sort time = 0
Buffer pool data logical reads = 413237
Buffer pool data physical reads = 84787
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 2065790
Buffer pool index physical reads = 190179
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Buffer pool xda logical reads = 0
Buffer pool xda physical reads = 0
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Total execution time (sec.microsec)= 1859.131385
Total user cpu time (sec.microsec) = 23.533948
Total system cpu time (sec.microsec)= 12.80671
Total statistic fabrication time (milliseconds) = 0
Total synchronous runstats time (milliseconds) = 0
Statement text = UPDATE TABNAME SET COL1=?,COL2=?,COL3=? WHERE COL4=? AND COL5=? AND COL6=?
物理IO的时候是不占用线程CPU的，大约每次执行使用的CPU时间为：
(23.533948+12.80671)/ 206419*1000=0.176毫秒，最快吞吐量为1*1000ms/0.176=5670。
为了确保每秒5000行的更新，物理IO几率需要降至1/((0.2ms-0.176ms)/5ms)=208，即至少208条update才能对应一个物理IO，也就是要保证100*(1-1/208)=99.5以上的缓冲区命中率才行。当前命中率为100*(1-((84787+190179)/( 413237+2065790)))=88.9%。
接下来就是如何提升IO性能了。
提升IO效率
提升命中率
就算换更高端的存储，产生的效果也不会太高，比如把5ms的IO降低到1ms最多能产生5倍的提升(400笔/秒的更新à2000笔/秒的更新)；如果把90%的命中率提高到99%，那么性能的飞跃将是10倍(10%的物理IOà1%的物理IO，400笔/秒的更新à4000笔/秒的更新)。
解决方案就是增大缓冲区，把尽可能多的数据都放入内存中。
结果：将缓冲池大小增大(比如7GB)，可以达到4000笔/秒，最高峰值超过5000笔/秒。
案例三：根据表名找到使用的物理卷
有时候执行一条SQL，通过TOPAS会发现某个PV的使用率一直为99%，那么该怎么确定这种现象就跟执行该SQL有关呢。这就需要根据表名找到表使用的物理卷(PV)，如果这个PV正是繁忙程度一直为99%的PV，那可以肯定是由于该SQL引起的磁盘瓶颈。
下面详细描述根据表名找到表使用的物理卷的步骤：
(1)、假设表名为T1，根据表名T1，通过如下SQL查找该表使用的表空间
db2 "select tbspaceid,tbspace, index_tbspace,long_tbspace from syscat.tables where tabname='T1'"
得到
TBSPACEID TBSPACE INDEX_SPACE LONG_TBSPACE
---------- -------------- ------ -----------------------------
3 IBMDB2SAMPLEREL
可知使用的数据库表空间为IBMDB2SAMPLEREL，数据表空间ID为3。如果使用了索引表空间和大字段表空间，则还需通过如下SQL找出索引表空间和大字段表空间对应的表空间ID。
select * from syscat.tablespaces
(2)、根据TBSPACEID=3，通过如下命令查找该表空间使用的容器
db2 list tablespace containers for 3
得到
Tablespace Containers for Tablespace 3
Container ID = 0
Name = home/db2ibits/db2ibits/NODE0000/SAMPLE/T0000003/C0000000.LRG
Type = File
可知文件系统为/home/db2ibits
(3)、根据文件系统/home/db2ibits，通过如下命令查找对应的逻辑卷
df –g
得到
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/bitsdevlv02 25.00 16.91 33% 1201 1% /home/db2ibits
可知逻辑卷为bitsdevlv02
(4)、根据逻辑卷bitsdevlv02，通过如下命令查找对应的PV
lslv -l bitsdevlv02
得到
PV COPIES IN BAND DISTRIBUTION
hdisk3 200:000:000 8% 160:016:000:024:000
如果第(2)步得到的是一个裸设备：
db2 list tablespace containers for 3
得到
Tablespace Containers for Tablespace 3
Container ID = 0
Name = dev/rhqbbprelv06
Type = Disk
可知容器为裸设备，该裸设备对应的逻辑卷为hqbbprelv06，然后通过如下命令查找对应的PV
lslv -l hqbbprelv06
得到
PV COPIES IN BAND DISTRIBUTION
hdisk6 400:000:000 39% 000:156:159:085:000
由此，就可以把表跟PV对应上了。
注意：数据中心一般会把PV规划成对应一个RAID。
1.3、内存瓶颈
虚拟内存=物理内存+交换空间，如果操作系统的物理内存耗尽，那么系统就会把一些内存放入交换空间，这种操作会大大降低系统的性能。vmstat的第6列和第7列(pi和po(page out，从物理内存交换到交换空间))就是关于交换空间的IO指标，一般情况下，这两列应该永远为0，表示没有任何内存交换，如果发现持续的交换发生，那么需要检查当前剩余的内存。
avm指当前系统中已经激活的虚拟内存页的数量，该数值不包含文件系统缓存。
fre指系统中空闲页的数量，操作系统在内核中维护一个空闲页列表。每当应用程序结束时，所有的工作页面(包括应用堆栈、数据和其他的共享内存段)都会被立刻返回给空闲列表，而文件系统缓存则驻留内存，并不会返回给空闲列表，如果一个内存页被一个应用程序请求，那么最先分配的是其他已经结束的程序所占用的文件系统内存。
因此，fre并不能完全代表系统中可用的空闲内存，可以通过svmon –G查看系统可用内存到底有多少(linux中为free命令)，例如：
在如上结果中，clnt与in use交叉的那一项，也就是110045，代表的就是有多少内存被文件系统使用。一般来说，这个值加上free列所对应的28020，就可以初步认为是该系统中可以被应用程序使用的内存，每个页面的单位为4096字节。
结论：如果po(page out，从物理内存交换到交换空间)不为0，说明物理内存资源紧张，可进一步通过AIX的svmon –G查看可用内存到底有多少。如果物理内存资源紧张，需要确认是数据库参数设置不合理还是物理内存的限制，如果是数据库参数设置不合理，需要尽快调整内存使用分布；如果是物理内存的限制，需要通过扩展内存的方式解决。
AIX上的内存参数设置包括操作系统参数设置和数据库参数设置，操作系统参数设置包括4个虚拟内存控制参数，建议设置如下(也为默认值)：
vmo -p -o maxperm%=90 -o maxclient%=90 -o minperm%=3 -o lru_file_repage=0
数据库参数设置可以参考：DB系列之数据库参数文章。
1.4、懒惰系统
没有CPU瓶颈、IO瓶颈和内存瓶颈，但交易仍然非常慢的系统称为懒惰系统。懒惰系统的根源就是等待，等待包括：IO等待(IO瓶颈)、锁等待、latch等待。
latch是db2的内部锁，用来保证数据结构的并发性，并防止并发地对数据结构进行修改，当大量数据操作需要对数据库的控制信息进行修改及读取时，会发生latch争用现象，部分线程处于等待状态。
Latch的监控方法：
1)、db2pd –latch
2)、表函数select * from table(mon_get_extended_latch_wait(null))
3)、db2pd –stack all 或db2pd –stack eduid
案例一：latch等待的例子，insert语句执行性能很差
有一个表，定义如下：
create table tabname(
c1 bigint not null generated by default as identity(
start with +1
increment by +1
minvalue +1
maxvalue +9223372036854775807
cycle
no cache
order
),
c2 varchar(10),
c3 varchar(10),
c4 varchar(10)
)
insert语句执行很慢，SQL快照信息如下：
Number of executions = 4597
Number of compilations = 1
Worst preparation time (ms) = 10
Best preparation time (ms) = 4
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 7154
Internal rows updated = 0
Rows written = 4470
Statement sorts = 0
Statement sort overflows = 0
Total sort time = 0
Buffer pool data logical reads = 12425
Buffer pool data physical reads = 15
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 72854
Buffer pool index physical reads = 191
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Buffer pool xda logical reads = 0
Buffer pool xda physical reads = 0
Buffer pool temporary xda logical reads = 0
Buffer pool temporary xda physical reads = 0
Total execution time (sec.microsec)= 15482.427607
Total user cpu time (sec.microsec) = 0.739025
Total system cpu time (sec.microsec)= 0.083831
Total statistic fabrication time (milliseconds) = 0
Total synchronous runstats time (milliseconds) = 0
Statement text = insert into tabname (col1,col2,col3,col4) values(?,?,?,?)
平均每条语句执行使用3.36秒：
15482.427607/4597=3.36秒
平均每条语句逻辑读数量为18.5个逻辑页：
(12425+72854)/ 4597=18.5
CPU使用时间几乎可以忽略不计，物理读数量也很少。
从哪个方面看，也没有道理使得每条插入使用3秒以上。
选取一个正在该执行插入语句的应用程序，通过db2pd –stack eduid获取该应用程序对应EDU的堆栈信息，能够发现如下函数调用顺序：sqldSeqGenerateàsqldSeqGetàsqloXlatchConflict(或者直接看<LatchInformation/>标签中描述的等待latch信息)
sqldSeqGenerate是在生成一个sequence数值，然后sqldSeqGet是真正去得到一个数值，然后是一个latch冲突，从这里能够看出其他任务得到了这个Latch，该线程需要等待。Latch在这里是为了确保没有两个任务能够得到同一个sequence数值。
所以，问题发生在sequence不能足够快地为所有的任务分配下一个数值，把identity列的no cache属性调整为cache 50，性能问题得到解决。
2数据库性能数据收集
收集CPU、IO、内存、数据库的性能数据，进行分析，分析脚本dbcheck.sh详见附件（共享链接详见文章末尾）
比如：
--平均执行时间最长的10条语句
select total_exec_time/(num_executions+1) as avg_exec_time from sysibmadm.snapdyn_sql order by avg_exec_time desc fetch first 10 rows only
--平均排序次数最多的10条语句
select stmt_sorts/(num_executions+1) as avg_sorts,total_exec_time/(num_executions+1) as avg_exec_time,substr(stmt_text,1,1000) as sql_stmt from sysibmadm.snapdyn_sql order by avg_sorts desc fetch first 10 rows only
--平均排序时间最多的10条语句
select total_sort_time/(num_executions+1) as avg_sort_time,total_exec_time/(num_executions+1) as avg_exec_time,substr(stmt_text,1,1000) as sql_stmt from sysibmadm.snapdyn_sql order by avg_sort_time desc fetch first 10 rows only
#当前锁等待详细信息
db2pd -d $dbname -locks wait show locks > lockwait.txt
#当前latch信息
db2pd -latch > latch.txt
3数据调优指标
3.1、缓冲池命中率(bufferpool hit ratio)
来源：db2pd -d dbname -bufferpools
指标：优秀>95%，良好>80%
描述：从内存中访问数据仅需纳秒级，而从磁盘访问数据则需数毫秒。DB2对数据的获取是通过缓冲池，如果数据已经缓存到缓冲池，就可通过缓冲池直接获取，如果数据不在缓冲池，则需要从磁盘读到缓冲池。因此，命中率越高，代表着读取同样的数据时需要的 I/O 越少，性能就越好。
分析：
TBSBP4K缓冲池大小为4.3G，数据和索引缓冲池命中率分别为：67.43%、93.94%，该缓冲池只被表空间TBS4K使用，所有应用表都位于表空间TBS4K上。
其余缓冲池IBMDEFAULTBP、TMPBP、OPMBP大小分别为28.2M(Automatic)、530.0M、1.5G，分别被表空间SYSCATSPACE、SYSTOOLSPACE，TEMPSPACE1，OPMTS使用到，这些缓冲池的命中率都大于99%，按照经验，OPMTS分配的内存可以降低，由当前的1.5G降低为0.5G。
通过db2 "call get_dbsize_info(?,?,?,-1)"得到数据库大小为223477669888Bytes，约为208G，即大约208G的数据应用4.3G的缓存，明显偏小。
建议：
1、 调整TBSBP4K缓冲池的大小，由当前的4.3G调整到16G。
db2 "alter bufferpool TBSBP4K immediate size 4194304"
2、 调整OPMBP缓冲池的大小，由当前的1.5G调整到0.5G。
db2 "alter bufferpool OPMBP immediate size 16000"
3、 表空间规划
创建一个缓冲池TBSBP4K_IDX，大小为4G、一个表空间TBS4K_IDX，大小为200G，把应用表的数据和索引分离，提高缓存的效率。
3.2、有效索引读(index read efficiency)
来源：数据库快照
公式：行读取/行选择（Rows read Rows selected）
指标：OLTP≤5
描述：Rows read是读的行数，Rows selected是返回的结果集，反映DB2为了获取一行数据， 需要读取多少行。如果远远大于指标值，说明DB2 不能根据有效的索引过滤结果集，意味着需要调优SQL。
分析：
Rows read= 17274837516
Rows selected= 148841239
指标值=17274837516/148841239=116
建议：
调优SQL
3.3、同步读取比例
来源：数据库快照
公式：(1-异步物理读/所有物理读)*100
指标：OLTP≥90%
描述：DB2的物理读主要包括异步物理读和同步物理读。如果采用高效的索引获取结果集时，DB2将使用同步物理读访问索引页和需要的数据页，当没有索引或物理设计不够有效时，DB2 将采用异步物理读扫描索引或数据页。异步物理读是DB2通过预取(prefetcher)线程执行的，当查询的结果集较大，DB2 认为顺序预取更有效率时就会触发预取请求，异步物理读的比例越高，则表示获取的数据量越大，性能就可能越差，而更高的同步物理读则表示索引的高效。 通过快照监控结果可知，并没有任何元素表示同步物理读的页数，但提供了异步物理读和所有物理读的值，用所有物理读减去异步物理读就可以计算出同步物理读的值，公式如下：
SRP=(1- (Asynchronous pool data page reads + Asynchronous pool index page reads)
/(Buffer pool data physical reads + Buffer pool index physical reads))*100
这个公式不仅适用于整个数据库异步读监控，也适用于表空间和缓冲池对象。
分析：
Asynchronous pool data page reads =643717941
Asynchronous pool index page reads =371388570
Buffer pool data physical reads =730030713
Buffer pool index physical reads =599375177
SRP=(1-(643717941+371388570)/(730030713+599375177))*100=23.65%
建议：
无
3.4、表空间每次异步读时间
来源：表空间快照
公式：表空间对应缓冲区异步读时间/异步读请求次数
指标：1～10 毫秒
描述：由于快照中不包括所有物理读的次数，所以单个物理读的时间无法计算；只包括异步物理读的次数和总的时间，所以选择异步读时间作为一个监控指标。可通过以下公式计算出完成一个异步物理读需要的平均时间：
Overall Average Read Time(ms) = (Total elapsed asynchronous read time/ (Asynchronous data read requests+Asynchronous index read requests+Asynchronous xda read requests))
对于现代存储系统来说，平均物理读/写时间一般在 1～10 毫秒左右（取决于存储系统的性
能与缓存） 。因此，如果我们监控的指标值超出此值，则需要结合操作系统 I/O 监控工具，调查I/O 系统的瓶颈。当发现该值过高时(譬如超过20毫秒)，一般来说系统会明显感觉到性能下降。这时，为了能够直观地证明存储的性能问题，可以通过 UNIX/Linux 中的 dd 工具向容器所在的文件系统(而不是容器文件)写入几千个数据页，计算平均写入时间(该数值为顺序写入)，然后可以使用同样的工具从容器文件中随机读取几千个数据页，计算平均读取时间。
如下公式可找出读时间最慢(每次读请求耗时)的表空间，然后评估这个表空间的设计是否为最佳。
db2 "select tbsp_name,(POOL_ASYNC_READ_TIME/(POOL_ASYNC_DATA_READ_REQS+POOL_ASYNC_INDEX_READ_REQS+POOL_ASYNC_XDA_READ_REQS+1)) as perReq_readTime_ms from sysibmadm.snaptbsp order by perReq_readTime_ms desc fetch first 10 rows only"
分析：
Total elapsed asynchronous read time = 1185200117
Asynchronous data read requests = 20916380
Asynchronous index read requests = 254630379
Asynchronous xda read requests = 0
perReq_readTime_ms (ms)=4.30ms
建议：
无
3.5、Direct I/O 时间
来源：数据库快照
公式：直接读取（写入）时间/直接读取（写入）次数
指标：1～10 毫秒
描述：Direct I/O 是指直接从磁盘访问而不经过缓冲区的 I/O，主要针对 LONG/LOB 数据的访问。当一列被定义为 LONG/LOB，那么该列的数据不存储在表的数据页内。而是用“指针”指向该数据真实所在的位置。很多时候，这些大对象的数据可以非常大（比如视频），因此，对于 LONG 与 LOB 数据类型，所有的读取都是直接 I/O，并不通过缓冲池。
DRIOMS - The average time (ms) required to complete a Direct Read
DWIOMS - The average time (ms) required to complete a Direct Write
DRIOMS = Direct reads elapsed time (ms)/Direct reads
DWIOMS = Direct write elapsed time (ms)/Direct writes
分析：
Direct reads elapsed time (ms) = 263031390
Direct write elapsed time (ms) = 6484591
Direct reads = 7810349234个页面
Direct writes = 384170060个页面
DRIOMS=263031390/7810349234=0.033ms
DWIOMS=6484591/384170060=0.016ms
建议：
无
3.6、直接I/O读取(写入)的时间比
来源：数据库快照
公式：直接读取(写入)时间/缓冲区读取(写入)时间
指标：非常低
描述：直接 I/O 需要物理读，而不能在缓冲池中保存数据。因此，在应用程序的设计中希望尽量少用 Direct I/O。通过对比 Direct I/O 与普通缓冲池读写时间的比例，可以知道到底有多少数据访问是读取大数据的。除了有限的特定应用外，大部分的应用程序都应该尽量避免频繁的大数据访问。一般来说，如果一列数据可以被定义为普通列的话，就尽量不要使用大对象。 当一定要使用大对象定义某列的时候，在应用的某些操作中不是真正需要访问该列时，尽量在查询时不要指定大对象数据列，避免不必要的 Direct I/O。
该指标结果不具有标准的意义，但可以参考，了解直接I/O的性能影响程度。
分析：
Total buffer pool read time (milliseconds) = 1744085916
Total buffer pool write time (milliseconds)= 18909521
Direct reads elapsed time (ms) = 263031390
Direct write elapsed time (ms) = 6484591
直接读时间比例= Direct reads elapsed time (ms)/Total buffer pool read time (milliseconds)= 263031390/1744085916=15.08%
直接写时间比例= Direct write elapsed time (ms)/ Total buffer pool write time (milliseconds)= 6484591/18909521=34.29%
建议：
查明直接I/O多的原因，以进行后续优化。
3.7、数据、索引页清除
来源：数据库快照
公式：异步写入/总写入(async writes/total writes)
指标：≥95%
描述：该指标代表着页面清除线程是否能够有效地将脏页在后台刷入磁盘。由于缓冲池的大小是有限的，一般来说数据库不可能把所有的数据都放入内存。这时，哪些数据需要驻留内存，哪些被更新的数据需要被写入磁盘，然后留出空间给其他数据，就是 DB2缓冲池管理模块需要决定的。当缓冲池中的被修改的数据页（脏页）与缓冲池总大小的比例超过一定阈值的时候(chngpgs_thresh)，DB2就会触发后台的页面清除线程，将被选择的页(victim pages)以异步方式物理写入磁盘。但是如果该清除机制触发得不够频繁，或者缓冲池太小使得系统无法有效地找到一个干净的页面，DB2 就会选择一个脏页，将它写入磁盘，然后读取另外一个页面进入内存，这种写入方式叫做同步写入。可以想象，相对异步写入，同步写入会对数据的读取造成很大的性能问题。因而，该指标的用途就是监测异步写入与总写入的比例。
分析：
Asynchronous pool data page writes = 15820385
Asynchronous pool index page writes = 18512169
Buffer pool data writes = 16141488
Buffer pool index writes = 18906035
指标值=(15820385+18512169)/(16141488+18906035)=34332554/35047523=97.96%
建议：
无
3.8、脏页偷取(dirty page steal)
来源：数据库快照
公式：脏页偷取触发次数(Dirty page steal cleaner triggers)
指标：非常低
描述：脏页偷取是一种对性能影响极大的操作。当系统中的脏页偷取过多的时候，意味着需要让页清除器工作得更加卖力。
如果发现系统中的页清除器一直很空闲，则可以通过调节softmax 与 chngpgs_thresh 来让它们忙起来。这两个参数都是控制何时触发页清除器的参数，其中 softmax 是按照缓冲池中 MinbuffLSN 与当前 LSN 之间的差距来计算何时需要触发，而chngpgs_thresh 则是计算缓冲池中脏页的数量与可用页面总数来进行计算。两者的作用同样都是触发页清除器，只不过从不同的角度计算而已。
但是如果通过db2pd -stack all 抓取的stack 发现所有的页清除器一直非常繁忙，但是无论如何刷新磁盘的速度也赶不上数据写入缓冲区的速度，这时就需要增加页清除器的数量了。
分析：
Dirty page steal cleaner triggers = 88831
LSN Gap cleaner triggers = 837134
Dirty page threshold cleaner triggers = 28238
数据库参数：
CHNGPGS_THRESH= 80
NUM_IOCLEANERS = AUTOMATIC(12)
SOFTMAX=520
建议：
NUM_IOCLEANERS = AUTOMATIC(12)，表示页清除器可以按需分配，数量充足。
Dirty page steal cleaner triggers= 88831，数值很高，表示页清除器工作的不充分，已经严重影响了数据库的缓冲池读性能，这种工作的不充分是受参数CHNGPGS_THRESH、SOFTMAX影响的，SOFTMAX超过阈值发生了837134，CHNGPGS_THRESH超过阈值发生了28238次，对比发现，有必要调整CHNGPGS_THRESH，由当前的80调整到50。语句为：
db2 update db cfg using CHNGPGS_THRESH 50
需要重启数据库
3.9、排序溢出比例
来源：数据库快照
公式：排序溢出次数/排序总数(Sort overflows/Total sorts)
指标：OLTP：0，或者在接近 0 的数值上长时间维持稳定
描述：排序溢出就是当排序内存不够时，数据需要使用临时空间进行排序。一般来说，希望数据尽可能在内存中完成。当发现大量的排序溢出时，就要看排序堆内存参数设置是否足够大。
分析：
Sort overflows = 10448
Total sorts = 145116
指标值=10448/145116=7.19%
数据库参数：
SORTHEAP = 16834
建议：
调整SORTHEAP参数值为51200，语句为：
db2 update db cfg using SORTHEAP 51200
不需要重启数据库
3.10、平均排序时间
来源：数据库快照
公式：排序总时间/排序总数(Total sort time (ms)/Total sorts)
指标：远小于系统预期平均语句的执行时间或50ms以内
描述：一般来说，一条语句的执行时间包括锁等待、数据读取时间（I/O）加上排序时间（逻辑读耗时和排序耗时都属于user CPU 时间）。因此用数据库中平均的排序时间对比平均语句返回的速度，就可以大概估算出执行一条语句时有多少的时间用于排序。一般都希望排序时间越少越好。如果该值过高，用户可以考虑优化查询与添加索引，尽量减少排序的消耗。
分析：
Total sorts = 145116
Total sort time (ms) = 1457123
指标值=1457123/145116=10.04ms
建议：
无
3.11、平均每条交易的排序次数
来源：数据库快照
公式：排序总数/交易总数(Total sorts/(Commit statements attempted+ Rollback statements attempted))
指标：OLTP<5
描述：对于 OLTP 应用来说，由于每条交易的短小精干的特性，我们需要尽可能减少每条交易所需的排序数量。
大部分情况下，这种调优需要应用开发人员的配合，一方面在数据库中建立合适索引的同
时，另一方面优化应用程序逻辑，减少 SQL 所需要排序的次数。在典型的 OLTP 系统中，尽量将每一条交易平均所需的排序数量维持在 5 以下。
分析：
Total sorts = 145116
Commit statements attempted = 283029692
Rollback statements attempted = 30766
指标值=145116/(283029692+30766)=0.000512
建议：
无
3.12、日志写入速度
来源：数据库快照
公式：日志写时间/日志写次数(Log write time (sec.ns)/Number write log IOs)
指标：<3 毫秒
描述：日志写入的速度有时会对经常进行提交的应用程序性能产生决定性的影响。
每一次的提交都会伴随着物理日志的写入。在一个频繁提交的系统中，如果物理日志的写入速度过低，会对性能产生非常大的影响。
如果发现该值超过 5 毫秒，则说明该部分有待提高。
分析：
Log write time (sec.ns) = 218453.590223000
Number write log IOs = 141769880
指标值=218453.590223000/141769880=1.54ms
建议：
无
3.13、包缓存命中率 (package cache hit ratio)
来源：数据库快照
公式：1-包缓存插入数量/包缓存读取数量(1–Package cache inserts Package cache lookups)
指标：1，或者能够长时间保持接近 1 的稳定数值
描述：该指标表示有多少查询语句可以直接在包缓存中找到。当一条查询被请求的时候，数据库在将其编译之前，首先要从包缓存中查找有没有已经被编译好的包可以直接运行。如果该包已经存在，那么该 SQL 可以直接被运行而不用再次编译。如果应用程序在运行一段时间后，绝大部分的语句都已经被缓存在包缓存中，那么可以节省很多 SQL 编译的时间与 CPU 消耗。
分析：
Package cache lookups=283055153
Package cache inserts= 823216
包缓存命中率=1-823216/283055153=99.7%
数据库参数PCKCACHESZ= AUTOMATIC(8222)
内存高水位= 330.9M
建议：
无
3.14、编目缓冲区插入比例
来源：数据库快照
公式： 编目缓冲区插入次数/编目缓冲区查询次数(Catalog cache inserts/Catalog cache lookups)
指标：0，或者在接近 0 的数值上长时间维持稳定
描述：在系统的每一个分区中，CATALOGCACHE_SZ中都会分配出一块空间用于缓冲编目表的信息。在数据库的日常操作中，查询编目表是一个非常频繁的操作。譬如当用户从一个表读取数据的时候，系统就要查询编目表，理解该表在什么表空间、应该如何访问等信息。因此为了性能着想，DB2 在数据库栈内存中单独开辟了一块空间，用于存储编目信息。
但是如果用户有很多数据库对象，而该编目缓存的大小过小，则该内存无法容纳下所有的
信息。那么当新的信息来临时，就会把一些不常用的信息替换出去。
如果该替换经常发生，那么每次当系统想要查找编目数据时，就要从编目表空间中查找，这样会导致系统性能一定程度上的下降。
因此，一般建议将 catalogcache_sz 设置逐渐增大，直到系统中不再频繁出现编目缓冲区插
入的操作。
分析：
Catalog cache lookups = 15709916
Catalog cache inserts = 3910
Catalog cache high water mark = 1279630
数据库参数：
CATALOGCACHE_SZ= 300
db2mtrk –I –d –w结果：
catcacheh高水位为：1.6M
建议：
调整CATALOGCACHE_SZ参数值为500，语句为：
db2 update db cfg using CATALOGCACHE_SZ 500
不需要重启数据库
3.15、平均结果集大小
来源：数据库快照
公式：行选择/执行Select SQL 的次数(Rows selected Select SQL statements executed)
指标：OLTP≤10
描述：Rows selected表示返回的结果集，Select SQL statements executed 表示查询语句的执行次数。平均结果集(Avg_Result_Set)用来表示平均每条 Select SQL 语句返回的结果行数。对于 OLTP 系统来说， 结果集一般很小，通常小于 10；而对于 OLAP 系统，结果集一般很大。假设某一个数据库是 OLTP 交易型系统，但发现平均结果集很大，这就说明应用程序有改善余地，比如可在 SQL 语句上做查询条件过滤并降低返回的结果集大小，而不是将所有数据取出，然后在应用逻辑上过滤。
分析：
Rows selected= 148841239
Select SQL statements executed= 5787334
Avg_Result_Set=148841239/5787334=25.72
建议：
无
3.16、每个事务包含的查询 SQL 语句数量
来源：数据库快照
公式： 查询语句数量/交易总数(Select SQL statements executed/(Commit statements attempted+ Rollback statements attempted))
指标：OLTP≤10
描述：对于 OLTP 系统来说，每个事务执行的查询次数一般小于 10。如果事务太长，可能会造成一些锁等，影响并发性能。
分析：
Select SQL statements executed = 5787334
Commit statements attempted = 283029692
Rollback statements attempted = 30766
指标值=5787334/(283029692+30766)=0.02
建议：
无
3.17、每个事务包含的增删改语句数量
来源：数据库快照
公 式 ： 插 入 、 更 新 与 删 除 语 句 的 数 量 交 易 总 数 (Update/Insert/Delete statements executed/( Commit statements attempted+ Rollback statements attempted))
指标：OLTP <= 5
描述：在 OLTP 系统中建议不要在同一条交易中使用过多的数据更改语句（插入、更新、删除），单条交易中过多的这类语句会造成大量的锁，容易引起锁等待甚至死锁，同时过长的交易可能会引起活动日志过长，导致日志空间占满。
分析：
Update/Insert/Delete statements executed = 280098006
Commit statements attempted = 283029692
Rollback statements attempted = 30766
指标值=280098006/(283029692+30766)=0.98
建议：
无
3.18、每个事务需要的缓冲区逻辑读
来源：数据库快照
公式：逻辑读的总数/事务总数((Buffer pool data logical reads + Buffer pool index logical reads)/( Commit statements attempted+ Rollback statements attempted))
指标：取决于业务量，在 OLTP 中尽量降低
描述：DB2 对数据页和索引页的读取是通过缓冲区执行的。如果请求的数据不在 bufferpool 中，则首先将磁盘数据读到缓冲区。如果数据已经在缓冲区，则直接从缓冲区中获取，这种数据读叫做逻辑读。大量的逻辑读一般代表的是大量的数据扫描，通常是由于缺乏良好的物理设计造成的，逻辑读和 CPU 资源消耗有很大关系，一个事务中执行的逻辑读越多，消耗的 CPU 资源就越多。
分析：
Buffer pool data logical reads = 2296253964
Buffer pool index logical reads = 9945944941
Commit statements attempted = 283029692
Rollback statements attempted = 30766
指标值=(2296253964+9945944941)/(283029692+30766)=43.29
建议：
无
3.19、检测索引页扫描
来源：数据库快照
公式：逻辑索引读的总数/事务总数（Buffer pool index logical reads/ (Commit statements
attempted + Rollback statements attempted)）
指标：取决于业务量，在 OLTP 中尽量降低
说明：索引是 B+结构，包含根节点，中间节点和叶子节点，数据存在叶子节点，根节点和中间节点提供了遍历的路径。对索引的访问一般有两种遍历方式：一种是从 root 页开始读，然后访问中间节点，最后访问指向数据页 RID 的叶子节点。另外一种是不通过根节点和中间节点，而直接遍历叶子节点获取需要的数据。
第一种方式是理想情况，一般每条 SQL 语句平均需要访问 3-4 个索引页。而第二种方式可
能需要更多的索引页访问，需要消耗大量的 I/O 和 CPU 时间。
除了查询语句， 更新和删除语句也应该充分利用索引。 假如一个事务包含 10 个查询, 2 个修改操作，那么理想的索引页扫描数应该是：
(10+2) *4*1.5=72 (4 表示 4 个索引页，1.5 考虑了额外的开销)
如果实际环境中该指标值过大，很可能是 DB2 在扫描索引页。
分析：
Buffer pool index logical reads = 9945944941
Commit statements attempted = 283029692
Rollback statements attempted = 30766
指标值=9945944941/(283029692+30766)=35.13
建议：
无
4表压性能测试
参考附件《表压缩性能测试.pdf》（共享链接详见文章末尾）
5疑惑解答与案例演示
5.1、主机的选择
从响应时间(应用向数据库服务器发起请求，服务器完成处理并返回结果给应用，中间总共消耗的时间，它反映了数据库服务器的处理速度)角度出发。
关键业务系统优先选择Power服务器。
从性价比考虑可以选择X86服务器。
由于预算的原因，可以先选择X86服务器作为过渡，随着数据量和用户量的变大，到了一定阶段再迁移到Power服务器上。
选择主机的第一步是决定CPU的个数，主机的CPU个数，可以采用倒推法。对于内存，推荐1个Power处理器配备8G内存，一个X86处理器配备4GB内存；对于存储，1个Power处理器可以处理300~500GB活动数据，1个X86处理器可以处理200~300GB活动数据。
按照经验：OLTP系统中，对于大小为200G(通过db2"callGET_DBSIZE_INFO(?,?,?,0)"得到的数值，不是数据库容量值)的数据库，为了达到90%的缓冲池命中率(最低要求)，缓冲池大小建议为20G，物理内存大小建议为32G，因此CPU个数建议为4个，如果SQL特别复杂、表关联很多、计算很多，那么CPU个数需要相应的增加。
5.2、小型机与普通服务器的比较
现在生产小型机的厂商主要有IBM、HP、SUN(ORACLE)、浪潮、曙光等．它们的主要特色在于年宕机时间只有几小时，所以又统称为z系列（zero 零）。
购买小型机时主要是考虑它的运算能力,I/O吞吐能力和TPMC值这几个重要指标,TPMC值越高,机器的事务处理能力就越强。
通常而言，小型机1个处理器可处理8GB内存数据，300到500GB活动数据；X86服务器一个处理器可处理4GB内存数据，200到300GB活动数据。
按CPU的类型来区分,小型机是基于RISC(精简指令集)架构的专用服务器,而X86服务器是基于CISC(复杂指令集)架构的PC服务器。
小型机相对于普通服务器来说，优势如下：
1、性能稳定,它具有处理复杂多任务的超强能力,宕机时间远远低于其他类型的服务器,号称零宕机；
2、可用性非常高,它的I/O吞吐能力极强,能有效处理并时突发的巨量数据,擅长于大型数据库的访问与处理；
3、扩展性强,给用户提供了尽可能多的扩充空间和升级的余地；
4、安全性高,一般使用专用的操作系统,有很强的独立性,从底层防止入侵的设计策略,确保了服务器的安全平稳运行；
5、负载能力强,小型机采用的动态分区管理,根据不同应用负载量的大小灵活地分配系统资源。
6、 单机处理能力强。生产环境出现性能问题，应用服务器可通过增加链路解决性能瓶颈，数据库服务器更多的情况下只有一台，更容易出现瓶颈，如果想解决当前状况的数据库瓶颈最好的方式是通过垂直扩容，即通过增加单个服务器的硬件资源解决数据库性能问题，垂直扩容方面小型机比X86有更强的优势。如果想对数据库服务器进行水平扩容，对于OLTP系统，解决方案为pureScale，对于OLAP系统，解决方案为DPF(分区数据库)。
劣势如下：
1、 小型机价格少则几十万，多则上百万，普通的服务器价格在十万以下。
2、 专有操作系统，比如AIX，UNIX的一种，没有开源操作系统LINUX使用面广。但安全性、可靠性、使用便捷性上要强于LINUX。
5.3、BITS数据库发生的索引页损坏
SWIFT_INPUT_MSG表数据位于DATA_DMS_32K，索引数据位于COMP_INDEX_DMS_8K。
在新一轮移植数据移入后，表空间COMP_INDEX_DMS_8K使用率超过70%，需要扩容，数据中心在接下来的一个星期四针对该表空间进行了扩容，在星期一执行批处理送数据给ODSS的时候报错，发现是执行如下语句的时候报错：
select * from SWIFT_INPUT_MSG where (timestamp_c >= '2015-07-09 08:41:40.158' and timestamp_c < '2015-07-11 08:10:16.351' and timestamp_u is null) or (timestamp_u >= '2015-07-09 08:41:40.158' and timestamp_u < '2015-07-11 08:10:16.351') with ur
在db2diag.log中发现有错误信息：提示数据页损坏，具体对象信息如下：
Obj:{pool:10;obj:111;type:1} Parent={8,26}
表示表空间10，对象111(syscat.indexes的列INDEX_OBJECTID)，类型1(即索引类型)，父对象为表空间8，对象26(即表的对象)
通过查看syscat.tables及syscat.indexes，发现在执行如上SQL时，需要扫描索引SWIFT_INPUT_MSG (TIMESTAMP_U)，在扫描到的过程中，发现索引页有损坏的情况。
对于该问题，可以使用db2dart MI参数，通过指定索引对象ID，将索引设置为不可用，然后在数据库启动时自动重新创建该索引。
db2dart bitscn MI /TSI 10 /OI 111
或者删除该索引，然后重新创建。
如果问题发生在数据上(type为0)，那么只能通过恢复加前滚日志的方式修复该表。
怎么排除问题是否发生在数据上，先找到DATA_DMS_32K表空间上的所有表，然后通过如下语句：
select * from db2obits.tabname with ur > /dev/null
进行检测，如果这些表在执行该语句时不报错，就表示该表的数据页不存在问题。
裸设备被多个表空间使用的情况模拟：
用root登陆
cd /dev
mklv -y newlv -t 'raw' bits_vg 5 hdisk5
mklv -y newlv1 -t 'raw' bits_vg 2 hdisk5
mklv -y newlv2 -t 'raw' bits_vg 2 hdisk5
chown db2ibits:db2ibits newlv
chown db2ibits:db2ibits rnewlv
chown db2ibits:db2ibits newlv1
chown db2ibits:db2ibits rnewlv1
chown db2ibits:db2ibits newlv2
chown db2ibits:db2ibits rnewlv2
用db2ibits登陆
db2 connect to bitscn
db2 "create tablespace DATA_DMS_32K_TEST managed by database using (DEVICE '/dev/rnewlv1' 500)"
db2 "create tablespace COMP_INDEX_8K_T managed by database using (DEVICE '/dev/rnewlv2' 500)"
db2 "alter tablespace DATA_DMS_32K_TEST add (DEVICE '/dev/rnewlv' 500)"
db2 terminate
db2untag /dev/rnewlv
输入YES后成功解除DB2标志
db2 connect to bitscn
db2 "alter tablespace COMP_INDEX_8K_T add(DEVICE '/dev/rnewlv' 500)"
db2pd -d bitscn –tablespaces
文章word版及附件共享链接：
https://pan.baidu.com/s/1o83idYy
供稿 | 黄海 编辑 | lin
