Re: Very Severe Performance Problems on my 7.22 UC2

In article <62lp3v$l86@cssun.mathcs.emory.edu>, sujata_soman_at_omm- la2-infotech-001@internet.omm.com writes > > 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 have 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 Set back to 0. > 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 response > 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 time > 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 -- 105936 > 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 11231972 > per sec: 110 109 26 2 54 > > Virtual processor summary: > class vps usercpu syscpu total > cpu 1 41278.76 1893.04 43171.80 > 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 Total > 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 wonder > 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 class > 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 process > is still slow. > We usually have just 4-5 people on this instance, hence the really > low NETTYPE on shmem. Do I need more ?? > > onstat -p> > INFORMIX-OnLine Version 7.22.UC2 -- On-Line -- Up 16:13:21 -- 105936 > Kbytes > > Profile > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > 5194023 5287778 387741663 98.66 17278 34574 44048 60.77 > > isamtot open start read write rewrite delete commit > rollbk > 378580638 291418 558766 375171525 4815 9064 4175 2550 > 0 > > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > 0 0 0 41528.02 1934.74 93 382 > > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress eqscans > 479924 0 749385220 0 0 9 623 4632 > > > ixda-RA idx-RA da-RA RA-pgsused lchwaits > 6782 1892945 30331 1928451 0 > > > > Let me know if any more info is required. > > Any suggestions ?? > > Thanks in advance > > Sujata > > > > > -- David Williams