Re: Initial prefetch performance testing

On Mon, 22 Sep 2008, Simon Riggs wrote:

> I'd prefer to set this as a tablespace level storage parameter.

That seems reasonable, but I'm not working at that level yet.  There's 
still a larger open questions about how the buffer manager interaction 
will work here, and I'd like to have a better view of that first before 
getting into the exact syntax used to set the parameter.  For now, a GUC 
works well enough, but you're right that something finer-grained may make 
sense before this actually hits the codebase.

> prefetch_... is a much better name since its an existing industry term.
> I'm not in favour of introducing the concept of spindles, since I can
> almost hear the questions about ramdisks and memory-based storage.

It's possible to make a case for exposing the internal number that's 
getting varied here, naming the parameter something like prefetch_depth, 
and letting people set that to whatever they want.  Based on the current 
data I might suggest a default of 256, using 0 to turn the feature off 
altogether, and a maximum of at least 8192 and possibly more.

In practice I expect there to only be a couple of popular values and the 
idea of fine-tuning is a bit questionable.  I think that's what Greg Stark 
was driving at with how the value was re-spun.  Instead of using 
effective_spindle_count, you could just as easily make a case for an enum 
like [off,low,medium,high] mapping to [0,16,256,8192].  From what I've 
seen so far, that would reduce tweaking time in the field considerably 
while not really changing the range of available behavior very much.

I will be running a set of tests on a fast SSD device before I'm done, 
that's another one that I'll try once I've got the database-level tests 
ready to run, too.  What I expect is that it will favor 0, presumably you 
might as well just read the blocks rather than advise about them when the 
seek overhead is close to zero.  Should be able to do a RAM disk run as 
well.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD