Low performance, high bufwait ratio
I'm new to this group and to informix, but would
greatly appreciate any hints as to why our db
is running slowly.
I used this little awk-snippet to calculate
the bufwait-ratio:
onstat -p | awk '{if (NR==6)
{pagreads=$2;pagwrites=$6;} else if (NR==18)
{bufwait=$1;}} END{printf("Pagreads: "pagreads",Pagwrites: "pagwrites", Bufwaits: "bufwait",
Bufwait-ratio: "(bufwait*100)/
(padreads+pagwrites)"\\n");}'
(Thats 1 line)
the bufwait-ratio climbs to around 10% a few
hours after the servere is restarted...
Specs:
Informix Dynamic Server Version 7.31.UC6SunOS
skryme 5.6 Generic_105181-21 sun4u sparc
SUNW,Ultra-4
onstat -p:
Informix Dynamic Server Version 7.31.UC6 -- On-
Line -- Up 09:14:10 -- 294912 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits
pagwrits bufwrits %cached
3750439 7781497 125364970 97.01 1299273
3773694 3842573 66.19
isamtot open start read write
rewrite delete commit rollbk
59318250 21825 5860214 39036716 558858
811658 61728 630 0
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 3785.78 366.62
124 248
bufwaits lokwaits lockreqs deadlks dltouts
ckpwaits compress seqscans
362768 0 13738398 0 0
126 224990 42113
ixda-RA idx-RA da-RA RA-pgsused lchwaits
2093540 63171 737978 2891363 13666
onconfig:
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspacename
ROOTPATH /usr/ifmxdata/rootdbs.000 # Path
for device containing root dbsp
ace
ROOTOFFSET 100 # Offset of rootdbspace 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 devicecontaining mirrored root
MIRROROFFSET 0 # Offset intomirrored device (Kbytes)
# Physical Log Configuration
PHYSDBS rootdbs # Location
(dbspace) of physical log
PHYSFILE 30000 # Physical log
file size (Kbytes)
# Logical Log Configuration
LOGFILES 6 # Number oflogical log files
LOGSIZE 500 # 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
SYSALARMPROGRAM /usr/informix/etc/evidence.sh #
System Alarm program path
TBLSPACE_STATS 1
# System Archive Tape Device
TAPEDEV /dev/rmt/0m # Tape device path
TAPEBLK 16 # Tape block size
(Kbytes)
TAPESIZE 70000000 # Maximum amountof data to put on tape (Kbytes)
# Log Archive Tape Device
LTAPEDEV /dev/null # Log tape devicepath
LTAPEBLK 16 # Log tape block
size (Kbytes)
LTAPESIZE 10240 # Max amount ofdata to put on log tape (Kbytes)
# Optical
STAGEBLOB # Informix
Dynamic Server/Optical staging area
# System Configuration
SERVERNUM 0 # Unique idcorresp to a IDS Server instance
DBSERVERNAME ol_dw_tcp # Name of defaultdatabase server
DBSERVERALIASES ol_dw_shm # List ofalternate dbservernames
#NETTYPE ipcshm,2,75,CPU #
Configure poll thread(s) for n
ettype
#NETTYPE tlitcp,2,75,NET #
Endret 21.06.00 lbm@ecsoft.no
NETTYPE ipcshm,1,,CPU #Configure poll thread(s) for nettype
NETTYPE tlitcp,1,,CPU
DEADLOCK_TIMEOUT 60 # Max time towait of lock in distributed env.
#RESIDENT 0 # Forced
residency flag (Yes = 1, No = 0)
RESIDENT 1 # Endret
20.06.00 - kjellh@informix.com
MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor
#NUMCPUVPS 1 # Number of user
(cpu) vps
NUMCPUVPS 2
SINGLE_CPU_VP 0 # If non-zero,limit number of cpu vps to one
#NOAGE 0 # Process aging
NOAGE 1
AFF_SPROC 0 # Affinity startprocessor
AFF_NPROCS 0 # Affinity numberof processors
# Shared Memory Parameters
#LOCKS 20000 # Maximum number
of locks
LOCKS 200000 # Endret
20.06.00 / kjellh@infomrix.com
#BUFFERS 50000 # Maximum numberof shared buffers
BUFFERS 100000 # Endret
20.06.00 / kjellh@informix.com#NUMAIOVPS # Number of IO vps
NUMAIOVPS 2 # Endret
20.06.00 - kjellh@informix.com
PHYSBUFF 128 # Physical log
buffer size (Kbytes)
LOGBUFF 32 # Logical log
buffer size (Kbytes)LOGSMAX 6 # Maximum number
of logical log files
#CLEANERS 1 # Number of
buffer cleaner processes
#CLEANERS 8 # endret 21.06.00
lbm@ecsoft.no
CLEANERS 64
SHMBASE 0x0A000000L # Sharedmemory base address
#SHMVIRTSIZE 32000 # initial virtual
shared memory segment size
SHMVIRTSIZE 65536 # Endret
20.06.00 - kjellh@informix.com
SHMADD 16384 # Size of newshared memory segments (Kbytes)
SHMTOTAL 0 # Total shared
memory (Kbytes). 0=>unlimited
CKPTINTVL 300 # Check point
interval (in sec)#LRUS 8 # Number of LRU
queues
LRUS 64 # Endret
21.06.00 - lbm@ecsoft.no pga 17% bufwaits
#LRU_MAX_DIRTY 50 # LRU percent
dirty begin cleaning limit
#LRU_MIN_DIRTY 40 # LRU percent
dirty end cleaning limit
LRU_MAX_DIRTY 10 # Endret
20.06.00 - kjellh@informix.com
LRU_MIN_DIRTY 5 # Endret
20.06.00 - kjellh@informix.com
LTXHWM 50 # Longtransaction high water mark percentage
LTXEHWM 60 # Longtransaction 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
For one thing, I would change OPTCOMPIND to 0.
And, I think, the number 64 for LRUs is one of the nasty ones. Try
increasing this a little (and cleaners to match).
HTH,
Doug
<tompilianas@my-deja.com> wrote in message
news:8isgmg$tsg$1@nnrp2.deja.com...
> I'm new to this group and to informix, but would
> greatly appreciate any hints as to why our db
> is running slowly.
>
> I used this little awk-snippet to calculate
> the bufwait-ratio:
>
> onstat -p | awk '{if (NR==6)
> {pagreads=$2;pagwrites=$6;} else if (NR==18)
> {bufwait=$1;}} END{printf("Pagreads: "pagreads",> Pagwrites: "pagwrites", Bufwaits: "bufwait",
> Bufwait-ratio: "(bufwait*100)/
> (padreads+pagwrites)"\\n");}'
>
> (Thats 1 line)
>
> the bufwait-ratio climbs to around 10% a few
> hours after the servere is restarted...
>
>
> Specs:
> Informix Dynamic Server Version 7.31.UC6SunOS
> skryme 5.6 Generic_105181-21 sun4u sparc
> SUNW,Ultra-4
>
> onstat -p:
> Informix Dynamic Server Version 7.31.UC6 -- On-
> Line -- Up 09:14:10 -- 294912 Kbytes>
> Profile
> dskreads pagreads bufreads %cached dskwrits
> pagwrits bufwrits %cached
> 3750439 7781497 125364970 97.01 1299273
> 3773694 3842573 66.19
>
> isamtot open start read write
> rewrite delete commit rollbk
> 59318250 21825 5860214 39036716 558858
> 811658 61728 630 0
>
> 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 3785.78 366.62
> 124 248
>
> bufwaits lokwaits lockreqs deadlks dltouts
> ckpwaits compress seqscans
> 362768 0 13738398 0 0
> 126 224990 42113
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 2093540 63171 737978 2891363 13666
>
>
> onconfig:
> # Root Dbspace Configuration
>
> ROOTNAME rootdbs # Root dbspace> name
> ROOTPATH /usr/ifmxdata/rootdbs.000 # Path
> for device containing root dbsp
> ace
> ROOTOFFSET 100 # 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 30000 # Physical log
> file size (Kbytes)>
> # Logical Log Configuration
>
> LOGFILES 6 # Number of> logical log files
> LOGSIZE 500 # 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
> SYSALARMPROGRAM /usr/informix/etc/evidence.sh #
> System Alarm program path
> TBLSPACE_STATS 1>
> # System Archive Tape Device
>
> TAPEDEV /dev/rmt/0m # Tape device path
> TAPEBLK 16 # Tape block size
> (Kbytes)
> TAPESIZE 70000000 # Maximum amount> of data to put on tape (Kbytes)
>
> # Log Archive Tape Device
>
> LTAPEDEV /dev/null # 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> corresp to a IDS Server instance
> DBSERVERNAME ol_dw_tcp # Name of default> database server
> DBSERVERALIASES ol_dw_shm # List of> alternate dbservernames
> #NETTYPE ipcshm,2,75,CPU #
> Configure poll thread(s) for n
> ettype
> #NETTYPE tlitcp,2,75,NET #
> Endret 21.06.00 lbm@ecsoft.no
> NETTYPE ipcshm,1,,CPU #> Configure poll thread(s) for nettype
> NETTYPE tlitcp,1,,CPU
> DEADLOCK_TIMEOUT 60 # Max time to> wait of lock in distributed env.
> #RESIDENT 0 # Forced
> residency flag (Yes = 1, No = 0)
> RESIDENT 1 # Endret
> 20.06.00 - kjellh@informix.com
>
> MULTIPROCESSOR 1 # 0 for single-> processor, 1 for multi-processor
> #NUMCPUVPS 1 # Number of user
> (cpu) vps
> NUMCPUVPS 2
> SINGLE_CPU_VP 0 # If non-zero,> limit number of cpu vps to one
>
> #NOAGE 0 # Process aging
> NOAGE 1
> AFF_SPROC 0 # Affinity start> processor
> AFF_NPROCS 0 # Affinity number> of processors
>
> # Shared Memory Parameters
>
> #LOCKS 20000 # Maximum number
> of locks
> LOCKS 200000 # Endret
> 20.06.00 / kjellh@infomrix.com
> #BUFFERS 50000 # Maximum number> of shared buffers
> BUFFERS 100000 # Endret
> 20.06.00 / kjellh@informix.com> #NUMAIOVPS # Number of IO vps
> NUMAIOVPS 2 # Endret
> 20.06.00 - kjellh@informix.com
> PHYSBUFF 128 # Physical log
> buffer size (Kbytes)
> LOGBUFF 32 # Logical log
> buffer size (Kbytes)> LOGSMAX 6 # Maximum number
> of logical log files
> #CLEANERS 1 # Number of
> buffer cleaner processes
> #CLEANERS 8 # endret 21.06.00
> lbm@ecsoft.no
> CLEANERS 64
> SHMBASE 0x0A000000L # Shared> memory base address
> #SHMVIRTSIZE 32000 # initial virtual
> shared memory segment size
> SHMVIRTSIZE 65536 # Endret
> 20.06.00 - kjellh@informix.com
> SHMADD 16384 # 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
> LRUS 64 # Endret
> 21.06.00 - lbm@ecsoft.no pga 17% bufwa> its
> #LRU_MAX_DIRTY 50 # LRU percent
> dirty begin cleaning limit
> #LRU_MIN_DIRTY 40 # LRU percent
> dirty end cleaning limit
> LRU_MAX_DIRTY 10 # Endret
> 20.06.00 - kjellh@informix.com
> LRU_MIN_DIRTY 5 # Endret
> 20.06.00 - kjellh@informix.com
> LTXHWM 50