过程优化方案报告

《过程优化方案报告》由会员分享，可在线阅读，更多相关《过程优化方案报告（25页珍藏版）》请在装配图网上搜索。

1、优化方案报告中国惠普有限公司北京市朝阳区建国路112号邮编：100022总机：（86-10） 65643888ent一、弓 I言31.1. 编写目的31.2. 术语定义3二、第一阶段32.1测试环境42.2. TUXEDO 及 INFROMIX 静态检查42.2.1应用服务器静态检查42.2.2数据库服务器静态检查52.3缺陷发现52.4问题定位及优化方案72.4.1 应用服务器问题定位及优化方案72.4.2 informix 数据库、应用 问题定位及优化方案82.4.3程序问题定位及优化方案错误！未定义书签。三、第二阶段143.1 TUXEDO 及 INFORMIX 静态检查143.1.1应

2、用服务器静态检查143.2.1数据库服务器静态检查143.2缺陷发现错误！未定义书签。3.4问题定位及优化方案193.4.1应用服务器问题定位及优化方案193.4.2 infOrmix 数据库、应用 问题定位及优化方案193.4.3程序问题定位错误！未定义书签。3.4.4应用及业务处理方式问题定位及优化方案23一、引言1.1. 编写目的本文档描述系统在性能测试诊断调优过程中涉及到的各阶段调优方案记录报告;1.2. 术语定义在本测试方案中，以下缩略语与术语有其特定含义： HP：指中国惠普有限公司惠普：指中国惠普有限公司 LR： 扌旨 Loadrunner二、第一阶段这一阶段是账管系统性能测试的开

3、始阶段，这一阶段主要是基准测试执行和首次的阶段性测试；目的 是为了对默认部署环境下的系统做出全面的性能评估，提出优化方案以最大化利用当前测试环境资源。批业务处理场景、稳定性测试场景等均在此优化基础上进行测试;2.1测试环境编号测试主机名称主机地址硬件配置数量基础软件应用软件1账管系统应用服务 器10.24.225.58C*32G1HP-UXTuxedo8.12账管系统数据库服 务器10.24.225.58C*32G1HP-UXInformix9.422tuxedo及infromix静态检查221应用服务器静态检査版本：tuxedo8.1IPCKEYMASTERUIDGID32803EAPS_H

4、P1121060666MAXACCESSERS MAXACLGROUPS MAXGTT DOMAINID MAXGROUPS MAXNETGROUPS MAXMACHINES MAXQUEUES MAXDRT 4 MAXRFT 2 MAXRTDATA MAXSPDATA MAXSERVERS MAXSERVICES MAXCONV MODEL LDBAL CMTRET MAXBUFTYPE MAXBUFSTYPE SCANUNIT SANITYSCAN DBBLWAIT BBLQUERY75016384100TDOMBIZ0000001008256100046010001000064SHMYC

5、OMPLETE163210122PERMBLOCKTIME6NOTIFYDIPINSYSTEM_ACCESS FASTPATHMAXINTERFACES 150MAXOBJECTS 1000SIGNATURE_AHEAD 3600SIGNATURE_BEHIND604800USIGNALSIGUSR2详细见服务器配置文件 版本：9.4 Dbspace 分布：IBM Informix Dynamic Server Version 9.40.FC9 862660 KbytesBlocked:CKPTDbspacesaddressnumberflagsfchunkc0000000482514a0 1

6、0x11c00000004938cb40 20x12C000000049391030 30x400013c0000000493914a040x420014c00000004939191050x4000155 active, 2047 maximumChunksaddresschunk/dbsoffsetsizec000000048251910 1120500000c00000004938c1802220512000c00000004938c320 33202048000c00000004938c4c0 4402048000c00000004938c660 552010240000c000000

7、04938c800 652010240000c00000004938c9a0 7520102400007 active, 32766 maximum2.2.2数据库服务器静态检査-On-Line (Prim) (CKPT REQ) - Up 6 days 23:56:38 -nchunks flagsownername1Ninformix rootdbs1Ninformix phydbs1NBinformix logdbs1N TBinformix tmpdbs3NBinformix eapsdbsfreebpagesflags pathname497305PO- ./dbschks/root

