Poor performance for a $750,000 box

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BF1CB6.07C0F518 Content-Type: text/plain; charset="iso-8859-1" Ladies and Gentlemen: A little background info - I inherited this SAP R3 job as the DBA - no training (will be forthcoming). 1 db, 53 dbspaces total, over 8000 tables, have moved all large tables(2+ gb) to separate dbspaces. The V box has 12 cpu's. SHM runs close to the edge. I think performance is marginal - Any suggestions would be helpful ! Update stats runs weekly via CRON. LVL 0 backup daily, continuous logging. Thanks in advance !! HP 9000/800/V2250 X12 cpu's, memory >= 1.75 GB HP-UX B.11.00 E cat $INFORMIXDIR/etc/*-cr INFORMIX-Connect Version 7.23.UC3 Copyright (C) 1984-1997 Informix Software, Inc. Informix Dynamic Server Version 7.30.UC7 Copyright (C) 1986-1999 Informix Software, Inc. INFORMIX-OnLine Dynamic Server Version 7.23.UC3 Copyright (C) 1986-1997 Informix Software, Inc. I have approx. 100 - 250 users at one time connecting using shared memory. In total the dbspaces occupy about 228 GB. I have managed extents and reorgs with sapdba cat $ONCONFIG ROOTNAME rootdbs # Root dbspace nameROOTPATH /informix/PRD/sapdata/physdev1/data1 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 50000 # Size of root dbspace (Kbytes)MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes)PHYSDBS physdbs # Location (dbspace) of physical log PHYSFILE 100000 # Physical log file size (Kbytes) LOGFILES 100 # Number of logical log files LOGSIZE 20000 # Logical log size (Kbytes) MSGPATH /informix/PRD/online.brachprd.prd.log # System message log file path CONSOLE /informix/PRD/console.brachprd.prd.log # System console message path ALARMPROGRAM /informix/PRD/etc/alarm.pat # Alarm program path SYSALARMPROGRAM /informix/PRD/etc/evidence.sh # System Alarm program path TBLSPACE_STATS 1 #TAPEDEV /dev/null TAPEDEV /dev/rmt/1m # Tape device path TAPEBLK 512 # Tape block size (Kbytes) TAPESIZE 70000000 # Maximum amount of data to put on tape (Kby TAPEBLK 512 # Tape block size (Kbytes) TAPESIZE 70000000 # Maximum amount of data to put on tape (Kbyt LTAPEDEV /dev/rmt/0m # Log tape device path LTAPEBLK 512 # Log tape block size (Kbytes) LTAPESIZE 24000000 # Max amount of data to put on log tape (KbytSTAGEBLOB # Informix Dynamic Server/Optical staging area SERVERNUM 1 # Unique id corresponding to a DynamicServer in DBSERVERNAME brachprdprdshm # Name of default database server DBSERVERALIASES brachprdprdtcp,vuserprdtcp # List of alternatedbservernames NETTYPE ipcshm,1,60,CPU #Override sqlhosts nettype parameters NETTYPE soctcp,3,100,NET #Override sqlhosts nettype parameters DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 11 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps toone #NOAGE 0 # Process aging NOAGE 1 #Process aging changed because its good for HP AFF_SPROC 1 # Affinity start processor NOAGE 1 #Process aging changed because its good for HP AFF_SPROC 1 # Affinity start processor AFF_NPROCS 11 # Affinity number of processorsMUTEX_WAIT_LISTS 0 # Never change this at all LOCKS 800000 # Maximum number of locks BUFFERS 200000 # Maximum number of shared buffers NUMAIOVPS 110 # Number of IO vps equil to total number of chunks ********* PHYSBUFF 1024 # Physical log buffer size (Kbytes) LOGBUFF 16 # Logical log buffer size (Kbytes)LOGSMAX 100 # Maximum number of logical log files #CLEANERS 139 # Number of buffer cleaner processes donot use CLEANERS 127 #128 is the max SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 160000 # initial virtual shared memory segmentsize SHMADD 65536 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 1200 # Check point interval (in sec) LRUS 127 # Number of LRU queues#LRU_MAX_DIRTY 1 # LRU percent dirty begin cleaning limit LRU_MAX_DIRTY 2 LRU_MIN_DIRTY 1#LRU_MIN_DIRTY 0 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water markpercentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 128 # Stack size (Kbytes) OFF_RECVRY_THREADS 10 # Default number of offline workerthreads ON_RECVRY_THREADS 1 # Default number of online worker threads DRAUTO 0 # DR automatic switchover DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec)DRLOSTFOUND /informix/PRD/etc/dr.lostfound # DR lost+found file path 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 BAR_ACT_LOG /tmp/bar_act.log BAR_MAX_BACKUP 4 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31ISM_DATA_POOL ISMData # If the data pool name is changed, be sure to ISM_LOG_POOL ISMLogs RA_PAGES 32 # Number of pages to attempt to read ahead RA_THRESHOLD 28 # Number of pages left before next group#RA_THRESHOLD 30 #changed per class DBSPACETEMP tmpdbs1,tmpdbs2 # Default temp dbspaces DUMPDIR /tmp # Preserve diagnostics in this directory DUMPSHMEM 1 # Dump a copy of shared memory DUMPGCORE 0 # Dump a core image using 'gcore' DUMPCORE 0 # Dump a core image (Warning:this abortsDynamic DUMPCNT 1

In article <7uq9ku$8t3$1@news.xmission.com>, DiMisa, John <John.DiMisa@brachs.com> writes > >This message is in MIME format. Since your mail reader does not understand >this format, some or all of this message may not be legible. > >------_=_NextPart_001_01BF1CB6.07C0F518 >Content-Type: text/plain; > charset="iso-8859-1" > > >Ladies and Gentlemen: > >A little background info - I inherited this SAP R3 job as the DBA - no >training (will be forthcoming). >1 db, 53 dbspaces total, over 8000 tables, have moved all large tables(2+ >gb) to separate dbspaces. The V box has 12 cpu's. SHM runs close to the >edge. I think performance is marginal - Any suggestions would be helpful ! >Update stats runs weekly via CRON. LVL 0 backup daily, continuous logging. > >Thanks in advance !! > > >HP 9000/800/V2250 X12 cpu's, memory >= 1.75 GB >HP-UX B.11.00 E > >cat $INFORMIXDIR/etc/*-cr >INFORMIX-Connect Version 7.23.UC3 >Copyright (C) 1984-1997 Informix Software, Inc. >Informix Dynamic Server Version 7.30.UC7 >Copyright (C) 1986-1999 Informix Software, Inc. >INFORMIX-OnLine Dynamic Server Version 7.23.UC3 >Copyright (C) 1986-1997 Informix Software, Inc. > >I have approx. 100 - 250 users at one time connecting using shared memory. >In total the dbspaces occupy about 228 GB. I have managed extents and reorgs >with sapdba > >cat $ONCONFIG >ROOTNAME rootdbs # Root dbspace name>ROOTPATH /informix/PRD/sapdata/physdev1/data1 > # Path for device containing root dbspace >ROOTOFFSET 0 # Offset of root dbspace into device >(Kbytes) >ROOTSIZE 50000 # Size of root dbspace (Kbytes)>MIRROR 0 # Mirroring flag (Yes = 1, No = 0) >MIRRORPATH # Path for device containing mirrored root >MIRROROFFSET 0 # Offset into mirrored device (Kbytes)>PHYSDBS physdbs # Location (dbspace) of physical log >PHYSFILE 100000 # Physical log file size (Kbytes) >LOGFILES 100 # Number of logical log files >LOGSIZE 20000 # Logical log size (Kbytes) >MSGPATH /informix/PRD/online.brachprd.prd.log> # System message log file path >CONSOLE /informix/PRD/console.brachprd.prd.log > # System console message path >ALARMPROGRAM /informix/PRD/etc/alarm.pat # Alarm program path >SYSALARMPROGRAM /informix/PRD/etc/evidence.sh # System Alarm program path >TBLSPACE_STATS 1 >#TAPEDEV /dev/null >TAPEDEV /dev/rmt/1m # Tape device path >TAPEBLK 512 # Tape block size (Kbytes) >TAPESIZE 70000000 # Maximum amount of data to put on tape >(Kby >TAPEBLK 512 # Tape block size (Kbytes) >TAPESIZE 70000000 # Maximum amount of data to put on tape >(Kbyt >LTAPEDEV /dev/rmt/0m # Log tape device path >LTAPEBLK 512 # Log tape block size (Kbytes) >LTAPESIZE 24000000 # Max amount of data to put on log tape >(Kbyt>STAGEBLOB # Informix Dynamic Server/Optical staging >area >SERVERNUM 1 # Unique id corresponding to a Dynamic>Server in >DBSERVERNAME brachprdprdshm # Name of default database server >DBSERVERALIASES brachprdprdtcp,vuserprdtcp # List of alternate>dbservernames >NETTYPE ipcshm,1,60,CPU #Override sqlhosts nettype parameters ipchsm,12,60,CPU. Use all CPU VPs for shm connections. >NETTYPE soctcp,3,100,NET #Override sqlhosts nettype parameters >DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed >env. >RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) RESIDENT 1. >MULTIPROCESSOR 1 # 0 for single-processor, 1 for>multi-processor >NUMCPUVPS 11 # Number of user (cpu) vps >SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to>one >#NOAGE 0 # Process aging >NOAGE 1 #Process aging changed because its good for HP >AFF_SPROC 1 # Affinity start processor >NOAGE 1 #Process aging changed because its good for HP >AFF_SPROC 1 # Affinity start processor >AFF_NPROCS 11 # Affinity number of processors>MUTEX_WAIT_LISTS 0 # Never change this at all Art, e-mail me what this is, it is undocumented... >LOCKS 800000 # Maximum number of locks >BUFFERS 200000 # Maximum number of shared buffers >NUMAIOVPS 110 # Number of IO vps> >equil to total number of chunks ********* Are the chunks on raw space or file systems? Look into KAIO. > >PHYSBUFF 1024 # Physical log buffer size (Kbytes) >LOGBUFF 16 # Logical log buffer size (Kbytes) Are database using buffered or unbuffered logging? >LOGSMAX 100 # Maximum number of logical log files >#CLEANERS 139 # Number of buffer cleaner processes donot >use >CLEANERS 127 #128 is the max >SHMBASE 0x0 # Shared memory base address >SHMVIRTSIZE 160000 # initial virtual shared memory segment>size >SHMADD 65536 # Size of new shared memory segments >(Kbytes) onstat -g seg. Set SHMVIRTSIZE so you only have 1 segment of type V. >SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited >CKPTINTVL 1200 # Check point interval (in sec) >LRUS 127 # Number of LRU queues>#LRU_MAX_DIRTY 1 # LRU percent dirty begin cleaning limit >LRU_MAX_DIRTY 2 >LRU_MIN_DIRTY 1 MAX=1 MIN=0 is the smallest values... >#LRU_MIN_DIRTY 0 # LRU percent dirty end cleaning limit >LTXHWM 50 # Long transaction high water mark>percentage >LTXEHWM 60 # Long transaction high water mark >(exclusive) >TXTIMEOUT 0x12c # Transaction timeout (in sec) >STACKSIZE 128 # Stack size (Kbytes) >OFF_RECVRY_THREADS 10 # Default number of offline worker>threads >ON_RECVRY_THREADS 1 # Default number of online worker threads >DRAUTO 0 # DR automatic switchover >DRINTERVAL 30 # DR max time between DR buffer flushes (in >sec) >DRTIMEOUT 30 # DR network timeout (in sec)>DRLOSTFOUND /informix/PRD/etc/dr.lostfound # DR lost+found file path >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 >BAR_ACT_LOG /tmp/bar_act.log >BAR_MAX_BACKUP 4 >BAR_RETRY 1 >BAR_NB_XPORT_COUNT 10 >BAR_XFER_BUF_SIZE 31>IS

DiMisa, John <John.DiMisa@brachs.com> wrote in message news:7uq9ku$8t3$1@news.xmission.com... > > This message is in MIME format. Since your mail reader does not understand > this format, some or all of this message may not be legible. > > ------_=_NextPart_001_01BF1CB6.07C0F518 > Content-Type: text/plain; > charset="iso-8859-1" > > > Ladies and Gentlemen: > > A little background info - I inherited this SAP R3 job as the DBA - no > training (will be forthcoming). > 1 db, 53 dbspaces total, over 8000 tables, have moved all large tables(2+ > gb) to separate dbspaces. The V box has 12 cpu's. SHM runs close to the > edge. I think performance is marginal - Any suggestions would be helpful ! > Update stats runs weekly via CRON. LVL 0 backup daily, continuous logging. > > Thanks in advance !! Hi John ! See below ... > > > HP 9000/800/V2250 X12 cpu's, memory >= 1.75 GB > HP-UX B.11.00 E > > cat $INFORMIXDIR/etc/*-cr > INFORMIX-Connect Version 7.23.UC3 > Copyright (C) 1984-1997 Informix Software, Inc. > Informix Dynamic Server Version 7.30.UC7 > Copyright (C) 1986-1999 Informix Software, Inc. > INFORMIX-OnLine Dynamic Server Version 7.23.UC3 > Copyright (C) 1986-1997 Informix Software, Inc. > > I have approx. 100 - 250 users at one time connecting using shared memory. > In total the dbspaces occupy about 228 GB. I have managed extents and reorgs > with sapdba > > cat $ONCONFIG > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /informix/PRD/sapdata/physdev1/data1 > # Path for device containing root dbspace > ROOTOFFSET 0 # Offset of root dbspace into device > (Kbytes) > ROOTSIZE 50000 # Size of root dbspace (Kbytes)> MIRROR 0 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH # Path for device containing mirrored root > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> PHYSDBS physdbs # Location (dbspace) of physical log > PHYSFILE 100000 # Physical log file size (Kbytes) > LOGFILES 100 # Number of logical log files > LOGSIZE 20000 # Logical log size (Kbytes) > MSGPATH /informix/PRD/online.brachprd.prd.log> # System message log file path > CONSOLE /informix/PRD/console.brachprd.prd.log > # System console message path > ALARMPROGRAM /informix/PRD/etc/alarm.pat # Alarm program path > SYSALARMPROGRAM /informix/PRD/etc/evidence.sh # System Alarm program path > TBLSPACE_STATS 1 > #TAPEDEV /dev/null > TAPEDEV /dev/rmt/1m # Tape device path > TAPEBLK 512 # Tape block size (Kbytes) > TAPESIZE 70000000 # Maximum amount of data to put on tape > (Kby > TAPEBLK 512 # Tape block size (Kbytes) > TAPESIZE 70000000 # Maximum amount of data to put on tape > (Kbyt > LTAPEDEV /dev/rmt/0m # Log tape device path > LTAPEBLK 512 # Log tape block size (Kbytes) > LTAPESIZE 24000000 # Max amount of data to put on log tape > (Kbyt> STAGEBLOB # Informix Dynamic Server/Optical staging > area > SERVERNUM 1 # Unique id corresponding to a Dynamic> Server in > DBSERVERNAME brachprdprdshm # Name of default database server > DBSERVERALIASES brachprdprdtcp,vuserprdtcp # List of alternate> dbservernames > NETTYPE ipcshm,1,60,CPU #Override sqlhosts nettype parameters > NETTYPE soctcp,3,100,NET #Override sqlhosts nettype parameters > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed > env. > RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) Set RESIDENT to 1. > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> multi-processor > NUMCPUVPS 11 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to> one > #NOAGE 0 # Process aging > NOAGE 1 #Process aging changed because its good for HP > AFF_SPROC 1 # Affinity start processor > NOAGE 1 #Process aging changed because its good for HP > AFF_SPROC 1 # Affinity start processor > AFF_NPROCS 11 # Affinity number of processors> MUTEX_WAIT_LISTS 0 # Never change this at all This is for the database system to use wait lists for internal database locks on multiprocessor machines. What this one stend for ? This is undocumented and gives overhead to multiprocessor machines ... Or this fixes some bugs ? This param is recommendent to be 0 in SAP environment (see note 41360). > LOCKS 800000 # Maximum number of locks > BUFFERS 200000 # Maximum number of shared buffers > NUMAIOVPS 110 # Number of IO vps> > equil to total number of chunks ********* > > PHYSBUFF 1024 # Physical log buffer size (Kbytes) > LOGBUFF 16 # Logical log buffer size (Kbytes)> LOGSMAX 100 # Maximum number of logical log files > #CLEANERS 139 # Number of buffer cleaner processes donot > use > CLEANERS 127 #128 is the max > SHMBASE 0x0 # Shared memory base address > SHMVIRTSIZE 160000 # initial virtual shared memorysegment > size > SHMADD 65536 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited > CKPTINTVL 1200 # Check point interval (in sec) > LRUS 127 # Number of LRU queues> #LRU_MAX_DIRTY 1 # LRU percent dirty begin cleaning limit > LRU_MAX_DIRTY 2 > LRU_MIN_DIRTY 1> #LRU_MIN_DIRTY 0 # LRU percent dirty end cleaning limit > LTXHWM 50 # Long transaction high water mark> percentage > LTXEHWM 60 # Long transaction high water mark > (exclusive) > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 128 # Stack size (Kbytes) > OFF_RECVRY_THREADS 10 # Default number of offline worker> threads > ON_RECVRY_THREADS 1 # Default number of online workerthreads > DRAUTO 0 # DR automatic switchover > DRINTERVAL 30 # DR max time between DR buffer flushes (in > sec) > DRTIMEOUT 30 # DR network timeout (in sec)> DRLOSTFOUND /informix/PRD/etc/dr.lostfound # DR lost+found file path > 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 CDRqueue > (Kb > BAR_ACT_LOG /tmp

Hi, I completely agree with Yuri: try to reduce the number of shared memory segments to at most 3. There is a huge penalty to pay on HP for having too many SHM segments. Other recommendations: * Assuming that there are no other applications running on the box you probably should set RESIDENT to -1 (keeps ALL shm segments resident). * 110 AIO VPs indicates that you are not using KAIO. KAIO works fine with the latest Informix releases (see SAP OSS note #85840). The lastest SAP certified version is 7.31.UC2XA (you should have received the version with you latest upgrade CD from SAP - otherwise SAP will provide it on request) but to my best knowledge KAIO works fine with 7.30.UC7. * Since you use a large amount of shared memory you should tell HP-UX to use large memory pages: 'chatr +pd 64M +pi 4M $INFORMIXDIR/bin/oninit'. BTW, this can be used for SAP's disp+work executable as well. * There are 11 CPU VPs - therefore I assume that most R/3 sessions connect remotely. In this case you should have the TCP/IP poll thread run inline witth the CPU VPs. Change the NETVP settings from: > NETTYPE ipcshm,1,60,CPU #Override sqlhosts nettype parameter > NETTYPE soctcp,3,100,NET #Override sqlhosts nettype parameters to: NETTYPE ipcshm,1,60,NET #Override sqlhosts nettype parameter NETTYPE soctcp,3,100,CPU #Override sqlhosts nettype parameters * If, on the other hand most connections are local and the system hosts the R/3 instances as well I wonder why you would need 3 soctcp poll threads? Also, in this case you have probably way too many CPU VPs configured. onstat -g glo would indicate how may of the CPU VPs are actually used. Hope this helps, Heiko Yuri Dovgart wrote: > DiMisa, John <John.DiMisa@brachs.com> wrote in message > news:7uq9ku$8t3$1@news.xmission.com... > > > > This message is in MIME format. Since your mail reader does not understand > > this format, some or all of this message may not be legible. > > > > ------_=_NextPart_001_01BF1CB6.07C0F518 > > Content-Type: text/plain; > > charset="iso-8859-1" > > > > > > Ladies and Gentlemen: > > > > A little background info - I inherited this SAP R3 job as the DBA - no > > training (will be forthcoming). > > 1 db, 53 dbspaces total, over 8000 tables, have moved all large tables(2+ > > gb) to separate dbspaces. The V box has 12 cpu's. SHM runs close to the > > edge. I think performance is marginal - Any suggestions would be helpful > ! > > Update stats runs weekly via CRON. LVL 0 backup daily, continuous logging. > > > > Thanks in advance !! > > Hi John ! See below ... > > > > > > > HP 9000/800/V2250 X12 cpu's, memory >= 1.75 GB > > HP-UX B.11.00 E > > > > cat $INFORMIXDIR/etc/*-cr > > INFORMIX-Connect Version 7.23.UC3 > > Copyright (C) 1984-1997 Informix Software, Inc. > > Informix Dynamic Server Version 7.30.UC7 > > Copyright (C) 1986-1999 Informix Software, Inc. > > INFORMIX-OnLine Dynamic Server Version 7.23.UC3 > > Copyright (C) 1986-1997 Informix Software, Inc. > > > > I have approx. 100 - 250 users at one time connecting using shared memory. > > In total the dbspaces occupy about 228 GB. I have managed extents and > reorgs > > with sapdba > > > > cat $ONCONFIG > > ROOTNAME rootdbs # Root dbspace name> > ROOTPATH /informix/PRD/sapdata/physdev1/data1 > > # Path for device containing root dbspace > > ROOTOFFSET 0 # Offset of root dbspace into device > > (Kbytes) > > ROOTSIZE 50000 # Size of root dbspace (Kbytes)> > MIRROR 0 # Mirroring flag (Yes = 1, No = 0) > > MIRRORPATH # Path for device containing mirrored root > > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> > PHYSDBS physdbs # Location (dbspace) of physical log > > PHYSFILE 100000 # Physical log file size (Kbytes) > > LOGFILES 100 # Number of logical log files > > LOGSIZE 20000 # Logical log size (Kbytes) > > MSGPATH /informix/PRD/online.brachprd.prd.log> > # System message log file path > > CONSOLE /informix/PRD/console.brachprd.prd.log > > # System console message path > > ALARMPROGRAM /informix/PRD/etc/alarm.pat # Alarm program path > > SYSALARMPROGRAM /informix/PRD/etc/evidence.sh # System Alarm program path > > TBLSPACE_STATS 1 > > #TAPEDEV /dev/null > > TAPEDEV /dev/rmt/1m # Tape device path > > TAPEBLK 512 # Tape block size (Kbytes) > > TAPESIZE 70000000 # Maximum amount of data to put on tape > > (Kby > > TAPEBLK 512 # Tape block size (Kbytes) > > TAPESIZE 70000000 # Maximum amount of data to put on tape > > (Kbyt > > LTAPEDEV /dev/rmt/0m # Log tape device path > > LTAPEBLK 512 # Log tape block size (Kbytes) > > LTAPESIZE 24000000 # Max amount of data to put on log tape > > (Kbyt> > STAGEBLOB # Informix Dynamic Server/Optical staging > > area > > SERVERNUM 1 # Unique id corresponding to a Dynamic> > Server in > > DBSERVERNAME brachprdprdshm # Name of default database server > > DBSERVERALIASES brachprdprdtcp,vuserprdtcp # List of alternate> > dbservernames > > NETTYPE ipcshm,1,60,CPU #Override sqlhosts nettype parameters > > NETTYPE soctcp,3,100,NET #Override sqlhosts nettype parameters > > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed > > env. > > RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)> > Set RESIDENT to 1. > > > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> > multi-processor > > NUMCPUVPS 11 # Number of user (cpu) vps > > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to> > one > > #NOAGE 0 # Process aging > > NOAGE 1 #Process aging changed because its good for HP > > AFF_SPROC 1 # Affinity start processor > > NOAGE 1 #Process aging changed because its good for HP > > AFF_SPROC 1 # Affinity start processor > > AFF_NPROCS 11 # Affinity number of processors> > MUTEX_WAIT_LISTS 0 # Never change this at all > > This is for the database system to use wait lists for internal database > locks on multiprocessor machines. What this one stend for ? This is > undocumented and gives overhead to multiprocessor machines ... Or this fixes > some bugs ? This param is recommendent to be 0 in SAP environment (see note > 41360). > > > LOCKS 800000 # Maximum number of locks > > BUFFERS 200000 # Maximum number of shared buffers > > NUMAIOVPS 110 # Number of IO vps> > > > equil to total number of chunks ********* > > > > PHYSBUFF

Heiko Giesselmann wrote: [Mainly good recommendations SNIPPED] > * There are 11 CPU VPs - therefore I assume that most R/3 sessions connect > remotely. In this case you should have the TCP/IP poll thread run inline > witth the CPU VPs. Change the NETVP settings from: > > NETTYPE ipcshm,1,60,CPU #Override sqlhosts nettype parameter > > NETTYPE soctcp,3,100,NET #Override sqlhosts nettype parameters > to: > NETTYPE ipcshm,1,60,NET #Override sqlhosts nettype parameter > NETTYPE soctcp,3,100,CPU #Override sqlhosts nettype parameters NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO!!!!!!!!!!!!!!!!!!!!!!!!! IGNORE THE MANUALS ON THIS ONE! NEVER NEVER put shared memory on NET VP or TCP connections on CPU VP! The CPU VPs should be too busy to poll the network connections and it wastes too many CPU cycles. The NET VPs can, and DO block on the network listening ports unless there is activity, the CPU VPs can't do that. On the other hand the NET VPs MUST block on TCP connections and cannot block on shared memory reads so they must poll shared memory which they will do constantly, burning even more CPU cycles and starving the CPU VPs out, while the CPU VPs will be busy with other things and so only poll between work and at certain break points in the code. That means that if you have the same number of CPU listeners as CPU VPs then which ever CPU VP is least busy will get the next request which helps load balancing! It does NOT slow response times since if the CPU VP is too busy to poll, which takes priority over normal thread releases that allow the VP to pick up work from the NET VPs, they will not be able to take the jobs from a NET VP either! These are my NETTYPE recommendations. OH, I say configure ALL the CPU VPs you can get away with (on HP that means up to 2 CPU VPs per CPU!): NETTYPE ipcshm,11,60,CPU NETTYPE soctcp,3,100,NET [SNIP] > Yuri Dovgart wrote: > > > DiMisa, John <John.DiMisa@brachs.com> wrote in message > > news:7uq9ku$8t3$1@news.xmission.com... > > > Ladies and Gentlemen: > > > > > > A little background info - I inherited this SAP R3 job as the DBA - no > > > training (will be forthcoming). > > > 1 db, 53 dbspaces total, over 8000 tables, have moved all large tables(2+ > > > gb) to separate dbspaces. The V box has 12 cpu's. SHM runs close to the > > > edge. I think performance is marginal - Any suggestions would be helpful > > ! > > > Update stats runs weekly via CRON. LVL 0 backup daily, continuous logging. [SNIP] > > > > > > Percentages: > > > Data 8.74 > > > Btree 83.77 > > > Other 7.49 > > > > Got you ! This is Informix bug, see note #167846. Btree percentage is too > > high. We've discussed this some weeks ago - see previous postings. > > > > > > > > onstat -g seg> > > Segment Summary: > > > id key addr size ovhd class blkused blkfree > > > 175129 1381451779 95ba1000 67108864 1632 V 8175 17 > > > 390170 1381451780 99c57000 67108864 1632 V 8155 37 > > > 252955 1381451781 9dc57000 67108864 1632 V 8138 54 > > > 96284 1381451782 a1c57000 67108864 1632 V 8139 53 > > > 71709 1381451783 a5c57000 67108864 1632 V 8001 191 > > > 31774 1381451784 a9c57000 67108864 1632 V 7593 599 > > > 25631 1381451785 adc57000 67108864 1632 V 7768 424 > > > 79904 1381451786 b1c57000 67108864 1632 V 1566 6626 > > > 68615 1381451777 c2e61000 482385920 10776 R 58879 6 > > > (shared) 1381451777 dfa6b000 163848192 3112 V 10031 9970 > > > 31752 1381451778 e96ad000 712704 620 M 80 7 > > > Total: - - 1183817728 - - 126525 17984 > > > > Oh, God ! Configure SHMVIRTSIZE, you must have _ONLY_ONE_ virtual segment > > for HP-UX. This is critical for performance on HP-UX. [SNIP] Art S. Kagel

"Art S. Kagel" wrote: > Heiko Giesselmann wrote: > [Mainly good recommendations SNIPPED] > > * There are 11 CPU VPs - therefore I assume that most R/3 sessions connect > > remotely. In this case you should have the TCP/IP poll thread run inline > > witth the CPU VPs. Change the NETVP settings from: > > > NETTYPE ipcshm,1,60,CPU #Override sqlhosts nettype parameter > > > NETTYPE soctcp,3,100,NET #Override sqlhosts nettype parameters > > to: > > NETTYPE ipcshm,1,60,NET #Override sqlhosts nettype parameter > > NETTYPE soctcp,3,100,CPU #Override sqlhosts nettype parameters> > NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO!!!!!!!!!!!!!!!!!!!!!!!!! > > IGNORE THE MANUALS ON THIS ONE! NEVER NEVER put shared memory on NET VP > or TCP connections on CPU VP! The CPU VPs should be too busy to poll the > network connections and it wastes too many CPU cycles. The NET VPs can, > and DO block on the network listening ports unless there is activity, the > CPU VPs can't do that. On the other hand the NET VPs MUST block on TCP > connections and cannot block on shared memory reads so they must poll > shared memory which they will do constantly, burning even more CPU cycles > and starving the CPU VPs out, while the CPU VPs will be busy with other > things and so only poll between work and at certain break points in the > code. That means that if you have the same number of CPU listeners as CPU > VPs then which ever CPU VP is least busy will get the next request which > helps load balancing! It does NOT slow response times since if the CPU VP > is too busy to poll, which takes priority over normal thread releases that > allow the VP to pick up work from the NET VPs, they will not be able to > take the jobs from a NET VP either! These are my NETTYPE recommendations. > OH, I say configure ALL the CPU VPs you can get away with (on HP that > means up to 2 CPU VPs per CPU!): > > NETTYPE ipcshm,11,60,CPU > NETTYPE soctcp,3,100,NET Art, I hate to disagree with you, but I think this is very much a question of what you are tuning for. If we are talking about a rather lightly loaded system your advice is perfectly correct - response time is not much of an issue and your approach would be the way to go to reduce resource consumption. My assumption is that there is considerable load on the system, in which case the inlined poll threads don't waste CPU cycles - there would be few useless polls only - and turn over network requests as quickly as possible to sqlexec threads (while with NET VPs there would be process switch involved). If response time matters this should be the way to go. This is not just based on the performance guide but a result of controlled experiments in a number of actual R/3 systems. Also, while it might have been true for old IDS versions that it makes sense to configure more CPU VPs than physical CPUs this is certainly not true anymore for any 7.3 release - if you have proof for such behavior it should be investigated as a bug. For more analysis it would be necessary to know how much the CPU VPs are actually utilized (onstat -g glo) and if the majority of activity is generated by remote or local sessions. Hope this helps, Heiko > > > [SNIP] > > Yuri Dovgart wrote: > > > > > DiMisa, John <John.DiMisa@brachs.com> wrote in message > > > news:7uq9ku$8t3$1@news.xmission.com... > > > > > Ladies and Gentlemen: > > > > > > > > A little background info - I inherited this SAP R3 job as the DBA - no > > > > training (will be forthcoming). > > > > 1 db, 53 dbspaces total, over 8000 tables, have moved all large tables(2+ > > > > gb) to separate dbspaces. The V box has 12 cpu's. SHM runs close to the > > > > edge. I think performance is marginal - Any suggestions would be helpful > > > ! > > > > Update stats runs weekly via CRON. LVL 0 backup daily, continuous logging. > [SNIP] > > > > > > > > Percentages: > > > > Data 8.74 > > > > Btree 83.77 > > > > Other 7.49 > > > > > > Got you ! This is Informix bug, see note #167846. Btree percentage is too > > > high. We've discussed this some weeks ago - see previous postings. > > > > > > > > > > > onstat -g seg> > > > Segment Summary: > > > > id key addr size ovhd class blkused blkfree > > > > 175129 1381451779 95ba1000 67108864 1632 V 8175 17 > > > > 390170 1381451780 99c57000 67108864 1632 V 8155 37 > > > > 252955 1381451781 9dc57000 67108864 1632 V 8138 54 > > > > 96284 1381451782 a1c57000 67108864 1632 V 8139 53 > > > > 71709 1381451783 a5c57000 67108864 1632 V 8001 191 > > > > 31774 1381451784 a9c57000 67108864 1632 V 7593 599 > > > > 25631 1381451785 adc57000 67108864 1632 V 7768 424 > > > > 79904 1381451786 b1c57000 67108864 1632 V 1566 6626 > > > > 68615 1381451777 c2e61000 482385920 10776 R 58879 6 > > > > (shared) 1381451777 dfa6b000 163848192 3112 V 10031 9970 > > > > 31752 1381451778 e96ad000 712704 620 M 80 7 > > > > Total: - - 1183817728 - - 126525 17984 > > > > > > Oh, God ! Configure SHMVIRTSIZE, you must have _ONLY_ONE_ virtual segment > > > for HP-UX. This is critical for performance on HP-UX. > [SNIP] > > Art S. Kagel

In article <7uq9ku$8t3$1@news.xmission.com>, "DiMisa, John" <John.DiMisa@brachs.com> wrote: Ladies and Gentlemen: A little background info - I inherited this SAP R3 job as the DBA - no training (will be forthcoming). 1 db, 53 dbspaces total, over 8000 tables, have moved all large tables(2+ gb) to separate dbspaces. The V box has 12 cpu's. SHM runs close to the edge. I think performance is marginal - Any suggestions would be helpful ! Update stats runs weekly via CRON. LVL 0 backup daily, continuous logging. Thanks in advance !! HP 9000/800/V2250 X12 cpu's, memory >= 1.75 GB HP-UX B.11.00 E [Snip] I have approx. 100 - 250 users at one time connecting using shared memory[Snip] Not according to your ONCONFIG. It is recommended to have no more than 150 users per poll thread for shared memory, and 100 for tcp. Thus you will want to configure 2 or 3 for shared memory. Also it is just NOT TRUE that having a poll thread running on every CPU vp will load balance, where did that idea get started. If what you say above is true you should change: [Snip] NETTYPE ipcshm,1,60,CPU #Override sqlhostsnettype parameters NETTYPE soctcp,3,100,NET #Override To NETTYPE ipcshm,2,150,CPU #Override sqlhostsnettype parameters NETTYPE soctcp,1,100,NET #Override [Snip] RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) RESIDENT -1 # A must if you use KAIO, which I recommend, due to abug in HPUX [Snip] AFF_SPROC 1 # Affinity startprocessor AFF_NPROCS 11 # Affinity number ofprocessors Not bang for the buck on HP using affinity, turn it off. [Snip] LOCKS 800000 # Maximum number oflocks Do you really need this many NUMAIOVPS 110 # Number of IO vpsUse KAIO but set the following env variables: IFMX_HPKIAO_VER=3 IFMX_HPKIAO_NUM_REQ=2500 (you can up this to 5000 if you like more memory is used) KAIOON=1 CLEANERS 127 #128 is the max LRUS 127 # Number of LRU queues You shouldn't need to have LRUS and CLEANERS set this high if you only have 200000 buffers, drop them down to 132 or less. [Snip] DBSPACETEMP tmpdbs1,tmpdbs2 # Default tempdbspaces Add more temp dbspaces [Snip] DD_HASHSIZE 613 DD_HASHMAX 30Do you really need to cache 18390 tables in the dictionary cache? Probably a lot of wasted memory onstat -p Informix Dynamic Server Version 7.30.UC7 -- On-Line -- Up 1 days 00:15:24 -- 1155376 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 210175611 103877763 4156542171 94.94 4924187 7725750 26673910 81.54 isamtot open start read write rewrite delete commit rollbk 2116898383 1456217 120325372 1626680595 3070405 2153483 1820038 479522 935 17 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 215937.50 106465.84 85 170 You need more buffers bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 44535351 17769 1636522670 0 0 262 602059 4616180 ixda-RA idx-RA da-RA RA-pgsused lchwaits 175771512 5531191 489796 179871106 24631837 [Snip] your onstat -P shows a bug, but you have to upgrade to fix that one. [Snip] onstat -g seg Segment Summary: id key addr size ovhd class blkused blkfree 175129 1381451779 95ba1000 67108864 1632 V 8175 17 390170 1381451780 99c57000 67108864 1632 V 8155 37 252955 1381451781 9dc57000 67108864 1632 V 8138 54 96284 1381451782 a1c57000 67108864 1632 V 8139 53 71709 1381451783 a5c57000 67108864 1632 V 8001 191 31774 1381451784 a9c57000 67108864 1632 V 7593 599 25631 1381451785 adc57000 67108864 1632 V 7768 424 79904 1381451786 b1c57000 67108864 1632 V 1566 6626 68615 1381451777 c2e61000 482385920 10776 R 58879 6 (shared) 1381451777 dfa6b000 163848192 3112 V 10031 9970 31752 1381451778 e96ad000 712704 620 M 80 7 Total: - - 1183817728 - - 126525 17984 Add up you virtual segments and put that number in SHMVIRTSIZE if you onstat -g seg always looks like this. Matur Suksema Sent via Deja.com http://www.deja.com/ Before you buy.

Heiko Giesselmann wrote: > > "Art S. Kagel" wrote: > > > Heiko Giesselmann wrote: > > [Mainly good recommendations SNIPPED] > > > * There are 11 CPU VPs - therefore I assume that most R/3 sessions connect > > > remotely. In this case you should have the TCP/IP poll thread run inline > > > witth the CPU VPs. Change the NETVP settings from: > > > > NETTYPE ipcshm,1,60,CPU #Override sqlhosts nettype parameter > > > > NETTYPE soctcp,3,100,NET #Override sqlhosts nettype parameters > > > to: > > > NETTYPE ipcshm,1,60,NET #Override sqlhosts nettype parameter > > > NETTYPE soctcp,3,100,CPU #Override sqlhosts nettype parameters> > > > NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO!!!!!!!!!!!!!!!!!!!!!!!!! > > > > IGNORE THE MANUALS ON THIS ONE! NEVER NEVER put shared memory on NET VP > > or TCP connections on CPU VP! The CPU VPs should be too busy to poll the > > network connections and it wastes too many CPU cycles. The NET VPs can, > > and DO block on the network listening ports unless there is activity, the > > CPU VPs can't do that. On the other hand the NET VPs MUST block on TCP > > connections and cannot block on shared memory reads so they must poll > > shared memory which they will do constantly, burning even more CPU cycles > > and starving the CPU VPs out, while the CPU VPs will be busy with other > > things and so only poll between work and at certain break points in the > > code. That means that if you have the same number of CPU listeners as CPU > > VPs then which ever CPU VP is least busy will get the next request which > > helps load balancing! It does NOT slow response times since if the CPU VP > > is too busy to poll, which takes priority over normal thread releases that > > allow the VP to pick up work from the NET VPs, they will not be able to > > take the jobs from a NET VP either! These are my NETTYPE recommendations. > > OH, I say configure ALL the CPU VPs you can get away with (on HP that > > means up to 2 CPU VPs per CPU!): > > > > NETTYPE ipcshm,11,60,CPU > > NETTYPE soctcp,3,100,NET> > Art, I hate to disagree with you, but I think this is very much a question of what Why? Except for Obnoxio and Mikey I don't bite anyone. ;-) Also I only become vehement when someone recommends RAID5 so you are safe. > you are tuning for. If we are talking about a rather lightly loaded system your > advice is perfectly correct - response time is not much of an issue and your > approach would be the way to go to reduce resource consumption. My systems are pretty busy, our price history servers for example are supplying historical pricing on millions of instruments to over 100,000 users 24x7. My experience has supported these recommendations. > My assumption is that there is considerable load on the system, in which case the > inlined poll threads don't waste CPU cycles - there would be few useless polls only > - and turn over network requests as quickly as possible to sqlexec threads (while > with NET VPs there would be process switch involved). If response time matters this You make a reasonable and reasoned argument and make sense. It is the same reasoning that the Informix engineers who designed the listener threads used to make their recommendation in the manuals. > should be the way to go. This is not just based on the performance guide but a > result of controlled experiments in a number of actual R/3 systems. Now that I absolutely respect. At a minimum it says that SAP R/3 systems have specific tuning requirements. At worst there are levels of transaction load that I have not seen which invalidate some assumptions I have made and do not match my tests. Interesting. This just points out how important it is to test test test and play with ALL of the parameters. Any recommendations - mine, those in the manuals, Heiko's, etc. - are just jumping off points for your own tuning efforts and guidelines to what things to try and which direction to go. > Also, while it might have been true for old IDS versions that it makes sense to > configure more CPU VPs than physical CPUs this is certainly not true anymore for any > 7.3 release - if you have proof for such behavior it should be investigated as a > bug. This I don't understand. How can it be a bug for the CPU to have more cycles available than the CPU VPs can utilize? Locking and latching and thread context switches are guaranteed to prevent any CPU VP from using 100% of a CPU if it is fast enough since many of these operations are constrained by slow memory even when cached. The recommendation to try NUMCPUVP > #CPUs is from postings on CDI by other users who have tried this on PA RISC and SPARC architectures with the newest fastest clocked versions and found that performance does indeed continue to scale beyond NUMCPUVPS == (#CPUs - 1) and even up to (2 x #CPUs)! I have seen reportsusing 7.24, 7.30 and 7.31. Now perhaps IDS.2000 has changed this I don't know. BTW while 450MHZ+ HP-PA RISC and Sun SPARC CPUs can be stressed like this testing with Intel Pentium class CPUs up to 500MHZ has so far shown no gain. > For more analysis it would be necessary to know how much the CPU VPs are actually > utilized (onstat -g glo) and if the majority of activity is generated by remote or > local sessions. I submit that you have to use OS level monitoring to see how the physical CPUs are being utilized and see. OS tuning may also be neccessary, for example it is difficult to push a Sun SMP box past about 70% utilization without changing the default timeslice down from 0.10 sec so this tunable would affect whether or not additional CPU VPs can get time on the processors. > Hope this helps, Heiko All reasoned discussion helps, thanks. Art S. Kagel In constant pursuit of knowledge. > > > > > > [SNIP] > > > Yuri Dovgart wrote: > > > > > > > DiMisa, John <John.DiMisa@brachs.com> wrote in message > > > > news:7uq9ku$8t3$1@news.xmission.com... > > > > > > > Ladies and Gentlemen: > > > > > > > > > > A little background info - I inherited this SAP R3 job as the DBA - no > > > > > training (will be forthcoming). > > > > > 1 db, 53 dbspaces total, over 8000 tables, have moved all large tables(2+ > > > > > gb) to separate dbspaces. The V box has 12 cpu's. SHM runs close to the > > > > > edge. I think performance is marginal - Any suggestions would be helpful > > > > ! > > > > > Update stats runs weekly via CRON. LVL 0 backup daily, continuous logging. > > [SNIP] > > > > > > > > > > Percentages: > > > > > Data 8.74 > > > > > Btree 83.77 > > > > > Other 7.49 > > > > > > > > Got you ! This is Informix bug, see note #167846. Btree percentage is too > > > > high. We've discussed this some weeks ago - see previous postings. > > > > > > > > > > > > > > onstat -g seg> > > > > Segment Summary: > > > > > id key addr size ovhd class blkused blkfree > > > > > 175129 1381451779 95ba1000 67108864 1632 V 8175 17 > > > > > 390170 1381451780 99c57000 67108864 1632 V 8155 37 > > > > > 252955 1381451781 9dc57000 67108864 1632 V 8138 54 > > >

"Art S. Kagel" wrote: > Heiko Giesselmann wrote: > [SNIP] > > > Why? Except for Obnoxio and Mikey I don't bite anyone. ;-) Also I only > become vehement when someone recommends RAID5 so you are safe. Hey There! I also bet you support Informix's ONWEB product too! :-P If Informix had half a brain they could have produced a much simpler app that didn't require Apache or Perl. But hey, what do I know. I've been too busy trying to pound some intelligence into Informix so that they will see the need for a TPC-C benchmark on Linux. And this has taken months of effort. But then again, what do you expect from their marketing, when some of their tech guys consider porting to be on the same level as rocket science. :-P -Mikey