Re: Single CPU Performance Problems?
I'm going to ask that you post some onstat output so we can diagnose the
problem. Just posting the ONCONFIG file is like handing a mechanic the
operator's manual for your car and asking whether the timing is too
advanced. That said I'll take some guesses and point up obvious problems.
The onstat output recommendations are below with my config recommendations:
"Gregory P. Schin" wrote:
>
> Hi,
>
> I need some help tuning a single processor Sun Microsystems Ultra 5
> system with 256 meg of memory running IDS 7.3 and Solaris 7. We have an
> application written in Java that frequently loads large amounts of data
> into the database. We can not seem to get reliably good performance
> from this application with IDS on the Sun Platform. The application
> also is being run on Windows NT Server 4.0 with SQL Server 7.0 and we
> get good performance. As a comparison, loading the same data on an NT
> box with 1 Pentium III 400 cpu and 256 meg of memory we can average
> loading 10 rows per second into the database - on the Sun box with IDS
You should expect >100 rows per second per process from IDS!
> 7.3 we can only average 3.5 rows per second. I think we should be able
> to get the performance to at least equal the NT box. Any ideas or
> suggestions?
>
> Thanks,
>
> Greg
>
> #**************************************************************************
>
> #
> # INFORMIX SOFTWARE, INC.
> #
> # Title: onconfig.std
> # Description: Informix Dynamic Server Configuration Parameters
> #
> #**************************************************************************
>
> # Root Dbspace Configuration
>
> ROOTNAME rootdbs # Root dbspace name> ROOTPATH /dev/informix_root # Path for device containing root dbspace
> ROOTOFFSET 2 # Offset of root dbspace into device (Kbytes)
> ROOTSIZE 20000 # 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
> PHYSFILE 1000 # Physical log file size (Kbytes)
Get the physical (and logical) logs out of the rootdbs and into a new
dbspace on a separate disk! BIG performance bottleneck! PHYSFILE is
probably too small. Post output from onstat -l and 30 lines or so from
the online message log (MSGPATH) from around the time of a load to
diagnose this.
>
> # Logical Log Configuration
>
> LOGFILES 6 # Number of logical log files
> LOGSIZE 500 # Logical log size (Kbytes)
MORE LOGS! Otherwise your load may block while the logfiles are being
archived. Configure at least 20 logfiles at this size or perform the
analysis recommended in the Administrators' Guide to determine how much
log space you need. BTW What is the logging mode of this database?
>
> # Diagnostics
>
> MSGPATH /usr/informix/online.log # System message log file path
> CONSOLE /dev/console # System console message path
> # ALARMPROGRAM /usr/informix/etc/log_full.sh # Alarm program path
> ALARMPROGRAM /usr/informix/etc/no_log.sh # Alarm program path> SYSALARMPROGRAM /usr/informix/etc/evidence.sh # System Alarm program
> path
> TBLSPACE_STATS 0>
> # System Archive Tape Device
>
> # TAPEDEV /dev/tapedev # Tape device path
> TAPEDEV /dev/null # Tape device path
> TAPEBLK 16 # Tape block size (Kbytes)
> TAPESIZE 10240 # Maximum amount of data to put on tape (Kbytes)>
> # Log Archive Tape Device
>
> LTAPEDEV /dev/tapedev # Log tape device path
> LTAPEBLK 16 # Log tape block size (Kbytes)
> LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes)>
> # Optical
>
> STAGEBLOB # Informix Dynamic Server/Optical staging area
>
> # System Configuration
>
> SERVERNUM 0 # Unique id corresponding to a Dynamic Server instance
> DBSERVERNAME engledive # Name of default database server
> DBSERVERALIASES # engledive_tp # List of alternate dbservernames
> NETTYPE # Configure poll thread(s) for nettype
Put in some nettype parameters, do not depend on defaults! Post sqlhosts
file and we will make some recommendations, but I'd guess:
NETTYPE ipcshm,1,50,CPU # if engledive is a shared memory
# connection
> DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.
> RESIDENT 1 # Forced residency flag (Yes = 1, No = 0)
>
> MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor
> NUMCPUVPS 1 # Number of user (cpu) vps
> SINGLE_CPU_VP 1 # 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
> BUFFERS 5000 # Maximum number of shared buffers
More buffers! Minimum of 20000. Post onstat -p output.
> NUMAIOVPS 2 # Number of IO vps
> PHYSBUFF 32 # Physical log buffer size (Kbytes)
> LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 6 # Maximum number of logical log files
> CLEANERS 8 # Number of buffer cleaner processes
> SHMBASE # Shared memory base address
> SHMVIRTSIZE 8000 # initial virtual shared memory segment size
> SHMADD 8192 # Size of new shared memory segments (Kbytes)
Post onstat -g seg output from after a load session. SHMVIRTSIZE seems
low to me.
> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
Checkpoint every five minutes is too frequent. Make that 600 or even 900.
> LRUS 8 # Number of LRU queues
> LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
The LRU_MAX/MIN_DIRTY values are too large. They will result in long
checkpoint duration which will block ANY update/inserts/deletes until the
checkpoint is complete. To minimize checkpoint duration use:
LRU_MAX_DIRTY 2
LRU_MIN_DIRTY 0
> LTXHWM 50 # Long transaction high water mark percentage
> LTXEHWM 60 # Long transaction high water mark (exclusive)
> TXTIMEOUT 300 # 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
> # 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
> ON_RECVRY_THREADS 1 # Default number of online worker threads>
> # Data Replication Variables
> # DRAUTO: 0 manual, 1 retain type, 2 reverse type
> DRAUTO 0 # DR automatic switchover
> DRINTERVAL 30 # DR max time between DR buffer flushes
Thank you all for the responses. We have made some changes to the onconfig and
had an improvement to around 18 rows a second. But - after about 1 hour it just
seems to hang. I will post below the new onconfig, the onstat output when it is
running good and then the onconfig output when it is hung. While it is hung the
cpu is pegged, the hard drives are not doing anything and that lasts for about
1/2 hour. After about 1/2 hour it goes back to processing the rows at about 18
rows a second.
I am going to keep trying different things to improve the performance. Also, I
am trying to figure out if the database is causing the 1/2 hr delays.
"Art S. Kagel" wrote:
> I'm going to ask that you post some onstat output so we can diagnose the
> problem. Just posting the ONCONFIG file is like handing a mechanic the
> operator's manual for your car and asking whether the timing is too
> advanced. That said I'll take some guesses and point up obvious problems.
> The onstat output recommendations are below with my config recommendations:
New onconfig -
#**************************************************************************
#
# INFORMIX SOFTWARE, INC.
#
# Title: onconfig.std
# Description: Informix Dynamic Server Configuration Parameters
#
#**************************************************************************
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace nameROOTPATH /dev/informix_root # Path for device containing root dbspace
ROOTOFFSET 2 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 2097149 # 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
PHYSFILE 100000 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 20 # Number of logical log files
LOGSIZE 50000 # Logical log size (Kbytes)
# Diagnostics
MSGPATH /usr/informix/online.log # System message log file path
CONSOLE /dev/console # System console message path
# ALARMPROGRAM /usr/informix/etc/log_full.sh # Alarm program path
ALARMPROGRAM /usr/informix/etc/no_log.sh # Alarm program pathSYSALARMPROGRAM /usr/informix/etc/evidence.sh # System Alarm program path
TBLSPACE_STATS 0
# System Archive Tape Device
# TAPEDEV /dev/tapedev # Tape device path
TAPEDEV /dev/null # Tape device path
TAPEBLK 16 # Tape block size (Kbytes)
TAPESIZE 20000000 # Maximum amount of data to put on tape (Kbytes)
# Log Archive Tape Device
LTAPEDEV /dev/tapedev # Log tape device path
LTAPEBLK 16 # Log tape block size (Kbytes)
LTAPESIZE 20000000000 # Max amount of data to put on log tape (Kbytes)
# Optical
STAGEBLOB # Informix Dynamic Server/Optical staging area
# System Configuration
SERVERNUM 0 # Unique id corresponding to a Dynamic Server instance
DBSERVERNAME engledive # Name of default database server
DBSERVERALIASES # engledive_tp # List of alternate dbservernames
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 0 # 0 for single-processor, 1 for multi-processor
NUMCPUVPS 1 # Number of user (cpu) vps
SINGLE_CPU_VP 1 # 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
BUFFERS 5000 # Maximum number of shared buffers
NUMAIOVPS 2 # Number of IO vps
PHYSBUFF 32 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 40 # Maximum number of logical log files
CLEANERS 8 # Number of buffer cleaner processes
SHMBASE # Shared memory base address
SHMVIRTSIZE 8000 # initial virtual shared memory segment size
SHMADD 8192 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 8 # Number of LRU queues
LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water mark percentage
LTXEHWM 60 # Long transaction high water mark (exclusive)
TXTIMEOUT 300 # 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
# 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
ON_RECVRY_THREADS 1 # Default number of online worker threads
# Data Replication Variables
# DRAUTO: 0 manual, 1 retain type, 2 reverse type
DRAUTO 0 # DR automatic switchover
DRINTERVAL 30 # DR max time between DR buffer flushes (in sec)
DRTIMEOUT 30 # DR network timeout (in sec)
DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file path
# CDR Variables
CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes)
CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional)
CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes)CDR_LOGDELTA 30 # % of log space allowed in queue memory
CDR_NUMCONNECT 16 # Expected connections per server
CDR_NIFRETRY 300 # Connection retry (seconds)
CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max)
# Backup/Restore variables
BAR_ACT_LOG /tmp/bar_act.log
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 10
BAR_XFER_BUF_SIZE 31
# Informix Storage Manager variables
ISM_DATA_POOL ISMData # If the data pool name is changed, be sure to
# update $INFORMIXDIR/bin/onbar. Change to
# ism_catalog -create_bootstrap -pool <new name>
ISM_LOG_POOL ISMLogs
# Read Ahead Variables
RA_PAGES 32 # Number of pages to attempt to read ahead
RA_THRESHOLD 28 # Number of pages left before next group
# DBSPACETEMP:
# Dynamic Server equivalent of DBTEMP for SE. This is the list of dbspaces
# that the Dynamic Server SQL Engine will use to create temp tables etc.
# If specified it must be a colon separated list of dbspaces that exist
# when the Dynamic Server system is brought online. If not specified, or if
# all dbspaces specified are invalid, various ad hoc queries will create
# temporary files in /tmp instead.
DBSPACETEMP # Default temp dbspaces
# DUMP*:
# The following parameters control the type of diagnostics information which
# is preserved when an unanticipated error condition (assertion failure) occurs@
I see the following:
Your logs (physical and logical) are still in the rootdbs which is
flooding that chunk with I/O's (look at the onstat -D output!).
The virtual segment is almost used up, up SHMVIRTSIZE just a tad.
You are also hammering dev1. Consider shifting some active tables to the
other dbspaces or fragmenting the active table if there is only one.
You could probably benefit from more buffers but from the onstat -p output
I'd say don't go over board.
We also need more output to help further:
onstat -F
onstat -R
onstat -g dic
onstat -g dsc
dbschema -ss output (or myschema output) for the active table(s).
If you do deletes you may be running into the buffer priority bug, the
onstats above will help us determine that. Have you updated statistics
according to the recommendations in the Performance Guide?
Art S. Kagel
"Gregory P. Schin" wrote:
>
> Thank you all for the responses. We have made some changes to the onconfig and
> had an improvement to around 18 rows a second. But - after about 1 hour it just
> seems to hang. I will post below the new onconfig, the onstat output when it is
> running good and then the onconfig output when it is hung. While it is hung the
> cpu is pegged, the hard drives are not doing anything and that lasts for about
> 1/2 hour. After about 1/2 hour it goes back to processing the rows at about 18
> rows a second.
>
> I am going to keep trying different things to improve the performance. Also, I
> am trying to figure out if the database is causing the 1/2 hr delays.
>
> "Art S. Kagel" wrote:
>
> > I'm going to ask that you post some onstat output so we can diagnose the
> > problem. Just posting the ONCONFIG file is like handing a mechanic the
> > operator's manual for your car and asking whether the timing is too
> > advanced. That said I'll take some guesses and point up obvious problems.
> > The onstat output recommendations are below with my config recommendations:
>
> New onconfig -
> #**************************************************************************
> #
> # INFORMIX SOFTWARE, INC.
> #
> # Title: onconfig.std
> # Description: Informix Dynamic Server Configuration Parameters
> #
> #**************************************************************************
>
> # Root Dbspace Configuration
>
> ROOTNAME rootdbs # Root dbspace name> ROOTPATH /dev/informix_root # Path for device containing root dbspace
> ROOTOFFSET 2 # Offset of root dbspace into device (Kbytes)
> ROOTSIZE 2097149 # 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
> PHYSFILE 100000 # Physical log file size (Kbytes)>
> # Logical Log Configuration
>
> LOGFILES 20 # Number of logical log files
> LOGSIZE 50000 # Logical log size (Kbytes)>
> # Diagnostics
>
> MSGPATH /usr/informix/online.log # System message log file path
> CONSOLE /dev/console # System console message path
> # ALARMPROGRAM /usr/informix/etc/log_full.sh # Alarm program path
> ALARMPROGRAM /usr/informix/etc/no_log.sh # Alarm program path> SYSALARMPROGRAM /usr/informix/etc/evidence.sh # System Alarm program path
> TBLSPACE_STATS 0>
> # System Archive Tape Device
>
> # TAPEDEV /dev/tapedev # Tape device path
> TAPEDEV /dev/null # Tape device path
> TAPEBLK 16 # Tape block size (Kbytes)
> TAPESIZE 20000000 # Maximum amount of data to put on tape (Kbytes)>
> # Log Archive Tape Device
>
> LTAPEDEV /dev/tapedev # Log tape device path
> LTAPEBLK 16 # Log tape block size (Kbytes)
> LTAPESIZE 20000000000 # Max amount of data to put on log tape (Kbytes)>
> # Optical
>
> STAGEBLOB # Informix Dynamic Server/Optical staging area
>
> # System Configuration
>
> SERVERNUM 0 # Unique id corresponding to a Dynamic Server instance
> DBSERVERNAME engledive # Name of default database server
> DBSERVERALIASES # engledive_tp # List of alternate dbservernames
> 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 0 # 0 for single-processor, 1 for multi-processor
> NUMCPUVPS 1 # Number of user (cpu) vps
> SINGLE_CPU_VP 1 # 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
> BUFFERS 5000 # Maximum number of shared buffers
> NUMAIOVPS 2 # Number of IO vps
> PHYSBUFF 32 # Physical log buffer size (Kbytes)
> LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 40 # Maximum number of logical log files
> CLEANERS 8 # Number of buffer cleaner processes
> SHMBASE # Shared memory base address
> SHMVIRTSIZE 8000 # initial virtual shared memory segment size
> SHMADD 8192 # Size of new shared memory segments (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> LRUS 8 # Number of LRU queues
> LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
> LTXHWM 50 # Long transaction high water mark percentage
> LTXEHWM 60 # Long transaction high water mark (exclusive)
> TXTIMEOUT 300 # 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
> # 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
> ON_RECVRY_THREADS 1 # Default number of online worker threads>
> # Data Replication Variables
> # DRAUTO: 0 manual, 1 retain type, 2 reverse type
> DRAUTO 0 # DR automatic switchover
> DRINTERVAL 30 # DR max time between DR buffer flushes (in sec)
> DRTIMEOUT 30 # DR network timeout (in sec)
> DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file path>
> # CDR Variables
> CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes)
> CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional)
> CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
> CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes)> CDR_LOGDELTA 30 # % of log space allowed in queue memory
> CDR_NUMCONNECT 16 # Expected connections per server
> CDR_NIFRETRY 300 # Connection retry (seconds)
> CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 non
I see the following:
Your logs (physical and logical) are still in the rootdbs which is
flooding that chunk with I/O's (look at the onstat -D output!).
The virtual segment is almost used up, up SHMVIRTSIZE just a tad.
You are also hammering dev1. Consider shifting some active tables to the
other dbspaces or fragmenting the active table if there is only one.
You could probably benefit from more buffers but from the onstat -p output
I'd say don't go over board.
The LRU_MAX/MIN_DIRTY are STILL WAY too high for a data load/OLTP system!
Get that down to 5/2 or even 3/0.
We also need more output to help further:
onstat -F
onstat -R
onstat -g dic
onstat -g dsc
dbschema -ss output (or myschema output) for the active table(s).
If you do deletes you may be running into the buffer priority bug, the
onstats above will help us determine that. Have you updated statistics
according to the recommendations in the Performance Guide?
Art S. Kagel
"Gregory P. Schin" wrote:
>
> Thank you all for the responses. We have made some changes to the onconfig and
> had an improvement to around 18 rows a second. But - after about 1 hour it just
> seems to hang. I will post below the new onconfig, the onstat output when it is
> running good and then the onconfig output when it is hung. While it is hung the
> cpu is pegged, the hard drives are not doing anything and that lasts for about
> 1/2 hour. After about 1/2 hour it goes back to processing the rows at about 18
> rows a second.
>
> I am going to keep trying different things to improve the performance. Also, I
> am trying to figure out if the database is causing the 1/2 hr delays.
>
> "Art S. Kagel" wrote:
>
> > I'm going to ask that you post some onstat output so we can diagnose the
> > problem. Just posting the ONCONFIG file is like handing a mechanic the
> > operator's manual for your car and asking whether the timing is too
> > advanced. That said I'll take some guesses and point up obvious problems.
> > The onstat output recommendations are below with my config recommendations:
>
> New onconfig -
> #**************************************************************************
> #
> # INFORMIX SOFTWARE, INC.
> #
> # Title: onconfig.std
> # Description: Informix Dynamic Server Configuration Parameters
> #
> #**************************************************************************
>
> # Root Dbspace Configuration
>
> ROOTNAME rootdbs # Root dbspace name> ROOTPATH /dev/informix_root # Path for device containing root dbspace
> ROOTOFFSET 2 # Offset of root dbspace into device (Kbytes)
> ROOTSIZE 2097149 # 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
> PHYSFILE 100000 # Physical log file size (Kbytes)>
> # Logical Log Configuration
>
> LOGFILES 20 # Number of logical log files
> LOGSIZE 50000 # Logical log size (Kbytes)>
> # Diagnostics
>
> MSGPATH /usr/informix/online.log # System message log file path
> CONSOLE /dev/console # System console message path
> # ALARMPROGRAM /usr/informix/etc/log_full.sh # Alarm program path
> ALARMPROGRAM /usr/informix/etc/no_log.sh # Alarm program path> SYSALARMPROGRAM /usr/informix/etc/evidence.sh # System Alarm program path
> TBLSPACE_STATS 0>
> # System Archive Tape Device
>
> # TAPEDEV /dev/tapedev # Tape device path
> TAPEDEV /dev/null # Tape device path
> TAPEBLK 16 # Tape block size (Kbytes)
> TAPESIZE 20000000 # Maximum amount of data to put on tape (Kbytes)>
> # Log Archive Tape Device
>
> LTAPEDEV /dev/tapedev # Log tape device path
> LTAPEBLK 16 # Log tape block size (Kbytes)
> LTAPESIZE 20000000000 # Max amount of data to put on log tape (Kbytes)>
> # Optical
>
> STAGEBLOB # Informix Dynamic Server/Optical staging area
>
> # System Configuration
>
> SERVERNUM 0 # Unique id corresponding to a Dynamic Server instance
> DBSERVERNAME engledive # Name of default database server
> DBSERVERALIASES # engledive_tp # List of alternate dbservernames
> 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 0 # 0 for single-processor, 1 for multi-processor
> NUMCPUVPS 1 # Number of user (cpu) vps
> SINGLE_CPU_VP 1 # 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
> BUFFERS 5000 # Maximum number of shared buffers
> NUMAIOVPS 2 # Number of IO vps
> PHYSBUFF 32 # Physical log buffer size (Kbytes)
> LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 40 # Maximum number of logical log files
> CLEANERS 8 # Number of buffer cleaner processes
> SHMBASE # Shared memory base address
> SHMVIRTSIZE 8000 # initial virtual shared memory segment size
> SHMADD 8192 # Size of new shared memory segments (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> LRUS 8 # Number of LRU queues
> LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
> LTXHWM 50 # Long transaction high water mark percentage
> LTXEHWM 60 # Long transaction high water mark (exclusive)
> TXTIMEOUT 300 # 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
> # 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
> ON_RECVRY_THREADS 1 # Default number of online worker threads>
> # Data Replication Variables
> # DRAUTO: 0 manual, 1 retain type, 2 reverse type
> DRAUTO 0 # DR automatic switchover
> DRINTERVAL 30 # DR max time between DR buffer flushes (in sec)
> DRTIMEOUT 30 # DR network timeout (in sec)
> DRLOSTFOUND /usr/informix/etc/dr.lostfound # DR lost+found file path>
> # CDR Variables
> CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes)
> CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional)
> CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds)
> CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes)> CDR_LOGDELTA 30 # % of log space allowed in queue memory
> CDR_NUMCONNECT 16 # Expected connections per se
Art,
Thank you - you have been very helpful!
I am trying to find the best performance with this hardware platform before I start to
add SCSI hard drives/more hard drives, etc. By the way - we have turned off logging
for now.
"Art S. Kagel" wrote:
> I see the following:
>
> Your logs (physical and logical) are still in the rootdbs which is
> flooding that chunk with I/O's (look at the onstat -D output!).
Since I have only two disks right now, I thought it made sense to have the rootdbs
with the logs on the first disk and the other dbspaces on the second disk. Do you
think I should have the logs in a separate dbspace on the first disk?
> The virtual segment is almost used up, up SHMVIRTSIZE just a tad.
>
> You are also hammering dev1. Consider shifting some active tables to the
> other dbspaces or fragmenting the active table if there is only one.
All of the tables are be fragmented between the four dbspaces on the second disk now.
>
>
> You could probably benefit from more buffers but from the onstat -p output
> I'd say don't go over board.
>
> The LRU_MAX/MIN_DIRTY are STILL WAY too high for a data load/OLTP system!
> Get that down to 5/2 or even 3/0.
I tried this but found a fairly large performance improvement with 80/60. However, I
have not tested this environment under a heavy user load (with lots of queries etc.)
So I may end up going back to lower numbers as you suggest.
>
>
> We also need more output to help further:
>
> onstat -F
Informix Dynamic Server Version 7.31.UC2A -- On-Line -- Up 01:12:05 -- 57344 Kbytes
Fg Writes LRU Writes Chunk Writes
0 4790 27563
address flusher state data
c04c504 0 I 0 = 0X0
c04c9f0 1 I 0 = 0X0
c04cedc 2 I 0 = 0X0
c04d3c8 3 I 0 = 0X0
c04d8b4 4 I 0 = 0X0
c04dda0 5 I 0 = 0X0
c04e28c 6 I 0 = 0X0
c04e778 7 I 0 = 0X0
states: Exit Idle Chunk Lru
>
> onstat -R
Informix Dynamic Server Version 7.31.UC2A -- On-Line -- Up 01:12:53 -- 57344 Kbytes
8 buffer LRU queue pairs priority levels
# f/m pair total % of length LOW MED_LOW MED_HIGH HIGH
0 f 1246 24.0% 299 0 276 23 0
1 m 76.0% 947 0 938 9 0
2 f 1246 23.2% 289 1 259 29 0
3 m 76.8% 957 0 952 5 0
4 F 1244 24.6% 306 0 278 28 0
5 m 75.4% 938 0 934 4 0
6 f 1246 25.3% 315 0 285 30 0
7 m 74.7% 931 0 929 2 0
8 f 1246 21.5% 268 0 239 29 0
9 m 78.5% 978 0 974 4 0
10 f 1249 25.5% 318 1 292 25 0
11 m 74.5% 931 0 927 4 0
12 f 1245 25.0% 311 0 283 28 0
13 m 75.0% 934 0 923 11 0
14 f 1245 26.3% 328 0 299 29 0
15 m 73.7% 917 0 913 4 0
7533 dirty, 9967 queued, 10000 total, 16384 hash buckets, 2048 buffer size
start clean at 80% (of pair total) dirty, or 1000 buffs dirty, stop at 60%
0 priority downgrades, 0 priority upgrades
>
> onstat -g dic
Dictionary Cache: Number of lists: 31, Maximum list size: 10
list# size refcnt dirty? heapptr table name
--------------------------------------------------------
0 3 4 no c4de020 exp2@explorer4:root.status_contact
3 no c45b020 exp2@explorer4:root.agent
1 no c4b6420 exp2@explorer4:root.info_contact
1 1 2 no c48f420 exp2@explorer4:root.count_contact
2 2 1 no c45a820 exp2@explorer4:root.date_dow
1 no c536c20 exp2@explorer4:root.file_errors
3 2 3 no c4c0020 exp2@explorer4:root.ssr
2 no c366020 exp2@explorer4:root.nn
4 1 4 no c445020 exp2@explorer4:root.contact
7 4 3 no c4d4820 exp2@explorer4:root.status_segment
2 no c4ecc20 exp2@explorer4:root.v_segment
2 no c4ac420 exp2@explorer4:root.info_segment
17 no c360820 exp2@explorer4:root.ms
8 6 1 no c481c20 exp2@explorer4:root.count_segment
4 no c096e30 exp2@explorer4:root.db_status
0 no c532820 exp2@explorer4:root.query
0 no c376420 exp2@explorer4:root.v_desc
0 no c373820 exp2@explorer4:root.status_desc
0 no c36b420 exp2@explorer4:root.info_desc
9 2 18 no c434820 exp2@explorer4:root.file_status
0 no c372020 exp2@explorer4:root.count_desc
10 1 0 no c517020 exp2@explorer4:root.login
11 1 5 no c435020 exp2@explorer4:root.segment
12 1 2 no c516020 exp2@explorer4:root.v_type
13 1 2 no c480420 exp2@explorer4:root.agent_segment
14 2 5 no c42c020 exp2@explorer4:root.key_table
0 no c36a820 exp2@explorer4:root.agent_desc
15 1 17 no c42cc20 exp2@explorer4:root.files_read
16 1 2 no c4c1820 exp2@explorer4:root.ssr_segment
17 1 0 no c377020 exp2@explorer4:root.ssr_desc
21 2 2 no c516820 exp2@explorer4:root.ssr_type
3 no c097630 exp2@explorer4:root.license_table
23 1 1 no c367820 exp2@explorer4:root.exp
25 4 6 no c4c7020 exp2@explorer4:root.status
3 no c4df420 exp2@explorer4:root.v
3 no c496c20 exp2@explorer4:root.info
0 no c096330 exp2@explorer4:informix.systables
26 1 0 no c536420 exp2@explorer4:root.query_type
27 2 1 no c537c20 exp2@explorer4:root.disposition
0 no c372c20 exp2@explorer4:root.stroke_desc
Total number of dictionary entries: 40
>
> onstat -g dsc
Distribution Cache:
Number of lists : 31
DS_POOLSIZE : 127
Number of entries : 29 Number of entries in use : 0
Distribution Cache Entries:
list# id ref_cnt dropped? heap_ptr distribution name
-----------------------------------------------------------------
17 0 0 0 c553420
exp2@explorer4:root.file_errors.files_read_key@@NL
Art,
I hope this is what you wanted. It was a long day yesterday. Thanks again for your
help. The performance now consistently meets or exceeds the comparable NT box that is
running SQL Server. I have read over the Performance Guide and we are going to try to
implement the Update Statistics suggestions with distributions. The only time things
slow down is during a checkpoint. Now I am going to try to get this working with
logging turned on!
Greg Schin
dbschema -ss -d exp2 -t all
DBSCHEMA Schema Utility INFORMIX-SQL Version 7.31.UC2A
Copyright (C) Informix Software, Inc., 1984-1998
Software Serial Number AAC#J718948
{ TABLE "root".date_dow row size = 8 number of columns = 3 index size = 12 }
create table "root".date_dow
(
date_val integer not null ,
dow smallint,
week smallint,
primary key (date_val) constraint "root".pk_date_dow
)
fragment by round robin in exp1 , exp2 , exp3 , exp4
extent size 16 next size 16 lock mode row;
revoke all on "root".date_dow from "public";
{ TABLE "root".key_table row size = 59 number of columns = 3 index size = 88 }
create table "root".key_table
(
table_name varchar(50) not null ,
parent_key integer not null ,
last_key integer,
primary key (table_name,parent_key) constraint "root".pk_key_table
)
fragment by round robin in exp1 , exp2 , exp3 , exp4
extent size 16 next size 16 lock mode row;
revoke all on "root".key_table from "public";
{ TABLE "root".db_status row size = 56 number of columns = 9 index size = 0 }
create table "root".db_status
(
create_date datetime year to second,
update_date datetime year to second,
orig_version integer,
curr_version integer,
status integer,
max_call_days integer,
last_discard datetime year to second,
stop_process float,
curr_percent float
)
fragment by round robin in exp1 , exp2 , exp3 , exp4
extent size 16 next size 16 lock mode row;
revoke all on "root".db_status from "public";
{ TABLE "root".nn_desc row size = 33 number of columns = 2 index size = 9 }
create table "root".nn_desc
(
nn_desc_key smallint not null ,
nn_descr varchar(30),
primary key (nn_desc_key) constraint "root".pk_nn_desc
)
fragment by round robin in exp1 , exp2 , exp3 , exp4
extent size 16 next size 16 lock mode row;
revoke all on "root".nn_desc from "public";
{ TABLE "root".ms_desc row size = 33 number of columns = 2 index size = 9 }
create table "root".ms_desc
(
ms_desc_key smallint not null ,
descr varchar(30),
primary key (ms_desc_key) constraint "root".pk_ms_desc
)
fragment by round robin in exp1 , exp2 , exp3 , exp4
extent size 16 next size 16 lock mode row;
revoke all on "root".ms_desc from "public";
{ TABLE "root".v_type row size = 33 number of columns = 2 index size = 9 }
create table "root".v_type
(
v_type_key smallint not null ,
descr varchar(30),
primary key (v_type_key) constraint "root".pk_v_type
) extent size 16 next size 16 lock mode page;
revoke all on "root".v_type from "public";
{ TABLE "root".ssr_type row size = 33 number of columns = 2 index size = 9 }
create table "root".ssr_type
(
ssr_type_key smallint not null ,
descr varchar(30),
primary key (ssr_type_key) constraint "root".pk_ssr_type
)
fragment by round robin in exp1 , exp2 , exp3 , exp4
extent size 16 next size 16 lock mode row;
revoke all on "root".ssr_type from "public";
{ TABLE "root".query_type row size = 35 number of columns = 2 index size = 12 }
create table "root".query_type
(
query_type_key integer not null ,
descr varchar(30),
primary key (query_type_key) constraint "root".pk_query_type
)
fragment by round robin in exp1 , exp2 , exp3 , exp4
extent size 16 next size 16 lock mode row;
revoke all on "root".query_type from "public";
{ TABLE "root".exp row size = 55 number of columns = 2 index size = 12 }
create table "root".exp
(
exp_key integer not null ,
descr varchar(50),
primary key (exp_key) constraint "root".pk_exp
)
fragment by round robin in exp1 , exp2 , exp3 , exp4
extent size 16 next size 16 lock mode row;
revoke all on "root".exp from "public";
{ TABLE "root".file_status row size = 53 number of columns = 2 index size = 9 }
create table "root".file_status
(
file_status_key smallint not null ,
descr varchar(50),
primary key (file_status_key) constraint "root".pk_file_status
)
fragment by round robin in exp1 , exp2 , exp3 , exp4
extent size 16 next size 16 lock mode row;
revoke all on "root".file_status from "public";
{ TABLE "root".disposition row size = 35 number of columns = 3 index size = 21 }
create table "root".disposition
(
disposition smallint not null ,
ms_desc_key smallint not null ,
descr varchar(30),
primary key (disposition,ms_desc_key) constraint "root".pk_disposition
)
fragment by round robin in exp1 , exp2 , exp3 , exp4
extent size 16 next size 16 lock mode row;
revoke all on "root".disposition from "public";
{ TABLE "root".fact_desc row size = 35 number of columns = 3 index size = 21 }
create table "root".fact_desc
(
fact_desc_key smallint not null ,
ms_desc_key smallint not null ,
descr varchar(30),
primary key (fact_desc_key,ms_desc_key) constraint "root".pk_fact_desc
) extent size 16 next size 16 lock mode page;
revoke all on "root".fact_desc from "public";
{ TABLE "root".status row size = 28 number of columns = 5 index size = 48 }
create table "root".status
(
exp_key integer not null ,
status_key integer not null ,
disposition smallint,
ms_desc_key smallint,
status char(16),
primary key (exp_key,status_key) constraint "root".pk_status
)
fragment by round robin in exp1 , exp2 , exp3 , exp4
extent size 16 next size 16 lock mode row;
revoke all on "root".status from "public";
create index "root".idx_exp_status on "root".status (exp_key,ms_desc_key,
disposition);
{ TABLE "root".login row size = 50 number of columns = 5 index size = 18 }
create table "root".login
(
exp_key integer not null ,
login_key integer not null ,
login varchar(10),
passwd varchar(10),
privs char(20),
primary key (exp_key,login_key) constraint "root".pk_login
)
fragment by round robin in exp1 , exp2 , exp3