DB2数据库-性能测试监控

一．DB2数据库介绍

1. DB2架构介绍

➢ 概要介绍

DB2是IBM公司研发的关系数据库产品，目前广泛应用于金融、通信、交通等行业，在IBM随需应变的战略体系中扮演着重要角色。因为川农信属于金融行业，因此也在使用DB2，其版本为v9.7，所以在这里介绍一些9.7版本的新特性。

 支持索引压缩、临时表数据压缩和xml压缩，更加降低了存储空间成本。  支持内联大对象。  在线表迁移功能。  支持实时表字段更改。

 在性能监控方面DB29.7有了极大增强，新的监控模型不仅可以快速找出问题瓶颈，而且对系统的影响非常小。特别是对锁的监控，通过新的Locking Event Monitor可同时监控死锁、锁等待和锁超时。

 移植性增强。  HADR备机可读。

➢ 三种常用架构简介

当前的应用系统主要分为两类：联机事务处理（OLTP）和联机分析处理（OLAP）。OLTP主要执行日常的事务处理，比如银行存取款、商场购物等，它的主要特点是对响应时间要求高，数据量一般较小，并发多，面向应用。OLAP主要指数据仓库、决策分析类系统，主要特点是数据量大，对实时性要求不高，面向主题。

针对这两种典型的系统，DB2提供了很好的支持。对于OLTP系统和数据量较小的OLAP系统，可以采用单分区架构。

但是有一些OLAP系统，比如国内一些通信公司和电力公司的经营分析系统，包含的数据超过几十TB，一台机器的处理性能根本无法满足要求。这时，可考虑DB2的多

分区架构，即Shared Nothing架构。这种架构的优点就是能够充分利用系统资源，将一个大型的查询分解成若干个小查询并行运行在不同的系统中。由于每一个分区只能够访问自己分区的数据，当查询数据需要关联时。需要在分区中交换必要的数据，分区之间使用一种叫做FCM（Fast Communication Manager）的通信机制。这种架构对系统设计人员要求较高，一定要充分理解优化器与系统访问数据的规则，并且设计很好的分区键，才能够尽可能避免分区间大量的数据交换。

与Share-Nothing相对的另外一种常见的架构是Share-Disk。Share-Disk架构允许所有机器都可以访问全部的数据，好处是管理起来相对方便，而且任意一台机器宕机后，只要存储部分不出问题，其他机器上的系统可以照样访问数据。Share-Disk的设计目标主要是提供高可用性，一般用于OLTP系统。

2. 主要模块介绍

上图描述了DB2的进程模型，长方形代表处理进程，椭圆形代表处理线程，DB2的主进程是db2sysc，在这个处理进程下有许多线 程，最主要的线程也是叫db2sysc，这个主要的线程派生了其他子线程。当一个远程的应用程序比如采用sql connect语句链接服务器时，通讯协议的远程监听器将接收这个请求，并联系db2agent，agent是一个代表DB2实现一些小操作的处理程序， 当发出请求的应用程序是本地的，也就是和DB2服务器在同一服务器上，如果不在同一个服务器上，那么采用db2tcpcm处理本地请求，如果在一台服务器 上采

用db2ipccm线程来处理请求。如果发生本地和异地并行的情况，db2agent会生成其他线程的代理db2agntp线程。其他的线程如 db2pfchr、db2loggr、db2dlock它们应用到不同的目的。

3. 主要进程介绍

➢ db2sysc (Linux) db2syscs (Win)

DB2的主系统控制器或者引擎，对于一个完整的分区其中只有一个包含多线程的主引擎进程。所有的引擎可以分 派单元都是进程中的线程。没有这个进程数据库服务器是无法工作的。

➢ db2acd

主管运行状况监视器和自动维护实用程序的自主计算守护程序。此进程以前称为db2hmon ➢ Db2wdong

DB2的看门狗，是db2sysc的父进程。如果db2sysc集成非正常终止，它将清除所占用的资源。 ➢ Db2vend

在主 进程之外的围栏进程，所有db29.5的第三方代码都在这 个进程中运行。 ➢ Db2fmp

