RE: Performance Problems with 7.30.UC7XB
Phil,
=A0
The database has been performing fairly well, at least not exhibiting =
the
behavior that we are seeing now.=A0 We have just recently changed from =
3.1h
SAP kernel to 3.1I kernel and as a result moved from 7.24... to =
7.30.UC5 and
now 7.30.UC7XB of Informix.=A0 Other than setting the parameters =
recommended
by SAP, no changes have been made to our existing systems.=A0 We just =
bounced
the system at 10:30 this morning, saw good performance for the first =
couple
of hours, went to lunch, and now the read cache rate is down around =
80%, the
CPU load on the database server has gone from 2.xx to over 6.00, the =
onstat-P shows over 98% of the buffer pool dedicated to Btree buffers (the =
new
'feature' of 7.3x) -- was 60% when we went to lunch and users have gone =
from
'this is better than it was' to 'this is killing us'.=A0 This =
persistent
degradation cycle (boot system to get good performance, wait 12 hours,
reboot) has been with us ever since we went to 7.3x and we're at the
'clueless' point.
=A0
Doug
=A0
(Opinions here are mine alone -- no one else wants them)
-----Original Message-----
From: Tallent, Phil [mailto:Phil.Tallent@brachs.com]
Sent: Tuesday, June 29, 1999 1:34 PM
To: 'Doug Agnew'; informix-list@iiug.org
Subject: RE: Performance Problems with 7.30.UC7XB
I have seen this behavior on our SAP systems. Has the database
performed well in the past? The reason I ask this question is because =
people
would blame Informix for getting out of tune when in fact there were =
some
transports that came through that killed the database. In performance =
tuning
we were taught that 70% of tuning SAP is ABAP optimization. After being =
in
this environment I believe it.=A0 If it is sporadic I would try to do =
some
ABAP and SQL traces. I have found programs that would read 7 gig tables =
and
return one row of data. Good luck.
Thanks=20
Phil Tallent=20
SAP Administration Informix DBA=20
(423)-510-7701 FAX (423) 510-7895=20
email: <mailto:phil.tallent@brachs.com> phil.tallent@brachs.com=20
BM__MailData-----Original Message-----=20
From:=A0=A0 Doug Agnew [ mailto:dagnew@charlottepipe.com
<mailto:dagnew@charlottepipe.com> ]=20
Sent:=A0=A0 Tuesday, June 29, 1999 10:12 AM=20
To:=A0=A0=A0=A0 informix-list@iiug.org=20
Subject:=A0=A0=A0=A0=A0=A0=A0 Performance Problems with 7.30.UC7XB=20
Running Informix 7.30.UC7XB (for SAP) on=20
HP-UX 10.20 -- machine is T520 w/4 processors and 1.75 Gb memory.=A0 =
EMC=20
Symmetrix with HP-PB Fast/Wide SCSI connections.=20
Experiencing cache-hit read % of < 80% (and needless to say, our
users are=20
screaming)=20
onstat -g seg shows:=20
Segment Summary:=20
id=A0=A0=A0=A0=A0=A0 key=A0=A0=A0=A0=A0=A0=A0 addr=A0=A0=A0=A0 =
size=A0=A0=A0=A0=A0=A0 ovhd=A0=A0=A0=A0 class blkused=A0 blkfree=20
5897=A0=A0=A0=A0 1381386242 80000000 819200000=A0 13108=A0=A0=A0 =
V=A0=A0=A0=A0 60392=A0=A0=A0 39608=20
5130=A0=A0=A0=A0 1381386243 c0931000 712704=A0=A0=A0=A0 =
620=A0=A0=A0=A0=A0 M=A0=A0=A0=A0 80=A0=A0=A0=A0=A0=A0 7=20
6152=A0=A0=A0=A0 1381386241 c0d13000 540164096=A0 9156=A0=A0=A0=A0 =
R=A0=A0=A0=A0 65932=A0=A0=A0 6=20
Total:=A0=A0 -=A0=A0=A0=A0=A0=A0=A0=A0=A0 -=A0=A0=A0=A0=A0=A0=A0 =
1360076800 -=A0=A0=A0=A0=A0=A0=A0 -=A0=A0=A0=A0 126404=A0=A0 39621=20
onstat -p shows (a 6.5 minute sample)=20
Informix Dynamic Server Version 7.30.UC7XB -- On-Line -- Up 02:43:58
--=20
1327504 Kbytes=20
Profile=20
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached=20
768443=A0=A0 1066750=A0 3684443=A0 79.14=A0=A0 9147=A0=A0=A0=A0 =
15228=A0=A0=A0 38270=A0=A0=A0 76.10=20
isamtot=A0 open=A0=A0=A0=A0 start=A0=A0=A0 read=A0=A0=A0=A0 =
write=A0=A0=A0 rewrite=A0 delete=A0=A0
commit=20
rollbk=20
1530220=A0 5017=A0=A0=A0=A0 113613=A0=A0 1121266=A0 6015=A0=A0=A0=A0 =
2116=A0=A0=A0=A0 2971=A0=A0=A0=A0 481=A0=A0=A0=A0=A0 0=20
gp_read=A0 gp_write gp_rewrt gp_del=A0=A0 gp_alloc gp_free=A0 gp_curs=20
0=A0=A0=A0=A0=A0=A0=A0 0=A0=A0=A0=A0=A0=A0=A0 0=A0=A0=A0=A0=A0=A0=A0 =
0=A0=A0=A0=A0=A0=A0=A0 0=A0=A0=A0=A0=A0=A0=A0 0=A0=A0=A0=A0=A0=A0=A0 0=20
ovlock=A0=A0 ovuserthread ovbuff=A0=A0 usercpu=A0 syscpu=A0=A0 =
numckpts flushes=20
0=A0=A0=A0=A0=A0=A0=A0 0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
0=A0=A0=A0=A0=A0=A0=A0 695.44=A0=A0 607.25=A0=A0 1=A0=A0=A0=A0=A0=A0=A0 =
2=20
bufwaits lokwaits lockreqs deadlks=A0 dltouts=A0 ckpwaits compress
seqscans=20
109937=A0=A0 8=A0=A0=A0=A0=A0=A0=A0 1188516=A0 0=A0=A0=A0=A0=A0=A0=A0 =
0=A0=A0=A0=A0=A0=A0=A0 5=A0=A0=A0=A0=A0=A0=A0 932=A0=A0=A0=A0=A0 81=20
ixda-RA=A0 idx-RA=A0=A0 da-RA=A0=A0=A0 RA-pgsused lchwaits=20
648128=A0=A0 251=A0=A0=A0=A0=A0 4837=A0=A0=A0=A0 386873=A0=A0=A0=A0 =
57477=20
environment variables that have been set (per info from SAP)=20
NO_KEYFIRST=3D1=20
NO_SUBQF=3D1=20
SINGLESEG_OFF=3D1=20
DBBLOBBUF=3D64=20
onconfig file follows:=20
#=20
#=A0=A0=A0=A0 C H A N G E=A0 .=A0 L O G=20
#=A0=A0=A0=A0 ---------------------=20
#=20
# rag-10/10/97 chg'd NUMCPUVPS from 2 to 4.=20
#=20
# rag-10/10/97 chg'd cause we added 768 mb mem to server.=20
#=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 is=A0=A0=A0=A0=A0=A0 =
wuz=20
# SHMVIRTSIZE=A0=A0=A0=A0 870000=A0=A0 450000=20
# BUFFERS=A0=A0=A0=A0=A0=A0=A0=A0 300000=A0=A0 210000=20
#=20
# rag-10/10/97 chg'd per SAP Carl Rossini & Sari Nathans - Golive=20
Perf.problem=20
#=20
# LRU_MAX_DIRTY=A0=A0 2=A0=A0 5=20
# LRU_MIN_DIRTY=A0=A0 1=A0=A0 3=20
# CLEANERS=A0=A0=A0=A0=A0=A0=A0 30=A0 40=20
# CKPTINTVL=A0=A0=A0=A0=A0=A0 600 900=A0 (from 15 minutes to 10 =
minutes).=20
# NETTYPE=A0=A0=A0=A0=A0=A0=A0=A0 soctcp,1,120,CPU=A0=A0=A0 per Sari =
Nathans=20
#=20
# rag-10/09/97 chg'd per SAP Dave Green for SAP Golive performance =
problem.=20
#=20
# MULTIPROCESSOR=A0 0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (cpu) =
0=3D1 cpu, 1=3D2 cpu=20
# NUMCPUVPS=A0=A0=A0=A0=A0=A0 =
1=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (cpu) 1=3D1 cpu, 2=3D2 cpu=20
# SINGLE_CPU_VP=A0=A0 1=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 (cpu) =
1=3D1 cpu, 0=3D2 cpu=20
#=20
# rag-07/29/97 made following chenges per Sari Nathans=20
#=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 and EarlyWatch Subhash per =
Sari Nathans=20
#=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
new=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 old=20
#=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
---=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ---=20
# LRUS=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
64=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 32=20
# NETTYPE=A0=A0=A0=A0=A0=A0=A0=A0 soctcp,1,60,CPU=A0=A0 =
soctcp,1,100,NET=A0=A0 (and EarlyWatch)=20
# RA_PAGES=A0=A0=A0=A0=A0=A0=A0 =
32=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 8=20
# RA_THRESHOLD=A0=A0=A0 30=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
4=20
#=20
#=20
# rag-06/04/97 NUMAIOVPS 10 -> 16=A0 # Number of AIO vps. note 41360=20
#=20
# mlc-03/03/97 MULTIPROCESSOR=A0 1=A0=A0=A0=A0=A0=A0 (from 0)=20
#=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 NUMCPUVPS=A0=A0=A0=A0=A0=A0 =
2=A0=A0=A0=A0=A0=A0 (from 1)=20
#=A0=A0=A0=A0