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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD