--0-1958201504-1124376709=:23030
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Content-Id:
Content-Disposition: inline


Hi,
I'm using IDS9.21 UC6 running on a SOlaris 8.0
Operating system.
The output of the onstat -P is not acceptable and I
would appreciate it if I could be helped get a better
results. The system is not performing well enough.
Attached is the output of the onstat -p.
I have also attached the onconfig file for the
database configuration.
please help.

Leona
Ghana Telecom Limited





__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--0-1958201504-1124376709=:23030
Content-Type: text/plain; name=profilereport
Content-Description: 77641972-profilereport
Content-Disposition: inline; filename=profilereport


Informix Dynamic Server 2000 Version 9.21.UC6 -- On-Line (Prim) -- Up 09:07:09 -- 3063808 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
19684838 20263046 442077955 95.55 154214 585984 701015 78.00

isamtot open start read write rewrite delete commit rollbk
293317600 2211956 672535038 2439958442 467066 100815 288 7332 58

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 55320.52 1476.21 248 542

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
669902 17 16555846 0 0 166 13625 88420

ixda-RA idx-RA da-RA RA-pgsused lchwaits
1382582 2081794 4439769 7891122 5540349


--0-1958201504-1124376709=:23030
Content-Type: text/plain; name=configuration
Content-Description: 1832273169-configuration
Content-Disposition: inline; filename=configuration

#************************************************* *************************
#
# INFORMIX SOFTWARE, INC.
#
# Title: onconfig.std
# Description: Informix Dynamic Server 2000 Configuration Parameters
#
#************************************************* *************************

# Root Dbspace Configuration

ROOTNAME rootdbs # Root dbspace name
ROOTPATH /dev/online10_1
# Path for device containing root dbspace
ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 2097150 # 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 50000 # Physical log file size (Kbytes)

# Logical Log Configuration

LOGFILES 800 # Number of logical log files
LOGSIZE 5000 # Logical log size (Kbytes)

# Diagnostics

MSGPATH /usr/acct/informix/log/online.log # System message log file path
CONSOLE /dev/console # System console message path
ALARMPROGRAM /usr/acct/informix/IDS9.21/etc/no_log.sh # Alarm program path
TBLSPACE_STATS 0 # Maintain tblspace statistics

# System Archive Tape Device

TAPEDEV /dev/rdlt1c #/dev/rdlt1c # Tape device path
TAPEBLK 128 # Tape block size (Kbytes)
TAPESIZE 70000000 # Maximum amount of data to put on tape (Kbytes)

# Log Archive Tape Device

LTAPEDEV /dev/rdatc # /dev/rdatc # Log tape device path
LTAPEBLK 128 # Log tape block size (Kbytes)
LTAPESIZE 8000000 # Max amount of data to put on log tape (Kbytes)

# Optical

STAGEBLOB # Informix Dynamic Server 2000 staging area

# System Configuration

SERVERNUM 2 # Unique id corresponding to a OnLine instance
DBSERVERNAME gtsun2 # Name of default database server
DBSERVERALIASES gtsun2net # List of alternate dbservernames
NETTYPE tlitcp,2,200,NET # Configure poll thread(s) for nettype
NETTYPE ipcstr,1,50,CPU # Configure poll thread(s) for nettype
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
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one

# NUMCPUVPS 3 # Number of user (cpu) vps
# NUMAIOVPS 4 # Number of IO vps
# NOAGE 1 # Process aging
# AFF_SPROC 0 # Affinity start processor
# AFF_NPROCS 0 # Affinity number of processors

VPCLASS CPU,NUM=3
VPCLASS AIO,NUM=3

# Shared Memory Parameters

LOCKS 2500000 # Maximum number of locks
BUFFERS 1200000 #512000 # Maximum number of shared buffers
PHYSBUFF 64 #32 # Physical log buffer size (Kbytes)
LOGBUFF 128 #64 # Logical log buffer size (Kbytes)
LOGSMAX 1000 # Logical log buffer size (Kbytes)
CLEANERS 8 #4 # Number of buffer cleaner processes
SHMBASE 0xa000000 # Shared memory base address
SHMVIRTSIZE 294912 # initial virtual shared memory segment size
SHMADD 32768 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 120 # Check point interval (in sec)
LRUS 32 #16 # Number of LRU queues
LRU_MAX_DIRTY 8 #4 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 4 #2 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water mark percentage
LTXEHWM 60 # Long transaction high water mark (exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 64 # Stack size (Kbytes)

# System Page Size
# BUFFSIZE - OnLine no longer supports this configuration parameter.
# To determine the page size used by OnLine 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
DRINTERVAL 30 # DR max time between DR buffer flushes (in sec)
DRTIMEOUT 30 # DR network timeout (in sec)
DRLOSTFOUND /usr/acct/informix/IDS9.21/dr.lostfound
# DR lost+found file path

# CDR Variables
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_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max)

# Backup/Restore variables
BAR_ACT_LOG /usr/informix/bar_act.log # ON-Bar Log file - not in /tmp please
BAR_DEBUG_LOG /usr/informix/bar_dbug.log
# ON-Bar Debug Log - not in /tmp please
BAR_MAX_BACKUP 0
BAR_RETRY 1
BAR_NB_XPORT_COUNT 10
BAR_XFER_BUF_SIZE 31
RESTARTABLE_RESTORE off
BAR_PROGRESS_FREQ 0

# Informix Storage Manager variables
ISM_DATA_POOL ISMData
ISM_LOG_POOL ISMLogs

# Read Ahead Variables
RA_PAGES 128 # Number of pages to attempt to read ahead
RA_THRESHOLD 32 # Number of pages left before next group

# DBSPACETEMP:
# OnLine equivalent of DBTEMP for SE. This is the list of dbspaces
# that the OnLine SQL Engine will use to create temp tables etc.
# If specified it must be a colon separated list of dbspaces that exist
# when the OnLine 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 temp1dbs,temp2dbs # 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 OnLine operations.
# For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No.

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

FILLFACTOR 70 # Fill factor for building indexes

# method for OnLine 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 # Maximum allowed pdqpriority
DS_MAX_QUERIES 10 # Maximum number of decision support queries
DS_TOTAL_MEMORY 40000 # Decision support memory (Kbytes)
DS_MAX_SCANS 1048576 # Maximum number of decision support scans
DATASKIP off # List of dbspaces to skip

# OPTCOMPIND
# 0 => Nested loop joins will be preferred (where
# possible) over sortmerge joins and hash joins.
# 1 => If the transaction isolation mode is not
# "repeatable read", optimizer behaves as in (2)
# below. Otherwise it behaves as in (0) above.
# 2 => Use costs regardless of the transaction isolation
# mode. Nested loop joins are not necessarily
# preferred. Optimizer bases its decision purely
# on costs.
OPTCOMPIND 1 # To hint the optimizer

DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0)

ONDBSPACEDOWN 1 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAIT
OPCACHEMAX 0 # Maximum optical cache size (Kbytes)

# HETERO_COMMIT (Gateway participation in distributed transactions)
# 1 => Heterogeneous Commit is enabled
# 0 (or any other value) => Heterogeneous Commit is disabled
HETERO_COMMIT 0

SBSPACENAME # Default smartblob space name - this is where blobs
# go if no sbspace is specified when the smartblob is
# created. It is also used by some datablades as
# the location to put their smartblobs.
SYSSBSPACENAME # Default smartblob space for use by the Informix
# Server. This is used primarily for Informix Server
# system statistics collection.

BLOCKTIMEOUT 3600 # Default timeout for system block
SYSALARMPROGRAM /usr/acct/informix/IDS9.21/etc/evidence.sh
# System Alarm program path

# Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
OPT_GOAL 0

ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or anything but 1)

#
# The following are default settings for enabling Java in the database.
# Replace all occurrences of /usr/informix with the value of $INFORMIXDIR.

#VPCLASS jvp,num=1 # Number of JVPs to start with

JVPJAVAHOME /usr/informix/extend/krakatoa/jre/
# JRE installation root directory
JVPHOME /usr/informix/extend/krakatoa # Krakatoa installation directory

JVPPROPFILE /usr/informix/extend/krakatoa/.jvpprops # JVP property file

JDKVERSION 1.2 # JDK version supported by this server
JVMTHREAD native # Java VM thread type (green or native)

# The path to the JRE libraries relative to JVPJAVAHOME
JVPJAVALIB /lib/sparc/

# The JRE libraries to use for the Java VM

JVPJAVAVM hpi:jvm:java:net:math:zip:jpeg

# Classpath to use upon Java VM start-up (use _g version for debugging)

# JVPCLASSPATH /usr/informix/extend/krakatoa/krakatoa.jar:/usr/informix/extend/krakatoa/jdbc.jar
JVPCLASSPATH

STMT_CACHE 1 # SQL Statement cache on AK 26/3/2
STMT_CACHE_HITS 3 # Only fully cache statements with >= 3 hits
STMT_CACHE_SIZE 10000 # SSC size in kB

CDR_LOGDELTA 30 # % of log space allowed in queue memory
CDR_NUMCONNECT 16 # Expected connections per server
CDR_NIFRETRY 300 # Connection retry (seconds)
LBU_PRESERVE 0 # Preserve last log for log backup
BAR_BSALIB_PATH /opt/omni/lib/libob2informix.so

--0-1958201504-1124376709=:23030--
sending to informix-list

What, precisely, is it that you are unhappy with?
Are you OLTP, DSS?
How many CPU's?
How many hard drives?
What is your overall CPU activity?
What does you I/O look like?
Etc, etc, etc ...


-----Original Message-----
From: owner-informix-list (AT) iiug (DOT) org [mailtowner-informix-list (AT) iiug (DOT) org] On Behalf Of Leona Ankrah
Sent: Thursday, August 18, 2005 7:52 AM
To: informix-list (AT) iiug (DOT) org
Subject: Onstat -P output

<< File: 77641972-profilereport >> << File: 1832273169-configuration >>
Hi,
I'm using IDS9.21 UC6 running on a SOlaris 8.0
Operating system.
The output of the onstat -P is not acceptable and I
would appreciate it if I could be helped get a better
results. The system is not performing well enough.
Attached is the output of the onstat -p.
I have also attached the onconfig file for the
database configuration.
please help.

Leona
Ghana Telecom Limited





__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com


sending to informix-list

We need more information as requested by Jay Konigsberg otherwise we're
guessing a bit.

Leona Ankrah wrote:

Do you still have support for version 9.21.UC6? You should consider
upgrading to 9.40 or 10.00.


Your cached reads and particularly writes are low. Is this what you're
dissatisfied with?

With the big caveat that I don't know your disc architecture (please
post this), you could experiment with reducing these values
substantially so that fewer extra pages are read into memory when disc
reads take place.

Ben.

Hi,
The processing is OLTP. A SUN 4800 server with 4 CPUs
at 750MHZ each. The overall CPU activity is about 65%.
The server has a total of 1.8TB (18 disks of 73GB).
Out of this 400GB is allocated to the file system and
the rest is configured like this: 6 for RAID 10 and 5
for RAID 5. 2 for hot standby and 2 for parity. Memory
is 8GB.
Well, my worry is the response from the system has
slowed down drastically and the users are beginning to
complain. we have done index rebuilding,
defragmentation on the tables but this has not
improved.
i have attached the results of onstat -d as well.

--- "Konigsberg, Jay" <jaykon (AT) tower (DOT) com> wrote:




__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Informix Dynamic Server 2000 Version 9.21.UC6 -- On-Line -- Up 1 days 10:14:29 -- 3063808 Kbytes

Dbspaces
address number flags fchunk nchunks flags owner name
b310d7d0 1 0x1 1 5 N informix rootdbs
b3844018 2 0x2001 4 4 N T informix temp1dbs
b3844160 3 0x2001 5 4 N T informix temp2dbs
b38442a8 4 0x1 6 33 N informix datadbs1r5
b38443f0 5 0x1 36 30 N informix datadbs2r10
b3844538 6 0x1 56 30 N informix datadbs3r5
b3844680 7 0x1 85 30 N informix datadbs4r10
b38447c8 8 0x1 106 34 N informix indexdbs
b3844910 9 0x1 126 10 N informix smalldbs
9 active, 2047 maximum

Chunks
address chk/dbs offset size free bpages flags pathname
b310d918 1 1 0 1048575 523224 PO- /dev/online10_1
b314c5f0 2 1 0 1048575 0 PO- /dev/online10_2
b314c760 3 1 0 1048575 54429 PO- /dev/online10_3
b314c8d0 4 2 0 1048575 1046964 PO- /dev/online10_4
b314ca40 5 3 0 1048575 1046204 PO- /dev/online10_5
b314cbb0 6 4 0 1048575 113068 PO- /dev/online5_1
b314cd20 7 4 0 1048575 0 PO- /dev/online5_2
b314ce90 8 4 0 1048575 0 PO- /dev/online5_3
b310da88 9 4 0 1048575 0 PO- /dev/online5_4
b310dbf8 10 4 0 1048575 524286 PO- /dev/online5_5
b310dd68 11 4 0 1048575 0 PO- /dev/online5_6
b313da30 12 4 0 1048575 0 PO- /dev/online5_7
b313dba0 13 4 0 1048575 0 PO- /dev/online5_8
b313dd10 14 4 0 1048575 0 PO- /dev/online5_9
b313de80 15 4 0 1048575 0 PO- /dev/online5_10
b382c018 16 4 0 1048575 0 PO- /dev/online5_11
b382c188 17 4 0 1048575 0 PO- /dev/online5_12
b382c2f8 18 4 0 1048575 0 PO- /dev/online5_13
b382c468 19 4 0 1048575 0 PO- /dev/online5_14
b382c5d8 20 4 0 1048575 0 PO- /dev/online5_15
b382c748 21 4 0 1048575 605928 PO- /dev/online5_16
b382c8b8 22 4 0 1048575 524286 PO- /dev/online5_17
b382ca28 23 4 0 1048575 524286 PO- /dev/online5_18
b382cb98 24 4 0 1048575 0 PO- /dev/online5_19
b382cd08 25 4 0 1048575 524286 PO- /dev/online5_20
b382ce78 26 4 0 1048575 561429 PO- /dev/online5_21
b382d018 27 4 0 1048575 104607 PO- /dev/online5_22
b382d188 28 4 0 1048575 575845 PO- /dev/online5_23
b382d2f8 29 4 0 1048575 0 PO- /dev/online5_24
b382d468 30 4 0 1048575 575845 PO- /dev/online5_25
b382d5d8 31 4 0 1048575 0 PO- /dev/online5_26
b382d748 32 4 0 1048575 1048572 PO- /dev/online5_27
b382d8b8 33 4 0 1048575 1048572 PO- /dev/online5_28
b382da28 34 4 0 1048575 1048572 PO- /dev/online5_29
b382db98 35 4 0 1048575 1048572 PO- /dev/online5_30
b382dd08 36 5 0 1048575 48581 PO- /dev/online10_6
b382de78 37 5 0 1048575 0 PO- /dev/online10_7
b382e018 38 5 0 1048575 0 PO- /dev/online10_8
b382e188 39 5 0 1048575 0 PO- /dev/online10_9
b382e2f8 40 5 0 1048575 99593 PO- /dev/online10_10
b382e468 41 5 0 1048575 0 PO- /dev/online10_11
b382e5d8 42 5 0 1048575 0 PO- /dev/online10_12
b382e748 43 5 0 1048575 0 PO- /dev/online10_13
b382e8b8 44 5 0 1048575 0 PO- /dev/online10_14
b382ea28 45 5 0 1048575 0 PO- /dev/online10_15
b382eb98 46 5 0 1048575 0 PO- /dev/online10_16
b382ed08 47 5 0 1048575 0 PO- /dev/online10_17
b382ee78 48 5 0 1048575 0 PO- /dev/online10_18
b382f018 49 5 0 1048575 0 PO- /dev/online10_19
b382f188 50 5 0 1048575 0 PO- /dev/online10_20
b382f2f8 51 5 0 1048575 524286 PO- /dev/online10_21
b382f468 52 5 0 1048575 524286 PO- /dev/online10_22
b382f5d8 53 5 0 1048575 0 PO- /dev/online10_23
b382f748 54 5 0 1048575 524286 PO- /dev/online10_24
b382f8b8 55 5 0 1048575 561429 PO- /dev/online10_25
b382fa28 56 6 0 1048575 176821 PO- /dev/online5_31
b382fb98 57 6 0 1048575 0 PO- /dev/online5_32
b382fd08 58 6 0 1048575 0 PO- /dev/online5_33
b382fe78 59 6 0 1048575 0 PO- /dev/online5_34
b3830018 60 6 0 1048575 262143 PO- /dev/online5_35
b3830188 61 6 0 1048575 0 PO- /dev/online5_36
b38302f8 62 6 0 1048575 0 PO- /dev/online5_37
b3830468 63 6 0 1048575 0 PO- /dev/online5_38
b38305d8 64 6 0 1048575 0 PO- /dev/online5_39
b3830748 65 6 0 1048575 0 PO- /dev/online5_40
b38308b8 66 6 0 1048575 0 PO- /dev/online5_41
b3830a28 67 6 0 1048575 0 PO- /dev/online5_42
b3830b98 68 6 0 1048575 0 PO- /dev/online5_43
b3830d08 69 6 0 1048575 0 PO- /dev/online5_44
b3830e78 70 6 0 1048575 0 PO- /dev/online5_45
b3833018 71 6 0 1048575 0 PO- /dev/online5_46
b3833188 72 6 0 1048575 517106 PO- /dev/online5_47
b38332f8 73 6 0 1048575 524286 PO- /dev/online5_48
b3833468 74 6 0 1048575 0 PO- /dev/online5_49
b38335d8 75 6 0 1048575 524286 PO- /dev/online5_50
b3833748 76 6 0 1048575 524286 PO- /dev/online5_51
b38338b8 77 6 0 1048575 786429 PO- /dev/online5_52
b3833a28 78 6 0 1048575 0 PO- /dev/online5_53
b3833b98 79 6 0 1048575 1048572 PO- /dev/online5_54
b3833d08 80 6 0 1048575 1048572 PO- /dev/online5_55
b3833e78 81 6 0 1048575 1048572 PO- /dev/online5_56
b3834018 82 6 0 1048575 1048572 PO- /dev/online5_57
b3834188 83 6 0 1048575 1048572 PO- /dev/online5_58
b38342f8 84 6 0 1048575 1048572 PO- /dev/online5_59
b3834468 85 7 0 1048575 222643 PO- /dev/online10_26
b38345d8 86 7 0 1048575 0 PO- /dev/online10_27
b3834748 87 7 0 1048575 0 PO- /dev/online10_28
b38348b8 88 7 0 1048575 0 PO- /dev/online10_29
b3834a28 89 7 0 1048575 524286 PO- /dev/online10_30
b3834b98 90 7 0 1048575 0 PO- /dev/online10_31
b3834d08 91 7 0 1048575 0 PO- /dev/online10_32
b3834e78 92 7 0 1048575 0 PO- /dev/online10_33
b3835018 93 7 0 1048575 0 PO- /dev/online10_34
b3835188 94 7 0 1048575 0 PO- /dev/online10_35
b38352f8 95 7 0 1048575 0 PO- /dev/online10_36
b3835468 96 7 0 1048575 0 PO- /dev/online10_37
b38355d8 97 7 0 1048575 0 PO- /dev/online10_38
b3835748 98 7 0 1048575 0 PO- /dev/online10_39
b38358b8 99 7 0 1048575 524286 PO- /dev/online10_40
b3835a28 100 7 0 1048575 0 PO- /dev/online10_41
b3835b98 101 7 0 1048575 0 PO- /dev/online10_42
b3835d08 102 7 0 1048575 524286 PO- /dev/online10_43
b3835e78 103 7 0 1048575 524286 PO- /dev/online10_44
b3836018 104 7 0 1048575 517106 PO- /dev/online10_45
b3836188 105 6 0 1048575 1048572 PO- /dev/online5_60
b38362f8 106 8 0 1048575 329 PO- /dev/online10_46
b3836468 107 8 0 1048575 12438 PO- /dev/online5_61
b38365d8 108 8 0 1048575 10815 PO- /dev/online10_47
b3836748 109 8 0 1048575 25920 PO- /dev/online5_62
b38368b8 110 8 0 1048575 8264 PO- /dev/online10_48
b3836a28 111 8 0 1048575 72981 PO- /dev/online5_63
b3836b98 112 8 0 1048575 67778 PO- /dev/online10_49
b3836d08 113 8 0 1048575 36683 PO- /dev/online5_64
b3836e78 114 8 0 1048575 108798 PO- /dev/online10_50
b3837018 115 8 0 1048575 91261 PO- /dev/online5_65
b3837188 116 8 0 1048575 3785 PO- /dev/online10_51
b38372f8 117 8 0 1048575 107204 PO- /dev/online5_66
b3837468 118 8 0 1048575 61989 PO- /dev/online10_52
b38375d8 119 8 0 1048575 161060 PO- /dev/online5_67
b3837748 120 8 0 1048575 236157 PO- /dev/online10_53
b38378b8 121 8 0 1048575 136169 PO- /dev/online5_68
b3837a28 122 8 0 1048575 175589 PO- /dev/online10_54
b3837b98 123 8 0 1048575 702648 PO- /dev/online5_69
b3837d08 124 8 0 1048575 500054 PO- /dev/online10_55
b3837e78 125 8 0 1048575 714970 PO- /dev/online5_70
b3838018 126 9 0 1048575 69 PO- /dev/online10_56
b3838188 127 9 0 1048575 110 PO- /dev/online5_81
b38382f8 128 9 0 1048575 600 PO- /dev/online10_57
b3838468 129 9 0 1048575 69900 PO- /dev/online5_82
b38385d8 130 9 0 1048575 412226 PO- /dev/online10_58
b3838748 131 9 0 1048575 1048572 PO- /dev/online5_83
b38388b8 132 9 0 1048575 1048572 PO- /dev/online10_59
b3838a28 133 9 0 1048575 1048572 PO- /dev/online5_84
b3838b98 134 9 0 1048575 1048572 PO- /dev/online10_60
b3838d08 135 9 0 1048575 1048572 PO- /dev/online5_85
b3838e78 136 5 0 1048575 156880 PO- /dev/online10_61
b383d018 137 5 0 1048575 24286 PO- /dev/online10_62
b383d188 138 5 0 1048575 0 PO- /dev/online10_63
b383d2f8 139 5 0 1048575 825845 PO- /dev/online10_64
b383d468 140 5 0 1048575 1048572 PO- /dev/online10_65
b383d5d8 141 5 0 1048575 1048572 PO- /dev/online10_66
b383d748 142 5 0 1048575 1048572 PO- /dev/online10_67
b383d8b8 143 5 0 1048575 1048572 PO- /dev/online10_68
b383da28 144 5 0 1048575 1048572 PO- /dev/online10_69
b383db98 145 5 0 1048575 1048572 PO- /dev/online10_70
b383dd08 146 7 0 1048575 0 PO- /dev/online10_71
b383de78 147 7 0 1048575 0 PO- /dev/online10_72
b383f018 148 7 0 1048575 1048572 PO- /dev/online10_73
b383f188 149 7 0 1048575 1048572 PO- /dev/online10_74
b383f2f8 150 7 0 1048575 1048572 PO- /dev/online10_75
b383f468 151 7 0 1048575 1048572 PO- /dev/online10_76
b383f5d8 152 7 0 1048575 1048572 PO- /dev/online10_77
b383f748 153 7 0 1048575 1048572 PO- /dev/online10_78
b383f8b8 154 7 0 1048575 1048572 PO- /dev/online10_79
b383fa28 155 7 0 1048575 1048572 PO- /dev/online10_80
b383fb98 156 1 0 1048575 622508 PO- /dev/online10_81
b383fd08 157 1 0 1048575 1048572 PO- /dev/online10_82
b383fe78 158 8 0 1048575 0 PO- /dev/online10_86
b3841018 159 8 0 1048575 104858 PO- /dev/online5_71
b3841188 160 2 0 1048575 1048572 PO- /dev/online10_83
b38412f8 161 3 0 1048575 1048572 PO- /dev/online10_84
b3841468 162 8 0 1048575 896408 PO- /dev/online10_87
b38415d8 163 2 0 1048575 1048572 PO- /dev/online10_85
b3841748 164 8 0 1048575 211992 PO- /dev/online10_88
b38418b8 165 8 0 1048575 626259 PO- /dev/online5_72
b3841a28 166 3 0 1048575 1048572 PO- /dev/online10_89
b3841b98 167 8 0 1048575 384963 PO- /dev/online10_90
b3841d08 168 8 0 1048575 546775 PO- /dev/online5_73
b3841e78 169 8 0 1048575 872998 PO- /dev/online10_91
b3842018 170 2 0 1048575 1048572 PO- /dev/online10_92
b3842188 171 3 0 1048575 1048572 PO- /dev/online10_93
b38422f8 172 8 0 1048575 21790 PO- /dev/online10_94
b3842468 173 8 0 1048575 228969 PO- /dev/online10_95
b38425d8 174 8 0 1048575 1048572 PO- /dev/online5_74
b3842748 175 8 0 1048575 1048572 PO- /dev/online5_75
b38428b8 176 8 0 1048575 1048572 PO- /dev/online5_76
b3842a28 177 8 0 1048575 1048572 PO- /dev/online5_77
b3842b98 178 4 0 1048575 1048572 PO- /dev/online5_78
b3842d08 179 4 0 1048575 1048572 PO- /dev/online5_79
b3842e78 180 4 0 1048575 1048572 PO- /dev/online5_80
180 active, 2047 maximum
sending to informix-list

