I am running Informix 7.31 32 bit on a Sun Blade 1500 running Solaris 5.8 and my performance is very poor.

sar indicates no problems with CPU, RAM or I/O.

I have listed my configuration parameters below. Does anyone know of anything I can do to improve performance?

$ onstat -c

Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 55 days 22:42:38 -- 30736 Kbytes

Configuration File: /export/home/informix/etc/onconfig
#
# Script: onconfig.informix
# Subsystem: 1
# %version: 1.1.1 %
#
# INFORMIX SOFTWARE, INC.
#
# Title: onconfig.std
# Description: INFORMIX-OnLine Configuration Parameters
#
# During installation the location DISKNAME must be changed to raw partition
#
#************************************************* *************************

# Root Dbspace Configuration

ROOTNAME rootdbs # Root dbspace name
ROOTPATH /dev/rdsk/c0t0d0s6 # Path for device containing root dbspace
ROOTOFFSET 100 # 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 1000 # Physical log file size (Kbytes)

# Logical Log Configuration

LOGFILES 9 # Number of logical log files
LOGSIZE 1000 # Logical log size (Kbytes)

# Diagnostics

MSGPATH /export/home/informix/online.log # System message log file path
CONSOLE /export/home/informix/console.log # System console message path
ALARMPROGRAM # Alarm program path

# System Archive Tape Device

TAPEDEV /dev/null # Tape device path
TAPEBLK 16 # Tape block size (Kbytes)
TAPESIZE 10240 # 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 omcserver # Name of default database server
DBSERVERALIASES dbremote # of alternate dbservernames
NETTYPE # Override sqlhosts nettype parameters
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.
RESIDENT 0 # 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 0 # Process aging
AFF_SPROC 0 # Affinity start processor
AFF_NPROCS 0 # Affinity number of processors

# Shared Memory Parameters

LOCKS 100000 # Maximum number of locks
BUFFERS 200 # Maximum number of shared buffers
NUMAIOVPS # Number of IO vps
PHYSBUFF 32 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
LOGSMAX 40 # Maximum number of logical log files
CLEANERS 1 # 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 300 # Check point interval (in sec)
LRUS 8 # Number of LRU queues
LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 50 # 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.

# 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

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

# DBSPACETEMP:
# OnLine equivalent of DBTEMP for SE.

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

# ADT*
# The following parameters control the type and level of secure auditing
# present in the OnLine system. By default, ADTMODE is 0 and auditing
# is disabled

ADTMODE 0 # Auditing mode
ADTPATH /tmp # Directory where audit trails will be written by OnLine
ADTSIZE 50000 # Maximum size of any single audit trail file
ADTERR 0 # 0 ==> retry failed audit writes; 1 ==> log failure

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)
PDQPRIORITY 0 # Degree of parallelism: 0 ... 100,
# OFF => 0, LOW => 1, HIGH => 100
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
# "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 2 # To hint the optimizer

ONDBSPACEDOWN 0 # 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)

$ onstat -g iof

Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 55 days 22:46:44 -- 30736 Kbytes

AIO global files:
gfd pathname totalops dskread dskwrite io/s
3 c0t0d0s6 9974580 8587646 1386934 2.1

$ onstat -p

Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 55 days 22:47:34 -- 30736 Kbytes

Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
28461424 530031 449106960 93.66 1904448 1214565 7091575 73.14

isamtot open start read write rewrite delete commit rollbk
235529842 7273150 18114360 157123512 1488876 168187 450015 405928 51

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 43 9156.71 1153.19 17871 35804

bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
6458856 0 432367738 0 0 1950 146846 429791

ixda-RA idx-RA da-RA RA-pgsused lchwaits
172167 26679 26518650 26648083 16198

Hi,

You have quite some work to do, the configuration is far from optimal. First of all you probable need to create several dbspaces. But most important and some easy steps that you can follow are some parameter changes:

BUFFERS:
Now 200, this measn 400KB cache. Raise this a lot. I don't know your database size but let's say raising it upto 128 MB. So new value: 64000
Main idea is, the bigger the better (as long as you have memory ofcourse)

LRUS:
Change this value to 64.

CLEANERS:
Change this to 4

OPTCOMPIND:
Change this to 0

And give the command: UPDATE STATISTICS for every database.

I think your performance will be beter after this.

Greetz,

Rob Prop

Also increase the size of you PHYSLOG

I modified the parameters suggested above and achieved better than 50% reduction in wait times.

What do you mean by "First of all you probably need to create several dbspaces"?

Thanks for the help.

I don't know what PHYSLOG is, but I have PHYSFILE set to 1000 KBytes and PHYSBUFF set to 32 KBytes.

My page size is 8192, and my number of concurrent connections should be very, very low.

What do you recommend for PHYSFILE and PHYSBUFF?

The physical log parameters have a relation with the writing of data to disk,and will not have a enormous impact on the overall performance. About your question regarding my advice to create multiple dbspaces I can say: When you have multiple dbspaces you can split for example data and indexes and improve parallel disk access. In the manuals of IDS you can find a lot more info about this topic.

Hope this helps,

Rob