围栏进程，在防火墙外运行用户的存储程序 和用户定义函数代码。此进程代替了db2老版本中的db2udf和db2dari进程。

4. 主要线程介绍

➢ Db2sysc

系 统控制线程。负责实例的启动关闭和管理正在运行的实例。 ➢ Db2tcpcm

tcoip交互监听器 ➢ Db2agent

协调代理代表应用程序实现数据库操作

➢ Db2agntp

如果intra-parallel的属性是yes，那么会产生活动的副代理。它会为应用程序执行数据库操作。Dbagent将协调不同的db2agntp副代理的工作。 ➢ Db2pfchr

db2异步io数据读取 ➢ Db2pclnr

db2异步io数据写入

二．DB2数据库配置参数

收集性能测试项目中DB2数据库一些参数的最佳配置或DB2官网的推荐配置。

1. 监控开关参数

Db2数据库默认情况下，监控参数一部分是处于关闭状态的，因此在需要监控数据库时，需打开部分监控，命令如下：

➢ 查看当前监控开关配置情况：

Db2 get dbm cfg on为开启 off为关闭

➢ 修改某项开关的配置：

Db2 update dbm cfg using DFT_MON_BUFPOOL = on --开启缓冲池的监控 Db2 update dbm cfg using DFT_MON_BUFPOOL = off --关闭缓冲池的监控

建议：因监控需消耗系统资源，尽量减少对数据库性能的影响，建议开启需要监控项的监控开关。

➢ 锁事件提醒级别查看及配置：

db2 get db cfg |grep MON_LCK_MSG_LVL 返回当前数据库的配置，其中 0 为不记录信息 1 为仅记录错误

2 记录服务和非服务错误 缺省是3，记录db2的错误和警告 4 是记录全部信息，包括成功执行的信息

修改返回信息级别：

Db2 update db cfg MON_LCK_MSG_LVL using 3 --将级别改为3

2. 影响数据库性能参数

➢ 查看当前数据库缓冲池页信息：

db2 \"select bpname, npages, pagesize from syscat.bufferpools\" 返回数据库缓冲池名、缓冲池页数、页大小

若数据库缓冲池使用自动增长方式，上述指令不能查询到缓冲池的页数，建议使用以下方式查看：

使用Spogtight 连接需要监控的数据库，使用快捷键ctrl + p 切换到数据库缓冲池信息页，其中size 显示当前缓冲池大小、缓冲池页数。 ➢ 数据库缓冲池大小修改：

alter bufferpool bp4k immediate size 1010 将缓冲池BP4K 的页数调整到1010 建议：将 BUFFPAGE 设置成 40000 个页（160 MB），或者等于机器总内存的 10%，或将bufferpool设置成自动增长。 ➢ 表空间信息查看

Db2pd –d scnx_db –tablespaces 显示表空间信息

重点关注数据库使用了哪些表空间、表空间的页数、页大小、已使用多少页、剩余多少页、是否自动增加

建议：根据表的大小设置不同规格（4k、8k、16k、32k）的表空间，将表空间设置为自动增长的方式。

➢ 锁配置信息查看

Db2 get db cfg |grep –i lock

获取LOCKTIMEOUT 这项指标的值，若为-1 表示锁永远不会超时。 建议：根据业务需求，修改锁超时时间。

➢ 修改锁超时时间

Update db cfg LOCKTIMEOUT using 1 ---表示锁等待超时时间为1秒

➢ 数据库最大应用数

Db2 get db cfg |grep –i applications 返回MAXAPPLS 建议：将此指标值设置为自动增长

➢ 最大应用数修改方法

Db2 update db cfg MAXAPPLS using AUTOMATIC 建议：使用将最大值修改为自动增长

➢ 代理应用程序数

db2 -v get snapshot for dbm |grep -i \"agent\" 返回agent 相关信息 idle agent 显示空闲代理数量；

agents assigned from pool 一个代理分配的次数；

agents creted from empty pool 空闲情况下必须创建的代理数量；

建议：若agents crested from empty pool/agents assigned from pool 的比例

较高（不低于5:1） 则需要增加num_poolagents；

若比例非常低，则暗示num_pollagents可能被设得过大，浪费代理资源。

➢ 日志参数查看及配置

db2 get snapshot for all on sortdb |grep -i \"log\"---查看 log pages read、log pages writen两项参数

建议：将log pages read 的值保持在0 左右

➢ 日志大小及路径配置

Db2 get db cfg fot scnx_db |grep –I “log” ---查看日志大小、个数、路径等配置 Path to log files---日志存放路径 LOGFILSIZ ---单个日志文件大小 LOGPRIMARY ----主日志文件个数 LOGSECOND ----辅日志文件个数

在数据库首次连接时，会分配主日志文件个数那么多的日志文件，当主日志文件写完后，启用辅日志文件。

当前日志文件空间配置：

（LOGPRIMARY+ LOGSECOND ）* LOGFILSIZ * 4K

建议：

 日志文件与数据文件分离

 日志空间需充足，否则会影响交易

 日志初始值建议设置成数据库大小的10%-20%

 主辅日志文件个数不要超过255，日志空间大小不要超过256G，且单个不易过大（最

好不要超过1G） ➢ 修改日志大小方法：

Db2 update db cfg for scnx_db using logsecond 30 --将辅日志文件个数修改为30个

三．DB2监控指标

1. 测试指南指标

➢ 根据《TEST_GUIDE_NFUN_02非功能测试监控指南》整理出DB2数据库在性能测试项

目中需监控的指标如下：

指标类型 指标名称 appls_cur_cons 指标描述 指出当前已连接到数据库的应用程序数 拍快照时，以所选择的级别为所有排序分配 sort_heap_allocated total_sorts DB2 total_sorts_time active_sorts log_reads log_writes lock_waits 的排序推空间的总页面数 已经执行的排序总数 所有已执行排序的总已用时间（毫秒） 数据库中当前已经分配了排序堆的排序数 由记录程序从磁盘读取的日志页数 由记录程序写入磁盘的日志页数 应用程序或连接等待锁定的总次数 2. 其他指标

➢ 根据日常性能测试总结归纳出《监控指南》中现缺少的性能指标如下： 指标类型 指标名称 Overall_hit_ratio 指标描述 数据库缓冲池命中率 数据库索引命中率 DB2 Index_hit_rate Total_Locks Memory_Current_size Memory_percent_total 数据库当前锁总数 数据库当前内存使用大小 数据库内存使用比例 Memory_hight_watermark 数据库内存高水位 Sort_Overflows SQL_Current Tb_scan Num_SQ_execut Package_Cache_Ratio LOCK_SQL Hight_CPU_TIME_SQL 数据库排序溢出总次数 数据库当前执行的SQL语句 数据库全表扫描的情况 执行次数较多的SQL 包缓存命中率 造成数据库锁的SQL 最消耗系统资源的SQL 四．DB2监控方法

根据DB2监控指标中列出的指标名称，按测试指南指标、其他常用指标逐一给出详细的监控方法。

1. 常用监控工具配置

 DB2客户端配置

 安装DB2客户端

 点击开始，选择IBM DB2---DB2COPY1 （缺省值）----命令行工具-----命令行窗口  输入DB2 并回车

 执行catalog tcpip node node1 remote 10.0.193.133 server 60010 创建节点，其中

node1为节点名、10.0.193.133 为数据库服务器地址、60010为开放端口  执行catalog database xir_trd at node node1 为监控数据库分配节点，其中xir_trd为

需要监控的数据库、node1为上一步创建的节点名

 Spotlight客户端配置

 完成DB2客户端的安装及配置

    点击file选择connect ---spotlight on db2 LUN----new connection DB2 instance 选择配置DB2客户端时创建的节点名 DB2 database选择配置DB2客户端时指定的数据库

DB2user、DB2password中输入连接数据库的用户名和密码，如下图所示：

 点击OK 完成创建

 选择创建的连接，点击connect，完成连接  Loadrunner监控配置

 完成DB2客户端的安装及配置

 在Loadrunner中选择DB2，选择Add measurements

 配置服务端的地址及选择操作系统类型,地址方式如：

10.0.193.229@node2

 点击resource下的add按钮，输入数据库账号、密码  选择数据库，并在右方选择需要监控的监控项，如下图所示：

 DB2TOP使用方法

