Re: Allow a per-tablespace effective_io_concurrency setting

On 2 Sep 2015 14:54, "Andres Freund" <andres(at)anarazel(dot)de> wrote:
>
>
> > + /*----------
> > + * The user-visible GUC parameter is the number of drives (spindles),
> > + * which we need to translate to a number-of-pages-to-prefetch target.
> > + * The target value is stashed in *extra and then assigned to the actual
> > + * variable by assign_effective_io_concurrency.
> > + *
> > + * The expected number of prefetch pages needed to keep N drives busy is:
> > + *
> > + * drives | I/O requests
> > + * -------+----------------
> > + * 1 | 1
> > + * 2 | 2/1 + 2/2 = 3
> > + * 3 | 3/1 + 3/2 + 3/3 = 5 1/2
> > + * 4 | 4/1 + 4/2 + 4/3 + 4/4 = 8 1/3
> > + * n | n * H(n)
>
> I know you just moved this code. But: I don't buy this formula. Like at
> all. Doesn't queuing and reordering entirely invalidate the logic here?

I can take the blame for this formula.

It's called the "Coupon Collector Problem". If you hit get a random
coupon from a set of n possible coupons, how many random coupons would
you have to collect before you expect to have at least one of each.

This computation model assumes we have no information about which
spindle each block will hit. That's basically true for the case of
bitmapheapscan for most cases because the idea of bitmapheapscan is to
be picking a sparse set of blocks and there's no reason the blocks
being read will have any regularity that causes them all to fall on
the same spindles. If in fact you're reading a fairly dense set then
bitmapheapscan probably is a waste of time and simply reading
sequentially would be exactly as fast or even faster.

We talked about this quite a bit back then and there was no dispute
that the aim is to provide GUCs that mean something meaningful to the
DBA who can actually measure them. They know how many spindles they
have. They do not know what the optimal prefetch depth is and the only
way to determine it would be to experiment with Postgres. Worse, I
think the above formula works for essentially random I/O but for more
predictable I/O it might be possible to use a different formula. But
if we made the GUC something low level like "how many blocks to
prefetch" then we're left in the dark about how to handle that
different access pattern.

I did speak to a dm developer and he suggested that the kernel could
help out with an API. He suggested something of the form "how many
blocks do I have to read before the end of the current device". I
wasn't sure exactly what we would do with something like that but it
would be better than just guessing how many I/O operations we need to
issue to keep all the spindles busy.