DB2锁问题处理最佳实践.ppt

《DB2锁问题处理最佳实践.ppt》由会员分享，可在线阅读，更多相关《DB2锁问题处理最佳实践.ppt（39页珍藏版）》请在三一办公上搜索。

1、,DB2锁问题处理最佳实践,徐明伟,北京普远天成科技有限公司 技术总监,DTCC2012,2,3,4,5,议题,1,DB2锁概述DB2锁问题监控和定位DB2锁问题调优DB2 9.7锁机制深入分析DB2锁案例分享,DTCC20122,为什么需要锁 一致性机制,事务日志锁隔离级别,锁 维护数据一致性 控制并发性 锁分类 锁的对象（表、行、表空间、索引）锁的模式（S,X等）DTCC20123,锁导致问题/锁现象 锁的几种现象,锁等待锁超时死锁锁升级锁转换,锁产生的问题 系统运行慢 应用回滚DTCC20124,2,3,4,5,议题,1,DB2锁概述DB2锁问题监控和定位DB2锁问题调优DB2 9.7锁

2、机制深入分析DB2锁案例分享,DTCC20125,锁问题监控和定位,锁问题监控定位工具,Snapshot快照,deadlock event monitor with details historydb2pd(8.2后)db2pdcfg(9.1),db2_capture_locktimeout(9.5)New Locking event monitor(9.7),锁是症状，不是根源,DTCC2012,6,=0,锁快照监控 通过get snapshot for database on 或sysibmadm.snapdb 死锁、锁等、锁超时、锁升级等统计信息Database Snapshot,Dat

3、abase name,=CRMDB,First database connect timestampSnapshot timestampLocks held currently,=10/10/2011 20:50:28.421301=03/07/2012 11:10:08.120847=1,数据库连接时间快照时间,Lock waitsTime database waited on locks(ms)Lock list memory in use(Bytes)Deadlocks detectedLock escalationsExclusive lock escalationsAgents cu

4、rrently waiting on locksLock Timeouts,=471967=2039693347=1740480=470=0=0=24782,锁等数量发生锁等的时间死锁数量锁升级数量锁超时数量DTCC20127,db2pd监控锁等,db2pd d-locks wait tra app dyn,定位引起锁等的事务、应用和动态语句,DTCC2012,8,db2pdcfg捕获锁超时(9.1版本)db2pd结合db2cos回调脚本捕获锁超时或死锁 改写db2cos回调脚本LOCKTIMEOUT),echo Lock Timeout Caughtif!-n$database thendb

5、2pd-instelsedb2pd-db$database-locks-tra-app-dyn,$logfile$logfile$logfile,fi;db2pdcfg catch locktimeout count=1 当发生锁超时时，会调用db2cos脚本DTCC20129,db2_capture_locktimeout捕获锁超时(9.1 fp4-9.5),设置db2_capture_locktimeout注册变量,db2set DB2_CAPTURE_LOCKTIMEOUT=ON,创建死锁事件监控器,db2“create event monitor dlockevm for deadlo

6、cks with details history write to file,/home/db2inst1/locks”,锁请求信息,请求锁模式,请求的SQL语句,DTCC2012,10,锁拥有者信息,当前锁模式,占有锁的SQL语句,DTCC2012,11,Deadlock event monitor监控死锁,创建deadlock事件监控器,db2 create event monitor dlockevm for deadlocks with details history,write to file/home/db2inst1/deadlock“,Deadlock id,死锁的表,表上已有

7、的锁模式,当前请求的锁模式,死锁语句,DTCC2012,12,Deadlock event monitor监控死锁(2),Deadlock id,锁的表,请求的锁类型,死锁语句,DTCC2012,13,锁事件监控器(9.7版本)9.7引入了新的锁事件监控器,用同一个锁事件监控器就可捕获死锁、锁超时和锁等待 采用UE表存取锁事件结果，使得分析更简单 锁事件监控器的使用包含三个步骤：,1.创建锁事件监控器,2.设置数据收集的类型和级别 3.格式化并分析数据,DTCC2012,14,创建锁事件监控器create event monitor lockevmon for lockingwrite to

