Re: Informix performance and CPU use.

Leopold Bloom said: > Hello, > > We are having some problems with an Informix database and, as I'm > pretty new to this environment, would appreciate any help. > > We have an Informix instance running on a 2 way Intel box with > Windows 2000. This system has 3 Gb memory. > > The main symthoms I can see in this system is slowness in some > moments and high CPU time. The disk time does not seem that high and > the memory comsumption seems pretty stable also. > > I have been reviewing the configuration file and, overall, does > not seem to be that bad. Cache hits is over 90% almost all the time, AAAAARGH!!! Read cache of only 90% would give me the trots. Read cache needs to be consistently above 95% ALL the time for adequate performance, and above 99% for good performance. Please post output from onstat -p onstat -g rea onstat -R onstat -F onstat -u onstat -l onstat -g seg onstat -g glo > checkpoints are under 2-3 seconds all the time.... does not seem a big > error but maybe just some more CPU needed. > > The number of user sessions is 500-900 all the time, so this is > not a small user number. Most of them go through an application -more > controlled- and some of them make ad-hoc queries against the database. > This are still to be controlled. > > I paste the oncfg file below; we are also getting two messages in > the log, which are: > > -<<IBM Informix Dynamic Server>>> Checkpoint log record may not fit > into the logical log buffer. > Recommended minimum value for LOGBUFF is 20. > > --<<IBM Informix Dynamic Server>>> WARNING! Physical Log size 80000 is > too small. > Physical Log overflows may occur during peak activity. > Recommended minimum Physical Log size is 20 times maximum > concurrent user threads. > > This both do not seem that big problem. They would both worry me, although not from a performance perspective. > # Physical Log Configuration > > #PHYSDBS rootdbs # Location (dbspace) of physical log > PHYSDBS physdbs # Location (dbspace) of physical log > #PHYSFILE 2000 # Physical log file size (Kbytes) > #PHYSFILE 50000 # Physical log file size (Kbytes) # Try 500000 > PHYSFILE 80000 # Physical log file size (Kbytes)> > # Logical Log Configuration > > #LOGFILES 6 # Number of logical log files > LOGFILES 10 # Number of logical log files > LOGSIZE 1500 # Logical log size (Kbytes)> #LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, > CONT) > LOG_BACKUP_MODE CONT # Logical log backup mode (MANUAL, CONT) > > # Diagnostics > > #MSGPATH E:\\informix\\ol_olinformix.log # System message log file path > #CONSOLE E:\\informix\\conol_olinformix.log # System console message > path > MSGPATH X:\\IFMXDATA\\ol_olinformix.log # System message log > file path > CONSOLE X:\\IFMXDATA\\conol_olinformix.log # System console > message path > > # To automatically backup logical logs, edit alarmprogram.bat and set > # BACKUPLOGS=Y > ALARMPROGRAM E:\\informix\\etc\\log_full.bat # Alarm > program path > TBLSPACE_STATS 1 # Maintain tblspace statistics > #ALARMPROGRAM X:\\IFMXDATA\\log_full.bat # Alarm program path> > > # System Diagnostic Script. > # SYSALARMPROGRAM - Full path of the system diagnostic script (e.g. > # c:\\informix\\etc\\evidence.bat.) Set this parameter > # if you want a different Diagnostic Script than > # {INFORMIXDIR}\\etc\\evidence.bat, which is default. > > # System Archive Tape Device > > #TAPEDEV \\\\.\\TAPE0 # Tape device path > TAPEDEV V:\\CopiaSeguridadL0\\Informix\\copia # Tape device path > #TAPEDEV NUL # Tape device path > TAPEBLK 16 # Tape block size (Kbytes) > TAPESIZE 75000000 # Maximum amount of data to put on tape > (Kbytes)> > # Log Archive Tape Device > > #LTAPEDEV \\\\.\\TAPE1 # Log tape device path > #LTAPEDEV NUL # Log tape device path > LTAPEDEV V:\\CopiaSeguridad\\Informix\\copilog # Log tape device path > LTAPEBLK 16 # Log tape block size (Kbytes)> #LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes) > LTAPESIZE 80000000 # Max amount of data to put on log tape > (Kbytes)> > # Optical > > STAGEBLOB # Informix Dynamic Server/Optical > staging area > OPTICAL_LIB_PATH # Location of Optical Subsystem driver DLL > > # System Configuration > > SERVERNUM 1 # Unique id corresponding to a OnLine instance > DBSERVERNAME ol_olinformix # Name of default database server > DBSERVERALIASES # List of alternate dbservernames > ### Modificado maximo concurrentes NETTYPE soctcp,2,200,NET #> Override sqlhosts nettype parameters > NETTYPE soctcp,2,500,NET # Override sqlhosts nettype parameters > DEADLOCK_TIMEOUT 90 # Max time to wait of lock in distributed env. > #RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) > RESIDENT 1 # Forced residency flag (Yes = 1, No = 0)> > #MULTIPROCESSOR 0 # 0 for single-processor, 1 for > multi-processor > MULTIPROCESSOR 1 # 0 for single-processor, 1 for> multi-processor > #NUMCPUVPS 1 # Number of user (cpu) vps > NUMCPUVPS 2 # 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 2000 # Maximum number of locks > # Modificado con incremento de usuarios > #LOCKS 100000 # Maximum number of locks > LOCKS 500000 # Maximum number of locks> #BUFFERS 200 # Maximum number of shared buffers > #BUFFERS 200000 # Maximum number of shared buffers > BUFFERS 300000 # Maximum number of shared buffers > NUMAIOVPS 1 # Number of IO vps> #PHYSBUFF 32 # Physical log buffer size (Kbytes) > #PHYSBUFF 64 # Physical log buffer size (Kbytes) > PHYSBUFF 128 # Physical log buffer size (Kbytes)> #LOGBUFF 64 # Logical log buffer size (Kbytes) > #LOGBUFF 32 # Logical log buffer size (Kbytes) Try 32 > LOGBUFF 16 # Logical log buffer size (Kbytes) > CLEANERS 8 # Number of buffer cleaner processes > SHMBASE 0xC000000L # Shared memory base address> #SHMVIRTSIZE 8192 # initial virtual shared memory segment size > #SHMVIRTSIZE 128000 # initial virtual shared memory segment > size > SHMVIRTSIZE 256000 # initial virtual shared memory segment> size > #SHMADD 8192 # Size of new shared memory segments > (Kbytes) > SHMADD 32768 # Size of new shared memory segments > (Kbytes)> #SHMADD 0 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited> #SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited@