Performance Problem/Question
All:
I am currently having a performance problem. This system is primarily
a batch system with most of the updates occuring at night. During the
day reports are pulled on the data loaded at night. If a report is requested
for a one month period of time the performance is acceptable although not
outstanding. However, if you ask for longer than one month then the
time required increases dramatically. A one month report may take a few
minutes while a six month report takes 24-36 hours. I found that the
long reports were waiting on buffers, but I don't understand this because
while these reports are waiting other one month reports will still finish
in a normal amount of time. My cache percentages are good and I can't see
anything else that indicates a lack of buffers, but perhaps I am overlooking
something or misinterpreting some of the statistics.
Anyone with any ideas of what to try or check?
Any help is appreciated.
TIA
Mark Stannard
Ameritech
Partial output of onstat -u:
Userthreads
address flags sessid user tty wait tout locks nreads nwrites
4003a014 ---P--D 1 informix - 0 0 0 779 2962
4003a454 ---P--F 0 informix - 0 0 0 0 44011
4003a894 ---P--F 0 informix - 0 0 0 0 95697
400489d4 B--PR-- 3930 tpmr - 3043da9c 0 1 811773 4400
40049694 B--PR-- 8611 tpmr - 3043da9c 0 1 81136 544
4004d214 ---PR-- 6074 tpmr - 0 0 1 773924 2480
4004f414 B--PR-- 9638 tpmr - 30346d0c 0 1 3005 48
40050514 ---PR-- 9111 tpmr - 0 0 1 57224 256
39 active, 128 total, 108 maximum concurrent
Output of onstat -p:
INFORMIX-OnLine Version 7.23.UC1 -- On-Line -- Up 3 days 12:06:10 --
130368 Kbytes
Profile
dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
12556989 13981606 450358287 97.21 341510 1239267 8368077 95.92
isamtot open start read write rewrite delete commit rollbk
405978035 327559 115904130 65528131 3441601 8847 72315 759 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 63520.79 10761.69 383 766
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
11249654 0 128449184 0 0 746 1983 4207
ixda-RA idx-RA da-RA RA-pgsused lchwaits
4050080 196202 70260 4274630 150416
Output of onstat -R:
INFORMIX-OnLine Version 7.23.UC1 -- On-Line -- Up 3 days 11:43:50 --
130368 Kbytes
8 buffer LRU queue pairs
# f/m length % of pair total
0 f 1829 98.5% 1857
1 m 28 1.5%
2 f 1832 98.4% 1862
3 m 30 1.6%
4 f 1845 98.9% 1866
5 m 21 1.1%
6 f 1914 98.7% 1940
7 m 26 1.3%
8 F 1817 98.2% 1850
9 m 33 1.8%
10 f 1840 98.5% 1868
11 m 28 1.5%
12 f 1836 98.8% 1859
13 m 23 1.2%
14 f 1856 98.7% 1881
15 m 25 1.3%
214 dirty, 14983 queued, 15000 total, 16384 hash buckets, 4096 buffer size
start clean at 10% (of pair total) dirty, or 187 buffs dirty, stop at 5%
My configuration file:
#**************************************************************************
#
# INFORMIX SOFTWARE, INC.
#
# Title: onconfig.std
# Description: INFORMIX-OnLine Configuration Parameters
#
#**************************************************************************
# Root Dbspace Configuration
ROOTNAME rootdbs # Root dbspace name
ROOTPATH /dev/rtchk2 # Path for device containing root dbspace
ROOTOFFSET 4 # Offset of root dbspace into device (Kbytes)
ROOTSIZE 256000 # 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 20000 # Physical log file size (Kbytes)
# Logical Log Configuration
LOGFILES 3 # Number of logical log files
LOGSIZE 2000 # 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/alarm/alarm.sh # Alarm program path
# System Archive Tape Device
#TAPEDEV /netadmin/tpmr/online_backup/online.archive # Tape device
path
TAPEDEV /dev/rmt0.1
#TAPEDEV /dev/null
TAPEBLK 16 # Tape block size (Kbytes)
TAPESIZE 1024000000 # 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 appi # Name of default database server
DBSERVERALIASES # List of alternate dbservernames
DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.
RESIDENT 0 # Forced residency flag (Yes = 1, No = 0)
# Not supported on AIX 4.14
MULTIPROCESSOR 1 # 0 for single-processor,
1 for multi-processor
NUMCPUVPS 3 # 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 22000 # Maximum number of locks
BUFFERS 15000 # Maximum number of shared buffers
NUMAIOVPS 20 # Number of IO vps
PHYSBUFF 64 # Physical log buffer size (Kbytes)
LOGBUFF 64 # Logical log buffer size (Kbytes) LOGSMAX 6 # Maximum number of logical log files
CLEANERS 8 # Number of buffer cleaner processes
SHMBASE 0x30000000 # Shared memory base address
SHMVIRTS