使用远程连接工具（如XSHELL）连接到数据库服务器（必须是数据库用户），使用db2 connect to XXX 完成连接到XXX数据库；

使用db2top –d xxx 实现db2top工具与数据库监控连接； 使用DB2TOP相应命令完成指定项的监控。

2. 测试指南指标

➢ appls_cur_cons------指出当前已连接到数据库的应用程序数

db2 list applications

➢ sort_heap_allocated-------拍快照时，以所选择的级别为所有排序分配的排序推空间的总

页面数

db2 get snapshot for all on dbname | grep heap |more

➢ total_sorts--------已经执行的排序总数

db2 get snapshot for db on dbname | grep sorts |more

排序的操作会影响数据库的性能，因此此项指标越低，性能越优 ➢ total_sorts_time------所有已执行排序的总已用时间（毫秒）

db2 get snapshot for all on dbname | grep time |more

➢ active_sorts------数据库中当前已经分配了排序堆的排序数

db2 get snapshot for db on dbname | grep sorts |more

➢ log_reads-------由记录程序从磁盘读取的日志页数

客户端创建实例： 1) 开始运行cmd

2） cd c:\\program file\\IBM\\SQLINB\\BIN [进入客户端安装bin目录]

3） c:\\program file\\IBM\\SQLINB\\BIN >DB2CW.BAT [初始化DB2命令]

4） 在新开的cmd中执行“db2 catalog tcpip node test remote 192.168.42.102 server 50000” [test是自定义的节点名称，也是连接时的实例名称；192.168.42.102是远程连接的主机地址；50000是服务端的端口号]

5） 执行“db2 catalog dbsmsdb at node test” [使节点与数据库绑定，smsdb是需要连接的数据库名称；test是自己创建的节点名称]

6）执行“db2 terminate” [使绑定生效]

7） 执行“db2 connect to smsdbuser user_name using password” [连接数据库成

功 ]

使用loadrunner监测db2数据库：

需要在本机建立到db2数据库服务器的连接，使用db2的client端进行配置就可以了，下边是对loadrunner进行的设置

打开db2资源监控图后选择Add meaSurements菜单后增加数据的ip和节点名称 如图1

图一

在输入的name框中需要使用数据库的实例名称，这个名称可以通过db2client端的控制中心工具中看到（在建立完到db2 server以后就已经存在的）,

输入数据库用户名和密码后，即可以选择要监测的项目，最后选择log_reads完成监控配置，如图三所示：

图3

此项指标为数据库从磁盘读的页数，值越大，产生IO 的可能性越大

➢ log_writes-----由记录程序写入磁盘的日志页数

方法同log_reads

➢ lock_waits----应用程序或连接等待锁定的总次数

db2 get snapshot for db on dbname | grep waits |more

3. 其他指标

➢ Overall_hit_ratio ----缓冲池命中率

select substr(bp_name,1,30) as bp_name, data_hit_ratio_percent, index_hit_ratio_percent, total_hit_ratio_percent from sysibmadm.bp_hitratio where bp_name not like 'ibmsystem%'

缓冲池的命中率应不低于95%

➢ Index_hit_rate -----某缓冲池索引命中率

 db2 get snapshot for bufferpools on scnx_db

 获得某个缓冲池下以下两项指标的值

Buffer pool index logical reads Buffer pool index physical reads

 通过如下公式计算索引命中率

索引命中率 = (1 - ( 缓冲池索引物理读 / 缓冲池索引逻辑读 ) ) ) * 100%

索引命中率应不低于90%

➢ Total_Locks ---查看当前系统锁的总数

db2pd -db scnx_db -locks |grep 0x |wc –l 统计总数

db2pd –db scnx_db –locks 查看当前系统锁的信息

➢ Memory_Current_size 当前内存使用情况

 使用db2top –d scnx_db 连接到scnx_db数据库

 键入m ,查看current size栏，显示当前各个内存池的内存使用情况

➢ Memory_hight_watermark 内存使用高水位

 使用db2top –d scnx_db 连接到scnx_db数据库

 键入m ,查看hight_watermart栏，显示当内存的高水位

➢ Sort_Overflows 排序溢出的次数