8、chk16947PO- ./dbschks/phychk47947PO-B ./dbschks/logchk2047375PO-B ./dbschks/tmpchk2243458PO-B ./dbschks/eapschk0110239997PO-B ./dbschks/eapschk0210239997PO-B ./dbschks/eapschk03Expanded chunk capacity mode: enabled2.3缺陷发现用户混合业务测试场景在有批单业务处理（续期6万批单）的情况下对其它事务交易响应时间的是很大的，具体看如下的测试结果:Transaction NameAvera

9、ge （不带批单）Average（带批单）保留账户5 709122 404保留账户保存_030.850.903保留账户导入_021.5491.583保留账户提交040.887117.436保留账户_查询_012.1832.243信息变更10.35121.253信息变更保存_031.232110.506信息变更导入026.0315.964信息变更_提交_040.8882.598信息变更_查询_012.1992.184公共缴费577117 289公共缴费保存_031.13631.155公共缴费导入020.7450.754公共缴费提交041.08882.53公共缴费_查询_012.8012.849增

10、加人员9.588192.123增加人员_保存确认_040.33646.427增加人员打开文件070.2380.504增加人员_批改流水号确认_050.2860.277增加人员_点击保存_030.1180.066增加人员点击增加人员菜单011.4321.433增加人员_点击提交_111.02980.226增加人员_点击清单导入_060.2370.239增加人员_生成校验文件_083.343.362增加人员确认090.56620.32增加人员确认数量100.65337.913增加人员_计划查询_021.3491.354登录2 9983006续期缴费67298727续期缴费保存流水号确认050.27

11、21.597续期缴费保存确认040.33823.6续期缴费_点击保存_030.0680.068续期缴费_点击打开文件_070.23715.479续期缴费一点击提交_111 123113 362续期缴费点击清单导入060.2380.237续期缴费一点击生成校验文件_080 31231 535续期缴费一点击确定_090 69256 989续期缴费点击续期缴费菜单011.5761.436续期缴费一确定导入_1004853.041续期缴费计划号查询021.3641.383退休一次性领取5 35576 284退休一次性领取保存批改流水号确认05 0.2760.312退休一次性领取一提交_060 8392

12、2 271退休一次性领取一提交流水号确认_070 30622 682退休一次性领取一点击保存_040 57927 935退休一次性领取_点击退休一次性领取_011.4341.444退休一次性领取计划号查询_020.7910.758退休一次性领取_计划证明号查询_031.130.882账户间资产转移：Transaction NameAverage （批单）6万批单导入校验223.1726万批单导入确定1,298.4786万批单续期1,526.165系统资源：系统CPU资源利用率较低，内存资源保持在30%左右；系统资源瓶颈体现在数据库服务器磁盘I/O繁忙100%，磁盘的I/O资源争用集中在单个个磁

13、盘; 磁盘问题：AIO global files:gfd pathnametotalops dskread dskwrite io/s3 rootchk5937148844490.04 ./dbschks/phychk7712677060.05 ./dbschks/logchk370195851522850430.66 ./dbschks/tmpchk7577625155506210.17 eapschkOI87811307105444167568614.58 eapschk022200.09 eapschk032200.0缺陷：可以发现批单业务运行的情况下，其它短事务响应时间受到的影响是很大

14、的，可以人为系统主要性能问题主要在大数据量操作方面，主要问题体现在磁盘I/O方面，从以上监控结果可以看出；2.4问题定位及优化方案241应用服务器问题定位及优化方案2.4.1.1调节中间件tuxedo服务配置参数调整Edr、Edr2服务建议调整为最少10个服务，min=10, max=12 连接池:调整:XAGRP1 LMID=EAPS HP GRPNO=1OPENINFO=INFORMIX-OnLin e:ea nntTMSNAME=TMS INFENVFILE=/eaps/eapsapp/tran s/install/bize nvTMSC0UNT=2改为:TMSCOUNT=12增加数据库

15、链接进程，提高并发处理能力;2.4.1.2优化应用log日志的输出这种日志级别会造成大量的log，产生I/O问题，从而影响性能； 调试日志级别打开，建议将调试日志级别改为生产运行的日志级别，避免过多的日志输出; TLOG建议使用裸设备2.4.2informix数据库、应用问题定位及优化方案2.4.2.1 Onconfig 配置修改onconfig文件，调整如下参数：Buffers： 600000改为：1000000Z/增加缓冲池大小，减少磁盘0NETTYPE soctcp,2,150,NET改为：soctcp,3,300,/增加轮询线索以增加并发能力；NUMCPUVPS2改为：7 /为 cpu

