查看: 14547|回复: 37

[精华] 一个由于应用程序逻辑设计的失误导致的性能问题

[复制链接]
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2008-10-25 03:32 | 显示全部楼层 |阅读模式
100pub币
好久没有发贴了,今天比较清闲发一个前两天弄得case。

客户的一个应用程序报告速度过慢。运行了reorgchk发现F2太高,超过了60%竟然,做了reorg以后,几个小时同样的问题继续出现,F2总是在reorg之后一段时间窜上去居高不下,而应用程序的batch job也从正常的30分钟变成了将近两个小时。

客户说,他们的这个表平时每周都会reorg一次,这次不知道怎么回事不停地overflow。

下面是结构

  1. Column                         Type      Type
  2. name                           schema    name               Length   Scale Nulls
  3. ------------------------------ --------- ------------------ -------- ----- ------
  4. REQ_ID                     SYSIBM    INTEGER                   4     0 No
  5. FEED_ID                    SYSIBM    CHARACTER                10     0 No
  6. CORREL_ID                   SYSIBM    CHARACTER                51     0 No
  7. REQ_ISS_TS                  SYSIBM    TIMESTAMP                10     0 No
  8. SYSTEM_ID                 SYSIBM    CHARACTER                 3     0 No
  9. STA_CD                     SYSIBM    CHARACTER                 3     0 No
  10. ISS_CT                     SYSIBM    DECIMAL                   3     0 No
  11. UPDT_TS                    SYSIBM    TIMESTAMP                10     0 No
  12. SERV_ID                        SYSIBM    VARCHAR                  48     0 No
  13. DATA_TX                    SYSIBM    VARCHAR               15000     0 No
  14. RESP_DATA_TX                   SYSIBM    VARCHAR               15000     0 Yes
  15. DATA_IMG                  SYSIBM    BLOB                1048576     0 Yes
  16. ACT_TRK_GRP_ID             SYSIBM    CHARACTER                10     0 Yes
  17. BATCH_NO                       SYSIBM    INTEGER                   4     0 Yes
  18. PRI_POINT_NO               SYSIBM    SMALLINT                  2     0 Yes


  19. TGXPR01                         T8781CUFE_N01      P                           4 +REQ_ID+CORREL_ID+FEED_ID+REQ_ISS_TS
  20. TGXPR01                         T8781_IDX1         D                           1 +CORREL_ID
复制代码

该表类似于一个session table。并发应用程序首先向表里面写入一条数据,然后调用外部的函数得到一些 XML数据然后插入DATA_TX和RESP_DATA_TX中。

而变慢的是一个Stored Procedure。那个SP主要反复调用一个查询:

  1. SELECT DISTINCT T8781.TRK_ID,T8781.FEED_ID,T8781.PRI_POINT_NO
  2. FROM TGXPR01.T8781_REQ_TRK_CUFE T8781
  3. WHERE (T8781.STA_CD = 'RTO' OR T8781STA_CD = 'RQS' OR
  4.         T8781.STA_CD = 'RRJ') AND (T8781.ACT_TRK_GRP_ID = '0' OR
  5.         T8781.ACT_TRK_GRP_ID IS NULL) AND NOT EXISTS
  6.    (SELECT DISTINCT REQ_ID,FEED_ID
  7.    FROM TGXPR01.T8781_REQ_TRK_CUFE
  8.    WHERE REQ_ID = T8781.REQID AND FEED_ID = T8781.FEED_ID
  9.            AND (STA_CD = 'WFR' OR STA_CD = 'RNC' OR STA_CD =
  10.         'RIP'))
  11. ORDER BY PRI_POINT_NO
  12. fetch first 1000 rows only
  13. with UR
复制代码


第一个问题,各位能够想象出来为什么OV会那么高吗?

很显然,对于这个应用程序设计,OV高基本是必然的结果,因为它的工作流程是首先写一条数据,在那两个VARCHAR(15000)上面为空,然后向记录里面update两个很大的string。这样在一个高并发的系统中,一个page可能很快就被几十个很小的记录填满,当应用程序UPDATE数据时,该页则没有可以使用的空间,从而使string overflow到另外的页上。
这点比较好理解。

