Re: Performance issues vis a vis checkpoints and uhhh what am I doing wrong...

From: "adiosadios" <adiosadios@crosswinds.net> > > After a nice leisurely summer, the fall has brought on performance >issues that I would like your advice on. Specifically checkpoints that >used So, you've had a fall in performance, then? >to take 0-1 seconds are now taking 2-6 seconds and at least twice per day >will escalate to 15-20 seconds, which begets user groaning and dba job >pressure. In addition the bufwaits ratio will avg 10-17% during the first >hour of being zero'd out. As the sample period progresses to 2-3 hours it >edges back down to 5-10%. The same is true for bufwaits only inversed, >starting at 92-97% and dropping to 75-85%. Occasionally, but not always, >the problems occur when the app (primarily a oltp back office application) >is used by staff for DSS reporting (fy year reports, ad hoc analysis, etc). >One thing I am considering trying is setting the DSS session env parameters >to take advantage of PDQ and modifying all the sql for dirty read...? PDQ and PSORT_* may give valuable improvements. >Each week I have been incrementing the buffers, shmvirt, and last weekend >the ra parameters, otherwise I haven't been toying with much else. > >One area I know needs to be addresses is the single tempdbs, which will be >fixed this weekend by creating two additional tempdbs. Another area some >of >you pointed out in a previous posting is the amount of physical log space >and the root dbs which will also be increased this weekend. I do not have >KAIO on as I recall several posts which indicated sporadic problems with >the Bollocks. Switch it on. >HP 11x platform. I think onstat -g * output looks ok, except in onstat iov >the io/wup's can reach above 1.0 for all the aiovp's, not sure if this is >an >indicator of something serious. > >HP 11.0 64 bit >Infx 7.31 UC2A 32 bit >8 gig mem >4 cpu >Usual unix apps/services but nothing consuming resources. 30-55 users with >300-375 active 450-550 total connections. onstat -p onstat -R onstat -F onstat -P | tail -5 onstat -m onstat -d onstat -D onstat -l onstat -g iov (during the "bad times") >p.s. I hope I haven't made an obvious error aka the poor stiff posting the >issue of the 32 gig db piped to 19gig tape...I don't think I could handle >the clowning around of carlos, et al, oohh probably don't have to worry, as >the holder of the procrastinators list he'll put off any broadsides till >tomorrow. > >Informix Dynamic Server Version 7.31.UC2A -- On-Line -- Up 1 days Get an FC port -- you won't believe how much more of that 8GB you'll be able to reach. >13:52:59 -- 482128 Kbytes ># Root Dbspace Configuration >ROOTNAME rootdbs # Root dbspace name>ROOTPATH /dbms/links/rootdbs # Path for device containing root >dbspace >ROOTOFFSET 512 # Offset of root dbspace into device >(Kbytes) >ROOTSIZE 127488 # Size of root dbspace (Kbytes) Small. ># Physical Log Configuration >PHYSDBS plogdbs # Location (dbspace) of physical log >PHYSFILE 5000 # Physical log file size (Kbytes) Small. ># Logical Log Configuration >LOGFILES 50 # Number of logical log files >LOGSIZE 5000 # Logical log size (Kbytes) Small. ># Diagnostics >TBLSPACE_STATS 0> ># Optical >STAGEBLOB # Informix Dynamic Server/Optical staging >area > ># System Configuration >SERVERNUM 0 # Unique id corresponding to a Dynamic>Server instance >DBSERVERNAME online # Name of default database server >DBSERVERALIASES online2 # List of alternate dbservernames >NETTYPE ipcshm,2,200,CPU # 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) Try -1 >MULTIPROCESSOR 1 # 0 for single-processor, 1 for>multi-processor >NUMCPUVPS 3 # change from 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 Try 1. >AFF_NPROCS 3 # change from 3 Affinity number of>processors > ># Shared Memory Parameters >LOCKS 75000 # Maximum number of locks Very low. x100? >BUFFERS 150000 # Maximum number of shared buffers >NUMAIOVPS 28 # (from 0 to 2 * chuncks) Number of IO vps KAIO. >PHYSBUFF 256 # Physical log buffer size (Kbytes) >LOGBUFF 256 # 128 Logical log buffer size (Kbytes)>LOGSMAX 50 # Maximum number of logical log files >CLEANERS 127 # Number of buffer cleaner processes >SHMBASE 0x0 # Shared memory base address >SHMVIRTSIZE 155456 # initial virtual shared memory segment>size >SHMADD 24576 # Size of new shared memory segments >(Kbyte) >SHMTOTAL 0 # Total shared memory (Kbytes). >0=>unlimited >CKPTINTVL 360 #300Check point interval (in sec) >LRUS 127 # Number of LRU queues >LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit >LRU_MIN_DIRTY 1 # 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)> ># 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 /dbms/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) >CDR_LOGDELTA 30 # % of log space allowed in queue memory >CDR_NUMCONNECT 16 # Expected connections per server >CDR_NIFRETRY 300 # Connection retry (seconds)

-- Tony Flaherty Snr. A/P, Informix DBA, HpUx Admin, Gimmi a broom! MFS Ltd. [SNIP] >># 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> >I don't think this actually helps anyone on HP, but I could be wrong. I was told by an engineer from Informix a day or two ago that the SHMBASE 0x0 results in unreadable shared memory dumps for HPUX users as all of the internal pointers are screwed up in the dump file. [SNIP]