DB2性能指标及监控

DB2数据库性能优化 第2课
-DB2性能指标及监控
DATAGURU专业数据分析社区

本课目标
完成本课后，你应该能够
–
理解OLTP系统关键性能指标是指什么
–
性能与哪些因素相关
–
如何监控SQL语句性能
–
了解数据库层面关键性能指标及获取方法
DATAGURU专业数据分析社区

OLTP系统关键指标:事务吞吐量,平均响应时间
事务吞吐量：指单位时间内数据库所能完成的事务处理或查询的笔数 平均响应时间：平均完成每笔事务或查询需要的时间 这两项指标通常作为系统的非功能需求被提出；基准性能测试指标：TPC-C
DATAGURU专业数据分析社区

性能与哪些因素相关?
数据库服务器硬件配置及应用部署架构
– – 硬件配置：CPU，Memory， Storage（IOPS, Throughput），Network 数据库与应用服务器距离
事务/查询本身复杂度 数据量大小及数据库相关设计
– – – 表及索引 表空间 数据库参数
代码质量
– – 处理逻辑 SQL语句
DATAGURU专业数据分析社区

如何获取TPS, Response Time
性能测试工具如Load Runner， 但仅在模拟性能测试时用 应用层如有日志，可从中统计出来 数据库层
– 下面命令可获取到当前时间为止, 数据库总的TPS及Response Time； 如需要了解TPS及Response Time 在不同时段的变化，需要按一定时间间隔获取数据,然后计算出来 db2 “SELECT current time as CURRENT_TIME, TOTAL_APP_COMMITS, TOTAL_ACT_TIME, TOTAL_APP_COMMITS/TIMESTAMPDIFF(2,CURRENT TIMESTAMP - DB_CONN_TIME) as TPS, TOTAL_ACT_TIME/TOTAL_APP_COMMITS as RESPONSE_TIME FROM TABLE (MON_GET_DATABASE(-2))“ CURRENT_TIME TOTAL_APP_COMMITS TOTAL_ACT_TIME TPS RESPONSE_TIME ------------ -------------------- -------------------- -------------------- -------------------17:46:30 276817 2168651 56 7 … 17:49:00 281127 2202164 55 7
DATAGURU专业数据分析社区
17:46:30 至 17:49:00之间 TPS=(281127-276817)/150 =28

监控重点-SQL语句
时间
– SQL语句执行时间，包括各种等待时间比如锁等待、写日志等待时间
空间
– 耗用CPU， Memory，IO资源
如何评估SQL的运行时间及资源使用是合理的？
– 执行时间短的SQL不一定没问题
如何判断SQL执行异常
– – 建立基线 设定偏离标准
DATAGURU专业数据分析社区

如何获取SQL的相关性能数据
SQL分为静态，动态两种。
– 静态SQL是采用嵌入式编程方式开发的，在运行之前就编译生成语句执行计划并保存在数据库内，执行 时不需要编译，而且执行计划不会自动改变。 – 动态SQL是执行时才编译的，比如应用通过ODBC，JDBC接口访问数据库，发给数据库的就是动态SQL
SQL执行后，执行计划及执行状况信息被保存在Package Cache Package Cache大小由数据库配置参数PCKCACHESZ决定；太小可能导致语句经常被换出，太大 时监控可能需要较长时间才能拿到数据 可通过表函数MON_GET_PKG_CACHE_STMT获取相关性能数据；比如下面语句可列出平均执行 时间排前十的SQL语句
db2 "SELECT TOTAL_ACT_TIME/NUM_EXEC_WITH_METRICS as AVG_EXE_TIME, SUBSTR(STMT_TEXT,1,40) as SQL FROM TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) WHERE NUM_EXEC_WITH_METRICS>=1 ORDER BY 1 desc fetch first 10 rows only"
DATAGURU专业数据分析社区

关注正在运行的SQL
正在运行的SQL是从状态为“Executing”数据库应用连接获取的 保存在Package Cache中的信息是关于每条SQL语句的汇总信息，正在执行的SQL没有计算进来 系统出问题时往往出现许多变慢的SQL同时运行 下面命令按已执行时间和耗用CPU时间由大到小列出正在运行的SQL
db2 "SELECT APPLICATION_HANDLE, ELAPSED_TIME_SEC, TOTAL_CPU_TIME, ROWS_READ, SUBSTR(STMT_TEXT,1,40) as SQL FROM SYSIBMADM.MON_CURRENT_SQL ORDER BY 2 desc, 3 desc"
DATAGURU专业数据分析社区

操作系统层面监控对象及获取方法
关注资源的使用状况
– – – – – CPU Memory IO Network Filesystems
获取方法
– – 监控工具；比如Tivoli 操作系统自带工具或命令；比如AIX平台的topas, nmon, vmstat Linux 的top
DATAGURU专业数据分析社区

数据库层面监控指标及获取方法
Rows Read/Selected比 例如：表customer有10000条记录，下面语句从中选出一条
SELECT * FROM customer WHERE id=1234
如果表customer的字段id上没有任何索引，数据库将扫描全部记录，才能将符合条件的记录找出 来，这样我们在数据库层面看到Rows read = 10000，Rows Selected = 1； 如果id字段上有索引，而且这条语句在执行时用到了该索引，我们会看到Rows read = 1，Rows SELECTed = 1
获取方法
db2 "SELECT ROWS_READ_PER_ROWS_RETURNED FROM SYSIBMADM.MON_DB_SUMMARY"
DATAGURU专业数据分析社区

