Low performance, high bufwait ratio 
Author Message
 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 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         #

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

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

#BUFFERS        50000           # Maximum number
of shared buffers
BUFFERS         100000          # Endret

#NUMAIOVPS                      # Number of IO vps
NUMAIOVPS       2               # Endret

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

CLEANERS        64
SHMBASE         0x0A000000L             # Shared
memory base address
#SHMVIRTSIZE    32000           # initial virtual
shared memory segment size
SHMVIRTSIZE     65536           # Endret

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

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

LRU_MIN_DIRTY    5              # Endret

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        40              # Number of pages
to attempt to read ahead
RA_THRESHOLD    30              # 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
tempdbs,tempdbs1,tempdbs2,tempdbs3,tempdbs4,tempdb
s5,tempdbs6,tempdb
s7,tempdbs8,tempdbs9    # Default temp dbspaces

# DUMP*:
# The following parameters control the type of
diagnostics information which
# is preserved when an unanticipated error
condition (assertion failure) occurs
# during Dynamic Server operations.
# For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means
Yes, 0 means No.

DUMPDIR         /tmp            # Preserve
diagnostics in this directory
DUMPSHMEM       0               # Dump a copy of
shared memory
DUMPGCORE       0               # Dump a core
image using 'gcore'
DUMPCORE        0               # Dump a core
image (Warning:this aborts Dynamic
 Server)
DUMPCNT         1               # Number of
shared memory or gcore dumps for
                                # a single user's
session

FILLFACTOR      90              # Fill factor for
building indexes

# method for Dynamic Server to use when
determining current time
USEOSTIME       0       # 0: use internal time
(fast), 1: get time from OS(slow)

# Parallel Database Queries (pdq)
MAX_PDQPRIORITY 100     #
...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 Low performance, high bufwait ratio

For one thing, I would change OPTCOMPIND to 0.
And, I think, the number 64 for LRUs is one of the {*filter*} ones.   Try
increasing this a little (and cleaners to match).

HTH,
Doug

Quote:
> 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         #

> 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

> 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

> #BUFFERS        50000           # Maximum number
> of shared buffers
> BUFFERS         100000          # Endret

> #NUMAIOVPS                      # Number of IO vps
> NUMAIOVPS       2               # Endret

> 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

> CLEANERS        64
> SHMBASE         0x0A000000L             # Shared
> memory base address
> #SHMVIRTSIZE    32000           # initial virtual
> shared memory segment size
> SHMVIRTSIZE     65536           # Endret

> 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

> 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

> LRU_MIN_DIRTY    5              # Endret

> 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        40              # Number of pages
> to attempt to read ahead
> RA_THRESHOLD    30              # 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
> tempdbs,tempdbs1,tempdbs2,tempdbs3,tempdbs4,tempdb
> s5,tempdbs6,tempdb
> s7,tempdbs8,tempdbs9    # Default temp dbspaces

> # DUMP*:
> # The following parameters control the type of
> diagnostics information which
> # is preserved when an unanticipated

...

read more »



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. DEC Rdb:Highest TPC-A Performance and Lowest Price/Performance Records

2. Cache Hit Ratio very low.

3. Low hit ratio, but only for indexes

4. Large DB buffer, low cache hit ratio

5. Low lock hit ratio

6. lower and lower performance when restore logical log

7. Latch Ratio is high???

8. High BR ratio

9. SQL 6.5 Confilicting Performance: Memory-Page Faults: High AND Cache-Avg Free Page Scan High

10. TechTips: High-volume / High-performance Paradox

11. Finding high/low value of variable

12. Drop the highest and lowest


 
Powered by phpBB® Forum Software