7.21.UD1 Perf. Problem
Greetings!
We are upgrading a Version 5.03 OLTP system to Version 7.21.UD1, also
upgrading to 4GL 6.05 (pre-release due to bugs in 6.04).
We have a group of programs that are doing a lot of "behind the
scenes" processing. They were optimized for indexes, etc., under
Version 5.03 and average 16 seconds with other users on the system.
Under Version 7.21, the programs are averaging 48 seconds
stand-alone!!!! We have tried all sorts of things and are stumped.
Our system is NCR. The Version 5.03 is on a Quad 90 MHz processor
box, while the Version 7.12 has two 90 MHz and two 66 MHz processors
running NCR Unix SVR4 3.00.02. The Quad 90 only has 128 Meg of RAM,
while the 2-90/2-66 has 256 Meg. Also, the Quad 90 has a slower RAID
Level 5 cabinet (ten 2 Gig drives) than the 2-90/2-66 (which is a
6598(??) <can't remember exactly and I'm not at the office now to look
it up>) with five 4.3 Gig Fast/Narrow drives.
sar is not showing any problems with bottle-necking for I/O, CPUs or
memory. Sqlexplains show that Version 7.21 has a slight edge for
Costs and # of rows returned. Log files were moved out of rootdbs,
etc. Checkpoints are showing "0 seconds" in the message file.
I would greatly appreciate any help on this.
Thanks!
Kerry Davids (dino@ix.netcom.com)
Consultant to Menasha Corp.
Neenah, WI
7.21 ONCONFIG File
# Root Dbspace Configuration
ROOTNAME rootdbsROOTPATH /dev/root.pkgprod
ROOTOFFSET 0
ROOTNAME rootdbsROOTPATH /dev/root.pkgprod
ROOTOFFSET 0
ROOTSIZE 100000
# Disk Mirroring Configuration Parameters
MIRROR 1
MIRRORPATH
MIRROROFFSET 0
# Physical Log Configuration
PHYSDBS ppkgllogs
PHYSFILE 27000
# Logical Log Configuration
LOGFILES 12
LOGSIZE 7500
# Diagnostics
MSGPATH /usr/informix/7.21.UD1/pkgprod.log
CONSOLE /usr/informix/7.21.UD1/pkgprod.console
ALARMPROGRAM /usr/informix/7.21.UD1/etc/log_full.sh
# System Archive Tape Device
TAPEDEV /dattape
TAPEBLK 16
TAPESIZE 4000000
# Log Archive Tape Device
LTAPEDEV /dev/null
LTAPEBLK 16
LTAPESIZE 475000
# Optical
STAGEBLOB
# System Configuration
SERVERNUM 1
DBSERVERNAME pkgonlineprod
DBSERVERALIASES
NETTYPE tlitcp,1,13,NET
DEADLOCK_TIMEOUT 60
RESIDENT 1
MULTIPROCESSOR 1
NUMCPUVPS 3
SINGLE_CPU_VP 0
NOAGE 1
AFF_SPROC 0
AFF_NPROCS 4
# Shared Memory Parameters
LOCKS 256000
BUFFERS 12000 # Doubled from 6000 with no increase # in performance for those programs!!!
# KGD
NUMAIOVPS 2
PHYSBUFF 32
LOGBUFF 32LOGSMAX 20
CLEANERS 8
SHMBASE 0x10000000
SHMVIRTSIZE 8000
SHMADD 8192
SHMTOTAL 0
CKPTINTVL 300
LRUS 8
LRU_MAX_DIRTY 60
LRU_MIN_DIRTY 50
LTXHWM 50
LTXEHWM 60
TXTIMEOUT 0x12c
STACKSIZE 32
# Recovery Variables
OFF_RECVRY_THREADS 10
ON_RECVRY_THREADS 1
# Data Replication Variables
# DRAUTO: 0 manual, 1 retain type, 2 reverse type
DRAUTO 0
DRINTERVAL 30
DRTIMEOUT 30
DRLOSTFOUND /usr/informix/7.21.UD1/etc/dr.lostfound
# Backup/Restore variables
BAR_ACT_LOG /tmp/bar_act.log
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 10
BAR_XFER_BUF_SIZE 31
# Read Ahead Variables
RA_PAGES
RA_THRESHOLD
# DBSPACETEMP:
DBSPACETEMP ppkgtemp
# DUMP*:
# For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.
DUMPDIR /tmp
DUMPSHMEM 1
DUMPGCORE 0
DUMPCORE 0
DUMPCNT 1
FILLFACTOR 90
# method for OnLine to use when determining current time
USEOSTIME 0
# Parallel Database Queries (pdq)
MAX_PDQPRIORITY 100
DS_MAX_QUERIES
DS_TOTAL_MEMORY
DS_MAX_SCANS 1048576
DATASKIP off
OPTCOMPIND 1 # To hint the optimizer
ONDBSPACEDOWN 2LBU_PRESERVE 0
OPCACHEMAX 0
HETERO_COMMIT 0
NCR mtune file
* General Kernel Parameters ------------------
TRACESZ 8192 4096 8192
NCPU 32 1 32
NCALL 60 30 700
NFILE 150 100 5000
NMOUNT 25 25 25
NPROC 250 50 10240
NREGION 210 210 350
NCLIST 120 1 400
MAXUP 30 15 400
NOFILES 60 20 1000
NHBUF 64 32 1024
NPBUF 20 20 4000
NAUTOUP 60 0 120
FDFLUSHR 1 1 1
BDFLUSHR 1 1 1
MAXPMEM 0 0 16384
SHLBMAX 3 2 6
FLCKREC 300 100 2000
PUTBUFSZ 2000 2000 10000
MAXSLICE 100 25 100
ULIMIT 65536 2048 65536
SPTMAP 400 50 600
PIOMAP 50 50 50
PIOMAXSZ 64 4 64
MAXMINOR 0x3fffff 255 0x3fffff
NGROUPS_MAX 16 1 16
NBUF 100 100 3000
BUFHWM 0 0 65536
ARG_MAX 5120 1024 51200
RSTCHOWN 0 0 1
MAXLINK 2048 1000 32767
* File System Parameters ---------------
NINODE 500 100 5000
NS5INODE 500 100 1300
UFSNINODE 500 100 5000
NDQUOT 200 100 400
NRNODE 300 100 1300
S52KNBUF 100 100 400
S52KNHBUF 32 32 256
* ------ CDFS Parameters -----
NCDINODE 256 128 2048
NCDEXTENT OBSOLETE
NCDFILSYS OBSOLETE
* Paging Parameters -----------------
VHNDFRAC 16 8 32
AGEINTERVAL 9 2 100
MINPAGEFREE 64 64 64
GPGSLO 25 0 25
GPGSHI 40 1 40
GPGSMSK 0x00000420 0x00000420 0x00000420
MAXSC 1 1 1
MAXFC 1 1 1
MAXUMEM 2560 2560 32768
MINARMEM 100 100 160
MINASMEM 25 25 40
MINAKMEM 16 4 64
MINHIDUSTK 4 4 32
MINUSTKGAP 2 2 32
PAGES_UNLOCK 200 200 200
* Pageout Daemon Parameters
LOTSFREE 0 0 1024
DESFREE 0 0 512
MINFREE 0 0 256
* STREAMS Parameters ----------------
NQUEUE 96 1 4096
NSTREAM 32 1 512
NSTRPUSH 9 9 10
NSTREVENT 256 256 512
MAXSEPGCNT 1 0 32
NMUXLINK 87 1 87
STRMSGSZ 0 0 32767
STRCTLSZ 1024 1024 1024
STRTHRESH 0x400000 0 0x1000000
NBLK4096 0 0 2048
NBLK2048 20 0 2048
NBLK1024 20 0 2048
NBLK512 8 0 2048
NBLK256 8 0 2048
NBLK128 8 0 2048
NBLK64 40 0 2048
NBLK16 40 0 2048
NBLK4 40 1 2048
STRLOFRAC 80 0 80
STRMEDFRAC 90 80 100
* ------ log (strlog) related tunable parameters
NLOG 10 3 16
MAXWERRMSG 80 0 100
MAXWTRCMSG 80 0 100
MAXWCONSMSG 80 0 100
NUMSP 64 5 128
NUMTIM 128 1 8192
NUMTRW 16 1 8192
TIM_HIWATER OBSOLETE
TIM_LOWATER OBSOLETE
TRW_HIWATER 65535 512 1048576
TRW_LOWATER 40960 512 1048576
SOCK_HIWATER 8192 512 1048576
SOCK_LOWATER 4096 128 1048576
NUMSAD 8 1 16
NSTRPHASH 64 16 512
NAUTOPUSH 32 32 32
* Message Parameters ---------------
MSGMAP 100 10 400
MSGMAX 2048 512 8192
MSGMNB 4096 4096 4096
MSGMNI 50 50 1000
MSGSSZ 8 8 8
MSGTQL 40 40 384
MSGSEG 1024 1024 6144
* Semaphore Parameters -------------
SEMMAP 25 10 1000
SEMMNI 25 10 1000
SEMMNS 60 60 10