Tuning OLTP - PHYSBUFF

We have a database using unbuffered logging. The nature of the application (4GL / D4GL) keeps approx. two years of case files with continuous (24x7) file maintenance and retrieval. IDS 7.30.UC10 (recently upgraded from UC2) D4GL 2.10 SCO Unixware 7.0.1 (possible to upgrade to UXW 7.1.1, but only recently were able to order IDS and it looks like we cannot get D4GL 2.1 port and we are not ready/willing? to jump to D4GL 3) 4 CPU Compaq Proliant 6500 (5500??) Tuning Question: What are people's experiences with increasing the size of PHYSBUFF. Background: We have had numerous problems (asserts, performance degredation) on the UXW 7 port and have done a lot of playing with BUFFERS, unixware kernel tuning and patches, Compaq hardware patches, etc. A typical day has about 500 sessions which consume about 750 MB to 1000 MB of logical log usage (5 or 10 MB files). Application is on the same server as the database and most connections use shared memory. As a minimum, we tune BUFFERS, LRU Min/Max, CKPTINVL, NUMAIOVPS to maximize LRU writes and are pretty successful at keeping checkpoints < 1 second. We also increase PHYSFILE, PHYSBUFF and LOGBUFF. Best performance to date (with NETTYPE) has been with 1 CPU poll thread and a large number of connections. Problems: 1) Various Assert failures: - seems to have been cleared up by 1 CPU poll thread. 2) New connections get locked out of both shared memory and TCP connections. Negative sleep times reported with onstat -g ath. - work-aound (from Informix techinfo searches) is to add a poll thread. This will get me back to first problem and reduced performance. - we upgraded to IDS 7.30.UC10, now we get the same results, but do not see negative sleep times. The only indication is that checkpoints increase from 0 seconds to anywhere from 5 to 250 seconds. UNIX CPU time is 95% idle, but still getting long checkpoints and new connection locked out. 3) System reboots during ontape archives. In addition to problem #2, at various time (not always), UNIX is rebooting shortly after starting our level-0 archive with ontape (no informix assert, no Unix Panic, nothing in unix osmlog files). Still has us perplexed.... So we have reverted back to a bare bones onconfig file with minor increase in buffers and locks and increased SHMVIRTSIZE for the number of shared memory users. We are now in the process of tuning each parameter to try to isolate the problem. Starting with more buffers, lrus. Then move to PHYSBUFF. Which gets me back to my question. What are people's experiences with increasing the size of PHYSBUFF. Has it increased through-put. A larger file should reduce the number of writes into PHYSFILE, but isn't that negated by the UNBUFFERED database setting? Thanks, Allan #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig # Description: Informix Dynamic Server Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace nameROOTPATH /IFX_DEVICES/raw01 ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 50000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS logspace # Location (dbspace) of physical log PHYSFILE 10000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 190 # Number of logical log files LOGSIZE 5000 # Logical log size (Kbytes) # Diagnostics MSGPATH /usr2/informix/online.log # System message log file path CONSOLE /dev/null # System console message path ALARMPROGRAM /usr2/informix/etc/log_full.sh # Alarm program pathSYSALARMPROGRAM /usr2/informix/etc/evidence.sh # System Alarm program path TBLSPACE_STATS 1 # System Archive Tape Device TAPEDEV /dev/rmt/ntape3 TAPEBLK 16 # Tape block size (Kbytes) TAPESIZE 15000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dev/rmt/ntape2 LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 15000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # Informix Dynamic Server/Optical staging area # System Configuration SERVERNUM 3 # Unique id corresponding to a DynamicServer in stance DBSERVERNAME ids # Name of default database server DBSERVERALIASES idstcp # List of alternate dbservernames DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) NETTYPE ipcshm,1,900,CPU NETTYPE tlitcp,1,50,NET MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 3 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps toone NOAGE 0 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 50000 # Maximum number of locks BUFFERS 100000 # Maximum number of shared buffers NUMAIOVPS 8 # Number of IO vps PHYSBUFF 256 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes)LOGSMAX 200 # Maximum number of logical log files CLEANERS 8 # Number of buffer cleaner processes SHMBASE 0xa000000L # Shared memory base address SHMVIRTSIZE 128000 SHMADD 128000 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 900 # Check point interval (in sec) LRUS 32 # Number of LRU queues LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 1 # 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 32 # Stack size (Kbytes) # System Page Size # BUFFSIZE - Dynamic Server no longer supports this configuration parameter. # To determine the page size used by Dynamic Server on your platform #