Based on your description, it sounds like you're hitting a table without an index that has grown in size and a single SQL/SPL/UDR is to blame.

I've attached a script called "qio" to the bottom of the post, it will show you the SQL's in the system that are the biggest I/O users. It ain't pretty, but it works.

Jay

Hi,
The processing is OLTP. A SUN 4800 server with 4 CPUs
at 750MHZ each. The overall CPU activity is about 65%.
The server has a total of 1.8TB (18 disks of 73GB).
Out of this 400GB is allocated to the file system and
the rest is configured like this: 6 for RAID 10 and 5
for RAID 5. 2 for hot standby and 2 for parity. Memory
is 8GB.
Well, my worry is the response from the system has
slowed down drastically and the users are beginning to
complain. we have done index rebuilding,
defragmentation on the tables but this has not
improved.
i have attached the results of onstat -d as well.

--- "Konigsberg, Jay" <jaykon (AT) tower (DOT) com> wrote:




__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Informix Dynamic Server 2000 Version 9.21.UC6 -- On-Line -- Up 1 days 10:14:29 -- 3063808 Kbytes

Dbspaces
address number flags fchunk nchunks flags owner name
b310d7d0 1 0x1 1 5 N informix rootdbs
b3844018 2 0x2001 4 4 N T informix temp1dbs
b3844160 3 0x2001 5 4 N T informix temp2dbs
b38442a8 4 0x1 6 33 N informix datadbs1r5
b38443f0 5 0x1 36 30 N informix datadbs2r10
b3844538 6 0x1 56 30 N informix datadbs3r5
b3844680 7 0x1 85 30 N informix datadbs4r10
b38447c8 8 0x1 106 34 N informix indexdbs
b3844910 9 0x1 126 10 N informix smalldbs
9 active, 2047 maximum