下一个问题,为什么overflow会对该SQL造成影响?

要知道这条SQL里面并没有调用两列中的任意一列,为什么该查询还会受到影响呢?

所以说,我们要抓什么数据?自然是db2expln了(别忘了这个可是SP,并不是动态查询,所以想到db2exfmt的面壁去)

  1.                                         RETURN
  2.                                         (   1)
  3.                                           |   
  4.                                         UNIQUE
  5.                                         (   2)
  6.                                           |   
  7.                                         NLJOIN
  8.                                         (   3)
  9.                                   /----/      \---\
  10.                             TBSCAN                 FETCH  
  11.                             (   4)                 (   3)
  12.                               |                      |   
  13.                              SORT                  IXSCAN
  14.                             (   5)                 (   3)
  15.                               |                      |   
  16.                             FETCH              Index:        
  17.                             (----)             TGXPR01      
  18.                       /----/      \            T8781CUFE_N01
  19.                 RIDSCN  Table:               
  20.                 (   7)  TGXPR01               
  21.                   |     T8781_REQ_TRK_CUFE
  22.                  SORT  
  23.                 (   8)
  24.                   |   
  25.                 IXSCAN
  26.                 (   9)
  27.                /      \
  28. Index:         Table:               
  29. TGXPR01        TGXPR01               
  30. T8781CUFE_N01  T8781_REQ_TRK_CUFE
复制代码


为什么这个plan会受到OV的影响呢?

首先我们来看一看这个plan是怎样工作的。从左下角开始看,首先一个IXSCAN+SORT+RIDSCN,很明显是list prefetching,也就是拿到了RID以后根据RID排序,然后做prefetch。
prefetch是怎样prefetch的呢?也就是把RID列表中所有的row都要读出来,然后一边读一边对这个结果集作TBSCAN,出来的东西按照ORDER BY PRI_POINT_NO排序一下再TBSCAN,完了以后和另一个IXSCAN做NLJOIN,最后得到结果。
怎么样?看出瓶颈了吗?

很明显瓶颈就是那个PREFETCH嘛。在FETCH每一行的时候,db2都需要找到那一行的所有数据。当OV很多的时候会发生什么呢?
DB2说:给我page 12345
然后OS乖乖地把page 12345给了db2
db2然后在page 里面找到了所要得那一行,但是突然发现,有两个列的数据竟然在别的page上。
这样db2又说了:给我page 00038,然后再给我45678
这样I/O继续乖乖地读了两页出来,db2满意了,继续看下一个RID,但是发现下一行数据竟然还有东西在别的page上……

这样就是为什么即使查询里面没有这两列,OV也会对性能造成负面影响。

好了,既然知道了发生了什么事情,下一个问题就是……
实际上是下两个问题:
1)为什么OV再这一段时间内频繁发生?
2)怎样修复它

不知道大家对1)有什么好的见解。至少俺从db2上看不到任何东西能够给出答案。
根据我们上面谈到的OV的产生原因,很显然DB2自己对OV是不能做任何判断的。它只是接受用户的update请求,当发现page里面没有地方的时候就把数据放到别的page,所以这个答案需要仔细研究应用程序在这段时间来得异常行为。我们在这里不做讨论。

对于2),不知道各位兄台有什么妙招呢?(不一定非要从db2来解决,假设如果你是系统架构师,有权利修改整个框架,你要怎样解决才能做到cost最少最安全呢?同时不要忘了也许有别的查询在这个表上存在类似的问题)

100PB象征性奖励最有价值和创造性的答案
回答时请不要只给出方案,需要给出为什么这样做,有什么好处,有什么缺点(很多方案都是复合方案,这个case很难用一个改变就解决所有问题)。

最佳答案

查看完整内容

