Re: Increase default maintenance_io_concurrency to 16

On Tue, Mar 18, 2025 at 05:04:46PM -0400, Andres Freund wrote:
> Hi,
>
> On 2025-03-18 16:35:29 -0400, Bruce Momjian wrote:
> > Uh, the random_page_cost = 4 assumes caching, so it is assuming actual
> > random I/O to be 40x slower, which I doubt is true for SSDs:
>
> Uh, huh:
>
> > https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS
> >
> > 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.
>
> Is that actually a good description of what we assume? I don't know where that
> 90% is coming from? Briefly skimming through selfuncs.c and costsize.c I don't
> see anything.

The next paragraph is:

If you believe a 90% cache rate is an incorrect assumption
for your workload, you can increase random_page_cost to better
reflect the true cost of random storage reads. Correspondingly,
if your data is likely to be completely in cache, such as when
the database is smaller than the total server memory, decreasing
random_page_cost can be appropriate. Storage that has a low random
read cost relative to sequential, e.g., solid-state drives, might
also be better modeled with a lower value for random_page_cost,
e.g., 1.1.

> The relevant change:
>
> commit c1d9df4fa227781b31be44a5a3024865a7f48049
> Author: Bruce Momjian <bruce(at)momjian(dot)us>
> Date: 2012-02-14 16:54:54 -0500
>
> Document random page cost is only 4x seqeuntial, and not 40x.
>
> The relevant discussion seems to be:
> https://postgr.es/m/4F31A05A.1060506%402ndQuadrant.com
>
> But I don't see any origin of that number in that thread.
>
> I am not sure if I found the correct email for Greg Smith?

Yes, I can't say there is much research behind the value, and even if
there was, the assumed hardware is unlikely to be relevant today.
8
--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.