RE: Informix Performance
> -----Original Message-----
> From: daniel.castro@feliciorocho.org.br
> [SMTP:daniel.castro@feliciorocho.org.br]
> Sent: Thursday, February 26, 2004 1:55 PM
> To: informix-list@iiug.org
> Subject: Informix Performance
>
> Hi,
>
> I am having informix performance problems. I don't know where to
> start.
> Sometimes the database hangs, always from 00:00am to 5:00 am, but it
> gives no error in the log file. Can you help me?
> Sometimes the users says that the system is slow, but I can't identify
> why.
> First, the result of onstat -p:
>
> Informix Dynamic Server Version 9.30.UC1 -- On-Line -- Up 9 days
> 21:06:09 -- 1582656 Kbytes
>
> Profile
> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached
> 67223627 173897813 2077681908 96.76 12042759 61104633 222993237
> 94.60
>
> isamtot open start read write rewrite delete commit
> rollbk
> 2790216351 184683421 616967012 4148277112 65017319 12219659 28562330
> 8807460 884
>
> 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
> 7 0 0 336038.88 116702.45 1385 4099
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress
> seqscans
> 7192942 14028 903998567 4 0 6224 6824574
> 39496093
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 12311760 5075181 15389056 32074507 56519934
>
> Second my onconfig file:
>
> #*************************************************************************
> *
> #
> # INFORMIX SOFTWARE, INC.
> #
> # Title: onconfig.producao
> # Description: Informix Dynamic Server Configuration Parameters
> #
> #*************************************************************************
> *
> # Root Dbspace Configuration
> ROOTNAME rootdbs # Root dbspace name> ROOTPATH /cooked_file/dbs_rootP # Path for device containing
> root dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device
> (Kbytes)
> ROOTSIZE 471039 # 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 wpdphlog # Location (dbspace) of physical log
> PHYSFILE 60000 # Physical log file size (Kbytes)> # Logical Log Configuration
> LOGFILES 40 # Number of logical log files
> LOGSIZE 10000 # Logical log size (Kbytes)> # Diagnostics
> MSGPATH /opt/informix/producao.log # System message log file
> path
> CONSOLE /dev/console # System console message path
> ALARMPROGRAM /opt/informix/etc/log_full.sh # Alarm program path
> TBLSPACE_STATS 1 # Maintain tblspace statistics> # System Archive Tape Device
> TAPEDEV /dev/st0 # Tape device path
> TAPEBLK 16 # Tape block size (Kbytes)
> TAPESIZE 100000000 # Maximum amount of data to put on
> tape (Kbytes)> # Log Archive Tape Device
> LTAPEDEV /dev/st0 # Log tape device path
> LTAPEBLK 16 # Log tape block size (Kbytes)
> LTAPESIZE 100000000 # Max amount of data to put on log
> tape (Kbytes)
> # Optical> STAGEBLOB # Informix Dynamic Server staging area
> # System Configuration
> SERVERNUM 1 # Unique id corresponding to a OnLine> instance
> DBSERVERNAME producao # 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)
> MULTIPROCESSOR 1 # 0 for single-processor, 1 for> multi-processor
> # NUMCPUVPS 1 # Number of user (cpu) vps
[Bill Dare]
I'm not sure what happens when you initialize the server with
NUMCPUVPS commented out. But, if you are running with only 1 CPU VP then
SINGLE_CPU_CP should be set to 1 and MULTIPROCESSOR to 0 or you will have
performance problems.
> 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 200000 # Maximum number of locks
> BUFFERS 600000 # Maximum number of shared buffers> # NUMAIOVPS # Number of IO vps
> PHYSBUFF 128 # Physical log buffer size (Kbytes)
> LOGBUFF 128 # Logical log buffer size (Kbytes)
> CLEANERS 4 # Number of buffer cleaner processes
> SHMBASE 0x50000000 # Shared memory base address
> SHMVIRTSIZE 154000 # initial virtual shared memory> segment size
> SHMADD 32768 # Size of new shared memory segments
> (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
> 0=>unlimited
> CKPTINTVL 600 # Check point interval (in sec)
> LRUS 64 # Number of LRU queues
[Bill Dare] This may not be enough LRUs to handle 600000 buffers. What are your
checkpoint durations?
> LRU_MAX_DIRTY 3 # LRU percent dirty begin cleaning> limit
> LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
> 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
> 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_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)
> CDR_SERIAL 0,0 # Serial Column