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 () 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
> 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
> 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
> 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
>
> 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 1928451 0
>
>
Write cache% is low. The Liz Suto Performance book recommends it should be
85% or more. Do you have enough memory to increase BUFFERS without causing
paging/swapping?
Others have already suggested changing OPTCOMPIND to 0.
You have got some sequential scans, which could be a problem if they are
scans of large tables. Examine the query execution plan (using SET
EXPLAIN ON) for queries running against the database. Query the syssesprof
table in the sysmaster database to find sessions with particularly high
sequential sca