DB2性能优化
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
通过抓取高峰期的SQL快照，并按每个SQL每次执行的平均读取数据量 （Row Read/Num Exe）进行从高到低排序，逐一分析高读取数据量的前 10个SQL 详见服务报告
17
© 2010 IBM Corporation
硬件– 存储
• 存储性能瓶颈现象
• 平均响应时间AVG_SERVICE_TIME高
• 案例2 某电信集团EAI系统在做完HACMP切换后，出现系统资源使用率100% 和业务无法进行。
• 案例3
某银行大型业务系统在系统高峰期出现SYS CPU达70%，任何命令和操作变得 十分缓慢。
4
© 2010 IBM Corporation
性能优化概述 - 性能瓶颈现象
• 操作系统
• 资源使用（CPU/IO/MEM）率高
• 优化考虑要素
• 性能底线
• 优化代价、时间、效果 • 优化后的性能能否支撑业务发展 • 制定可行的中短长期优化计划
• 优化目标
• 应用程序指标
• 交易平均响应时间 • 最大并发交易量 • 时间窗口内日终作业执行时间
• 数据库指标
• CPU使用率、内存使用率 • 高峰期IO吞吐量和IOWAIT • SQL平均执行时间和数据量
11
© 2010 IBM Corporation
应用程序性能优化 – 相关指标
• 响应时间
用户发起交易请求到应用系统返回处理结果的时间差
• 执行时间 程序执行时间 • 最长响应时间 普通交易最长的响应时间 • 最大并行交易量 应用系统能同一时刻处理的交易量上限 • SQL平均影响的数据量 SQL在执行时影响的数据量
16
© 2010 IBM Corporation
应用程序性能优化 – 案例
• 案例
某股份商业银行网银系统（4 WAS Server + 1 DB2）数据库在业务高 峰期，IO吞吐量巨大，存储均出现报警。如持续下去会影响新网银的性能 和稳定性。由于IO吞吐量高，应该是SQL执行时需要读取大量数据导致， 所以进行大数据量的SQL进行分析和调优。
22
© 2010 IBM Corporation
硬件 – 存储
• 脚本：
LPAR 2 ： for i in 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 do dd if=/dev/rlv_dm_1_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_2_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_3_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_4_p${i} of=/dev/null bs=1m count=50000 &
硬件– 存储
• 使用db2top查看表空间的读写情况，发现部分表空间的平均读写时间为 300-764.81毫秒之间，而老环境表空间读写情况为7-50毫秒之前，与 sar –d的PV平均响应时间较为吻和。
新环境表空间高峰期读写情况
老环境表空间高峰期读写情况
21
© 2010 IBM Corporation
• OLAP系统：OLAP系统面向管理、决策层人员，数据来源于OLTP系统，通过
抽取、装载、转换的处理过程按不同主题重新组织数据，系统支持复杂查询，在 SQL运行时涉及大量数据，数据库保留历史数据，数据量在几百GB到几百TB之 间。
10
© 2010 IBM Corporation
性能优化原则 – 优化原则
原生Dynamic SQL快照
15
按平均Read Row排序的 Dynamic SQL快照
© 2010 IBM Corporation
应用程序性能优化 – 生产环境SQL调优方法
• 获取生产环境的全库DDL和统计信息
db2look –d dbname –e –l –o dbname.ddl
db2look –d dbname –m –o dbname_stat.sql • 创建模拟数据库 修改dbname.ddl中的表空间定义为file container和大小，适应模拟环境
• 命令和操作执行缓慢
• 存储
• 平均服务时间过高AVG_SERVICE_TIME
• 系统吞吐量上限低、IOPS上限低
• DB2 • SQL执行效率差、执行时间长 • OLTP系统，事务平均数据量高
• 死锁和锁等待
5
© 2010 IBM Corporation
性能优化概述 - 性能优化起因
• 影响系统正常运行
DB2性能优化
吴建城
IBM CDL LBS
1
© 2010 IBM Corporation
议程
• 性能相关案例
• 性能优化概述
• 性能瓶颈现象 • 性能优化的起因 • 性能优化目标 • 性能优化原则 • 性能模型 • 优化原则 • 应用程序性能优化 • 相关指标 • 执行计划 • 选择合适的索引
2
© 2010 IBM Corporation
6
© 2010 IBM Corporation
性能优化概述 - 性能优化起因
• 性能测试和压力测试
• 深入调优以性能满足业务要求
• 优化关键交易的处理效率
• 系统性能扩展评估
• 建立性能/配置/效率的模型
• 未来业务发展必要时，进行扩容的依据
7
© 2010 IBM Corporation
性能优化概述 – 优化目标
• 评估每个交易的资源消耗是否合理
• 应用系统类型定位
• 性能差的相关SQL的隔级级别是否合理 • 性能差的相关SQL影响的数据量是否合理 • SQL的执行计划是否稳定
• 评估操作系统资源消耗是否合理
• CPU、I/O和内存间是否保持平衡 • 进程状态、Pagingspace使用是否正常 • 内外网的网速是否稳定、高速 • 评估数据库配置是否合理 • 数据库架构是否合理 • DB2SET变量设置是否合理 • 实例参数和数据库参数配置是否合理
18
© 2010 IBM Corporation
硬件– 存储
• 案例
• 现象：某电信集团经分系统在迁移后，新环境（AIX 6.1 6LPAR + DB2 V9.7.5 DPF 109节点 + 新存储）相比旧环境（AIX 5.3 4LPAR+ DB2 V9.1.11 DPF 64节点+ 旧存储）出现IO WAIT高，而且大部分时间USR CPU比IO WAIT低。数据库10个活动并发时执行sar -d查看PV的平均响 应时间AVG_SERVICE_TIME (单位：ms):
8
© 2010 IBM Corporation
性能优化原则 – 性能模型
• 保持数据库的最佳性能必须保证CPU、I/O和内存间的平衡
在理想情况下，这个三角形应该是一个等边三角形，使系统资源达到完美平衡。在DB2服务 器端调优时，应该考虑资源是否平衡再做出调优决策。例如，调整一个数据库的排序性能， 如果只调整内存（减少排序堆），将会使用临时表导致CPU和I/O使用率上升。最小化排序 相关的I/O，需要增加分配给排序堆的内存和提高CPU使用率。
19
© 2010 IBM Corporation
硬件– 存储
• 特别是LPAR TDW4的平均响应时间AVG_SERVICE_TIME更长
(单位：ms)
与系统工程师沟通后， 得知TDW4上DB2相关 VG与GPFS共用光纤
卡，调整后，avserv与
其他LPAR无异
20
© 2010 IBM Corporation
9
© 2010 IBM Corporation
性能优化原则 – 优化原则
• 应用系统分类
• OLTP系统：典型OLTP系统面向大量并发用户，支持日常业务处理，每个事务
通常只涉及少量记录；系统要求响应时间尽量短，在高并发情况下要求一定的事 务处理吞吐能力；有的OLTP系统中有批处理的需求，要求在特定的时间窗口内 能够完成涉及大量数据的运算；通常OLTP数据库只保留当前数据，数据量在 100MB到几个TB之间。 OLTP系统主要有内部系统和外部系统两类。 内部系统：面向公司内部人员使用，并发量较小，功能和性能问题不会造成太多 的负面影响，如OA系统。 外部系统：面向公众、客户使用，并发量很大，一般为内部系统的几十倍到几 千倍，功能和性能问题会造成严重的负面影响，如网银系统、网上支付系统。
硬件– 存储
• 与存储工程师、系统工程师沟通后，新存储的官方吞吐量上限为4GB/s， 而光纤卡不存在复用的情况，对比新老环境的存储差异如下：
由于一个表空间对应4个裸设备，采用dd模拟DB2读取一个表空间的数 据，脚本如下：
LPAR 1 ： for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 do dd if=/dev/rlv_dm_1_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_2_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_3_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_4_p${i} of=/dev/null bs=1m count=50000 & done
• 索引字段个数、索引大小 • 索引全扫描与索引快速扫描 • 示例SQL SELECT * FROM WHERE AND T1 C1= 1 C2= 'D'
14
© 2010 IBM Corporation
应用程序性能优化 – 生产环境SQL调优方法
• 捕获问题SQL
通过快照、db2pd、admin view等手段获取性能差的SQL语句，并再平均 读取数据量进行排序，找出最消耗资源的SQL
议程
• 应用程序性能优化
• 生产环境SQL调优方法
• 案例 • 硬件性能优化 • 存储 • 数据库性能优化 • 理解数据库行为 • REORG慢 • 复杂SQL执行慢 • 表属性优化 • 高峰期数据库Hang住问题
3
© 2010 IBM Cwenku.baidu.comrporation
性能案例描述
• 案例1
近一段时间，某银行电子票据系统每周五晚上10点开始做数据库维护 （REORG/RUNSTATS/REBIND），维护时间需要12小时左右，影响系统正常正业； 经优化脚本生成详细维护日志，检查维护部分表的REORG时间过长。
• 交易高峰期，系统资源使用率持续接近100%
• 交易高峰期，IO吞吐量接近存储极限，存储报警
• 当前性能不能满足业务要求
• 非高峰期，交易性能差
• 高峰期，交易性能差
• 日终作业未在规定时间窗口内完成、生成日志过多
• 当前性能不能满足业务发展要求
• 当前高峰期的压力不能满足未来业务发展的要求 • 当前日终作业时间窗口无法满足未来业务发展的要求
按照bp->tbsp->sequence->table->view->function->procedure创建数据库
• 导入生产环境的统计信息 db2 –tvf dbname_stat.sql|tee –a dbname_stat.log • 查看SQL的执行统计 通过图形工具、db2expln、db2exfmt查看SQL的执行计划并调优
12
© 2010 IBM Corporation
应用程序性能优化 – 执行计划
13
© 2010 IBM Corporation
应用程序性能优化 – 选择合适的索引
• 考虑要素
• SQL语句、WHERE和JOIN字段
• 目前的数据分布 • 理论平均过滤数据量 • 未来数据分布
• 未来理论平均数据数据量
sar –d • IO吞吐量远低于存储的性能上限（DB2的理论吞吐量为dd测试结果的 80% - 90%），可根据数据库的表空间配置情况，对多个裸设备同时执 行dd 进行测试 dd if=/dev/lv01 of=/dev/null bs=1m count=50000 & dd if=/dev/lv02 of=/dev/null bs=1m count=50000 & • 存储性能排查法则 • 确保当前吞吐量没有超过存储吞吐量极限 • RAID组配置的性能上限 • 光纤卡与光纤交换机的配置是否合理 • 存储相关参数配置是否合理 • 数据库相关参数是否合理
17
© 2010 IBM Corporation
硬件– 存储
• 存储性能瓶颈现象
• 平均响应时间AVG_SERVICE_TIME高
• 案例2 某电信集团EAI系统在做完HACMP切换后，出现系统资源使用率100% 和业务无法进行。
• 案例3
某银行大型业务系统在系统高峰期出现SYS CPU达70%，任何命令和操作变得 十分缓慢。
4
© 2010 IBM Corporation
性能优化概述 - 性能瓶颈现象
• 操作系统
• 资源使用（CPU/IO/MEM）率高
• 优化考虑要素
• 性能底线
• 优化代价、时间、效果 • 优化后的性能能否支撑业务发展 • 制定可行的中短长期优化计划
• 优化目标
• 应用程序指标
• 交易平均响应时间 • 最大并发交易量 • 时间窗口内日终作业执行时间
• 数据库指标
• CPU使用率、内存使用率 • 高峰期IO吞吐量和IOWAIT • SQL平均执行时间和数据量
11
© 2010 IBM Corporation
应用程序性能优化 – 相关指标
• 响应时间
用户发起交易请求到应用系统返回处理结果的时间差
• 执行时间 程序执行时间 • 最长响应时间 普通交易最长的响应时间 • 最大并行交易量 应用系统能同一时刻处理的交易量上限 • SQL平均影响的数据量 SQL在执行时影响的数据量
16
© 2010 IBM Corporation
应用程序性能优化 – 案例
• 案例
某股份商业银行网银系统（4 WAS Server + 1 DB2）数据库在业务高 峰期，IO吞吐量巨大，存储均出现报警。如持续下去会影响新网银的性能 和稳定性。由于IO吞吐量高，应该是SQL执行时需要读取大量数据导致， 所以进行大数据量的SQL进行分析和调优。
22
© 2010 IBM Corporation
硬件 – 存储
• 脚本：
LPAR 2 ： for i in 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 do dd if=/dev/rlv_dm_1_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_2_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_3_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_4_p${i} of=/dev/null bs=1m count=50000 &
硬件– 存储
• 使用db2top查看表空间的读写情况，发现部分表空间的平均读写时间为 300-764.81毫秒之间，而老环境表空间读写情况为7-50毫秒之前，与 sar –d的PV平均响应时间较为吻和。
新环境表空间高峰期读写情况
老环境表空间高峰期读写情况
21
© 2010 IBM Corporation
• OLAP系统：OLAP系统面向管理、决策层人员，数据来源于OLTP系统，通过
抽取、装载、转换的处理过程按不同主题重新组织数据，系统支持复杂查询，在 SQL运行时涉及大量数据，数据库保留历史数据，数据量在几百GB到几百TB之 间。
10
© 2010 IBM Corporation
性能优化原则 – 优化原则
原生Dynamic SQL快照
15
按平均Read Row排序的 Dynamic SQL快照
© 2010 IBM Corporation
应用程序性能优化 – 生产环境SQL调优方法
• 获取生产环境的全库DDL和统计信息
db2look –d dbname –e –l –o dbname.ddl
db2look –d dbname –m –o dbname_stat.sql • 创建模拟数据库 修改dbname.ddl中的表空间定义为file container和大小，适应模拟环境
• 命令和操作执行缓慢
• 存储
• 平均服务时间过高AVG_SERVICE_TIME
• 系统吞吐量上限低、IOPS上限低
• DB2 • SQL执行效率差、执行时间长 • OLTP系统，事务平均数据量高
• 死锁和锁等待
5
© 2010 IBM Corporation
性能优化概述 - 性能优化起因
• 影响系统正常运行
DB2性能优化
吴建城
IBM CDL LBS
1
© 2010 IBM Corporation
议程
• 性能相关案例
• 性能优化概述
• 性能瓶颈现象 • 性能优化的起因 • 性能优化目标 • 性能优化原则 • 性能模型 • 优化原则 • 应用程序性能优化 • 相关指标 • 执行计划 • 选择合适的索引
2
© 2010 IBM Corporation
6
© 2010 IBM Corporation
性能优化概述 - 性能优化起因
• 性能测试和压力测试
• 深入调优以性能满足业务要求
• 优化关键交易的处理效率
• 系统性能扩展评估
• 建立性能/配置/效率的模型
• 未来业务发展必要时，进行扩容的依据
7
© 2010 IBM Corporation
性能优化概述 – 优化目标
• 评估每个交易的资源消耗是否合理
• 应用系统类型定位
• 性能差的相关SQL的隔级级别是否合理 • 性能差的相关SQL影响的数据量是否合理 • SQL的执行计划是否稳定
• 评估操作系统资源消耗是否合理
• CPU、I/O和内存间是否保持平衡 • 进程状态、Pagingspace使用是否正常 • 内外网的网速是否稳定、高速 • 评估数据库配置是否合理 • 数据库架构是否合理 • DB2SET变量设置是否合理 • 实例参数和数据库参数配置是否合理
18
© 2010 IBM Corporation
硬件– 存储
• 案例
• 现象：某电信集团经分系统在迁移后，新环境（AIX 6.1 6LPAR + DB2 V9.7.5 DPF 109节点 + 新存储）相比旧环境（AIX 5.3 4LPAR+ DB2 V9.1.11 DPF 64节点+ 旧存储）出现IO WAIT高，而且大部分时间USR CPU比IO WAIT低。数据库10个活动并发时执行sar -d查看PV的平均响 应时间AVG_SERVICE_TIME (单位：ms):
8
© 2010 IBM Corporation
性能优化原则 – 性能模型
• 保持数据库的最佳性能必须保证CPU、I/O和内存间的平衡
在理想情况下，这个三角形应该是一个等边三角形，使系统资源达到完美平衡。在DB2服务 器端调优时，应该考虑资源是否平衡再做出调优决策。例如，调整一个数据库的排序性能， 如果只调整内存（减少排序堆），将会使用临时表导致CPU和I/O使用率上升。最小化排序 相关的I/O，需要增加分配给排序堆的内存和提高CPU使用率。
19
© 2010 IBM Corporation
硬件– 存储
• 特别是LPAR TDW4的平均响应时间AVG_SERVICE_TIME更长
(单位：ms)
与系统工程师沟通后， 得知TDW4上DB2相关 VG与GPFS共用光纤
卡，调整后，avserv与
其他LPAR无异
20
© 2010 IBM Corporation
9
© 2010 IBM Corporation
性能优化原则 – 优化原则
• 应用系统分类
• OLTP系统：典型OLTP系统面向大量并发用户，支持日常业务处理，每个事务
通常只涉及少量记录；系统要求响应时间尽量短，在高并发情况下要求一定的事 务处理吞吐能力；有的OLTP系统中有批处理的需求，要求在特定的时间窗口内 能够完成涉及大量数据的运算；通常OLTP数据库只保留当前数据，数据量在 100MB到几个TB之间。 OLTP系统主要有内部系统和外部系统两类。 内部系统：面向公司内部人员使用，并发量较小，功能和性能问题不会造成太多 的负面影响，如OA系统。 外部系统：面向公众、客户使用，并发量很大，一般为内部系统的几十倍到几 千倍，功能和性能问题会造成严重的负面影响，如网银系统、网上支付系统。
硬件– 存储
• 与存储工程师、系统工程师沟通后，新存储的官方吞吐量上限为4GB/s， 而光纤卡不存在复用的情况，对比新老环境的存储差异如下：
由于一个表空间对应4个裸设备，采用dd模拟DB2读取一个表空间的数 据，脚本如下：
LPAR 1 ： for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 do dd if=/dev/rlv_dm_1_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_2_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_3_p${i} of=/dev/null bs=1m count=50000 & dd if=/dev/rlv_dm_4_p${i} of=/dev/null bs=1m count=50000 & done
• 索引字段个数、索引大小 • 索引全扫描与索引快速扫描 • 示例SQL SELECT * FROM WHERE AND T1 C1= 1 C2= 'D'
14
© 2010 IBM Corporation
应用程序性能优化 – 生产环境SQL调优方法
• 捕获问题SQL
通过快照、db2pd、admin view等手段获取性能差的SQL语句，并再平均 读取数据量进行排序，找出最消耗资源的SQL
议程
• 应用程序性能优化
• 生产环境SQL调优方法
• 案例 • 硬件性能优化 • 存储 • 数据库性能优化 • 理解数据库行为 • REORG慢 • 复杂SQL执行慢 • 表属性优化 • 高峰期数据库Hang住问题
3
© 2010 IBM Cwenku.baidu.comrporation
性能案例描述
• 案例1
近一段时间，某银行电子票据系统每周五晚上10点开始做数据库维护 （REORG/RUNSTATS/REBIND），维护时间需要12小时左右，影响系统正常正业； 经优化脚本生成详细维护日志，检查维护部分表的REORG时间过长。
• 交易高峰期，系统资源使用率持续接近100%
• 交易高峰期，IO吞吐量接近存储极限，存储报警
• 当前性能不能满足业务要求
• 非高峰期，交易性能差
• 高峰期，交易性能差
• 日终作业未在规定时间窗口内完成、生成日志过多
• 当前性能不能满足业务发展要求
• 当前高峰期的压力不能满足未来业务发展的要求 • 当前日终作业时间窗口无法满足未来业务发展的要求
按照bp->tbsp->sequence->table->view->function->procedure创建数据库
• 导入生产环境的统计信息 db2 –tvf dbname_stat.sql|tee –a dbname_stat.log • 查看SQL的执行统计 通过图形工具、db2expln、db2exfmt查看SQL的执行计划并调优
12
© 2010 IBM Corporation
应用程序性能优化 – 执行计划
13
© 2010 IBM Corporation
应用程序性能优化 – 选择合适的索引
• 考虑要素
• SQL语句、WHERE和JOIN字段
• 目前的数据分布 • 理论平均过滤数据量 • 未来数据分布
• 未来理论平均数据数据量
sar –d • IO吞吐量远低于存储的性能上限（DB2的理论吞吐量为dd测试结果的 80% - 90%），可根据数据库的表空间配置情况，对多个裸设备同时执 行dd 进行测试 dd if=/dev/lv01 of=/dev/null bs=1m count=50000 & dd if=/dev/lv02 of=/dev/null bs=1m count=50000 & • 存储性能排查法则 • 确保当前吞吐量没有超过存储吞吐量极限 • RAID组配置的性能上限 • 光纤卡与光纤交换机的配置是否合理 • 存储相关参数配置是否合理 • 数据库相关参数是否合理