16、 个数-1LTXHWM70改为：80LTXEHWM80改为：90 保证长事务回滚期间系统的并发处理能力 OPTCOMPIND2改为基于索引的优化策略# Number of user (cpu) vps# Number of LRU queuesNUMCPUVPS改为：7LRUS改为：72.4.2.2数据库数据文件10平衡问题（逻辑卷和卷组及PV布局）：/bmc(/dev/vgOO/lvbmc):41 % used blocks/eaps/eapsapp(/dev/vgeaps/lv_eapsapp):68 % used blocks/eaps/eapsdba(/dev/vgeaps/lv_ea

17、psdba):38 % used blocks/eaps/eapshdr(/dev/vgeaps/lv_eapshdr):5 % used blocks/eaps/eapsprt(/dev/vgprt/lv_eapsprt ):6 % used blocks/home(/dev/vg00/lvol5):1 % used blocks/informix(/dev/vgOO/lvinformix ):64 % used blocks/opt(/dev/vg00/lvol6):56 % used blocks/tmp(/dev/vg00/lvol4):14 % used blocks/tuxedo(

18、/dev/vg00/lvtuxedo):21 % used blocks/usr(/dev/vg00/lvol7):50 % used blocks/var(/dev/vg00/lvol8):14 % used blocks/stand(/dev/vg00/lvol1):18 % used blocks/(/dev/vg00/lvol3):25 % used blocks系统使用：LV Name/dev/vg00/lvol1LV Statusavailable/syncdLV Size (Mbytes)1792Current LE56Allocated PE56Used PVInformix

19、使用：LV Name/dev/vg00/lvinformixLV Statusavailable/syncdLV Size (Mbytes)4096Current LE128Allocated PE128Used PV1Tuxedo 使用：LV Name/dev/vg00/lvtuxedoLV Statusavailable/syncdLV Size (Mbytes)2048Current LE64Allocated PE64Used PV1vg00所使用物理硬盘:-Physical volumes -PV NamePV Status/dev/disk/disk1_p2 availableTo

20、tal PE4343Free PE1756AutoswitchOnProactive PollingOn通过以上信息可以了解到，系统、数据库、应用中间件均安装在同一VG下的同一 PV上，这就 造成I/O热块，I/O未能均衡分布； 数据库informix dbspace物理布局:Dbspacesnumberaddress c0000000482514a0 1 c00000004938cb40 2C000000049391030 3 c0000000493914a04C0000000493919105addresschunk/dbsC000000048251910 1pathname./dbsch

21、ks/rootchkc00000004938c1802c00000004938c320 3c00000004938c4c0 4c00000004938c660 5c00000004938c800 6c00000004938c9a0 7./dbschks/phychk ./dbschks/logchk ./dbschks/tmpchk ./dbschks/eapschk01 ./dbschks/eapschk02 ./dbschks/eapschk03eapschk01 - /dev/vgdata/rlveapschkdbs01 eapschk02 - /dev/vgdata/rlveapsch

22、kdbs02 eapschk03 - /dev/vgdata/rlveapschkdbs03 logchk - /dev/vgdata/rlveapslogdbs phychk - /dev/vgdata/rlveapsphydbs rootchk - /dev/vgdata/rlveapsrootdbs tmpchk - /dev/vgdata/rlveapstmpdbs-Logical volumes -LV Name/dev/vgdata/lveapsrootdbsUsed PV1LV Name/dev/vgdata/lveapsphydbsUsed PV1LV Name/dev/vgd

23、ata/lveapslogdbsUsed PV1LV Name/dev/vgdata/lveapstmpdbsUsed PV1LV Name/dev/vgdata/lveapschkdbs01Used PV1LV Name/dev/vgdata/lveapschkdbs02Used PV1LV Name/dev/vgdata/lveapschkdbs03Used PV1LV Name/dev/vgdata/lvsecrootdbsUsed PV1LV Name/dev/vgdata/lvsecphydbsUsed PV2LV Name/dev/vgdata/lvseclogdbsUsed PV

