Re: Puzzling performance issue?

Art S. Kagel wrote: > On Tue, 25 Nov 2003 11:14:40 -0500, Eric wrote: > > Eric, comments and suggestions below: > > <SNIP> > > > >># Read Ahead Variables >>RA_PAGES 4 # Number of pages to attempt to read ahead >>RA_THRESHOLD 2 # Number of pages left before next group> > > Good catch. I've always held that with fast disks and a caching controller the > RA_ parameters are no longer effective. I like 8 & 4 because 8 pages is a 'big' > read that the server will execute in a single operation, but these #s seem to be > working for you. > I suggest that you try changing the ratios to 4 to 1 or 3 to 1. i.e. 16 / 4 or 32 / 8 or 64 / 16 or 128 / 16 - real life tests on these values are real interesting ;) High values (i.e. over 256) give high CPU usage as well as potentially wasting BUFFERS and I/O resource if the sequential scans just stop after the RA_THRESHOLD. > >>DBSPACETEMP temp_dbs # Default temp dbspaces> > > Most sequential scans result in a sort to satisfy an ORDER BY or GROUP BY clause > so if you have three or more temp dbspaces listed above (or if you set the > PSORT_NPROCS=12 (or more) and PSORT_DBTEMP=<list of 3-6 filesystems> in the > environment) the improved sort speed can dramatically improve user performance > perceptions. Also it's a good idea to list at least one 'normal' (not temp) > dbspace in DBSPACETEMP so that logged temp tables have a place to go instead of > the ROOTDB space which is critical to performance. List a low load dbspace, > preferably on a different disk structure than the ROOTDB, logical & physical > logs and other high activity chunks, that is NOT a temp space for this. > > Not sure about that. a. Create your database somewhere other than the rootdbspace, and logged temp tables will get created in that dbspace. b. You don't want to mix logged and non-logged dbspaces in DBSPACETEMP - wierd recovery requirements ;) c. Always have loads of (real) temp dbspaces i.e. 3 or more.