You're not caching every LOBs at the same time, are you? My understanding of your case is that the SP is suffering from excessfive overflow, so you don't want those VARCHAR fields to adversely affect the SP. The access to VARCHAR fields is limited.
论坛徽章:
18
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:142012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:522012新春纪念徽章
日期:2012-02-13 15:09:52
发表于 2008-10-25 03:32 | 显示全部楼层
原帖由 wangzhonnew 于 30/10/2008 10:53 发表
恩……下一个关于LOB得问题就是,一般的数据库服务器不会留出很多无用的内存的,这样的话实际上留给OS FS Cache得并不是很多。
这个表本身大概1百到1百五十万左右的数据,如果两列VARCHAR(15000)都转成LOB以后,在理想情况下如果所有数据都驻留FS CACHE则需要20G以上的空闲内存,恐怕一般的生产系统不会有那么多空闲。而且FS CACHE是一个global得,不可能只对于这两个LOB列CACHE,所以那样的话则需要更多的内存……对于这个问题有没有什么解决方法呢?


You're not caching every LOBs at the same time, are you? My understanding of your case is that the SP is suffering from excessfive overflow, so you don't want those VARCHAR fields to adversely affect the SP. The access to VARCHAR fields is limited.

使用道具 举报

回复
招聘 : Linux运维
认证徽章
论坛徽章:
235
紫蜘蛛
日期:2007-09-26 17:05:46玉兔
日期:2007-09-26 17:05:05现任管理团队成员
日期:2011-05-07 01:45:08玉兔
日期:2006-08-29 20:38:48紫蜘蛛
日期:2007-09-26 17:05:34阿斯顿马丁
日期:2013-11-19 10:38:16奔驰
日期:2013-10-16 09:08:58红旗
日期:2014-01-09 11:57:39路虎
日期:2013-08-13 14:52:35林肯
日期:2015-05-19 13:01:16
发表于 2008-10-25 09:31 | 显示全部楼层
很明显OV是由于update 那2个长字段造成的!

看看这张表也有点古怪!2个1500的,还有一个blob!这样的查询能不慢吗!!就算没有ov,速度也不会好到哪儿去的!~!

能不能搞一个附表,专门存这3个大字段?这样更新的操作,就可以用附表的insert来替代!

使用道具 举报

回复
招聘 : Linux运维
认证徽章
论坛徽章:
235
紫蜘蛛
日期:2007-09-26 17:05:46玉兔
日期:2007-09-26 17:05:05现任管理团队成员
日期:2011-05-07 01:45:08玉兔
日期:2006-08-29 20:38:48紫蜘蛛
日期:2007-09-26 17:05:34阿斯顿马丁
日期:2013-11-19 10:38:16奔驰
日期:2013-10-16 09:08:58红旗
日期:2014-01-09 11:57:39路虎
日期:2013-08-13 14:52:35林肯
日期:2015-05-19 13:01:16
发表于 2008-10-25 09:38 | 显示全部楼层
附:那个sql也有点问题吧!狼你也不优化?

not exists 里边有distinct~!?真的必要吗?

还有那3个大字段应该是表T8781_REQ_TRK_CUFE 的吧!

可否做一个索引之类的,或者再弄个include,让查询走索引全扫描如何呢?

使用道具 举报

回复
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2008-10-25 10:12 | 显示全部楼层
原帖由 myfriend2010 于 2008-10-25 10:31 发表
很明显OV是由于update 那2个长字段造成的!

看看这张表也有点古怪!2个1500的,还有一个blob!这样的查询能不慢吗!!就算没有ov,速度也不会好到哪儿去的!~!

能不能搞一个附表,专门存这3个大字段?这样更新的操作,就可以用附表的insert来替代!

呵呵,作新表我到没有想过,不过要知道这个表的PK可是4个column,想想如果要是用join再新表里面查询的话会对性能造成什么样的影响?如果用generated column,同样会有很大的overhead。并且原来insert一个表就好,现在要insert两个,而且对logging得需求加大,会对insert/update/delete造成影响

使用道具 举报