db2 get snapshot for database on scnx_db |grep -i sort 排序溢出数目越低，数据库排序效果越好 ➢ SQL_Current 查看当前数据库执行的SQL

 使用spotlight 连接到数据库

 使用快捷键 ctrl+s 切换到SQL_CURRENT界面，从而查询当前数据库执行的SQL

重点关注SQL耗时 ➢ 查看数据库全表扫描的情况

db2pd -db scnx_db –tcbstats 其中scans显示某表被全表扫描的总次数

➢ 查询造成锁的SQL语句

db2 \"select AGENT_ID, substr(STMT_TEXT,1,60) as STMT_TEXT, STMT_ELAPSED_TIME_MS from table (SNAPSHOT_STATEMENT('SCNX_DB', -1)) as B where AGENT_ID in (select AGENT_ID_HOLDING_LK from table(SNAPSHOT_LOCKWAIT('SCNX_DB',-1))as A order by LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY) order by STMT_ELAPSED_TIME_MS DESC\"

➢ 监控执行次数最多的SQL

Select num_executions as “num execs”, average_execution_time_s as “avg time(sec)”,

stmt_sorts as “num sorts”, sort_per_execution as “sorts per stmt”, substr(stmt_text,1,35) as “sqlstmt” from sysibmadm.top_dynamic_sql where num_executions>0 order by 1 desc fetch first 5rows only ➢ 包缓存命中率监控

使用spotlight连接到目标数据库后，在首页便能获取数据库当前package cache hit rate 的值。

建议：若系统是静态的或没有应用开发，可以考虑减少Catalog Cache的内存分配， 若为统计系统则需要增大Catalog Cache的分配 ➢ 引起锁的SQL监控

select AGENT_ID, substr(STMT_TEXT,1,60) as STMT_TEXT, STMT_ELAPSED_TIME_MS from table (SNAPSHOT_STATEMENT('adms', -1)) as B where AGENT_ID in (select

AGENT_ID_HOLDING_LK from table(SNAPSHOT_LOCKWAIT ('adms',-1))as A order by LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY) order by STMT_ELAPSED_TIME_MS DESC

➢ 最消耗CPU资源的SQL

select (total_usr_cpu_time+total_sys_cpu_time) as total_cpu_time,

substr(stmt_text,1,256)as stmt_text from table(snap_get_dyn_sql_v91('',-2)) as s order by total_cpu_time desc fetch first 10 rows only

五．项目实践

收集实际性能测试项目中对DB2数据库的监控、调优的思路或方法，为以后的性能测试项目提供参考。

1. 排序溢出

现象：

某项目通过spotlight监控数据库发现出现大量排序溢出的现象，且数据库IO较高。通过监控数据库内存池的分配，发现排序堆设置过小，且不会自动增长。

优化：

将缓冲池修改为自动增加以上问题解决

在单交易负载测试时（查询交易），数据库CPU消耗过高，IO较低，通过监控，

发现出现大量排序溢出。

使用db2 get snapshot for database on adms |grep -i sort 查看排序的信息，发现排序数逐渐增加，且出现大量排序溢出。溢出比例高达20%。

使用select STMT_SORTS, SORTS_PER_EXECUTION, substr(STMT_TEXT,1,600) as STMT_TEXT from SYSIBMADM.TOP_DYNAMIC_SQL 查看排序次数较多的SQL 获取SQL 后分析其执行计划。

根据执行计划，建议开发组根据DB2ADVIS生成的建议进行调整。

2. 包缓存命中率过低

现象：

在项目测试中，执行单交易负载测试时，通过Spotlight监控发现，PACKAGE-CACHE –HIT-RATIO命中率较低，约65%，在增加PACKACE CACHE 大小后，其命中率无明显改善。 分析解决：

使用SPOTLIGHT监控当前执行SQL ，发现数据库中多条相似SQL 出现在当前执行的SQL中，仔细分析，发现这些SQL仅数值不同。因此同一SQL被DB2识别成不同SQL导致，数据库 在执行前多次编译，因此导致包缓存命中率较低。最后使用变量绑定的方式解决问题。

3. SQL语句不合理

4. 索引不合理