Re: OnLine 7.13 performance problems - comments please?

Hi, Your output shows quite a few waits in several areas. One thing you can try if you can spare the resources is to change your lock level on database tables to row level locking (if not set there already). This will ease contention for the tables. Your read cache is about as good as it is going to get (over 99%) so BUFFERS doen't need to be increased. The only other thing I see worth noting is the issue of multiprocessing. You have the system set for multiproc yet you only have one CPU. How many processors does this machine have? Can you spare any? If not turn off multiprocessing within the engine - this will ease some contention/ overhead that the engine is going through. Let me know if anything helps, Bill } } We're running OnLine 7.13 under Solaris 2.4, on a Sparc 20 with 160M of } memory. The load is increasing, and the users are complaining about } performance. } } Here's my onstat -p in the 12 hours since I zeroed the stats: } } Profile } dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached } 1581705 2954933 454881229 99.65 105177 221459 827392 87.29 } } isamtot open start read write rewrite delete commit } rollbk } 301750703 34728791 54605347 158736271 67734 66525 88369 } 22019 712 } } ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes } 0 0 0 45736.59 1212.92 73 146 } } bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans } 166771 2568 872540367 0 0 137 6094 2058386 } } ixda-RA idx-RA da-RA RA-pgsused lchwaits } 806840 188 397745 1204459 1016 } } ..... and here are selected highlights of my onconfig file. My } explanation to the users is that the machine, which consistently shows } 0% idle CPU, is simply overwhelmed by the application (which is } bought-in, so we can't tune it). Any other comments, suggestions or } opinions would be gratefully received. } } Thanks } Neil } } ROOTNAME rootdbs # Root dbspace name } ROOTPATH /dev/rdsk/informix_1 } ROOTOFFSET 0 # Offset of root dbspace into device } (Kbytes) } ROOTSIZE 15000 # Size of root dbspace (Kbytes) } } MIRROR 0 # Mirroring flag (Yes = 1, No = 0) } MIRRORPATH # Path for device containing mirrored } root } MIRROROFFSET 0 # Offset into mirrored device (Kbytes) } } PHYSDBS logdbs # Location (dbspace) of physical log } PHYSFILE 14750 # Physical log file size (Kbytes) } } LOGFILES 10 # Number of logical log files } LOGSIZE 5950 # Logical log size (Kbytes) } } SERVERNUM 2 # Unique id corresponding to a OnLine } instance } DBSERVERNAME trafic2_online # Name of default database server } DBSERVERALIASES trafic2_shm # List of alternate dbservernames } DEADLOCK_TIMEOUT 60 # Max time to wait of lock in } distributed env. } RESIDENT 1 # Forced residency flag (Yes = 1, No = } 0) } } MULTIPROCESSOR 1 # 0 for single-processor, 1 for } multi-processor } NUMCPUVPS 1 # Number of user (cpu) vps } SINGLE_CPU_VP 1 # 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 } } LOCKS 20000 # Maximum number of locks } BUFFERS 20000 # Maximum number of shared buffers } NUMAIOVPS 1 # Number of IO vps } PHYSBUFF 32 # Physical log buffer size (Kbytes) } LOGBUFF 32 # Logical log buffer size (Kbytes) } LOGSMAX 60 # Maximum number of logical log files } CLEANERS 8 # Number of buffer cleaner processes } SHMBASE 0xa000000 # Shared memory base address } SHMVIRTSIZE 24756 # initial virtual shared memory segment } size } SHMADD 8192 # Size of new shared memory segments } (Kbytes) } SHMTOTAL 0 # Total shared memory (Kbytes). } 0=>unlimited } CKPTINTVL 900 # Check point interval (in sec) } LRUS 4 # Number of LRU queues } LRU_MAX_DIRTY 20 # LRU percent dirty begin cleaning limit } LRU_MIN_DIRTY 10 # 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) } } RA_PAGES # Number of pages to attempt to read } ahead } RA_THRESHOLD # Number of pages left before next group } } DBSPACETEMP tempdbs # Default temp dbspaces } } FILLFACTOR 90 # Fill factor for building indexes } } PDQPRIORITY 0 # Degree of parallelism: 0 ... 100, } MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority } DS_MAX_QUERIES # Maximum number of decision support } queries } DS_TOTAL_MEMORY # Decision support memory (Kbytes) } DS_MAX_SCANS 1048576 # Maximum number of decision support } scans } DATASKIP off # List of dbspaces to skip } } OPTCOMPIND 0 # To hint the optimizer } -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com