From: cs_mj@gsbacd.uchicago.edu (Mark Jaeger)
Subject: Re: alternate sort areas
Organization: 
Date: Tue, 15 Sep 1992 19:59:21 GMT

In article <1370@niktow.canisius.edu>, pavlov@fstrf.org (Greg Pavlov) writes:
> We are running 6.4/01 on DEC RISC under ULTRIX.
> 
> Per the 6.4 release notes and DBA manual, we created alternate sort areas.
> This improved performance some and all worked well for a while.  Then prob-
> lems cropped up: periodically, INGRES failed to find or could not create
> a temporary file (it seemed to encounter problems creating/deleting files
> with "unique" names).
> 
> After several go-arounds with tech support, we were finally told that temp-
> orary sort areas are "not supported" and that they couldn't help us.  So
> the areas were decommisioned.  Has anyone else encountered this ?  

What sort of problems did you encounter?  Could you include sample code,
sample error messages, and an extract from errlog.log?  

If Tech Support told me that a documented feature were not supported, I
would ask for very explicit information about just what will and won't
work, and I would ask them to help me figure out what might be wrong
with my configuration.  If it's not a genuine product bug, then, at the
very least, they should enter a documentation bug.

I know that there has been some confusion about alternate sort areas,
and it may be due to inadequate documentation about the new feature.  My
understanding is that alternate sort areas work for SQL statements such
as "modify" and "create index", but not for sorting the temporary tables
that the server sometimes creates as intermediate steps in executing
complicated queries.

> Also, what information do people have re reallocation of default area - based
> functions to alternate locations ?  At present, our largest database is 
> distributed across 5 areas but the default is a perennial bottleneck: INGRES
> uses it for umpteen functions and we can't count on our users to consistently
> route THEIR private/temporary tables to other areas.  Will we ever see the
> ability to specify an alternate "default" area for temporary/private tables?
> Is it that difficult to implement this ???

I can't tell if you're already doing this or not, but it might help if
you don't use the INGRES default data location.  Put the database on its
own location.  Before you create your database, first create a new
location, and then use the "-d" switch with the "createdb" command to
tell INGRES to make that the default location for the database.  You're
still restricted to having all of the system catalogs for that database
on that location, but at least you can move all of your other tables
off.  All user-created and temporary tables will be created on the
primary location that you specified.

When we create databases, we create new locations for all parts of the
database (data, checkpoint, journal, and dump), even if those locations
map to the same _area_ as the default INGRES locations.  That way, we
can change them later without rebuilding the database (unload/reload) or
affecting other databases.  We _never_ create a database without the
"-d" switch (except the iidbdb, and for testing).

--Mark Jaeger				internet: cs_mj@gsbvax.uchicago.edu
Graduate School of Business		yellnet:  (312) 702-0328
University of Chicago			faxnet:   (312) 702-0233
Disclaimer: My opinions are my own and not those of my employer.
Ich bin ein Virus.  Mach' mit und kopiere mich in Deine .signature.