Re: Changing the default random_page_cost value

On Thu, Oct 31, 2024 at 1:43 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> > I am not a fan of this patch. I don't see why _removing_ the
> magnetic
> > part helps because you then have no logic for any 1.2 was chosen.
> >
> > Okay, but we have no documented logic on why 4.0 was chosen either. :)
>
> Uh, we do, and it is in the docs:
>
> Random access to mechanical disk storage is normally much more
> expensive
> than four times sequential access. However, a lower default is
> used
> (4.0) because the majority of random accesses to disk, such as
> indexed
> reads, are assumed to be in cache. The default value can be
> thought of
> as modeling random access as 40 times slower than sequential, while
> expecting 90% of random reads to be cached.
>
> You surely saw this when you created the patch and removed the text.
>

Yes, I did, but there is no documentation to support those numbers. Is it
really 40 times slower? Which hard disks, in what year? Where did the 90%
come from, and is that really an accurate average for production systems
with multiple caching layers? I know Tom ran actual tests many years ago,
but at the end of the day, all of these numbers will vary wildly depending
on a host of factors, so we have to make some educated guesses.

I guess my point was that my 1.2 (based on many years of tuning many client
systems) seems no less imprecise than 4.0 (based on actual tests many years
ago, with hardware that has changed a lot), for a default value that people
should tune for their specific system anyway.

Maybe these new tests people are asking for in this thread to determine a
better default rpc for SSDs can also help us determine a better rpc for HDs
as well.

Cheers,
Greg