8、unformatted event table(table,locks)创建锁事件监控器,创建锁事件监控器set event monitor lockevmon state=1,Unformatted Event(UE)表 解释说明DTCC201215,设置锁事件参数,捕获死锁事件,db2 update db cfg using mon_deadlock hist_and_values,捕获锁超时事件,db2 update db cfg using mon_locktimeout hist_and_values,捕获锁等待事件,db2 update db cfg using mon_lw_th

9、resh 5000000,db2 update db cfg using mon_lockwait hist_and_values,要监控的锁事件信息详细程度:,Without_hist：收集基本事件信息,With_hist：一个事务里最多收集250个活动 Hist_and_values：收集活动和值 None：不收集事件,DTCC2012,16,格式化锁事件输出结果,一旦捕获了锁事件，下一步就要对锁数据进行分析 对UE表数据的格式化有三种方法：,Db2evmonfmttool,产生文本格式输出报告,EVMON_FORMAT_UE_TO_XML_UDF函数,产生XML格式输出报告,EVMON_

10、FORMAT_UE_TO_TABLE存储过程,将UE数据格式化成关系表数据分析处理变得简单,DTCC2012,17,死锁举例,Application#1,update t2 set name=t2.where id=200select*from employee,Application#2,update t1 set name=t11.where id=100update t2 set name=t2new.where id=200,update t1 set name=t222 where id=100(911 error),模拟一个死锁场景,DTCC2012,18,T,T,T,T,使用EV

11、MON_FORMAT_UE_TO_TABLE格式化call EVMON_FORMAT_UE_TO_TABLES(LOCKING,NULL,NULL,NULL,NULL,NULL,RECREATE_FORCE,-1,select*from locks order by event_timestamp),LOCK_ACTIVITY_VALUESLOCK_EVENTLOCK_PARTICIPANTSLOCK_PARTICIPANT_ACTIVITIES,INST97INST97INST97INST97,2012-04-03-22.22.42.3688242012-04-03-22.22.41.600

12、1762012-04-03-22.22.41.9211192012-04-03-22.22.42.112358DTCC201219,与者信息,XMLID,查看死锁和参与者信息Select xmlid,event_id,event_type,event_timestamp,member,dl_conns,rolled_back_participant_no from lock_event,查看锁信息,XMLID EVENT_TYPE EVENT_TIMESTAMP MEMBER DL_CONNS ROLLED_BACK_PARTICIPANT_NO-,db2LockEvent_4 DEADLOC

13、K 2012-04-03-21.27.19.660632,0,2,2,Select xmlid,participant_no,participant_type,participant_no_holding_lk,application_handle fromlock_participants where xmlid like db2LockEvent_1%PARTICIPANT_NO PARTICIPANT_TYPE PARTICIPANT_NO_HOLDING_LK,查看死锁参,APPLICATION_HANDLE-db2LockEvent_4 1 Requester 2 7db2LockE

14、vent_4 2 Requester 1 60,DTCC201220,查看死锁参与者SQL活动信息select activity_id,activity_type,uow_id,stmt_type,substr(stmt_text,1,100)as stmt_textfrom lock_participant_activitieswhere xmlid like db2LockEvent_4%,查看死锁参与者SQL活动信息,ACTIVITY_ID ACTIVITY_TYPE UOW_ID,STMT_TYPE,STMT_TEXT,-,12123,pastcurrentpastpastcurren

15、t,44111,2 update t1 set name=t11.where id=1002 update t2 set name=t2new.where id=2002 update t2 set name=t2.where id=2002 select*from employee2 update t1 set name=t222 where id=100,DTCC201221,2,3,4,5,议题,1,DB2锁概述DB2锁问题监控和定位DB2锁问题调优DB2 9.7锁机制深入分析DB2锁案例分享,DTCC201222,性能调优关键 锁是症状，SQL是问题的根源 I/O 最关键,减少I/O最