24、1LV Name/dev/vgdata/lvsectmpdbsUsed PV1LV Name/dev/vgdata/lvsecchkdbs01Used PV1LV Name/dev/vgdata/lvsecchkdbs02Used PV1LV Name/dev/vgdata/lvsecchkdbs03Used PV1-Physical volumes -PV Name/dev/dsk/c5t0d0PV Name/dev/dsk/c5t0d1PV Name/dev/dsk/c5t0d2PV Name/dev/dsk/c5t0d3PV Name/dev/dsk/c5t0d4PV Name/dev/

25、dsk/c5t0d5通过以上信息可以了解到数据库的dbspace分布，数据文件lvsecchkdbsOl、lvsecchkdbs02、 lvsecchkdbs02都放在同一个pv上，这就造成严重I/O争用；解决方案：可以将目前账管系统所能使用的磁盘资源统一规划，建议根据业务数据的特点将数据库的dbspace的 数据文件均衡划分在多个磁盘上，以最大化平衡磁盘的I/O争用；针对informix下列是在实际运行环境中有效地分配磁盘的一般目标：1、最大化平衡磁盘的I/O争用；重点逻辑日志和物理日志移动到独立分区，一定与其它dbspace分开，建议将数据库的rootdbs、logdbs、 tmpdbs和

26、datadbs放到不同pv上平衡i/o争用；2、隔离咼使用表；将高I/O活动频率的表放到只用于该表的磁盘设备上(pv)；3、考虑备份与恢复性能；从管理者的角度来说，尽可能考虑在性能最优化与高可用性及恢复最优化之间寻取可接受的平衡方案；4.2.2.3 Sql、存储过程应用优化优化sql，在运行过程中随机抓取sql来看，部分表显然没建立索引 例如：1、QUERY:Select * FROM T_PlcBank A WHERE AplNo = 2008110000A07600000010 AND ( EndDate = 0 OR EndDate is NULL )AND Status = 03Est

27、imated Cost: 1Estimated # of Rows Returned: 11) informixa SEQUENTIAL SCANFilters: (informix.a.status = 03 AND informix.a.aplno = 2008110000A07600000010 ) AND(informix.a.enddate = 1899/12/31 OR informix.a.enddate IS NULL )建议针对条件建立索引；2、QUERY:Current SQL statement :SELECT COUNT ( * ) FROM T_PlcInvPack

28、WHERE PlcNo = ? AND Status = 03AND AnntAcctCode 1 , 1 = ?建议：对表T_PlcInvPack锁模式改为行间锁，同时调整索引3、QUERY:SELECT distinct PlcNo,EdrType FROM T_EdrPsnLstWHERE PlcNo = 99000009001500000010AND PlcNo = 99000009001500005080AND EdrNo IN (SELECT EdrNo FROM T_EdrBaseWHERE GrPlcNo = 990000090015 ANDStatus IN (01,02,0

29、5,11,77)AND EdrType IN(SELECT SUBSTR(MutexEdrType,0,2)FROM T_EdrExclusive WHERE TrigEdrType = 09AND MutexLevel = 2) AND EdrNo 2010110000000007070 ) ORDER BY PlcNoEstimated Cost: 47Estimated # of Rows Returned: 1Temporary Files Required For: Order By1) eapsapp.t_edrpsnlst: INDEX PATH(1) Index Keys: e

30、drno plcno (Serial, fragments: ALL)Lower Index Filter: (eapsapp.t_edrpsnlst.edrno = ANY AND eapsapp.t_edrpsnlst.plcno =99000009001500000010)Upper Index Filter: eapsapp.t_edrpsnlst.plcno = 99000009001500005080Subquery:Estimated Cost: 42Estimated # of Rows Returned: 11) eapsapp.t_edrbase: INDEX PATHFi

31、lters: (eapsapp.t_edrbase.edrtype = ANY AND eapsapp.t_edrbase.status IN (01 , 02 , 05 , 11, 77 ) AND eapsapp.t_edrbase.edmo != 2010110000000007070)(1) Index Keys: grplcno (Serial, fragments: ALL)Lower Index Filter: eapsapp.t_edrbase.grplcno = 990000090015Subquery:Estimated Cost: 2建议：SQL过于复杂，请参照执行计划进