数据库层面监控指标及获取方法
Bufferpool命中率，预读率 命中率低意味着为BP分配的内存不足或配置不合理；提高命中率的方法：把最常用的数据保留在 BP中 获取方法 数据库总体BP命中率
db2 "SELECT TOTAL_BP_HIT_RATIO_PERCENT FROM SYSIBMADM.MON_DB_SUMMARY" 每个BP数据及索引的命中率，预取率 db2 "SELECT SUBSTR(BP_NAME,1,18) as BP_NAME, DATA_HIT_RATIO_PERCENT, INDEX_HIT_RATIO_PERCENT, PREFETCH_RATIO_PERCENT FROM SYSIBMADM.MON_BP_UTILIZATION"
DATAGURU专业数据分析社区

数据库层面监控指标及获取方法
Disk IO 性能
– – – 数据需要从存储读入内存 LOB(大对象)不进入BP 日志写入
获取方法
db2 "SELECT SUBSTR(TBSP_NAME,1,20) as TBSP_NAME, POOL_READ_TIME/ case when POOL_DATA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS>0 then POOL_DATA_P_READS+POOL_INDEX_P_READS+POOL_TEMP_DATA_P_READS+POOL_TEMP_INDEX_P_READS else 1 end as AVG_BP_READ_TIME, POOL_WRITE_TIME/ case when POOL_DATA_WRITES+POOL_INDEX_WRITES>0 then POOL_DATA_WRITES+POOL_INDEX_WRITES else 1 end as AVG_BP_WRITE_TIME, DIRECT_READ_TIME / case when DIRECT_READS/8 > 0 THEN DIRECT_READS/8 else 1 end as AVG_DIRECT_READ_TIME, DIRECT_WRITE_TIME / case when DIRECT_WRITES/8 > 0 THEN DIRECT_WRITES/8 else 1 end as AVG_DIRECT_WRITE_TIME FROM TABLE(MON_GET_TABLESPACE(NULL,NULL))“
日志缓存满的次数及平均日志写入时间
db2 "SELECT NUM_LOG_BUFFER_FULL, LOG_WRITE_TIME / case when LOG_WRITES > 0 then LOG_WRITES else 1 end as AVG_LOG_WRITE_TIME FROM TABLE(MON_GET_TRANSACTION_LOG(-2))" ~
DATAGURU专业数据分析社区

数据库层面监控指标及获取方法
锁等待次数及平均锁等待时间 锁等待时间长意味着应用的并发处理能力有问题 典型的场景，用户自己生成流水号
UPDATE syscfg SET seqno = seqno + 1
获取方法
db2 "SELECT LOCK_WAITS, LOCK_WAIT_TIME/LOCK_WAITS as AVG_LOCK_WAIT_TIME FROM TABLE(MON_GET_DATABASE(-2))"
DATAGURU专业数据分析社区

数据库层面监控指标及获取方法
锁超时，锁提升，死锁发生次数 锁超时：等锁时间超过数据库参数LOCKTIMEOUT设置的时长 锁提升：锁资源(LOCKLIST)被耗尽或单个应用达到MAXLOCKS规定的上限时发生，由多个记录锁 提升成一个表级锁，然后释放记录锁 死锁： 获取方法
db2 "SELECT LOCK_TIMEOUTS, LOCK_ESCALS_MAXLOCKS, LOCK_ESCALS_LOCKLIST, DEADLOCKS FROM TABLE(MON_GET_DATABASE(-2))"
两个或两个以上应用互相等锁
DATAGURU专业数据分析社区

数据库层面监控指标及获取方法
排序溢出率(Sort Overflow) 排序内存SORTHEAP不能容纳参与排序的记录，会用到系统临时表空间缓存数据 获取方法
db2 "SELECT (SORT_OVERFLOWS * 100) / TOTAL_SORTS as SORT_OVERFLOW_RATIO, TOTAL_SORTS FROM TABLE(MON_GET_DATABASE(-2))"
DATAGURU专业数据分析社区

数据库层面监控指标及获取方法
Package Cache 命中率 不好编程方法可能导致Package Cache 命中率低
SELECT password FROM user WHERE id = ‘0001’; SELECT password FROM user WHERE id = ‘0002’; …… SELECT password FROM user WHERE id = ‘8999’; => SELECT password FROM user WHERE id = ?;
获取方法
db2 "SELECT (PKG_CACHE_LOOKUPS-PKG_CACHE_INSERTS)*100 / PKG_CACHE_LOOKUPS FROM TABLE(MON_GET_DATABASE(-2))"
DATAGURU专业数据分析社区

性能监控工具
实时监控工具db2top
– – 类似AIX上字符界面监控工具topas, 支持AIX, Linux 非常简单直接的工具，小写h可获取帮助
? db2top –d
DATAGURU专业数据分析社区

性能监控工具
IBM Data Ser ver Manager
– – – 浏览器界面图形工具 可作实时监控,也可保留历史数据 有告警及报表功能
第三方工具
– Quest等
DATAGURU专业数据分析社区

DB2 OLTP数据库性能评分模型
DATAGURU专业数据分析社区

内容回顾
OLTP系统关键性能指标: TPS / Response Time
性能与哪些因素相关:硬件配置,应用复杂度,数据量,代码质量等
如何监控SQL语句
数据库层面关键性能指标及获取方法
DATAGURU专业数据分析社区