Very Severe Performance Problems on my 7.22 UC2
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
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