public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bruce Momjian <[email protected]>
To: Andres Freund <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Greg Smith <[email protected]>
Subject: Re: Increase default maintenance_io_concurrency to 16
Date: Tue, 18 Mar 2025 17:19:41 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <4p7gtb2nfr3njhgq7bmpe24unsbyoerlom7zrcu5sl2vyyutlp@ol5ywrm7j5ok>
References: <[email protected]>
<[email protected]>
<fycuaxeisofdyv4265ejiluixuklmoaaoywa7nhz3heytf4na6@3vd6owgwvadc>
<[email protected]>
<rdd4m4fze6zt4fjmp2p2ez5smokglno7pqefloyiql36kkudw6@pwpmabadygqz>
<[email protected]>
<4p7gtb2nfr3njhgq7bmpe24unsbyoerlom7zrcu5sl2vyyutlp@ol5ywrm7j5ok>
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 <[email protected]>
> 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 <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Increase default maintenance_io_concurrency to 16
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox