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 »

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

...

read more »