MIME-Version: 1.0
References: <E1tuWgZ-0035xZ-0x@gemulon.postgresql.org>
 <Z9nStg5TJssYfg_b@momjian.us>
 <fycuaxeisofdyv4265ejiluixuklmoaaoywa7nhz3heytf4na6@3vd6owgwvadc>
 <Z9nWFVBx3OJSwqg6@momjian.us>
 <rdd4m4fze6zt4fjmp2p2ez5smokglno7pqefloyiql36kkudw6@pwpmabadygqz>
 <Z9nZET_03i2CQ1tP@momjian.us>
 <4p7gtb2nfr3njhgq7bmpe24unsbyoerlom7zrcu5sl2vyyutlp@ol5ywrm7j5ok>
In-Reply-To: <4p7gtb2nfr3njhgq7bmpe24unsbyoerlom7zrcu5sl2vyyutlp@ol5ywrm7j5ok>
From: Gregory Smith <gregsmithpgsql@gmail.com>
Date: Tue, 18 Mar 2025 19:52:04 -0400
Message-ID: 
 <CAHLJuCVJkfNozHikJYGUe+xHnPGL-+YB7RWsuC0c_XR0ytrnQg@mail.gmail.com>
Subject: Re: Increase default maintenance_io_concurrency to 16
To: Andres Freund <andres@anarazel.de>
Cc: Bruce Momjian <bruce@momjian.us>,
 Melanie Plageman <melanieplageman@gmail.com>,
	PostgreSQL-development <pgsql-hackers@lists.postgresql.org>,
	Greg Smith <greg.smith@crunchydata.com>
Content-Type: multipart/alternative; boundary="0000000000003a80bc0630a698a4"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHLJuCVJkfNozHikJYGUe%2BxHnPGL-%2BYB7RWsuC0c_XR0ytrnQg%40mail.gmail.com>
Precedence: bulk

--0000000000003a80bc0630a698a4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Mar 18, 2025 at 5:04=E2=80=AFPM Andres Freund <andres@anarazel.de> =
wrote:

> Is that actually a good description of what we assume? I don't know where
> that
> 90% is coming from?


That one's all my fault.  It was an attempt to curve-fit backwards why the
4.0 number Tom set with his initial commit worked as well as it did given
that underlying storage was closer to 50X as slow, and I sold the idea well
enough for Bruce to follow the reasoning and commit it.  Back then there
was a regular procession of people who measured the actual rate and
wondered why there was the order of magnitude difference between those
measurements and the parameter.  Pointing them toward thinking in terms of
the cached read percentage too did a reasonable job of deflecting them onto
why the model was more complicated than it seems.  I intended to follow
that up with more measurements, only to lose the whole project into a
non-disclosure void I have only recently escaped

I agree with your observation that the underlying cost of a non-sequential
read stall on cloud storage is not markedly better than the original
random: sequential ratio of mechanical drives.   And the PG17 refactoring
to improve I/O chunking worked to magnify that further.

The end of this problem I'm working on again is assembling some useful mix
of workloads such that I can try changing one of these magic constants with
higher confidence. My main working set so far is write performance
regression test sets against the Open Street Map loading workload, that
I've been blogging about, plus the old read-only queries of the SELECT-only
spaced along a scale/client grid.  My experiments so far have been around
another Tom special, the maximum buffer usage count limit,  which turned
into another black hole full of work I have only recently escaped.  I
haven't really thought much yet about a workload set that would allow
adjusting random_page_cost.  On the query side we've been pretty heads down
on the TPC-H and Clickbench sets.  I don't have buffer internals data from
those yet though, will have to add that to the work queue.

--
Greg Smith
Director of Open Source Strategy, Crunchy Data
greg.smith@crunchydata.com

--0000000000003a80bc0630a698a4
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div dir=3D"ltr">On Tue, Mar 18, 2025 at 5:04=E2=80=
=AFPM Andres Freund &lt;<a href=3D"mailto:andres@anarazel.de">andres@anaraz=
el.de</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container">=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex">Is that actually a good d=
escription of what we assume? I don&#39;t know where that<br>
90% is coming from?</blockquote><div><br></div>That one&#39;s all my fault.=
=C2=A0 It was an attempt to curve-fit backwards why the 4.0 number Tom set =
with his initial commit worked as well as it did given that underlying stor=
age was closer to 50X as slow, and I sold the idea well enough for Bruce to=
 follow the reasoning and commit it.=C2=A0 Back then there was a regular pr=
ocession of people who measured the actual rate and wondered why there was =
the order of magnitude difference between those measurements and the parame=
ter.=C2=A0 Pointing them toward thinking in terms of the cached read percen=
tage too did a reasonable job of deflecting them onto why the model was mor=
e complicated than it seems.=C2=A0 I intended to follow that up with more m=
easurements, only to lose the whole project into a non-disclosure void I ha=
ve only recently escaped</div><div class=3D"gmail_quote gmail_quote_contain=
er"><br></div><div class=3D"gmail_quote gmail_quote_container">I agree with=
 your observation that the underlying cost of a non-sequential read stall o=
n cloud storage is not markedly better than the original random: sequential=
 ratio of mechanical drives.=C2=A0=C2=A0 And the PG17 refactoring to improv=
e I/O chunking worked to magnify that further.</div><div class=3D"gmail_quo=
te gmail_quote_container"><br></div><div class=3D"gmail_quote gmail_quote_c=
ontainer">The end of this problem I&#39;m working on again is assembling so=
me useful mix of workloads such that I can try changing one of these magic =
constants with higher confidence. My main working set so far is write perfo=
rmance regression test sets against the Open Street Map loading workload, t=
hat I&#39;ve been blogging about, plus the old read-only queries of the SEL=
ECT-only spaced along a scale/client grid.=C2=A0 My experiments so far have=
 been around another Tom special, the maximum buffer usage count limit,=C2=
=A0 which turned into another black hole full of work I have only recently =
escaped.=C2=A0 I haven&#39;t really thought much yet about a workload set t=
hat would allow adjusting random_page_cost.=C2=A0 On the query side we&#39;=
ve been pretty heads down on the TPC-H and Clickbench sets.=C2=A0 I don&#39=
;t have buffer internals data from those yet though, will have to add that =
to the work queue.</div><div class=3D"gmail_quote gmail_quote_container"><b=
r></div><div class=3D"gmail_quote gmail_quote_container">--</div><div class=
=3D"gmail_quote gmail_quote_container">Greg Smith</div><div class=3D"gmail_=
quote gmail_quote_container">Director of Open Source Strategy, Crunchy Data=
</div><div class=3D"gmail_quote gmail_quote_container"><div class=3D"gmail_=
quote gmail_quote_container"><a href=3D"mailto:greg.smith@crunchydata.com">=
greg.smith@crunchydata.com</a></div></div></div></div>

--0000000000003a80bc0630a698a4--