Performance problem after merging db server
Hi,
we have a heavy performance problem after 'merging' two db systems
to one. We did this because according to the Informix Performance guide
and the advices of Informix experts.
After merging we encountered a heavy performance hit on batch programs
as well as on dialogues. Now our client got additional 256 Meg of RAM
installed and it seemed that the got a little bit lighter, but it didn't
last for long.
Now the facts:
* Sun Enterprise 3000 with (now) 512 Meg RAM and 2x248 MHz Sparc proc's
* Informix OnLine 7.23.UC2
* One of our applications is built with Uniface Six (C/S development
tool), the others with C & ESQL/C or with RosiSQL (no C/S).
* 30-50 database users
(BTW, what hardware platform specification would you suggest? I'm very
interested in hearing about experiences from other sites.)
Today one of the members of the technical staff (that's the company
our client bought the server from) told me that they checked the Solaris
box last week and saw some oninit's running and each of them reserving
71 Meg of memory. I did the same thing today and here are my results:
Partial output of 'top' (at snapshot time no db batch was running):
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
187 root 2 34 -10 71M 44M sleep 31:07 0.09% oninit
189 root 2 34 -10 71M 34M sleep 36:56 0.04% oninit
195 root 1 34 -10 71M 2848K sleep 2:43 0.03% oninit
Last week the technical staff had the following (partial) 'top' output:
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
187 root 2 34 -10 71M 57M sleep 345:59 7.15% oninit
191 root 2 27 -10 71M 56M cpu/14 359:47 6.24% oninit
198 root 1 34 -10 71M 3064K sleep 48:14 3.15% oninit
Output of 'ps -eaf | grep oninit':
root 190 189 0 07:07:39 ? 0:00 oninit
root 187 1 1 07:07:32 ? 31:08 oninit
root 189 187 0 07:07:39 ? 36:56 oninit
root 188 187 0 07:07:34 ? 0:00 oninit
root 191 189 0 07:07:39 ? 0:00 oninit
root 192 187 0 07:07:39 ? 0:00 oninit
root 193 187 0 07:07:39 ? 0:00 oninit
root 194 189 0 07:07:39 ? 0:00 oninit
root 195 187 0 07:07:40 ? 2:44 oninit
root 591 187 0 07:09:43 ? 0:00 oninit
root 592 187 0 07:09:43 ? 0:00 oninit
Output of 'ps -o pid -o ppid -o rss -o vsz -o fname -eaf | grep oninit':
PID PPID RSS VSZ COMMAND
190 189 1848 72688 oninit
187 1 44872 72728 oninit
189 187 34504 72728 oninit
188 187 1288 72672 oninit
191 189 1840 72688 oninit
192 187 2536 72688 oninit
193 187 1656 72688 oninit
194 189 2784 72728 oninit
195 187 2848 72696 oninit
591 187 880 72720 oninit
592 187 864 72720 oninit
Now we're wondering about the utilization of memory of the several
oninit's. I think that the values in column 'RSS' (resident size)
are o.k.. Is it right that the value in column 'VSZ' (size in virtual
memory) reflects the shared memory pool where all the oninit's point to?
'Cause I don't think that each of them claims 72 Meg!? But then: what
causes the differences, e.g. between PID 189 and 188 (not that much, but
different)?
Neither the lack of performance nor a hardware upgrade (would cost
our client up to 60k US$ - and the machine is only about 1 year old!)
is accepted anymore (and I do understand that).
Can anyone of you gurus out there share her/his pool of experience
with us and give us some advice concerning tuning our client's database
system. If you need further information (see onconfig below) like an
onstat output, please let us know. (And please reply to the newsgroup
as well as to my email address directly.)
TIA and many regards,
Stephan.
Addendum: onconfig
#**************************************************************************
#
# INFORMIX SOFTWARE, INC.
#
# Title: onconfig.prima
# Description: INFORMIX-OnLine Configuration Parameters
# fuer PAPERS-media, PAPERS-prima und SIRIUS
#
#**************************************************************************
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace nameROOTPATH /dev/online.pap # Path for device containing root
dbspace
ROOTOFFSET 128 # Offset of root dbspace into device
(Kbytes)
ROOTSIZE 614400 # Size of root dbspace (Kbytes)
# Disk Mirroring Configuration Parameters
MIRROR 1 # Mirroring flag (Yes = 1, No = 0)
MIRRORPATH /dev/online.papmir # Path for device containing mirrored
root
MIRROROFFSET 128 # Offset into mirrored device (Kbytes)
# Physical Log Configuration
PHYSDBS rootdbs # Location (dbspace) of physical log
PHYSFILE 102400 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 6 # Number of logical log files
LOGSIZE 51200 # Logical log size (Kbytes)
# Diagnostics
MSGPATH /usr1/informix/papers.log # System message log file path
CONSOLE /dev/null # System console message pathALARMPROGRAM /usr1/informix/log_full.sh # Alarm program path
# System Archive Tape Device
TAPEDEV /dev/rmt/0 # Tape device path
TAPEBLK 2048 # Tape block size (Kbytes)
TAPESIZE 4096000 # Maximum amount of data to put on tape
(Kbytes)
# Log Archive Tape Device
LTAPEDEV /dev/null # Log tape device path
LTAPEBLK 16 # Log tape block size (Kbytes)
LTAPESIZE 10240 # Max amount of data to put on log tape
(Kbytes)
# Optical
STAGEBLOB ,1 # INFORMIX-OnLine/Optical staging area
# System Configuration
SERVERNUM 2 # Unique id corresponding to a OnLineinstance
DBSERVERNAME prima # Name of default database server
DBSERVERALIASES media # List of alternate dbservernames
NETTYPE tlitcp,1,64,NET # Configure poll thread(s) for nettype
DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env.
RESIDENT 0 # Forced residency flag (Yes = 1, No =
0)
# MULTIPROCESSOR 0 # 0 for single-processor, 1 for
multi-processor
# NUMCPUVPS 1 # Number of user (cpu) vps
# SINGLE_CPU_VP 1 # If non-zero, limit number of cpu vps
to one
MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor
NUMCPUVPS 2 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one
NOAGE 1 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors
# Shared Memory Parameters
LOCKS 200000 # Maximum number of locks
BUFFERS 5000 # Maximum number of shared buffers
NUMAIOVPS 2 # Numbe