Chunks
address chk/dbs offset size free bpages flags pathname
b310d918 1 1 0 1048575 523224 PO- /dev/online10_1
b314c5f0 2 1 0 1048575 0 PO- /dev/online10_2
b314c760 3 1 0 1048575 54429 PO- /dev/online10_3
b314c8d0 4 2 0 1048575 1046964 PO- /dev/online10_4
b314ca40 5 3 0 1048575 1046204 PO- /dev/online10_5
b314cbb0 6 4 0 1048575 113068 PO- /dev/online5_1
b314cd20 7 4 0 1048575 0 PO- /dev/online5_2
b314ce90 8 4 0 1048575 0 PO- /dev/online5_3
b310da88 9 4 0 1048575 0 PO- /dev/online5_4
b310dbf8 10 4 0 1048575 524286 PO- /dev/online5_5
b310dd68 11 4 0 1048575 0 PO- /dev/online5_6
b313da30 12 4 0 1048575 0 PO- /dev/online5_7
b313dba0 13 4 0 1048575 0 PO- /dev/online5_8
b313dd10 14 4 0 1048575 0 PO- /dev/online5_9
b313de80 15 4 0 1048575 0 PO- /dev/online5_10
b382c018 16 4 0 1048575 0 PO- /dev/online5_11
b382c188 17 4 0 1048575 0 PO- /dev/online5_12
b382c2f8 18 4 0 1048575 0 PO- /dev/online5_13
b382c468 19 4 0 1048575 0 PO- /dev/online5_14
b382c5d8 20 4 0 1048575 0 PO- /dev/online5_15
b382c748 21 4 0 1048575 605928 PO- /dev/online5_16
b382c8b8 22 4 0 1048575 524286 PO- /dev/online5_17
b382ca28 23 4 0 1048575 524286 PO- /dev/online5_18
b382cb98 24 4 0 1048575 0 PO- /dev/online5_19
b382cd08 25 4 0 1048575 524286 PO- /dev/online5_20
b382ce78 26 4 0 1048575 561429 PO- /dev/online5_21
b382d018 27 4 0 1048575 104607 PO- /dev/online5_22
b382d188 28 4 0 1048575 575845 PO- /dev/online5_23
b382d2f8 29 4 0 1048575 0 PO- /dev/online5_24
b382d468 30 4 0 1048575 575845 PO- /dev/online5_25
b382d5d8 31 4 0 1048575 0 PO- /dev/online5_26
b382d748 32 4 0 1048575 1048572 PO- /dev/online5_27
b382d8b8 33 4 0 1048575 1048572 PO- /dev/online5_28
b382da28 34 4 0 1048575 1048572 PO- /dev/online5_29
b382db98 35 4 0 1048575 1048572 PO- /dev/online5_30
b382dd08 36 5 0 1048575 48581 PO- /dev/online10_6
b382de78 37 5 0 1048575 0 PO- /dev/online10_7
b382e018 38 5 0 1048575 0 PO- /dev/online10_8
b382e188 39 5 0 1048575 0 PO- /dev/online10_9
b382e2f8 40 5 0 1048575 99593 PO- /dev/online10_10
b382e468 41 5 0 1048575 0 PO- /dev/online10_11
b382e5d8 42 5 0 1048575 0 PO- /dev/online10_12
b382e748 43 5 0 1048575 0 PO- /dev/online10_13
b382e8b8 44 5 0 1048575 0 PO- /dev/online10_14
b382ea28 45 5 0 1048575 0 PO- /dev/online10_15
b382eb98 46 5 0 1048575 0 PO- /dev/online10_16
b382ed08 47 5 0 1048575 0 PO- /dev/online10_17
b382ee78 48 5 0 1048575 0 PO- /dev/online10_18
b382f018 49 5 0 1048575 0 PO- /dev/online10_19
b382f188 50 5 0 1048575 0 PO- /dev/online10_20
b382f2f8 51 5 0 1048575 524286 PO- /dev/online10_21
b382f468 52 5 0 1048575 524286 PO- /dev/online10_22
b382f5d8 53 5 0 1048575 0 PO- /dev/online10_23
b382f748 54 5 0 1048575 524286 PO- /dev/online10_24
b382f8b8 55 5 0 1048575 561429 PO- /dev/online10_25
b382fa28 56 6 0 1048575 176821 PO- /dev/online5_31
b382fb98 57 6 0 1048575 0 PO- /dev/online5_32
b382fd08 58 6 0 1048575 0 PO- /dev/online5_33
b382fe78 59 6 0 1048575 0 PO- /dev/online5_34
b3830018 60 6 0 1048575 262143 PO- /dev/online5_35
b3830188 61 6 0 1048575 0 PO- /dev/online5_36
b38302f8 62 6 0 1048575 0 PO- /dev/online5_37
b3830468 63 6 0 1048575 0 PO- /dev/online5_38
b38305d8 64 6 0 1048575 0 PO- /dev/online5_39
b3830748 65 6 0 1048575 0 PO- /dev/online5_40
b38308b8 66 6 0 1048575 0 PO- /dev/online5_41
b3830a28 67 6 0 1048575 0 PO- /dev/online5_42
b3830b98 68 6 0 1048575 0 PO- /dev/online5_43
b3830d08 69 6 0 1048575 0 PO- /dev/online5_44
b3830e78 70 6 0 1048575 0 PO- /dev/online5_45
b3833018 71 6 0 1048575 0 PO- /dev/online5_46
b3833188 72 6 0 1048575 517106 PO- /dev/online5_47
b38332f8 73 6 0 1048575 524286 PO- /dev/online5_48
b3833468 74 6 0 1048575 0 PO- /dev/online5_49
b38335d8 75 6 0 1048575 524286 PO- /dev/online5_50
b3833748 76 6 0 1048575 524286 PO- /dev/online5_51
b38338b8 77 6 0 1048575 786429 PO- /dev/online5_52
b3833a28 78 6 0 1048575 0 PO- /dev/online5_53
b3833b98 79 6 0 1048575 1048572 PO- /dev/online5_54
b3833d08 80 6 0 1048575 1048572 PO- /dev/online5_55
b3833e78 81 6 0 1048575 1048572 PO- /dev/online5_56
b3834018 82 6 0 1048575 1048572 PO- /dev/online5_57
b3834188 83 6 0 1048575 1048572 PO- /dev/online5_58
b38342f8 84 6 0 1048575 1048572 PO- /dev/online5_59
b3834468 85 7 0 1048575 222643 PO- /dev/online10_26
b38345d8 86 7 0 1048575 0 PO- /dev/online10_27
b3834748 87 7 0 1048575 0 PO- /dev/online10_28
b38348b8 88 7 0 1048575 0 PO- /dev/online10_29
b3834a28 89 7 0 1048575 524286 PO- /dev/online10_30
b3834b98 90 7 0 1048575 0 PO- /dev/online10_31
b3834d08 91 7 0 1048575 0 PO- /dev/online10_32
b3834e78 92 7 0 1048575 0 PO- /dev/online10_33
b3835018 93 7 0 1048575 0 PO- /dev/online10_34
b3835188 94 7 0 1048575 0 PO- /dev/online10_35
b38352f8 95 7 0 1048575 0 PO- /dev/online10_36
b3835468 96 7 0 1048575 0 PO- /dev/online10_37
b38355d8 97 7 0 1048575 0 PO- /dev/online10_38
b3835748 98 7 0 1048575 0 PO- /dev/online10_39
b38358b8 99 7 0 1048575 524286 PO- /dev/online10_40
b3835a28 100 7 0 1048575 0 PO- /dev/online10_41
b3835b98 101 7 0 1048575 0 PO- /dev/online10_42
b3835d08 102 7 0 1048575 524286 PO- /dev/online10_43
b3835e78 103 7 0 1048575 524286 PO- /dev/online10_44
b3836018 104 7 0 1048575 517106 PO- /dev/online10_45
b3836188 105 6 0 1048575 1048572 PO- /dev/online5_60
b38362f8 106 8 0 1048575 329 PO- /dev/online10_46
b3836468 107 8 0 1048575 12438 PO- /dev/online5_61
b38365d8 108 8 0 1048575 10815 PO- /dev/online10_47
b3836748 109 8 0 1048575 25920 PO- /dev/online5_62
b38368b8 110 8 0 1048575 8264 PO- /dev/online10_48
b3836a28 111 8 0 1048575 72981 PO- /dev/online5_63
b3836b98 112 8 0 1048575 67778 PO- /dev/online10_49
b3836d08 113 8 0 1048575 36683 PO- /dev/online5_64
b3836e78 114 8 0 1048575 108798 PO- /dev/online10_50
b3837018 115 8 0 1048575 91261 PO- /dev/online5_65
b3837188 116 8 0 1048575 3785 PO- /dev/online10_51
b38372f8 117 8 0 1048575 107204 PO- /dev/online5_66
b3837468 118 8 0 1048575 61989 PO- /dev/online10_52
b38375d8 119 8 0 1048575 161060 PO- /dev/online5_67
b3837748 120 8 0 1048575 236157 PO- /dev/online10_53
b38378b8 121 8 0 1048575 136169 PO- /dev/online5_68
b3837a28 122 8 0 1048575 175589 PO- /dev/online10_54
b3837b98 123 8 0 1048575 702648 PO- /dev/online5_69
b3837d08 124 8 0 1048575 500054 PO- /dev/online10_55
b3837e78 125 8 0 1048575 714970 PO- /dev/online5_70
b3838018 126 9 0 1048575 69 PO- /dev/online10_56
b3838188 127 9 0 1048575 110 PO- /dev/online5_81
b38382f8 128 9 0 1048575 600 PO- /dev/online10_57
b3838468 129 9 0 1048575 69900 PO- /dev/online5_82
b38385d8 130 9 0 1048575 412226 PO- /dev/online10_58
b3838748 131 9 0 1048575 1048572 PO- /dev/online5_83
b38388b8 132 9 0 1048575 1048572 PO- /dev/online10_59
b3838a28 133 9 0 1048575 1048572 PO- /dev/online5_84
b3838b98 134 9 0 1048575 1048572 PO- /dev/online10_60
b3838d08 135 9 0 1048575 1048572 PO- /dev/online5_85
b3838e78 136 5 0 1048575 156880 PO- /dev/online10_61
b383d018 137 5 0 1048575 24286 PO- /dev/online10_62
b383d188 138 5 0 1048575 0 PO- /dev/online10_63
b383d2f8 139 5 0 1048575 825845 PO- /dev/online10_64
b383d468 140 5 0 1048575 1048572 PO- /dev/online10_65
b383d5d8 141 5 0 1048575 1048572 PO- /dev/online10_66
b383d748 142 5 0 1048575 1048572 PO- /dev/online10_67
b383d8b8 143 5 0 1048575 1048572 PO- /dev/online10_68
b383da28 144 5 0 1048575 1048572 PO- /dev/online10_69
b383db98 145 5 0 1048575 1048572 PO- /dev/online10_70
b383dd08 146 7 0 1048575 0 PO- /dev/online10_71
b383de78 147 7 0 1048575 0 PO- /dev/online10_72
b383f018 148 7 0 1048575 1048572 PO- /dev/online10_73
b383f188 149 7 0 1048575 1048572 PO- /dev/online10_74
b383f2f8 150 7 0 1048575 1048572 PO- /dev/online10_75
b383f468 151 7 0 1048575 1048572 PO- /dev/online10_76
b383f5d8 152 7 0 1048575 1048572 PO- /dev/online10_77
b383f748 153 7 0 1048575 1048572 PO- /dev/online10_78
b383f8b8 154 7 0 1048575 1048572 PO- /dev/online10_79
b383fa28 155 7 0 1048575 1048572 PO- /dev/online10_80
b383fb98 156 1 0 1048575 622508 PO- /dev/online10_81
b383fd08 157 1 0 1048575 1048572 PO- /dev/online10_82
b383fe78 158 8 0 1048575 0 PO- /dev/online10_86
b3841018 159 8 0 1048575 104858 PO- /dev/online5_71
b3841188 160 2 0 1048575 1048572 PO- /dev/online10_83
b38412f8 161 3 0 1048575 1048572 PO- /dev/online10_84
b3841468 162 8 0 1048575 896408 PO- /dev/online10_87
b38415d8 163 2 0 1048575 1048572 PO- /dev/online10_85
b3841748 164 8 0 1048575 211992 PO- /dev/online10_88
b38418b8 165 8 0 1048575 626259 PO- /dev/online5_72
b3841a28 166 3 0 1048575 1048572 PO- /dev/online10_89
b3841b98 167 8 0 1048575 384963 PO- /dev/online10_90
b3841d08 168 8 0 1048575 546775 PO- /dev/online5_73
b3841e78 169 8 0 1048575 872998 PO- /dev/online10_91
b3842018 170 2 0 1048575 1048572 PO- /dev/online10_92
b3842188 171 3 0 1048575 1048572 PO- /dev/online10_93
b38422f8 172 8 0 1048575 21790 PO- /dev/online10_94
b3842468 173 8 0 1048575 228969 PO- /dev/online10_95
b38425d8 174 8 0 1048575 1048572 PO- /dev/online5_74
b3842748 175 8 0 1048575 1048572 PO- /dev/online5_75
b38428b8 176 8 0 1048575 1048572 PO- /dev/online5_76
b3842a28 177 8 0 1048575 1048572 PO- /dev/online5_77
b3842b98 178 4 0 1048575 1048572 PO- /dev/online5_78
b3842d08 179 4 0 1048575 1048572 PO- /dev/online5_79
b3842e78 180 4 0 1048575 1048572 PO- /dev/online5_80
180 active, 2047 maximum
sending to informix-list


