possible Informix I/O performance?

[gargamel12]

Hi,

I'm new on informix database servers that's why I ask you guy's for help. The program wendor just say's buy a new server... Currently this runs on Sun 450 512Mb mem and two mirrored scsi disks. I now there should be a raid set. Also the vendor think's Solid state disk cures all but I would first like to now if this is the case.

Here is some output from the server:
onconfig
bash-2.00# more onconfig
#**************************************************************************
#
# INFORMIX SOFTWARE, INC.
#
# Title: onconfig.std
# Description: INFORMIX-OnLine Configuration Parameters
#
#**************************************************************************

# Root Dbspace Configuration

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

# Logical Log Configuration

LOGFILES 32 # Number of logical log files
LOGSIZE 500 # Logical log size (Kbytes)

# Diagnostics

MSGPATH /usr/informix/online.log # System message log file path
CONSOLE /usr/informix/console # System console message path
# ALARMPROGRAM /usr/informix/etc/log_full.sh # Alarm program path
ALARMPROGRAM /usr/informix/etc/no_log.sh
SYSALARMPROGRAM /usr/informix/etc/evidence.sh # System Alarm program path
TBLSPACE_STATS 1

# System Archive Tape Device

# TAPEDEV /dev/tapedev # Tape device path
TAPEDEV /backup/informix/dump
TAPEBLK 16 # Tape block size (Kbytes)
TAPESIZE 2000000 # 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-OnLine/Optical staging area

# System Configuration

SERVERNUM 1 # Unique id corresponding to a OnLine instance
DBSERVERNAME online1 # Name of default database server
DBSERVERALIASES online2,abheus037 # List of alternate dbservernames
NETTYPE ipcshm,1,200,CPU # Configure poll thread(s) for nettype
NETTYPE tlitcp,4,500,NET # Configure poll thread(s) for nettype
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.
RESIDENT 1 # Forced residency flag (Yes = 1, No = 0)

MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor
NUMCPUVPS 1 # 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 40000 # Maximum number of locks
BUFFERS 25000 # Maximum number of shared buffers
NUMAIOVPS 1 # Number of IO vps
PHYSBUFF 32 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
LOGSMAX 256 # Maximum number of logical log files
CLEANERS 32 # Number of buffer cleaner processes
SHMBASE 0xa000000 # Shared memory base address
SHMVIRTSIZE 8000 # initial virtual shared memory segment size
SHMADD 8192 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 45 # Check point interval (in sec)
LRUS 32 # 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 mark percentage
LTXEHWM 60 # Long transaction high water mark (exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 32 # 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
# 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)

# 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 10 # Number of pages to attempt to read ahead
RA_THRESHOLD 7 # 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 # 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 90 # 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 # Maximum number of decision support queries
DS_TOTAL_MEMORY # 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
# &quot;repeatable read&quot;, 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 2 # To hint the optimizer

ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAIT
LBU_PRESERVE 0 # Preserve last log for log backup
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
# Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS
OPT_GOAL -1

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

# Status of restartable restore
RESTARTABLE_RESTORE off

bash-2.00# onstat -p

Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 3 days 17:12:47 -- 81728 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
629361 1043468 12580813 95.00 59077 154815 1936402 96.95

isamtot open start read write rewrite delete commit rollbk
8390768 610963 1143648 2565586 182548 13213 5307 12250 35163

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 1246.65 117.14 905 10690

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
50195 0 8765318 0 0 30 16511 2028

ixda-RA idx-RA da-RA RA-pgsused lchwaits
23083 1651 413034 436279 12

bash-2.00#
bash-2.00# onstat -u

Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 3 days 17:13:55 -- 81728 Kbytes

