Tuning Recommendations for Informix V10

Greetings .. I am evaulating Informix IBM Informix Dynamic Server Version 10.00.FC3R1TL on a SunOS Solaris 9 Sun-Fire-V240 machine with 2 processors. RAM is 2 GB. We are planning to move our application running currently in Informix 9.3 TC1 version on Windows 2000 Server to a Sun Solaris box with Informix V10. ( I am doing this evaluation to test the functionality of the application. The final hardware configuration will be different). I have created the dbspaces in the Unix file system and trying to import a 40GB database. Using our existing Sun Machine's Informix onconfig, I have done the changes to the Informix 10 onconfig.. Find below the onconfig for Informix V10. #************************************************************************** # # 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 /usr1/rootdbs # Path for device containing rootdbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 1000000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirroredroot MIRROROFFSET 0 # Offset into mirrored device (Kbytes)# Physical Log Configuration PHYSDBS phydbs # Location (dbspace) of physical log PHYSFILE 200000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 25 # Number of logical log files LOGSIZE 20000 # 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 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/IBM/informix/online.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 /opt/IBM/informix/etc/alarmprogram.sh # Alarm program path ALRM_ALL_EVENTS 0 # Triggers ALARMPROGRAM for any eventoccur TBLSPACE_STATS 1 # Maintain tblspace statistics # System Archive Tape Device TAPEDEV /dev/null # Tape device path TAPEBLK 32 # Tape block size (Kbytes) TAPESIZE 10240 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dev/null # 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 0 # Unique id corresponding to a OnLineinstance DBSERVERNAME prime_sun # Name of default database server DBSERVERALIASES # List of alternate dbservernames DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env. RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 2 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 0 # Process aging AFF_SPROC 1 # Affinity start processor AFF_NPROCS 2 # Affinity number of processors # Shared Memory Parameters LOCKS 50000 # Maximum number of locks NUMAIOVPS 64 # Number of IO vps PHYSBUFF 512 # Physical log buffer size (Kbytes) LOGBUFF 512 # Logical log buffer size (Kbytes) CLEANERS 10 # Number of buffer cleaner processes SHMBASE 0x10a000000 # Shared memory base address SHMVIRTSIZE 8192 # initial virtual shared memory segmentsize SHMADD 8192 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) TXTIMEOUT 0x12c # 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 workerthreads ON_RECVRY_THREADS 1 # Default number of online workerthreads # Data Replication Variab

dhaya.snidhi@gmail.com wrote: > There seems to be a Warning with Buffer Pool Size. What change to I > have to do. Also advise on the Checkpoint duration. Assuming Informix is the only (significant) service running on this box and there is only one instance you aren't making much use of the 2Gb memory available on this machine. The BUFFERPOOL configured for a 2k page size is only 100000 big which amounts to 200Mb. Your initial shared memory size is only 8Mb as well. If you need a bigger BUFFERPOOL try increasing 100000 to about 500000 (1Gb) and go from there. Try substantially increasing SHMVIRTSIZE from 8192 to 262144. That uses about 1.25Gb for these areas of Informix and leaves the rest for everything else. These values are somewhat arbitrary and really can only be guesses given I can only work with the information you have supplied. Any changes you make will only take effect when you restart the engine. The long checkpoints may not be a problem with a data load although they'd be horrific for most systems when running normally. Checkpoints are a more efficient way of writing data than LRU writes. I wouldn't worry about them for now. Ben.

NO COOKED ON SOLARIS; NO COOKED ON SOLARIS...... use char mode raw devs instead.!!!!!!! Superboer Ben Thompson schreef: > dhaya.snidhi@gmail.com wrote: > > > There seems to be a Warning with Buffer Pool Size. What change to I > > have to do. Also advise on the Checkpoint duration. > > Assuming Informix is the only (significant) service running on this box > and there is only one instance you aren't making much use of the 2Gb > memory available on this machine. > > The BUFFERPOOL configured for a 2k page size is only 100000 big which > amounts to 200Mb. Your initial shared memory size is only 8Mb as well. > If you need a bigger BUFFERPOOL try increasing 100000 to about 500000 > (1Gb) and go from there. Try substantially increasing SHMVIRTSIZE from > 8192 to 262144. That uses about 1.25Gb for these areas of Informix and > leaves the rest for everything else. These values are somewhat arbitrary > and really can only be guesses given I can only work with the > information you have supplied. Any changes you make will only take > effect when you restart the engine. > > The long checkpoints may not be a problem with a data load although > they'd be horrific for most systems when running normally. Checkpoints > are a more efficient way of writing data than LRU writes. I wouldn't > worry about them for now. > > Ben.