### Cut here ###
#!/bin/ksh
#
# qio - Query I/O, shows the top queries ordered by the amount of I/O
#
#
exec 2>&1

initdots()
{
for idx in x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x
do
print -n "\b \b"
sleep 1
done
}
#
# getonstat - gets the onstat data and loads the 'CSessid', 'CNread' & CNwrite
# arrays
getonstat()
{
Data=""
idx=0
onstat -u | while read Line
do
if [ "$Line" ]
then
set $Line
else
continue
fi
[ "$1" = "address" ] && Data="TRUE" && continue
[ "$4" = "informix" ] && continue
[ "$2" = "active," ] && continue
Primary=${2#???}
Primary=${Primary%???}
[ "${Primary}" != "P" ] && continue
if [ "$Data" ]
then
#print "[$Line]"
#print "$3\t$9\t${10}"
CSessid[idx]=$3
CNread[idx]=$9
CNwrite[idx]=${10}
(( idx = idx + 1 ))
fi
done
(( TotalCurrent = idx -1 ))
}

#
# Loads the previous onstat into PSessid, PNread & PNwrite for comparission
#
assignprevious()
{
# Clean out excess array slots
while [ $TotalPrevious -gt $TotalCurrent ]
do
PSessid[$TotalPrevious]=""
PNread[$TotalPrevious]=""
PNwrite[$TotalPrevious]=""
(( TotalPrevious = TotalPrevious - 1 ))
done

idx=0
while [ $idx -le $TotalCurrent ]
do
PSessid[$idx]=${CSessid[$idx]}
PNread[$idx]=${CNread[$idx]}
PNwrite[$idx]=${CNwrite[$idx]}
(( idx = idx + 1 ))
done
((TotalPrevious=TotalCurrent))
}

#
# printcurrent - prints the values of the CSessid, CNread & CNwrite arrays.
#
printcurrent()
{
idx=0
while [ "${CSessid[idx]}" ]
do
#print "Current (${CSessid[idx]}|${CNread[idx]}|${CNwrite[idx]})"
print "[${CSessid[idx]}|${CNread[idx]}|${CNwrite[idx]}]"
(( idx = idx + 1 ))
done
print "Total: $TotalCurrent"
}

#
# printprevious - prints the values of the CSessid, CNread & CNwrite arrays.
#
printprevious()
{
idx=0
while [ "${PSessid[idx]}" ]
do
#print "Previous [${PSessid[idx]}|${PNread[idx]}|${PNwrite[idx]}]"
print "[${PSessid[idx]}|${PNread[idx]}|${PNwrite[idx]}]"
(( idx = idx + 1 ))
done
print "Total: $TotalPrevious"
}

#
# makedelta - find the entries with a difference and load delta
#
makedelta()
{
Cidx=0
Didx=0
while [ $Cidx -le $TotalCurrent ]
do
# Search the Previous array for a matchin CSessid
Pidx=0
while [ $Pidx -le $TotalPrevious ]
do
if [ ${PSessid[$Pidx]} = ${CSessid[Cidx]} ]
then
# Found a match, now check for a delta
Cio=$(( ${CNread[Cidx]} + ${CNwrite[Cidx]} ))
Pio=$(( ${PNread[Pidx]} + ${PNwrite[Pidx]} ))
if [ $Cio -gt $Pio ]
then
DSessid[$Didx]=${CSessid[$Cidx]}
(( Ddelta[$Didx] = $Cio - $Pio ))
(( Didx = Didx + 1 ))
fi
fi
(( Pidx = Pidx + 1 ))
done
(( Cidx = Cidx + 1 ))
done
(( TotalDelta = Didx - 1 ))
}

#
# print the delta out. This is the main program output
printdelta()
{
print -n "$sgr0$Thome"
print "$(date "+%D %T") Top queries by I/O (Delta > 100), Sample: $Pause seconds"
# Sort by delta descending

Didx=0
Rows=1
while [ $Didx -le $TotalDelta ]
do
print "${Ddelta[$Didx]}\t${DSessid[$Didx]}\t${Ddelta[$Didx]}"
#print "${Didx}: ${DSessid[$Didx]} | ${Ddelta[$Didx]}"
(( Didx = Didx + 1 ))
done | sort -nr | cut -d\ -f2,3 | while read Sessid Delta
do
[ $Delta -le 100 ] && continue
printf "%s\nSessid: %5d\tTotal I/O: %5d%s\n" $Teol $Sessid $Delta $Teol
(( Rows = Rows + 2 ))

priv onstat -g sql $Sessid > $WORK
if [ "$(grep "Current SQL statement" $WORK)" ]
then
sed -e '1,/Current SQL statement/d' \
-e '/Last parsed SQL statement/,$d' $WORK > ${WORK}.2
else
sed '1,/Last parsed SQL statement/d' $WORK > ${WORK}.2
fi
(( Rows = Rows + $(cat ${WORK}.2 | wc -l) ))
if [ $Rows -le $WinHeight ]
then
OIFS=$IFS
IFS="
"
cat ${WORK}.2 | \
while read InLine
do
printf "%s%s\n" $InLine $Teol
done
IFS=$OIFS
rm -f $WORK ${WORK}.2
else
rm -f $WORK ${WORK}.2
break
fi
done
print -n "EOR$Teod"
}

# main
export WORK=/usr/app/tmp/work.$PID
Thome=$(tput home)
sgr0=$(tput sgr0)
Tclear=$(tput clear)
Teol=$(tput el)
Teod=$(tput ed)

Pause=${1:-60}
WinHeight=$(stty -a | grep rows | cut -d\; -f2)
WinHeight=$(print $WinHeight)
WinHeight=${WinHeight% rows}
typeset -i WinHeight
if [ -z "$WinHeight" -o "$WinHeight" = "0" ]
then
WinHeight=20
else
((WinHeight=WinHeight-4))
fi

TotalPrevious=0
TotalCurrent=0
Pass=0
print -n $Tclear
print "$0: initilizing - $Pause seconds, please wait"
print -n "................................................. ..........."
while :
do
getonstat
((Pass = Pass + 1 ))
[ $Pass = 1 ] && assignprevious && initdots && continue
#printcurrent > Curr
#printprevious > Prev
makedelta
printdelta
assignprevious
#break
idx=1
while [ $idx -le $((Pause + 1 )) ]
do
print -n .
(( idx = idx + 1 ))
done
while [ $idx -gt 1 ]
do
print -n "\b \b"
sleep 1
(( idx = idx - 1 ))
done
done
sending to informix-list

Ben Thompson wrote:
700k writes vs 442M reads (1:500+); write performance is unlikely to be
a major issue, and that percentage doesn't matter. The low read cache
does matter; 1 read in 20 has to go to disk; you want nearer 1 in 100
(99% cache) if you can.

Given the large memory already allocated, that may be hard to do.


--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler (AT) earthlink (DOT) net, jleffler (AT) us (DOT) ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Hi Leona.
If anything I say in here evokes a response 'I know that dummy'(-) I
apologize.

Given your profile, Onconfig and the fact that you are running an OLTP I
would suggest you make the following changes to your onconfig. Some are
calculations, some out of experience:

You profile gives tells you your instance uses 3063808 Kbytes of memory. You
initial SHMVIRTSZ is set to 294912 Kbytes. Now, when started up the instance
will simply take what it needs base on buffers, poll threads, ds,locks etc,
etc, etc so you can assume the number in the profile is what is takes when
started. Now, when you set SHMVIRTSZ to 3063808 Kbytes you might end up with
a problem because the instance will usually double that number. I suggest
you set it to 1914880KB (that is half it uses according to your profile
times 1.25 to allow for a little buffer without having to assign new shared
memory segments.) When started up onstat - will probably show double that
(approx 3900000KB) or a bit more.

Whenever you get the possibility, rebuild your instance and have the root
chunk be used for nothing else but the instance tables, so do not create a
database in it (at all). I can't check how much your root dbs has filled, so
it is just a remark, but not enough space for your instance will result in
tremendous performance loss and the instance not working at all.

I case you haven't done this make sure your logical logs are in a dbspace of
there own and not in your root. Whenever possible do the same with your
physical log. Put it in a chunk of its own, not sharing with anything else
and no offset either. If you loose space don't worry about it.

Limit SHMTOTAL to 60%of what you have (8GB*.6)= SHMTOTAL 5033164 which will
leave the instance with plenty of room and makes sure your system will not
run into trouble (too soon). When the instance starts you may find a
'SHMTOTAL rounded to .....' in the online.log. When that happens just adjust
the number in the onconfig to that.

Change:
MAX_PDQ_PRIORITY 60 (instead of 100) to prevent dss bringing the instance to
a virtual halt)
DS_TOTAL_MEMORY 1258290KB ( 25% of SHMTOTAL )
DS_MAX_QUERIES (empty, so the instance calculates max queries)

