Re: Config for OLTP system

Neil Truby wrote: > IDS 10.0 v3 on Solaris 9. > > I'd be grateful for any comments on this OLTP system onconfig about to go > live on a SunFire v480 with 2 processors and 8GBytes of RAM. I have only 6 > extrenal disks fo the 60GByte database server. I've striped the database > across 3 of them with a 32k interlace then mirrored this stripe in a RAID > 0+1 arrangement. One thing you might try is inverting the array so you have 3 mirrored pairs then stripe the pairs to make RAID10. Recovery is faster and your data is 50% safer. It's hard to tell anything from just the ONCONFIG info. Nothing jumps out at me except the lack of entries for several critical parameters. The defaults are not great. I'd add: NETTYPE ipcshm,3,100,CPU # Adjust for max concurrent shm sessions NETTYPE tlitcp,2,100,NET # Adjust for max concurrent net sessions LRUS 128 CLEANERS 128 LRU_MAX_DIRTY 5 # These assume an OLTP-like server environment LRU_MIN_DIRTY 1 Otherwise, try posting the following output: onstat -p onstat -d onstat -D onstat -P onstat -g iov onstat -g iof onstat -g glo onstat -F onstat -R Art S. Kagel > Somehow it seems a little sluggish, though we've done no detailed > comparative testing .... > > thanks > Neil > > #************************************************************************** > # > # Licensed Material - Property Of IBM > # > # "Restricted Materials of IBM" > # > # IBM Informix Dynamic Server > # (c) Copyright IBM Corporation 1996, 2005 All rights reserved. > # > # Title: onconfig.std > # Description: IBM Informix Dynamic Server Configuration Parameters > # > #************************************************************************** > # Root Dbspace Configuration > ROOTNAME rootdbs # Root dbspace name > ROOTPATH /opt/informix/dbspaces1/rootdbs_1 > ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) > ROOTSIZE 512000 # Size of root dbspace (Kbytes)> # Disk Mirroring Configuration Parameters > MIRROR 1 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH # Path for device containing mirrored root > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> # Physical Log Configuration > PHYSDBS physdbs # Location (dbspace) of physical log > PHYSFILE 511894 # Physical log file size (Kbytes)> # Logical Log Configuration > LOGFILES 819 # Number of logical log files > LOGSIZE 1000 # Logical log size (Kbytes)> LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT) > # Security > # DBCREATE_PERMISSION: > # By default any user can create a database. Uncomment DBCREATE_PERMISSON to > # limit database creation to a specific user. Add a new DBCREATE_PERMISSION > # line for each permitted user. > #DBCREATE_PERMISSION informix > # IFX_EXTEND_ROLE: > # 0 => Disable use of EXTEND role to control who can register > # external routines. This is the default behaviour. > # 1 => Enable use of EXTEND role to control who can register > # external routines. > IFX_EXTEND_ROLE 0 # To control the usage of EXTEND role.> # Tablespace Tablespace Configuration in Root Dbspace > TBLTBLFIRST 0 # First extent size (Kbytes) (0 = default) > TBLTBLNEXT 0 # Next extent size (Kbytes) (0 = default)> # Diagnostics > MSGPATH /opt/informix/online_1.log # System message log file path > CONSOLE /dev/console # System console message path> # To automatically backup logical logs, edit alarmprogram.sh and set > # BACKUPLOGS=Y > ALARMPROGRAM /usr/informix/etc/alarmprogram.sh # Alarm program path > ALRM_ALL_EVENTS 0 # Triggers ALARMPROGRAM for any event occur > TBLSPACE_STATS 1 # Maintain tblspace statistics> # System Archive Tape Device > #TAPEDEV /opt/informix/dbspaces1/tapedev > TAPEDEV /dev/null > TAPEBLK 768 # Tape block size (Kbytes) > TAPESIZE 60000000 # Maximum amount of data to put on tape (Kbytes)> # Log Archive Tape Device > #LTAPEDEV /dev/null # Log tape device path > LTAPEDEV /opt/informix/backup/logs # Log tape device path > LTAPEBLK 32 # Log tape block size (Kbytes) > LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes) > # Optical> STAGEBLOB # Informix Dynamic Server staging area > # System Configuration > SERVERNUM 1 # Unique id corresponding to a OnLine instance > DBSERVERNAME ifx_shm_1 # Name of default database server > DBSERVERALIASES ifx_tcp_1 > NETTYPE # Configure poll thread(s) for nettype > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. > RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> multi-processor > NUMCPUVPS 4 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to> one > NOAGE 1 # Process aging > AFF_SPROC 0 # Affinity start processor > AFF_NPROCS 0 # Affinity number of processors> # Shared Memory Parameters > LOCKS 20000 # Maximum number of locks > NUMAIOVPS 3 # Number of IO vps > PHYSBUFF 10240 # Physical log buffer size (Kbytes) > LOGBUFF 1024 # Logical log buffer size (Kbytes) > CLEANERS 33 # Number of buffer cleaner processes > SHMBASE 0x10A000000L # Shared memory base address > SHMVIRTSIZE 327680 # initial virtual shared memory segment size > SHMADD 32768 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited > CKPTINTVL 300 # Check point interval (in sec) > TXTIMEOUT 300 # Transaction timeout (in sec) > STACKSIZE 64 # Stack size (Kbytes)> # Dynamic Logging > # DYNAMIC_LOGS: > # 2 : server automatically add a new logical log when necessary. (ON) > # 1 : notify DBA to add new logical logs when necessary. (ON) > # 0 : cannot add logical log on the fly. (OFF) > # > # When dynamic logging is on, we can have higher values for LTXHWM/LTXEHWM, > # because the server can add new logical logs during long transaction > rollback. > # However, to limit the number of new logical logs being added, > LTXHWM/LTXEHWM > # can be set to smaller values. > # > # If dynamic logging is off, LTXHWM/LTXEHWM need to be set to smaller values > # to avoid long transaction rollback hanging the server due to lack of > logical > # log space, i.e. 50/60 or lower. > # > # In case of system configured with CDR, the difference between LTXHWM and > # LTXEHWM should be atleast 30% so that we could minimize log overrun issue. > DYNAMIC_LOGS 2 > LTXHWM 70 > LTXEHWM 80> # System Page Size > # BUFFSIZE - OnLine no longer supports this configuration parameter. > # To determine the page size used by OnLine on your platform > # see the last line of output from the command, 'onstat -b'. > > # Recovery Variables > # OFF_RECVRY_THREADS: > # Number of parallel worker threads during fast recovery or an offline > restore. > # ON_RECVRY_THREADS: > # Number of parallel worker threads during an online restore. > OFF_RECVRY_THREADS 10 # Default number of offline worker threads@@N