Re: Slowness

From: Boscia <rwboscia@worldnet.att.net> > >Well I wasn't sure who to answer to I've answered all your questions below. >Hope this is not way too long and boring Thanks for all the help and >future help. So anyhow, here goes.... Great job. :-) >In answer to obnoxio@hotmail.com's questions.... I have some more: could you please send output from onstat -F onstat -R onstat -l (But not as a reply to this please, otherwise the message will exceed 100K!! :-) >******* OUTPUT from onstat -c *************** >Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 8 days >14:54:51 >-- 491520 Kbytes > >Configuration File: /home/informix/etc/onconfig.pinkfloyd Right. Interesting name for an instance? :-) ># Physical Log Configuration > >PHYSDBS logs # Location (dbspace) of physical log >PHYSFILE 10000 # Physical log file size (Kbytes) Seems a bit small. ># Logical Log Configuration > >LOGFILES 75 # Number of logical log files >LOGSIZE 10000 # Logical log size (Kbytes) > >TBLSPACE_STATS 1 If you don't need tblspace stats, set this to 0. It can improve performance. ># System Archive Tape Device > >TAPEDEV /home/informix/tape/tapelog # Tape device path >TAPEBLK 16 # Tape block size (Kbytes) >TAPESIZE 10240 # Maximum amount of data to put on That's a small tape? ># Log Archive Tape Device > >LTAPEDEV /home/informix/tape/ltapelog # Log tape device path >LTAPEBLK 16 # Log tape block size (Kbytes) >LTAPESIZE 10240 # Max amount of data to put on log That's a small tape? ># System Configuration > >SERVERNUM 1 # Unique id corresponding to a Dynamic>Server instance >DBSERVERNAME pinkfloyd # Name of default database server >DBSERVERALIASES floyd_net # List of alternate dbservernames >NETTYPE tlitcp,1,100,NET # Configure poll thread(s) for nettype >NETTYPE ipcshm,4,30,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 If this isn't an AIX box, set it to -1. What OS is it? >MULTIPROCESSOR 1 # 0 for single-processor, 1 for>multi-processor >NUMCPUVPS 4 # Number of user (cpu) vps How many physical CPUs do you have? >NOAGE 1 # Process aging >AFF_SPROC 0 # Affinity start processor >AFF_NPROCS 0 # Affinity number of processors You *may* get some performance improvement from tweaking these, but it's hard to say. ># Shared Memory Parameters > >LOCKS 150000 # Maximum number of locks I'd definitely configure at least 10x more LOCKS. >BUFFERS 200000 # Maximum number of shared buffers >NUMAIOVPS 4 # Number of IO vps >PHYSBUFF 64 # Physical log buffer size (Kbytes) >LOGBUFF 64 # Logical log buffer size (Kbytes)>LOGSMAX 100 # Maximum number of logical log files >CLEANERS 4 # Number of buffer cleaner processes >SHMBASE 0xa000000 # Shared memory base address >SHMVIRTSIZE 8192 # 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 4 # 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 128 # Stack size (Kbytes)> ># System Page Size ># BUFFSIZE - Dynamic Server no longer supports this configuration >parameter. ># To determine the page size used by Dynamic Server 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 /home/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 /home/informix/bar_act.log >BAR_MAX_BACKUP 3 >BAR_RETRY 1 >BAR_NB_XPORT_COUNT 10 >BAR_XFER_BUF_SIZE 31 >BAR_BSALIB_PATH /usr/lib/ibsad001.so> ># 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 32 # Number of pages to attempt to read ahead >RA_THRESHOLD 24 # Number of pages left before next group >OPTCOMPIND 2 # To hint the optimizer If this is an OLTP system, try setting this to 2. >********** OUTPUT FROM onstat-p ********** > >Informix Dynamic Server Version 7.31.UC5 -- On-Line -- Up 8 days >14:55:04 >-- 491520 Kbytes > >Profile >dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached >682295405 18023000 3083027087 77.87 71759590 15652589 1036581591 93.08 %Read Cache is *very* low. This indicates you may need more BUFFERS. >isamtot open start read write rewrite delete commit >rollbk >1492423941 18991866 243150836 3111959883 1137426567 4844731 2152933 80738 >0 > >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