After restarting your engine take a look in the online log and note the
number the instance assigns to D_MAX_QUERIES.
With that number recalculate your RA_ parameters. Your RA_pages seems way to
high for an OLTP and probably causing a lot of useless pages in your
buffers.

RA_PAGES (nbr_of_buffers * use% of buffers) / ( 2* [nbr of max queries]) + 2
Say 1200000 * .25 [for 25%] ) / ( 2* [ say 14000 =28000] ) + 2
RA_THRESHOLD (nbr_of_buffers * use% of buffers) / ( 2* [nbr of max queries])
- 2
Say 1200000 * .25 [for 25%] ) / ( 2* [ say 14000 =28000] )
- 2

You will probably end up with something like 28 and 24

TRY and set optcompind to 0

I do not know how many discs there are in you setup (even though you are
using raid), but you may want to take a look at your LRU and CLEANERS as
well.

Just in case it is not yet the case:
Have you OS administrators check the way your raid is set up and how they
distributed the discs. You would want your instance spread over as many raid
controllers as possible. Split your instance over more than 1 volume group,
but keep them balanced for i/o

When in need 'just holler'
Regards,

------------------------------------------------------------------------
R.A. Reissaus
Risdi, Zurich, Switzerland
Ibm/Informix Consultants
Telefoon: +41(0)43 534 67 94 : Phone
Mobiel : +41(0)79 592 99 13 : Mobile
E-mail : r.a.reissaus (AT) risdi (DOT) com
Website : www.risdi.com
------------------------------------------------------------------------
Disclaimer:
`Externe E-Mail wordt door partijen niet gebruikt voor het aangaan van
verplichtingen`

