FW: Performance Issues
Ps Always do update statistics on major row changes!!!
Min low, optimal high
Wayne
-----Original Message-----
From: Martin, Wayne E. [mailto:WMartin@kmart.com]
Sent: Thursday, June 22, 2000 9:26 AM
To: 'yener@my-deja.com'
Cc: informix-list@iiug.org
Subject: RE: Performance Issues
Increase the check point time interval; if there is a good UPS on the
system.
Increase page cleaners, put a value into the page read ahead
threshold, how long are your check points and what is the out
put from onstat -F any foreground writes?
This batch process does what, only inserts, updates, deletes etc.
Wayne E. Martin
Informix Database Administrator
Kmart Corp.
-----Original Message-----
From: yener@my-deja.com [mailto:yener@my-deja.com]
Sent: Wednesday, June 21, 2000 3:01 PM
To: informix-list@iiug.org
Subject: Performance Issues
Hi,
We are currently running informix on E5000 system which 4 CPUs and 2 MB
of RAM. We have got some performance problems on this system.
The performance of the application running on this system is not
comparable what we had expected. The system is a batch processing
system.
We analys the system and unable to find any suitable solution to
improve performance.
The listing below shows the output of onstat -p.
================================================
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
4043750739 59853281 604861800 0.00 122732622 70823866 1106184833
88.90
isamtot open start read write rewrite delete commit
rollbk
1018217434 2365010653 400986740 1380105727 152884987 127463044 8803283
39620880
366903
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 2000759.78 364288.72 8828 18200
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
179603434 0 3227542476 0 0 18465 9537417
11416813
ixda-RA idx-RA da-RA RA-pgsused lchwaits
1492613204 3357525 1959151075 3444017014 21784573
and the following listing shows our onconfig file
==================================================
#***********************************************************************
***
#
# INFORMIX SOFTWARE, INC.
#
# Title: onconfig.std
# Description: Informix Dynamic Server Configuration Parameters
#
#***********************************************************************
***
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace name
ROOTPATH /export/home/ifmxdata/s01_01 # Path for device containing root
dbspace
ROOTOFFSET 0 # Offset of root dbspace into device
(Kbytes)
ROOTSIZE 2000000 # 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 rootdbs # Location (dbspace) of physical log
PHYSFILE 1000000 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 600 # Number of logical log files
LOGSIZE 20000 # Logical log size (Kbytes)
# Diagnostics
MSGPATH /export/home/informix/online.log # System message log
file path
CONSOLE /dev/console # System console message path
ALARMPROGRAM /export/home/informix/etc/log_full.sh # Alarm programpath
SYSALARMPROGRAM /export/home/informix/etc/evidence.sh
# System Alarm program path
TBLSPACE_STATS 1
# System Archive Tape Device
TAPEDEV /export/home/ifmxdata/tape0 # Tape device path
TAPEBLK 16 # Tape block size (Kbytes)
TAPESIZE 90000000 # Maximum amount of data to put on
tape (Kbytes)
# Log Archive Tape Device
LTAPEDEV /export/home/ifmxdata/tape1 # Log tape device path
LTAPEBLK 16 # Log tape block size (Kbytes)
LTAPESIZE 40000000 # 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 DynamicServer instance
DBSERVERNAME prime # Name of default database server
DBSERVERALIASES kocprime # List of alternatedbservernames
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 for multi-processor
NUMCPUVPS 4 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one
NOAGE 1 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors
# Shared Memory Parameters
LOCKS 5000000 # Maximum number of locks
BUFFERS 400000 # Maximum number of shared buffers
NUMAIOVPS 2 # Number of IO vps
PHYSBUFF 512 # Physical log buffer size (Kbytes)
LOGBUFF 512 # Logical log buffer size (Kbytes)LOGSMAX 600 # Maximum number of logical log files
CLEANERS 32 # Number of buffer cleaner processes
SHMBASE 0xa000000 # Shared memory base address
SHMVIRTSIZE 128000 # initial virtual shared memory segmentsize
SHMADD 32000 # Size of new shared memory segments
(Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 127 # Number of LRU queues
LRU_MAX_DIRTY 10 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 5 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water markpercentage
LTXEHWM 60 # Long transaction high water mark
(exclusive)
TXTIMEOUT 0x12c # 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 onl
In article <8itefh$15d$1@news.xmission.com>,
"Martin, Wayne E." <WMartin@kmart.com> wrote:
>
>
> Ps Always do update statistics on major row changes!!!
We do update statistics for the whole system every week. It lasts 9
hours to update whole database using medium.
>
> Min low, optimal high
>
> Wayne
> -----Original Message-----
> From: Martin, Wayne E. [mailto:WMartin@kmart.com]
> Sent: Thursday, June 22, 2000 9:26 AM
> To: 'yener@my-deja.com'
> Cc: informix-list@iiug.org
> Subject: RE: Performance Issues
>
> Increase the check point time interval; if there is a good UPS on the
> system.
Yes, there is one. What is your suggestion?
>
> Increase page cleaners, put a value into the page read ahead
> threshold, how long are your check points and what is the out
> put from onstat -F any foreground writes?
During normal activity checpoint times are 0 or 1 seconds. However,
when there is a batch processing such as inserting transactions to the
system from a file, it differs from 20 to 40 seconds.
onstat -F doesn't cases any foreground writes.>
> This batch process does what, only inserts, updates, deletes etc.
You are right. It does just inserting, updating and deletes.
Each day over 100000 records inserted to the system.
Yener.
>
> Wayne E. Martin
> Informix Database Administrator
> Kmart Corp.
>
> -----Original Message-----
> From: yener@my-deja.com [mailto:yener@my-deja.com]
> Sent: Wednesday, June 21, 2000 3:01 PM
> To: informix-list@iiug.org
> Subject: Performance Issues
>
> Hi,
>
> We are currently running informix on E5000 system which 4 CPUs and 2
MB
> of RAM. We have got some performance problems on this system.
>
> The performance of the application running on this system is not
> comparable what we had expected. The system is a batch processing
> system.
>
> We analys the system and unable to find any suitable solution to
> improve performance.
>
> The listing below shows the output of onstat -p.
> ================================================
>
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 4043750739 59853281 604861800 0.00 122732622 70823866 1106184833
> 88.90
>
> isamtot open start read write rewrite delete commit
> rollbk
> 1018217434 2365010653 400986740 1380105727 152884987 127463044 8803283
> 39620880
> 366903
>
> gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> 0 0 0 0 0 0 0
>
> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> 0 0 0 2000759.78 364288.72 8828 18200
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress
seqscans
> 179603434 0 3227542476 0 0 18465 9537417
> 11416813
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 1492613204 3357525 1959151075 3444017014 21784573
>
> and the following listing shows our onconfig file
> ==================================================
>
>
#***********************************************************************
> ***
> #
> # INFORMIX SOFTWARE, INC.
> #
> # Title: onconfig.std
> # Description: Informix Dynamic Server Configuration Parameters
> #
>
#***********************************************************************
> ***
>
> # Root Dbspace Configuration
>
> ROOTNAME rootdbs # Root dbspace name
> ROOTPATH /export/home/ifmxdata/s01_01> # Path for device containing root
> dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device
> (Kbytes)
> ROOTSIZE 2000000 # 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 1000000 # Physical log file size (Kbytes)>
> # Logical Log Configuration
>
> LOGFILES 600 # Number of logical log files
> LOGSIZE 20000 # Logical log size (Kbytes)>
> # Diagnostics
>
> MSGPATH /export/home/informix/online.log # System message log
> file path
> CONSOLE /dev/console # System console message path
> ALARMPROGRAM /export/home/informix/etc/log_full.sh # Alarm program> path
> SYSALARMPROGRAM /export/home/informix/etc/evidence.sh
> # System Alarm program path
> TBLSPACE_STATS 1>
> # System Archive Tape Device
> TAPEDEV /export/home/ifmxdata/tape0 # Tape device path
> TAPEBLK 16 # Tape block size (Kbytes)
> TAPESIZE 90000000 # Maximum amount of data to put on
> tape (Kbytes)>
> # Log Archive Tape Device
>
> LTAPEDEV /export/home/ifmxdata/tape1 # Log tape device
path
> LTAPEBLK 16 # Log tape block size (Kbytes)
> LTAPESIZE 40000000 # 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 prime # Name of default database server
> DBSERVERALIASES kocprime # List of alternate> dbservernames
> DEADLOCK_TIMEOUT 60 # Max time to wait of lock in> distributed env.
> RESIDENT 0 # 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 5000000 # Maximum number of locks
> BUFFERS 400000 # Maximum number of shared buffers
> NUMAIOVPS 2 # Number of IO vps
> PHYSBUFF 512 # Physical log buffer size (Kbytes)
> LOGBUFF 512 # Logical log buffer size (Kbytes)> LOGSMAX 600 # Maximum number of logical log files
> CLEANERS 32 # Number of buffer cleaner processes
> SHMBASE 0xa000000 # Shared memory base address
> SHMVIRTSIZE 128000 # initial virtual shared memorysegment
> size
> SHMADD 32000 # Size of new shared memory segments
> (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
> 0=>unlimited
> CKPTINTVL 300
yener@my-deja.com wrote:
>
> In article <8itefh$15d$1@news.xmission.com>,
> "Martin, Wayne E." <WMartin@kmart.com> wrote:
> >
> >
> > Ps Always do update statistics on major row changes!!!
> We do update statistics for the whole system every week. It lasts 9
> hours to update whole database using medium.
Do you do a MEDIUM at the DATABASE level (ie without an ON TABLE clause)?
It is MUCH faster to do individual tables with the advantage that you can
update stats on multiple tables in parallel. Also using my dostats utility
or one of the scripts like it in the IIUG Software Repository will generate
better stats overall than just a simple MEDIUM will. In addition MEDIUM
does NOT update the LOW level stats for each index which help the optimizer
select the most operationally efficient among different indexes with
equivalent filter value. Dostats is part of the package utils2_ak.
Art S. Kagel
> > Min low, optimal high
> >
> > Wayne
> > -----Original Message-----
> > From: Martin, Wayne E. [mailto:WMartin@kmart.com]
> > Sent: Thursday, June 22, 2000 9:26 AM
> > To: 'yener@my-deja.com'
> > Cc: informix-list@iiug.org
> > Subject: RE: Performance Issues
> >
> > Increase the check point time interval; if there is a good UPS on the
> > system.
> Yes, there is one. What is your suggestion?
> >
> > Increase page cleaners, put a value into the page read ahead
> > threshold, how long are your check points and what is the out
> > put from onstat -F any foreground writes?
>
> During normal activity checpoint times are 0 or 1 seconds. However,
> when there is a batch processing such as inserting transactions to the
> system from a file, it differs from 20 to 40 seconds.
>
> onstat -F doesn't cases any foreground writes.> >
> > This batch process does what, only inserts, updates, deletes etc.
>
> You are right. It does just inserting, updating and deletes.
>
> Each day over 100000 records inserted to the system.
>
> Yener.
>
> >
> > Wayne E. Martin
> > Informix Database Administrator
> > Kmart Corp.
> >
> > -----Original Message-----
> > From: yener@my-deja.com [mailto:yener@my-deja.com]
> > Sent: Wednesday, June 21, 2000 3:01 PM
> > To: informix-list@iiug.org
> > Subject: Performance Issues
> >
> > Hi,
> >
> > We are currently running informix on E5000 system which 4 CPUs and 2
> MB
> > of RAM. We have got some performance problems on this system.
> >
> > The performance of the application running on this system is not
> > comparable what we had expected. The system is a batch processing
> > system.
> >
> > We analys the system and unable to find any suitable solution to
> > improve performance.
> >
> > The listing below shows the output of onstat -p.
> > ================================================
> >
> > dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> > 4043750739 59853281 604861800 0.00 122732622 70823866 1106184833
> > 88.90
> >
> > isamtot open start read write rewrite delete commit
> > rollbk
> > 1018217434 2365010653 400986740 1380105727 152884987 127463044 8803283
> > 39620880
> > 366903
> >
> > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> > 0 0 0 0 0 0 0
> >
> > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> > 0 0 0 2000759.78 364288.72 8828 18200
> >
> > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress
> seqscans
> > 179603434 0 3227542476 0 0 18465 9537417
> > 11416813
> >
> > ixda-RA idx-RA da-RA RA-pgsused lchwaits
> > 1492613204 3357525 1959151075 3444017014 21784573
> >
> > and the following listing shows our onconfig file
> > ==================================================
> >
> >
> #***********************************************************************
> > ***
> > #
> > # INFORMIX SOFTWARE, INC.
> > #
> > # Title: onconfig.std
> > # Description: Informix Dynamic Server Configuration Parameters
> > #
> >
> #***********************************************************************
> > ***
> >
> > # Root Dbspace Configuration
> >
> > ROOTNAME rootdbs # Root dbspace name
> > ROOTPATH /export/home/ifmxdata/s01_01> > # Path for device containing root
> > dbspace
> > ROOTOFFSET 0 # Offset of root dbspace into device
> > (Kbytes)
> > ROOTSIZE 2000000 # 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 1000000 # Physical log file size (Kbytes)> >
> > # Logical Log Configuration
> >
> > LOGFILES 600 # Number of logical log files
> > LOGSIZE 20000 # Logical log size (Kbytes)> >
> > # Diagnostics
> >
> > MSGPATH /export/home/informix/online.log # System message log
> > file path
> > CONSOLE /dev/console # System console message path
> > ALARMPROGRAM /export/home/informix/etc/log_full.sh # Alarm program> > path
> > SYSALARMPROGRAM /export/home/informix/etc/evidence.sh
> > # System Alarm program path
> > TBLSPACE_STATS 1> >
> > # System Archive Tape Device
> > TAPEDEV /export/home/ifmxdata/tape0 # Tape device path
> > TAPEBLK 16 # Tape block size (Kbytes)
> > TAPESIZE 90000000 # Maximum amount of data to put on
> > tape (Kbytes)> >
> > # Log Archive Tape Device
> >
> > LTAPEDEV /export/home/ifmxdata/tape1 # Log tape device
> path
> > LTAPEBLK 16 # Log tape block size (Kbytes)
> > LTAPESIZE 40000000 # 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 prime # Name of default database server
> > DBSERVERALIASES kocprime # List of alternate> > dbservernames
> > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in> > distributed env.
> > RESIDENT 0 # 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 # Pro