16、大化I/O效率存储规划物理设计,CPU 2大杀手 表扫描 排序 Memory命中率可能会骗人DTCC201223,锁的调优(1)应用层,写优秀的SQL语句,创建合适的索引避免表扫描,选择合适的隔离级别:UR,CS(CC),RS,RR 事务尽可能频繁的提交,在事务结尾执行insert/update/delete,DTCC2012,24,锁的调优(2)数据库层 调优locklist 和maxlocks数据库参数 9.1后设为automatic Locktimeout 锁超时参数 OLTP系统,建议设置为15-30秒 DW系统,建议60-120秒 锁延迟设计考虑 db2set DB2_EVALUNC

17、OMMITTED=ON db2set DB2_SKIPDELETED=ON db2set DB2_SKIPINSERTED=ON 9.7 当前已提交读特性(currently committed)数据表维护 Runstats更新统计信息,Reorg重组表和索引碎片 Rebind重新绑定包，更新执行计划,DTCC201225,DTCC2012,写好的SQL语句,只返回需要的行，避免用select*from t1 加过滤条件限制返回的行数,避免笛卡尔乘积，select*from a,b,使用参数化查询，where col1=?，减少编译时间,案例分析,避免对查询条件计算，where salary*

18、2xx 改为salary xx/2,无法利用索引,使用for read only或for fetch only 使用for update of 避免数字类型转换,避免数据类型不匹配,如果可能，尽量避免使用order by和distinct 尽量使用exists而不是用in 函数的效率很高，充分利用,26,DTCC2012,创建索引,索引对于提高SQL读有无可替代作用,提高查询性能,避免不必要的表扫描减少排序、减少锁减少CPU和I/O使用,索引创建最佳实践,为where查询条件、Sort排序(order by、max()、min()等）、join谓词创建索引 分析组合索引键的顺序,(a,b),(

19、b,a)完全不同不要创建冗余索引,(a),(a,b)(a)为冗余索引,确保索引被用到创建cluster 索引,通过include语句创建index-only索引,对于不稳定数据，可考虑用volatile强制走索引推荐使用db2advis建议索引,27,物理设计 海量数据库物理设计,索引多维索引物化视图分区表压缩,数据库分区 数据归档 减少数据，减少I/O等访问DTCC201228,2,3,4,5,议题,1,DB2锁概述DB2锁问题监控和定位DB2锁问题调优DB2 9.7锁机制深入分析DB2锁案例分享,DTCC201229,通过cur_commit数据库参数控制,隔离级别(isolation l

20、evel),隔离级别控制锁的范围和粒度，隔离级别只适用于读,DB2 9.7版本引入了currently committed机制,如果发现未提交的行，则使用当前已经提交的数据(before image)写不阻碍读，减少锁等和死锁，超时等,在locklist锁信息中加入标识，当另外事务读取数据时基于标识判断,DTCC2012,30,Currently Commited机制,第2行和第4行已经提交，日志记录写到TSM带库第3行数据正在更新，日志记录写到活动日志文件第5行数据正在插入，日志记录写到活动日志文件第1行数据正在删除，日志记录在Log buffer内存,当启用了CC机制，DB2会在每个行锁上

21、增加一个标识：,No information：表示记录已经加锁。,Uncommitted insert identifier：表示这行是新插入未提交行。,Log information：表示这行没有提交，包含了当前已落实数据的日志LSN。,DTCC2012,31,Currently Commited性能影响,使用currently committed的tips,如果观察到大量读写锁等时间，使用cc更好 严重读写竞争的应用会有5-10%以上的性能提升 建议调整logbufsz为2048，9.7缺省是256页,DTCC2012,32,2,3,4,5,议题,1,DB2锁概述DB2锁问题监控和定位DB

22、2锁问题调优DB2 9.7锁机制深入分析DB2锁案例分享,DTCC201233,=0,=0,=0,=0,=0,=0,DTCC2012,案例1：某通信行业电子运维系统 问题概述：全省统一的电子运维管理系统 1000多个并发，高峰期业务无法处理 大量锁等，响应慢，CPU使用率80%以上 数据库监控：,First database connect timestamp,=10/10/2011 20:50:28.421301,First database connect timestamp,=11/10/2011 20:09:23.982,Snapshot timestamp,=11/09/2011 1

23、7:19:31.409497,Snapshot timestamp,=12/11/2011 18:32:05.902,Number of Threshold Violations,Number of Threshold Violations,Locks held currentlyLock waitsTime database waited on locks(ms)Lock list memory in use(Bytes)Deadlocks detectedLock escalationsExclusive lock escalationsAgents currently waiting o

24、n locksLock Timeouts,=4=86248=1306986473=2186752=222=0=15560,Locks held currentlyLock waitsTime database waited on locks(ms)Lock list memory in use(Bytes)Deadlocks detectedLock escalationsExclusive lock escalationsAgents currently waiting on locksLock Timeouts,=4=9429=183176718=198742=42=0=2305,解决办法

25、：通过db2pd找到占有锁的SQL语句，优化 调优后，死锁和锁超时数据降低了80%，性能大大提升34,=1,=0,案例2：北京某部委优化项目 问题概述：北京市人口统计信息统一管理系统 平常800个并发，高峰1000多个，高峰期查询需要10分钟 大量锁等，响应慢，CPU利用率低 数据库监控：,First database connect timestampSnapshot timestampLocks held currentlyLock waitsTime database waited on locks(ms)Lock list memory in use(Bytes)Deadlocks d

26、etected,=01/16/2012 19:23:39.036989=02/17/2012 13:45:47.147392=263=2772575=9986324507=49920=4,说明：平均每天锁等达到 90万个平均每个锁等等待 3.6秒累计锁等时间达到 115天,Lock escalationsExclusive lock escalationsAgents currently waiting on locksLock Timeouts,=0=0,锁超时次数为0？,解决办法：调优bufferpool，从1.5G到25G 调优了5条SQL语句 DTCC2012 调优后，性能大幅度提升，

27、最慢的响应时间从10分钟降为秒级 35,=0,DTCC2012,案例3：某银行核心交易死锁问题处理(1)问题概述：核心银行交易系统 12月25日调优后，性能下降严重，死锁数量急剧增加 数据库监控：,First database connect timestampSnapshot timestampLocks held currentlyLock waitsTime database waited on locks(ms)Lock list memory in use(Bytes)Deadlocks detectedLock escalationsExclusive lock escalatio

28、nsAgents currently waiting on locksLock Timeouts,=12/29/2011 00:13:12.846036=12/30/2011 11:50:17.975290=263=62245=10514064=254720=531=0=0=10,说明：1天半时间发生了531个死锁,分析：应用是通过sqlc开发，嵌入式静态语句 创建deadlock event monitor，发现有一条语句存在重大嫌疑 该语句访问的表数据量大概20万行，单条语句执行速度很快，大概2秒36,Table:,VBSRUN,案例3：某银行核心交易死锁问题处理(2)通过db2expln

29、查看执行计划，发现该语句cost高达9645,Statement:DECLARE BIGAMT_CUR CURSORFORSELECT*FROM dpsbigamttransWHERE trandate=:H01057 AND acctno=:H01069 AND subacct=:H01071WITH URStatement Isolation Level=Uncommitted ReadSection Code Page=1386Estimated Cost=9645.297852,Access Table Name=VBSRUN.DPSBIGAMTTRANS ID=3,3337|#Col

30、umns=26|Relation Scan|Prefetch:Eligible|Lock Intents|Table:Intent Share|Row:Next Key Share|Sargable Predicate(s)|#Predicates=3|Return Data to Application|#Columns=26Return Data Completion,Estimated Cardinality=0.000002End of section,解决办法：创建索引RunstatsRebind package锁数量大大降低,Optimizer Plan:RETURN(1)|TBSCAN(2)|DPSBIGAMTTRANSDTCC201237,联系方式,徐明伟,资深DB2顾问（咨询、培训、服务支持）手机：13701141650,Email：qq:907342263,新浪微博：徐明伟_db2咨询,取得成绩：,2012年“IBM DB2迁移之星大赛”冠军团队 2011年，出版DB2数据库管理最佳实践书籍 2011年“IBM DB2十大人物”,2010年“IBM软件技术精英年度成就会员”,DTCC2012,38,DTCC2012,39,