Re: Single CPU Performance Problems?

From: "Gregory P. Schin" <gps@lucent.com> > >I need some help tuning a single processor Sun Microsystems Ultra 5 >system with 256 meg of memory running IDS 7.3 and Solaris 7. We have an >application written in Java that frequently loads large amounts of data >into the database. We can not seem to get reliably good performance >from this application with IDS on the Sun Platform. The application >also is being run on Windows NT Server 4.0 with SQL Server 7.0 and we >get good performance. As a comparison, loading the same data on an NT >box with 1 Pentium III 400 cpu and 256 meg of memory we can average >loading 10 rows per second into the database - on the Sun box with IDS >7.3 we can only average 3.5 rows per second. I think we should be able >to get the performance to at least equal the NT box. Any ideas or >suggestions? Plenty. :-) Apart from any tuning issues, why don't you use HPL or even DBLOAD to stick data in? That'll move the data much better. If you do make changes, remember to make one at a time and benchmark before making the next change. Also, are you running appropriate UPDATE STATISTICS commands? ># Physical Log Configuration > >PHYSDBS rootdbs # Location (dbspace) of physical log Move your physical log out of the rootdbs, prefereably onto a separate spindle. >BUFFERS 5000 # Maximum number of shared buffers This seems a bit low? >NUMAIOVPS 2 # Number of IO vps This also seems a bit low - are you using KAIO? >PHYSBUFF 32 # Physical log buffer size (Kbytes) >LOGBUFF 32 # Logical log buffer size (Kbytes) Please send the output from onstat -l >LOGSMAX 6 # Maximum number of logical log files Bump this up to at least 60 -- you never know when you're going to need more logs. :-) Also you might want to add more logs right now, and/or make them bigger. And put them in a different dbspace. :) >CLEANERS 8 # Number of buffer cleaner processes Can we get the output from onstat -d and onstat -D? >LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit >LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit For a load, you might want to try 80 and 60 rather than 60 and 50. ># Read Ahead Variables >RA_PAGES 32 # Number of pages to attempt to read ahead >RA_THRESHOLD 32 # Number of pages left before next group 32 and 30 or 32 and 28 would be better. ># OPTCOMPIND ># 0 => Nested loop joins will be preferred (where ># possible) over sortmerge joins and hash joins. ># 1 => If the transaction isolation mode is not ># "repeatable read", optimizer behaves as in (2) ># below. Otherwise it behaves as in (0) above. ># 2 => Use costs regardless of the transaction isolation ># mode. Nested loop joins are not necessarily ># preferred. Optimizer bases its decision purely ># on costs. >OPTCOMPIND 2 # To hint the optimizer Using 0 *might* help. ># Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS >OPT_GOAL -1 Similarly, using 0 *might* help here too. HTH. ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com