Re: Very Severe Performance Problems on my 7.22 UC2

In article <memo.19971026023519.4311A@bew.compulink.co.uk>, Brian Wilkins <bew@cix.co.uk> writes >In article <62lp3v$l86@cssun.mathcs.emory.edu>, >sujata_soman_at_omm-la2-infotech-001@internet.omm.com () wrote: > >> >> Hi everybody, >> I have been having some severe performance problems on my >> development box in the last couple of weeks, and it is obviously >> something I have changed !! If only I know what and why. Some >> processess went from 2.5 hrs to over 8hrs !! By the way, we did hav >> e a >> big database changes. I am hence running update statistics high on >> the >> entire database. This process normally takes about 21 hrs, but it >> looks like it will take over a couple of days this time !! >> OK some details:- >> >> Informix 7.22 UC2 on IBM AIX ver 4.1 >> Single CPU m/c >> >> >> Here is a copy of my onconfig, at least the portion of which I >> tried to fiddle with ! >> >> >> MULTIPROCESSOR 0 >> NUMCPUVPS 1 >> SINGLE_CPU_VP 1 >> >> NOAGE 1 >> AFF_SPROC 0 >> AFF_NPROCS 0>> >> # Shared Memory Parameters >> >> LOCKS 200000 >> BUFFERS 10000 >> NUMAIOVPS 1 >> PHYSBUFF 128 >> LOGBUFF 96 >> LOGSMAX 50 >> CLEANERS 16 >> SHMBASE 0x30000000 >> SHMVIRTSIZE 50000 >> SHMADD 8192 >> SHMTOTAL 0 >> CKPTINTVL 300 >> LRUS 12 >> LRU_MAX_DIRTY 10 >> LRU_MIN_DIRTY 5 >> LTXHWM 50 >> LTXEHWM 60 >> TXTIMEOUT 0x12c >> STACKSIZE 32 >> >> OPTCOMPIND 2 OPTCOMPIND=2 thus favouring sequential scans, >> ONDBSPACEDOWN 0 >> LBU_PRESERVE 1 >> OPCACHEMAX 128 >> NETTYPE ipcshm,1,100,CPU >> NETTYPE soctcp,2,50,NET>> >> CDR_LOGBUFFERS 2048 # size of log reading buffer pool >> (Kbytes) >> CDR_EVALTHREADS 1,1 # evaluator threads >> (per-cpu-vp,additional) >> CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) >> CDR_QUEUEMEM 4096 # Maximum amount of memory for any>> CDR >> queue (Kb >> ytes) >> BAR_ACT_LOG /tmp/bar_act.log >> BAR_MAX_BACKUP 0 >> BAR_RETRY 1 >> BAR_NB_XPORT_COUNT 10 >> BAR_XFER_BUF_SIZE 31 >> HETERO_COMMIT 0>> >> I tried playing with NETTYPE, LRUS, CLEANERS and LRU dirty >> parameters. I restored everything except the NETTYPE, and the respo >> nse >> was still pathetic >> Right now, all that is going on is my update statistics high and >> >> two application programs. >> My CPU is 0% idle. Running TOP shows my oninit using 95% CPU tim >> e >> No other processess are running on this box. >> >> >> Here are some outputs ( onstat ) >> >> onstat -g glo>> >> INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 16:09:22 -- 105 >> 936 >> Kbytes >> >> MT global info: >> sessions threads vps lngspins >> 3 34 9 0 >> >> sched calls thread switches yield 0 yield n yield >> >> forever >> total: 22194480 20280590 5910593 142601 11231 >> 972 >> per sec: 110 109 26 2 54 >> >> >> Virtual processor summary: >> class vps usercpu syscpu total >> cpu 1 41278.76 1893.04 43171.80 Lots of CPU VP usage (as you would expect sequential scanning a large table and doing a Dynamic Hash Join. >> aio 2 2.15 6.19 8.34 >> lio 1 2.11 6.06 8.17 >> pio 1 1.92 5.50 7.42 >> adm 1 2.95 4.60 7.55 >> soc 2 6.90 16.57 23.47 >> msc 1 0.07 0.01 0.08 >> total 9 41294.86 1931.97 43226.83 >> >> Individual virtual processors: >> vp pid class usercpu syscpu To >> tal >> 1 14244 cpu 41278.76 1893.04 43171 >> .80 >> 2 14758 adm 2.95 4.60 7 >> .55 >> 3 14504 lio 2.11 6.06 8 >> .17 >> 4 15786 pio 1.92 5.50 7 >> .42 >> 5 15276 aio 2.03 5.96 7 >> .99 >> 6 15534 msc 0.07 0.01 0 >> .08 >> 7 16048 soc 4.52 9.50 14 >> .02 >> 8 15026 soc 2.38 7.07 9 >> .45 >> 9 22830 aio 0.12 0.23 0 >> .35 >> tot 41294.86 1931.97 43226 >> .83 >> >> Notice the really high CPU VP usage ?? Is that a problem ?? I wonde >> r Yes, indicies are not being used. >> if it is because of KAIO ( which runs on CPU VP ) and the shmem >> connections ( which I changed to a CPU VP from a NET VP ). >> I tried adding a new shm VP, but I got an error of "system or cl >> ass >> limit reached ". >> I did however add a new AIO VP ( VP # 9 ), to take some load off >> >> the CPU VP ( I think ! ) >> >> By the way, I have taken transaction logging off, and the proces >> s >> is still slow. >> We usually have just 4-5 people on this instance, hence the real >> ly >> low NETTYPE on shmem. Do I need more ?? >> >> onstat -p>> >> INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 16:13:21 -- 105 >> 936 >> Kbytes >> >> Profile >> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cach >> ed >> 5194023 5287778 387741663 98.66 17278 34574 44048 60.7 >> 7 Millions of reads mainly buffered - Yep sequential scans. >> >> isamtot open start read write rewrite delete comm >> it >> rollbk >> 378580638 291418 558766 375171525 4815 9064 4175 25 >> 50 >> 0 >> >> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes >> 0 0 0 41528.02 1934.74 93 382 >> >> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress eqsc >> ans >> 479924 0 749385220 0 0 9 623 463 >> 2 >> >> >> ixda-RA idx-RA da-RA RA-pgsused lchwaits >> 6782 1892945 30331