`Any e-mail messages from sending parties named in this e-mail are given
in good faith but shall not be binding nor shall they be construed as
constituting any obligation on the part of any such party.`
------------------------------------------------------------------------




-----Oorspronkelijk bericht-----
Van: owner-informix-list (AT) iiug (DOT) org [mailtowner-informix-list (AT) iiug (DOT) org]
Namens Leona Ankrah
Verzonden: donderdag 18 augustus 2005 16:52
Aan: informix-list (AT) iiug (DOT) org
Onderwerp: Onstat -P output



Hi,
I'm using IDS9.21 UC6 running on a SOlaris 8.0
Operating system.
The output of the onstat -P is not acceptable and I
would appreciate it if I could be helped get a better
results. The system is not performing well enough.
Attached is the output of the onstat -p.
I have also attached the onconfig file for the
database configuration.
please help.

Leona
Ghana Telecom Limited





__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
sending to informix-list

Run onstat -u and find what sessions have the most reads.

Run it twice a few seconds apart (or use the qio script in another
reply)
and run onstat -g ses <session-id> and see what sql that session is
doing.

Take this sql and if it is a select

1. Run dbaccess against the sessions current database.

2. In dbaccess do Query -> New.

3. Put in

SET EXPLAIN ON;

and run that.

4. Choose New and put in the sql

5. Run that a wait a few seconds (say 10) and hit in the interrupt key
(normally Ctrl-C).

6. Exit dbaccess and look at the last sql in the sqexplain.out file
in the current directory.

Are there any sequential scans of large tables?
If so you need to look at the indexes on that table or run
the proper update statistics on that table. If the query is still
not using
the right index then this is a bug and you need to log a support
call
with IBM.

If indexes are used then are they the right ones for the query.
Queries should go from small tables to large ones and use indexes to
do the joins.

One thing I did see recently was a site where a table contain alarms
for people to action. There were 200,000+ alarms in the table!
Clearly
this table needed to be cleared down. If people have not actioned
alarms
for 3+ months then they surely they can be cleared down.

Let us know the results. You show be able to identify the sessions
and
sql's that are causing the problem.