Re: OnLine 7.13 performance problems - comments please?

In article <332A5CDF.2452@netcomuk.co.uk>, Neil Truby <ntruby@netcomuk.co.uk> writes >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 > Caching looks ok...I notcie the system is doing virtually all reads.. Have you checked indexing on the database? What does onstat -u say? Do some sessions have a lot more reads then others? If you repeat every 5 seconds do some sessions suddenly do thousands of reads per second if so they are sequential scanning a table and an index is missing somewhere...do onstat -g sql on the session a few times to see if to a query is running. IF so paste the query into dbaccess and put set explain on; above it. Then look at the sqexplain.out file generated and see if sequential scans are being done. Next run vmstat 3 3 and see if paging/swapping is occuring. If so possible read you BUFFERS until paging/swapping is miminal. If you want more info, then send me the output of onstat -g ath onstat -u onstat -g glo onstat -g sch onstat -g spi onstat -F onstat -D onstat -R >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 -- David Williams