In article <8are3p$e6u1@www.informix.com>, Al Wilson <allan.wilson@versaterm.com> writes >We have a database using unbuffered logging. The nature of the application >(4GL / D4GL) keeps approx. two years of case files with continuous (24x7) >file maintenance and retrieval. > IDS 7.30.UC10 (recently upgraded from UC2) Isn't IDS 7.31.UC4 and above available on UnixWare 7.1?? > D4GL 2.10 > SCO Unixware 7.0.1 (possible to upgrade to UXW 7.1.1, but only >recently were able to order IDS and it looks like we cannot get D4GL 2.1 >port and we are not ready/willing? to jump to D4GL 3) > 4 CPU Compaq Proliant 6500 (5500??) > >Tuning Question: What are people's experiences with increasing the size of >PHYSBUFF. > >Background: >We have had numerous problems (asserts, performance degredation) on the UXW >7 port and have done a lot of playing with BUFFERS, unixware kernel tuning >and patches, Compaq hardware patches, etc. > >A typical day has about 500 sessions which consume about 750 MB to 1000 MB >of logical log usage (5 or 10 MB files). Application is on the same server >as the database and most connections use shared memory. > >As a minimum, we tune BUFFERS, LRU Min/Max, CKPTINVL, NUMAIOVPS to maximize >LRU writes and are pretty successful at keeping checkpoints < 1 second. >We also increase PHYSFILE, PHYSBUFF and LOGBUFF. >Best performance to date (with NETTYPE) has been with 1 CPU poll thread and >a large number of connections. > >Problems: >1) Various Assert failures: >- seems to have been cleared up by 1 CPU poll thread. Move to 7.31, at least 7.31.UC4. Check Tech Alerts in Tech Info centre, there is a Tech Alert about UnixWare 7.1 and kernel patches required! > >2) New connections get locked out of both shared memory and TCP connections. >Negative sleep times reported with onstat -g ath. >- work-aound (from Informix techinfo searches) is to add a poll thread. >This will get me back to first problem and reduced performance. >- we upgraded to IDS 7.30.UC10, now we get the same results, but do not see >negative sleep times. The only indication is that checkpoints increase from >0 seconds to anywhere from 5 to 250 seconds. UNIX CPU time is 95% idle, but >still getting long checkpoints and new connection locked out. > >3) System reboots during ontape archives. >In addition to problem #2, at various time (not always), UNIX is rebooting >shortly after starting our level-0 archive with ontape (no informix assert, >no Unix Panic, nothing in unix osmlog files). Still has us perplexed.... > > >So we have reverted back to a bare bones onconfig file with minor increase >in buffers and locks and increased SHMVIRTSIZE for the number of shared >memory users. > >We are now in the process of tuning each parameter to try to isolate the >problem. Starting with more buffers, lrus. Then move to PHYSBUFF. Which >gets me back to my question. > >What are people's experiences with increasing the size of PHYSBUFF. Has it >increased through-put. A larger file should reduce the number of writes >into PHYSFILE, but isn't that negated by the UNBUFFERED database setting? > >Thanks, >Allan > > >#************************************************************************** ># ># INFORMIX SOFTWARE, INC. ># ># Title: onconfig ># Description: Informix Dynamic Server Configuration Parameters ># >#************************************************************************** > ># Root Dbspace Configuration > >ROOTNAME rootdbs # Root dbspace name>ROOTPATH /IFX_DEVICES/raw01 >ROOTOFFSET 0 # Offset of root dbspace into device >(Kbytes) >ROOTSIZE 50000 # Size of root dbspace (Kbytes)> ># Disk Mirroring Configuration Parameters > >MIRROR 0 # Mirroring flag (Yes = 1, No = 0) >MIRRORPATH # Path for device containing mirrored root >MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> ># Physical Log Configuration > >PHYSDBS logspace # Location (dbspace) of physical log >PHYSFILE 10000 # Physical log file size (Kbytes)> ># Logical Log Configuration > >LOGFILES 190 # Number of logical log files >LOGSIZE 5000 # Logical log size (Kbytes)> ># Diagnostics > >MSGPATH /usr2/informix/online.log # System message log file path >CONSOLE /dev/null # System console message path >ALARMPROGRAM /usr2/informix/etc/log_full.sh # Alarm program path>SYSALARMPROGRAM /usr2/informix/etc/evidence.sh # System Alarm program path >TBLSPACE_STATS 1> ># System Archive Tape Device > >TAPEDEV /dev/rmt/ntape3 > >TAPEBLK 16 # Tape block size (Kbytes) >TAPESIZE 15000000 # Maximum amount of data to put on tape >(Kbytes)> ># Log Archive Tape Device > >LTAPEDEV /dev/rmt/ntape2 >LTAPEBLK 16 # Log tape block size (Kbytes) >LTAPESIZE 15000000 # Max amount of data to put on log tape >(Kbytes)> ># Optical > >STAGEBLOB # Informix Dynamic Server/Optical staging >area > ># System Configuration > >SERVERNUM 3 # Unique id corresponding to a Dynamic>Server in >stance >DBSERVERNAME ids # Name of default database server >DBSERVERALIASES idstcp # List of alternate dbservernames >DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed >env. >RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) >NETTYPE ipcshm,1,900,CPU >NETTYPE tlitcp,1,50,NET > >MULTIPROCESSOR 1 # 0 for single-processor, 1 for>multi-processor >NUMCPUVPS 3 # Number of user (cpu) vps >SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to>one > >NOAGE 0 # Process aging >AFF_SPROC 0 # Affinity start processor >AFF_NPROCS 0 # Affinity number of processors> ># Shared Memory Parameters > >LOCKS 50000 # Maximum number of locks >BUFFERS 100000 # Maximum number of shared buffers >NUMAIOVPS 8 # Number of IO vps >PHYSBUFF 256 # Physical log buffer size (Kbytes) >LOGBUFF 64 # Logical log buffer size (Kbytes)>LOGSMAX 200 # Maximum number of logical log files >CLEANERS 8 # Number of buffer cleaner processes >SHMBASE 0xa000000L # Shared memory base address >SHMVIRTSIZE 128000 >SHMADD 128000 # Size of new shared memory segments >(Kbytes) >SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited >CKPTINTVL 900 # Check point interval (in sec) >LRUS 32 # Number of LRU queues >LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit >LRU_MIN_DIRTY 1 # LRU percent dirty end