Re: Novice needs tuning help!

In article <01bd8a68$3b5ca0c0$e4f92499@paul-lap>, Paul Kocsis <pkocsis@earthlink.net> writes >Hello, > >I've included a zip file of an onstat -a. Can anyone tell this informix >novice any obvious problems or changes I can make to improve performance >for my OLTP application? I'm running Informix Online 7.22 on a Sun SPARC >Ultra-2 with 128K RAM (Solaris 2.5.1). ^^^^ 128 K!! That's the same as the Sinclair 128K Spectrum! Get more RAM! OK. I'll assume 128Mb. Get more RAM. We normally quote at least 256Mb RAM. ROOTPATH /dev/rdsk/c0t2d0s7 # Path for device containing rootdbspace change to a link to the raw device!! If the disk fails then the new disk might have a new device name. You will not be able to restore unless you use a link which can be made to point to the new device!! CONSOLE /dev/console # System console message path If noramlly change it to .../online.cons so that a) you don't lose messages b) If some other process sends an error to the console you can read it before Informix scrolls it off the screen c) People can actually use the console!! LTAPEDEV /dev/null # Log tape device path No logging in your databases or at least you are not writing logs to tape??? RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) Try setting to 1 to avoid swapping/paging of Online shared memory.. 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 toone Is this a single cpu machine? How fast is the cpu? Also if NUMCPUVPS=1 set SINGLE_CPU_VP to 1. NOAGE 0 # Process aging Try setting this to 1 AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors Try setting these to 1 and 1 so it is pinned on the cpu. BUFFERS 3000 # Maximum number of shared buffers Try increasing this to 20% of physical memory. What else runs on this machine?? DBSPACETEMP rootdbs # Default temp dbspaces How many disks do you have? Try creating a temp dbspace (Temp=Y) and change DBSPACETEMP to point to the new temp dbspace. Userthreads address flags sessid user tty wait tout locks nreads nwrites a72b580 ---PR-- 1221 root - 0 0 1 5760533 208 Here is your problem. nread = 5,760,533!! What is this session doing? Run the sql from this session in dbaccess with SET EXPLAIN ON... Also check the other sessions with >100,000 reads.. Tblspaces n address flgs ucnt tblnum physaddr npages nused npdata nrows nextns 43 a74b9d0 0 1 1000a5 10158e 18584 17850 14033 238554 113 Run select tabname,hex(partnum) from systables for each database and find which table has hex(partnum) ending in "A5" Chunks address chk/dbs offset size free bpages flags pathname a71e170 1 1 0 100000 41911 PO- /dev/rdsk/c0t2d0s7 a71e280 2 1 0 100000 11 PO- /dev/rdsk/c0t3d0s7 a71e358 3 1 0 1000000 365997 PO- /dev/rdsk/c0t3d0s6 Again use links. Also consider spreading your data over more disks.. Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 290535044 291418966 917114446 68.32 4096894 6100606 52044156 92.13 You read cache percentage is low. But that is probably because some sessions are doing vast amounts of reads. Probably because an index is missing somewhere... >Thank you! > >P. Kocsis >pkocsis@earthlink.net > >[ A UUEncoded file (osa.ZIP) was included here. ] > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care