Informix performance and CPU use.
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,
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.
The oncfg file is:
#**************************************************************************
#
# INFORMIX SOFTWARE, INC.
#
# Title: onconfig.std
# Description: Informix Dynamic Server Configuration Parameters
#
#**************************************************************************
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace name
ROOTPATH X:\\IFMXDATA\\ol_olinformix\\rootdbs_dat.000
# Path for device containing root dbspace
ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 30720 # 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 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) #
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 formulti-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 vpsto 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)
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 segmentsize
#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
It would have been good to know some details of you system loading, cpu, IO and
ram. What is the average duration of a connection and session?
My experience is on Solaris, however if you have a dedicated 2CPU machine with
3GB of RAM, at least half of it should go to buffers....so you should set
Buffers = 7000000
Leopold Bloom wrote:
> 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,
> 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.
>
> The oncfg file is:
>
> #**************************************************************************
> #
> # INFORMIX SOFTWARE, INC.
> #
> # Title: onconfig.std
> # Description: Informix Dynamic Server Configuration Parameters
> #
> #**************************************************************************
>
> # Root Dbspace Configuration
>
> ROOTNAME rootdbs # Root dbspace name
> ROOTPATH X:\\IFMXDATA\\ol_olinformix\\rootdbs_dat.000>
> # Path for device containing root dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
> ROOTSIZE 30720 # 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 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) #
> 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)
> LOGBUFF 16 # Logical log buffer size (Kbytes)
> CLEANERS 8 # Number of buffer cleane
Have had another look at your onconfig and your comments.
Our checkpoint times avg 20 seconds in 600 sec interval.
I think your LRU parameters are set too low. You need to set the LRU Max to a
larger value so as to allow more data to stay in buffers. If you can tolerate
longer checkpoint durations by tweaking your LRU parameters you will get better
performance.
Bob
see below
Leopold Bloom wrote:
> 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,
> checkpoints are under 2-3 seconds all the time.... does not seem a big
> error but maybe just some more CPU needed.
The reason that your checkpoints are short duration and your cache hit rate is
somewhat low is because your LRU max parameter is set too low. Your should be
able to tolerate a 10 second checkpoint duration in a high volume environment.
>
> 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.
>
> The oncfg file is:
>
> #**************************************************************************
> #
> # INFORMIX SOFTWARE, INC.
> #
> # Title: onconfig.std
> # Description: Informix Dynamic Server Configuration Parameters
> #
> #**************************************************************************
>
> # Root Dbspace Configuration
>
> ROOTNAME rootdbs # Root dbspace name
> ROOTPATH X:\\IFMXDATA\\ol_olinformix\\rootdbs_dat.000>
> # Path for device containing root dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
> ROOTSIZE 30720 # 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 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) #
> 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
The Buffers should be much higher.
> NUMAIOVPS 1 # Number of IO vpss
If your db is