32、行优化；4、SQLINSERT INTO T_EdrPsnLst （ EdrNo , CustNo , EdrType , PlcNo , Name , Sex ,EdrContent , FundCalcType , BlockNo ） VALUES （ ? , ? , ? , ? , ? , ? , ?,? , ?）建议：1）监控到该sql在频繁写数据库，造成多次与数据库交互，建议：2）改变流程，先将数据写临时文件或临时表,在交易结束统一通过load指令或insert into。select* from temp_tbl将数据批量写数据库3）改 T_EdrPsnLst 为行锁5、SQL :

33、SELECT A, A.AplNo, A.GrPlcNo, A.AnntAcctCode, A.PlcNo, A.CustNo,A.IsFirst, A.EdrNo, A.IsSum, A.FundTimes, A.MngFeeMode, A.MngDrawType,A.EntpFeeWay, A.PsnFeeWay, A.FundAmt, A.FundPerc, A.FundType,A.MngFeeRate, A.MngFee, A.MngFeePayForOther, A.NetFund, A.FundDate, A.CalcDate, A.CreateDate, A.CreateTim

34、e, A.Status, A.BlockNo FROMT_AnntAcctFund A WHERE 1 =1 AND EdrNo = 2010110000000007070 ORDER BY CustNo,PlcNo,AnntAcctCode建议：1）修改T_AnntAcctFund锁模式为行锁;2）删除语句中1=1;3）根据order by后面的字段调整索引；三、第二阶段3.1 tuxedo 及 informix 静态检查主要针对第一阶段来说，优化的参数和配置做一个检查；311 应用服务器静态检査3211连接池检査*GROUPSXAGRP1 LMID=EAPS_HP GRPNO=1 OPEN

35、INFO=INFORMIX-OnLine:eannt TMSNAME=TMS_INFENVFILE=/eaps/eapsapp/trans/install/bizenvTMSCOUNT=123212服务线程检查Edr SRVGRP=XAGRP1 SRVID=60CLOPT=-t -ARQADDR=Queue6RQPERM=0660 REPLYQ=Y RPPERM=0666 MIN=10 MAX=12 CONV=NSYSTEM_ACCESS=FASTPATHMAXGEN=100 GRACE=0 RESTART=YMINDISPATCHTHREADS=0 MAXDISPATCHTHREADS=1

36、THREADSTACKSIZE=OSICACHEENTRIESMAX=500Edr2 SRVGRP=XAGRP1 SRVID=90CLOPT=-t -ARQADDR=Queue9RQPERM=0660 REPLYQ=Y RPPERM=0666 MIN=10 MAX=12 CONV=NSYSTEM_ACCESS=FASTPATHMAXGEN=100 GRACE=0 RESTART=YMINDISPATCHTHREADS=0 MAXDISPATCHTHREADS=1 THREADSTACKSIZE=0SICACHEENTRIESMAX=5003.2.2 数据库服务器静态检査3221配置参数检査BU

37、FFERS1000000# Maximum number of shared buffersNUMAIOVPS5# Number of IO vpsNETTYPELTXHWMLTXEHWM90soctcp,3,300,NET # Configure poll thread(s) for nettype80OPTCOMPINDNUMCPUVPS# To hint the optimizer# Number of user (cpu) vps3222 Informix数据库dbspace及存储物理布局-Logical volumes -LV Name/dev/datavg/lv2g_01VG Na

38、me/dev/datavgLV Permissionread/writeLV Statusavailable/syncdMirror copies0Consistency RecoveryMWCSchedulestripedLV Size (Mbytes)2048Current LE32Allocated PE32Stripes8Stripe Size (Kbytes)1024Bad blockonAllocationstrictIO Timeout (Seconds)defaultDistribution of logical volumePV NameLE on PV PE on PV20

39、 Nov 25 17:39 rootchk - /dev/datavg/rlv2g_01/dev/disk/disk3244/dev/disk/disk3344均采用裸设备方式Rootbs （informix 系统表空间）： lrwxr-x- 1 eapsdba informix 对应的逻辑卷及PV情况：/dev/disk/disk34 /dev/disk/disk35 /dev/disk/disk36 /dev/disk/disk37 /dev/disk/disk38/dev/disk/disk39Logdbs (日志表空间)：lrwxr-x-1 eapsdbalrwxr-x-1 eapsd