回复
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2008-10-25 10:15 | 显示全部楼层
原帖由 myfriend2010 于 2008-10-25 10:38 发表
附:那个sql也有点问题吧!狼你也不优化?

not exists 里边有distinct~!?真的必要吗?

还有那3个大字段应该是表T8781_REQ_TRK_CUFE 的吧!

可否做一个索引之类的,或者再弄个include,让查询走索引全扫描如何呢?

优化器同样会优化吧。

索引是好主意,你能说说哪几列加什么索引呀?(别用db2advis哦)

不过就算这个sql弄好了,其他的sql可能还会发生类似的问题哦……

使用道具 举报

回复
招聘 : Linux运维
认证徽章
论坛徽章:
235
紫蜘蛛
日期:2007-09-26 17:05:46玉兔
日期:2007-09-26 17:05:05现任管理团队成员
日期:2011-05-07 01:45:08玉兔
日期:2006-08-29 20:38:48紫蜘蛛
日期:2007-09-26 17:05:34阿斯顿马丁
日期:2013-11-19 10:38:16奔驰
日期:2013-10-16 09:08:58红旗
日期:2014-01-09 11:57:39路虎
日期:2013-08-13 14:52:35林肯
日期:2015-05-19 13:01:16
发表于 2008-10-25 11:08 | 显示全部楼层
原帖由 wangzhonnew 于 2008-10-25 10:12 发表

呵呵,作新表我到没有想过,不过要知道这个表的PK可是4个column,想想如果要是用join再新表里面查询的话会对性能造成什么样的影响?如果用generated column,同样会有很大的overhead。并且原来insert一个表就好,现在要insert两个,而且对logging得需求加大,会对insert/update/delete造成影响


原来1个insert ,1个update不是吗?

现在搞成1个insert+1个insert!insert日志要小得多吧!

使用道具 举报

回复
招聘 : Linux运维
认证徽章
论坛徽章:
235
紫蜘蛛
日期:2007-09-26 17:05:46玉兔
日期:2007-09-26 17:05:05现任管理团队成员
日期:2011-05-07 01:45:08玉兔
日期:2006-08-29 20:38:48紫蜘蛛
日期:2007-09-26 17:05:34阿斯顿马丁
日期:2013-11-19 10:38:16奔驰
日期:2013-10-16 09:08:58红旗
日期:2014-01-09 11:57:39路虎
日期:2013-08-13 14:52:35林肯
日期:2015-05-19 13:01:16
发表于 2008-10-25 11:10 | 显示全部楼层
原帖由 wangzhonnew 于 2008-10-25 10:15 发表

优化器同样会优化吧。

索引是好主意,你能说说哪几列加什么索引呀?(别用db2advis哦)

不过就算这个sql弄好了,其他的sql可能还会发生类似的问题哦……


如果sql多了,那还是找共性吧!在常使用的列上搞index了~!

使用道具 举报

回复
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2008-10-25 11:18 | 显示全部楼层
原帖由 myfriend2010 于 2008-10-25 12:08 发表


原来1个insert ,1个update不是吗?

现在搞成1个insert+1个insert!insert日志要小得多吧!

哪里是一个insert一个update啊,并发的程序哦。
原来的一个insert现在要两个,也就是logging 得 i/o增加一倍。别忘了这个i/o可是同步i/o,会对性能造成很大影响

使用道具 举报

回复
招聘 : c/c++研发
认证徽章
论坛徽章:
45
技术图书徽章
日期:2014-03-10 14:09:192012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-02-13 15:12:092012新春纪念徽章
日期:2012-01-04 11:51:22ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15现任管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:152011新春纪念徽章
日期:2011-01-25 15:41:50
发表于 2008-10-25 11:21 | 显示全部楼层
原帖由 myfriend2010 于 2008-10-25 12:10 发表


如果sql多了,那还是找共性吧!在常使用的列上搞index了~!

这个无法保证所有的查询都能够避免table fetch哦

俺先说一个方案,看看优点缺点都有什么
把VARCHAR换成CLOB

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表