DB2系列之数据库性能检查（二）

1指标分析

1、缓冲池命中率(bufferpool hit ratio)

来源：db2pd -d dbname -bufferpools

指标：优秀>95%，良好>80%

描述：从内存中访问数据仅需纳秒级，而从磁盘访问数据则需数毫秒。DB2对数据的获取是通过缓冲池，如果数据已经缓存到缓冲池，就可通过缓冲池直接获取，如果数据不在缓冲池，则需要从磁盘读到缓冲池。因此，命中率越高，代表着读取同样的数据时需要的 I/O 越少，性能就越好。

分析：

TBSBP4K的数据和索引缓冲池命中率分别为：67.43%、93.94%，该缓冲池大小为4.3G，只被表空间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，把应用表的数据和索引分离，提高缓存的效率。这步可以暂缓实施，由项目组决定是否执行。

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、包缓存命中率 (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=0.997

数据库参数PCKCACHESZ= AUTOMATIC(8222)

内存高水位= 330.9M

建议：

无

4、平均结果集大小

来源：数据库快照

公式：行选择/执行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

建议：

无

5、同步读取比例

来源：数据库快照

公式：(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%

建议：

无

6、数据、索引页清除

来源：数据库快照

公式：异步写入/总写入(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%

建议：

无

7、脏页偷取(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

需要重启数据库

8、缓冲区异步读时间

来源：数据库快照

公式：缓冲区异步读时间/异步读请求次数

指标：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 TBSP_RIO_TIME_MS from sysibmadm.snaptbsp order by TBSP_RIO_TIME_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

Overall Average Read Time(ms)=4.30ms

建议：

无

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

建议：

无

10、直接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多的原因，以进行后续优化。

11、编目缓冲区插入比例

来源：数据库快照

公式： 编目缓冲区插入次数/编目缓冲区查询次数(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

不需要重启数据库

12、排序溢出比例

来源：数据库快照

公式：排序溢出次数/排序总数(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

不需要重启数据库

13、平均排序时间

来源：数据库快照

公式：排序总时间/排序总数(Total sort time (ms)/Total sorts)

指标：远小于系统预期平均语句的执行时间

描述：一般来说，一条语句的执行时间包括锁等待、数据读取时间（内存+I/O）加上排序时间（内存读取和内存排序都属于user CPU 时间）。因此用数据库中平均的排序时间对比平均语句返回的速度，就可以大概估算出执行一条语句时有多少的时间用于排序。一般都希望排序时间越少越好。如果该值过高，用户可以考虑优化查询与添加索引，尽量减少排序的消耗。

分析：

Total sorts                                = 145116

Total sort time (ms)                       = 1457123

指标值=1457123/145116=10.04ms

建议：

调优SQL

14、平均每条交易的排序次数

来源：数据库快照

公式：排序总数/交易总数(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

建议：

无

15、每个事务包含的查询 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

建议：

无

16、每个事务包含的增删改语句数量

来源：数据库快照

公 式 ： 插 入 、 更 新 与 删 除 语 句 的 数 量 交 易 总 数 (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

建议：

无

17、每个事务需要的缓冲区逻辑读

来源：数据库快照

公式：逻辑读的总数/事务总数((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

建议：

无

18、检测索引页扫描

来源：数据库快照

公式：逻辑索引读的总数/事务总数（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

建议：

无

19、日志写入速度

来源：数据库快照

公式：日志写时间/日志写次数(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

建议：

无

2建议调整项

1、缓冲池调整

1.1、调整TBSBP4K缓冲池的大小

由当前的4.3G调整到16G。

db2 "alter bufferpool TBSBP4K immediate size 4194304"

1.2、调整OPMBP缓冲池的大小

由当前的1.5G调整到0.5G。

db2 "alter bufferpool OPMBP immediate size 16000"

1.3、表空间规划

创建一个缓冲池TBSBP4K_IDX，大小为4G、一个表空间TBS4K_IDX，大小为200G，把应用表的数据和索引分离，提高缓存的效率。这步可以暂缓实施，由项目组决定是否执行。

2、数据库参数CHNGPGS_THRESH调整

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、数据库参数CATALOGCACHE_SZ调整

调整CATALOGCACHE_SZ参数值为500，语句为：

db2 update db cfg using CATALOGCACHE_SZ 500

不需要重启数据库

4、数据库参数SORTHEAP调整

调整SORTHEAP参数值为51200，语句为：

db2 update db cfg using SORTHEAP 51200

不需要重启数据库

5、读大字段内容过多

查明直接I/O(读大字段内容)多的原因，以进行后续优化。

6、调优SQL

有效索引读指标值有点高，平均排序时间指标值由点高。

7、扩展内存

调整缓冲池需要内存：(16-4.3)+(-1)=10.7G。

当前数据库服务器物理内存为16G，在调整缓冲池前建议扩展到32G。