40、ba对应的逻辑卷及PV情况:informixinformix20 Nov 25 17:44 logchk - /dev/datavg/rlv4g_0220 Nov 25 17:44 phychk - /dev/datavg/rlv4g_01-Logical volumes -LV Name/dev/datavg/lv4g_02VG Name/dev/datavgLV Permissionread/writeLV Statusavailable/syncdMirror copies0Consistency RecoveryMWCSchedulestripedLV Size (Mbytes)409

41、6Current LE64Allocated PE64Stripes8Stripe Size (Kbytes)1024Bad blockonAllocationstrictIO Timeout (Seconds)default-Distribution of logical volume -PV NameLE on PV PE on PV/dev/disk/disk3288/dev/disk/disk3388/dev/disk/disk3488/dev/disk/disk3588/dev/disk/disk3688/dev/disk/disk3788/dev/disk/disk3888/dev

42、/disk/disk3988-Logical volumes -LV Name/dev/datavg/lv4g_01VG Name/dev/datavgLV Permissionread/writeLV Statusavailable/syncdMirror copies0Consistency RecoveryMWCSchedulestripedLV Size (Mbytes)4096Current LE64Allocated PE64Stripes8Stripe Size (Kbytes)1024Bad blockonAllocationstrictIO Timeout (Seconds)

43、default16-Distribution of logical volume -PV NameLE onPV PE on PV/dev/disk/disk3288/dev/disk/disk3388/dev/disk/disk3488/dev/disk/disk3588/dev/disk/disk3688/dev/disk/disk3788/dev/disk/disk3888/dev/disk/disk3988lrwxr-x-1 eapsdbainformixlrwxr-x-1 eapsdbainformixlrwxr-x-1 eapsdbainformixlrwxr-x-1 eapsdb

44、ainformixlrwxr-x-1 eapsdbainformixlrwxr-x-1 eapsdbainformixlrwxr-x-1 eapsdbainformixlrwxr-x-1 eapsdbainformixlrwxr-x-1 eapsdbainformixlrwxr-x-1 eapsdbainformixlrwxr-x-1 eapsdbainformixlrwxr-x-1 eapsdbainformixlrwxr-x-1 eapsdbainformixDatadbs （数据表空间）：21 Nov 25 17:44 tmpchk - /dev/datavg/rlvl0g_0121 N

45、ov 25 17:44 eapschkOl - /dev/datavg/rlv10g_0221 Nov 25 17:44 eapschk02 - /dev/datavg/rlv10g_0321 Nov 25 17:44 eapschk03 - /dev/datavg/rlv10g_0421 Nov 25 17:44 eapschk04 - /dev/datavg/rlv10g_0521 Nov 25 17:45 eapschk05 - /dev/datavg/rlv10g_0621 Nov 25 17:45 eapschk06 - /dev/datavg/rlv10g_0721 Nov 25

46、17:45 eapschk07 - /dev/datavg/rlv10g_0821 Nov 25 17:45 eapschk08 - /dev/datavg/rlv10g_0921 Nov 30 09:44 eapschk09 - /dev/datavg/rlv10g_1021 Nov 30 09:44 eapschk10 - /dev/datavg/rlv10g_1121 Nov 30 09:44 eapschk11 - /dev/datavg/rlv10g_1221 Nov 30 09:44 eapschk12 - /dev/datavg/rlv10g_13对应的逻辑卷及PV情况： tmp

47、chk - /dev/datavg/rlv10g_01 -Logical volumes -/dev/datavg/lv10g_01/dev/datavg read/write available/syncdLV NameVG NameLV PermissionLV StatusMirror copiesConsistency RecoveryScheduleLV Size (Mbytes)Current LEAllocated PEStripesStripe Size (Kbytes)Bad blockAllocation0MWC striped1024016016081024onstric

48、tIO Timeout (Seconds)defaultPV Name/dev/disk/disk3220/dev/disk/disk3320/dev/disk/disk3420/dev/disk/disk3520/dev/disk/disk3620/dev/disk/disk3720/dev/disk/disk3820/dev/disk/disk3920/dev/datavg/rlvl0g_02 -Logical volumes -LV NameVG NameLV PermissionLV StatusMirror copiesConsistency Recovery ScheduleLV

49、Size (Mbytes) Current LEAllocated PEStripesStripe Size (Kbytes) Bad blockAllocationIO Timeout (Seconds)-Distribution of logical volume -LE on PV PE on PV2020202020202020/dev/datavg/lv10g_02/dev/datavg read/write available/syncd0MWC striped1024016016081024onstrictdefaultPV NameLE on PV/dev/disk/disk3

50、22020/dev/disk/disk332020/dev/disk/disk342020/dev/disk/disk352020/dev/disk/disk362020/dev/disk/disk372020/dev/disk/disk382020/dev/disk/disk392020-Distribution of logical volume -PE on PV3.4问题定位及优化方案341应用服务器问题定位及优化方案3411配置参数连接池、线程池可根据今后监控服务队列来调整，原则是配置连接池和线程数使得工作队列较小;3.4.2 informix数据库、应用问题定位及优化方案3.4.2

51、1 Onconfig配置参数 Buffers：根据需要可以调整为物理内存的20-50%左右； SHMVIRTSIZE1048576 SHMADD524288 LRU_MAX_DIRTY 70.000000 LRU_MIN_DIRTY 50.000000 使用 SQL 语句缓存(SSC,SQL Statement Cache)功能STMT_CACHE 1或运行SQL命令：on mode e en able以激活SSC功能。用户使用前还必须定义环境变量STMT_CACHEexport STMT_CACHE = 1或运行 SQL 命令：set statement cache on经过以上设置后，所有

52、的查询都将充分基于SSC进行高效处理。3.4.22数据库表空间及chunk布局目前的布局依然存在不合理地方，首阶段优化并未完全按照建议调整informix的dbspace划分，建议 划分原则如下： 总体表空间布局原则： Rootdbs、logdbs、datadbs三种表空间分别放在不同的pv上; Rootdbs、logdbs可放在不同单独pv上； Datadbs可以均衡放在其余的所有pv 上;数据库表存放优化原则:将非常大的数据库表单独存放在某个dbspace上，同时根据业务情况对table做分区处理;3.4.23 Sql、存储过程问题1、第一个sqlselect OwnInvUnits,In

53、vBlnc, invfundcode ,status fromT_InvAnntAcct-where-invfundcode = 00000057into temp t_invacc_tmp解决方案：create index idx_t_inv_tmp on t_invacc_tmp(invfundcode ,status); update statistics for table t_invacc_tmp;SELECT NVL ( SUM ( OwnInvUnits ) , 0 ) , NVL ( SUM ( InvBlnc ) , 0 ) FROM t_invacc_tmp WHEREin

54、vfundcode=00000057ndStatus = 032、第二个sqlSELECT Count ( * ) FROM T_InvAnntAcctWHERE InvFundCode = 00000051 AND Status =03AND (LastPublishNo 2009110000000000000110 OR LastPublishNo Is Null)解决方案：可以一次性将T_InvAnntAcct所需统计信息在第一步中处理完成(零时表中得到) 同于一问题的解决方案；3、计价批处理存储过程pinvdeal 中 select 及 update 执行计划：QUERY:UPDATE

55、 T_AnntBlncSET Balance = 10000000,InvBlnc = 20000000,ModifyDate = 20080909WHERE GrPlcNo = 00000051AND CustNo = 000061AND AnntAcctCode = 02Estimated Cost: 39Estimated # of Rows Returned: 531) eapsapp.t_anntblnc: INDEX PATH(1) Index Keys: grplcno custno plcno anntacctcode (Key-First) (Serial, fragment

56、s: ALL)Lower Index Filter: (eapsapp.t_anntblnc.custno = 000061 AND eapsapp.t_anntblnc.grplcno = 00000051)Index Key Filters: (eapsapp.t_anntblnc.anntacctcode = 02)QUERY: select*FROM T_InvAnntAcctWHERE InvFundCode = 000051 ANDStatus=O3 AND(LastPublishNo 000051 OR LastPublishNo Is Null)Estimated Cost: 2840876Estimated # of Rows Returned: 3401381) eapsapp.t_invanntacct: INDEX PATHFilters: (eapsapp.t_invanntacct.status = 03 AND (eapsapp.t_invanntacc