Userthreads
address flags sessid user tty wait tout locks nreads nwrites
e04c018 ---P--D 1 root - 0 0 0 333 7402
e04c504 ---P--F 0 root - 0 0 0 0 48578
e04c9f0 ---P--F 0 root - 0 0 0 0 0
e04cedc ---P--F 0 root - 0 0 0 0 0
e04d3c8 ---P--F 0 root - 0 0 0 0 0
e04d8b4 ---P--F 0 root - 0 0 0 0 0
e04dda0 ---P--F 0 root - 0 0 0 0 0
e04e28c ---P--F 0 root - 0 0 0 0 0
e04e778 ---P--F 0 root - 0 0 0 0 0
e04ec64 ---P--F 0 root - 0 0 0 0 0
e04f150 ---P--F 0 root - 0 0 0 0 0
e04f63c ---P--F 0 root - 0 0 0 0 0
e04fb28 ---P--F 0 root - 0 0 0 0 0
e050014 ---P--F 0 root - 0 0 0 0 0
e050500 ---P--F 0 root - 0 0 0 0 0
e0509ec ---P--F 0 root - 0 0 0 0 0
e050ed8 ---P--F 0 root - 0 0 0 0 0
e0513c4 ---P--F 0 root - 0 0 0 0 0
e0518b0 ---P--F 0 root - 0 0 0 0 0
e051d9c ---P--F 0 root - 0 0 0 0 0
e052288 ---P--F 0 root - 0 0 0 0 0
e052774 ---P--F 0 root - 0 0 0 0 0
e052c60 ---P--F 0 root - 0 0 0 0 0
e05314c ---P--F 0 root - 0 0 0 0 0
e053638 ---P--F 0 root - 0 0 0 0 0
e053b24 ---P--F 0 root - 0 0 0 0 0
e054010 ---P--F 0 root - 0 0 0 0 0
e0544fc ---P--F 0 root - 0 0 0 0 0
e0549e8 ---P--F 0 root - 0 0 0 0 0
e054ed4 ---P--F 0 root - 0 0 0 0 287
e0553c0 ---P--F 0 root - 0 0 0 0 408
e0558ac ---P--F 0 root - 0 0 0 0 206
e055d98 ---P--F 0 root - 0 0 0 0 766
e056284 ---P--- 14 root - 0 0 0 0 2293
e056770 ---P--B 15 root - 0 0 0 7 51
e056c5c Y--P--- 201 user1 - e5a9370 0 1 28 983
e057148 Y--P--- 200 user1 - e5a8c48 0 1 66 98
e057634 ---P--D 18 user1 - 0 0 0 0 0
e057b20 Y--P--- 208 user1 - e55ac98 0 1 0 0
e05800c Y--P--- 199 user1 - e27f8e0 0 1 0 0
e0589e4 Y--P--- 207 user1 - e258948 0 1 0 0
e059d94 Y--P--- 146 user1 - e2734d0 0 1 614 695
e05a280 Y--P--- 162 user1 - e750680 0 1 0 0
e05a76c Y--P--- 192 user1 - eabd6a8 0 1 813 3291
e05ac58 Y--P--- 150 user1 - ea88680 0 1 0 0
e05b144 Y--P--- 161 user1 - e233870 0 1 0 0
e05b630 Y--P--- 147 user1 - e273bf8 0 1 8115 79920
e05bb1c Y--P--- 149 user1 - e1dc680 0 1 0 0
e05c008 Y--P--- 136 user1 - e3874d0 0 1 0 0
e05c4f4 Y--P--- 142 user1 - e37a680 0 1 0 0
e05cecc Y--P--- 131 user1 - e204da8 0 1 60 0
e05d3b8 Y--P--- 135 user1 - e386da8 0 1 136 629
e05d8a4 Y--P--- 145 user1 - e272da8 0 1 3 0
e05dd90 Y--P--- 194 user1 - e27e680 0 1 0 0
e05e27c Y--P--- 189 user1 - e1cdaf8 0 1 0 0
e05e768 Y--P--- 190 user1 - eabc858 0 1 0 0
e05ec54 Y--P--- 191 user1 - eabcf80 0 1 64 98
e05f140 Y--P--- 198 user1 - e27f1b8 0 1 104 0
e05f62c Y--P--- 195 user1 - e27eda8 0 1 0 0
e05fb18 Y--P--- 144 user1 - e272680 0 1 222 0
60 active, 128 total, 67 maximum concurrent


Thanks for possible help.

 

[rkirkw]

Can you send the output of onstat -g iov
That way we can know if you are running async I/O or not.
If not, you probably need more AIOVP's
Is there a /dev/async on the box or Sun's equivalent?
Are you using raw devices? or filesystem? for your dbspaces?

 

[gargamel12]

Ok here is the info I'm just reading more about this. The server has just been rebooted because some kernel parameter changes so the stats are quite low...

# onstat -g iov

Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 13:58:40 -- 81728 Kbytes

AIO I/O vps:
class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors
kio 0 i 0.0 0 0 0 0 0 0.0 0
kio 1 i 2.3 114292 89953 24339 0 190053 0.6 0
msc 0 i 0.0 271 0 0 0 272 1.0 0
aio 0 i 0.0 266 111 104 0 111 2.4 0
pio 0 i 0.0 0 0 0 0 1 0.0 0
lio 0 i 0.0 0 0 0 0 1 0.0 0

#

I read about async I/O on sun and there read that the /dev/vx = veritas volume manager disk supports kaio and the kaio module is loaded in the system. So everything is go for kaio.

Yes we are using raw devices. Here is some info on /dev

# ls -ltr /dev/onli*
lrwxrwxrwx 1 root 21 Oct 4 2000 /dev/online_root -> /dev/vx/rdsk/informix
# ls -ltr /dev/vx/rdsk/*
crw------- 1 root 156, 0 Oct 4 2000 /dev/vx/rdsk/rootvol
crw------- 1 root 156, 5 Oct 4 2000 /dev/vx/rdsk/swapvol
crw------- 1 informix 156, 7 Oct 4 2000 /dev/vx/rdsk/extrachunk
crw------- 1 informix 156, 8 Oct 4 2000 /dev/vx/rdsk/extrachunk2
crw-rw---- 1 informix 156, 6 Oct 5 2000 /dev/vx/rdsk/informix

/dev/vx/rdsk/rootdg:
total 0
crw------- 1 root 156, 0 Oct 4 2000 rootvol
crw------- 1 root 156, 5 Oct 4 2000 swapvol
crw-rw---- 1 informix 156, 6 Oct 4 2000 informix
crw------- 1 informix 156, 7 Oct 4 2000 extrachunk
crw------- 1 informix 156, 8 Oct 4 2000 extrachunk2

 

[FCVV]

HI,

First:

Try identify the Hot Spots:

(Table Reads)
database sysmaster;
select tabname , bufreads , isreads , pagreads
from sysptprof
where tabname not like &quot;%sys%&quot;
order by 4 desc

(table writes)
database sysmaster;
select tabname , bufwrites, iswrites , pagwrites
from sysptprof
where tabname not like &quot;%sys%&quot;
order by 4 desc

LOcate Tables (DBSPACE)

database YourDB;
select tabname , dbinfo(&quot;dbspace&quot;, partnum) from systables
where partnum > 0 and
tabid > 99
order by 2,1

Second:

Verify CheckPoint Interval and Duration.
>5 Seconds = Poor OLTP Performance.
Verify Page Locks (ROW LOCK = Good for OLTP)
Verify Page Cleaners (FG Writes = Very Bad for OLTP)
(onstat -F)

Third:
Reorganize (Less Extents)
Verify Indexes
Analize SQL (Explain)
Move Tables (Less IO Disk)
Fragment


Best Regards,
FCVV.