Re: 7.21.UD1 Perf. Problem
In article <5oai41$5ue@dfw-ixnews7.ix.netcom.com>, Kerry
<dino@ix.netcom.com> writes
>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 rootdbs>ROOTPATH /dev/root.pkgprod
>ROOTOFFSET 0
>ROOTNAME rootdbs>ROOTPATH /dev/root.pkgprod
>ROOTOFFSET 0
>ROOTSIZE 100000>
># Disk Mirroring Configuration Parameters
>
>MIRROR 1
>MIRRORPATH
Have MIRROR = 1 and MIRRORPATH blank means under certain versions
that online assumes that mirroring is in use and that the mirror is
in the same place as the normal chunk i.e. in case of failure the
corrupt chunk is still used!
>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 Increase to 64 (or 128 it is still works).
>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 Try increasing to 4. This may help.
>SINGLE_CPU_VP 0
>
>NOAGE 1
>AFF_SPROC 0
>AFF_NPROCS 4 Set AFF_NPROCS = NUMCPUVPs, how can you affine pin 4 CPUVPS when you
only have three of them?
>
># Shared Memory Parameters
>
>LOCKS 256000
>BUFFERS 12000 # Doubled from 6000 with no increase> # in performance for those programs!!!
> # KGD
>NUMAIOVPS 2 Run onstat -g ath, if no kaio threads are showing then incrtease
NUMAOPVPS to be equal to the number of physical disks you have in the
machine (without being silly, don't go above 10.).
>PHYSBUFF 32
>LOGBUFF 32>LOGSMAX 20
>CLEANERS 8 CLEANERS sbould = NUMCPUVPS, they only schedule the work for AIP VPS
or KAIO threads, they do not atually perform the I/O.
>SHMBASE 0x10000000
>SHMVIRTSIZE 8000 Run onstat -g seg and add up the sizes of all the segments in the
Virutal (V) class. Set SHMVIRTSIZE = this value. You only want only
one segment of class V.
>SHMADD 8192
>SHMTOTAL 0
>CKPTINTVL 300
>LRUS 8
Again set LURS = NUMCPUVPS.
>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>
Set these values and monitor usage by doing onstat -p
Note the RA (Read ahread values in the bootom left of the output
RA_used should be close to the total of the other Ra values.
If not reduce RA_PAGES and 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 Reduce this to 0 as you are clearly an OLTP site.
>DS_MAX_QUERIES
>DS_TOTAL_MEMORY
>DS_MAX_SCANS 1048576
>DATASKIP off
>
>OPTCOMPIND 1 # To hint the optimizer>
Set OPTCOMPIND = 0.
>ONDBSPACEDOWN 2>LBU_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