MIME-Version: 1.0
References: <CADX_1abjKJhk10vWz6S=cwgXsppGXw2VjNnLVByE3EMJzLzD4w@mail.gmail.com>
 <8b2525bcc0c714603cc4f44a686b8b0e4659ac92.camel@cybertec.at>
In-Reply-To: <8b2525bcc0c714603cc4f44a686b8b0e4659ac92.camel@cybertec.at>
From: Marc Millas <marc.millas@mokadb.com>
Date: Mon, 28 Apr 2025 13:05:59 +0200
Message-ID: <CADX_1ab8aRrnGffSwp9wWJdO=5A8qr9AVWt_NoZ2_c+RyybChw@mail.gmail.com>
Subject: Re: shared buffers
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e1cfef0633d4ab4a"
Archived-At: <https://www.postgresql.org/message-id/CADX_1ab8aRrnGffSwp9wWJdO%3D5A8qr9AVWt_NoZ2_c%2BRyybChw%40mail.gmail.com>
Precedence: bulk

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

I didnt know this.
thanks,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com


On Sat, Apr 26, 2025 at 12:46=E2=80=AFAM Laurenz Albe <laurenz.albe@cyberte=
c.at>
wrote:

> On Fri, 2025-04-25 at 15:42 +0200, Marc Millas wrote:
> > got something strange to me:
> > Same db ie. same data, around 1.2TB,one on pg13, one on pg16
> > same 16 GB of shared_buffers,
> > I am the single user.
> > both have track_io_timing on
> >
> > on pg13, if I run a big request with explain (analyze,buffers),
> > I see around 6 GB read
> > if I do rerun the very same request, no more read(s), all data in the
> shared buffers cache. fine
> > If I check with pg_buffercache what's in it, I see the biggest tables o=
f
> my request within
> > the biggest users (in number of blocks used). All this is fine.
> >
> > next, if I do the very same on the pg16 machine, whatever the number of
> times I rerun the
> > explain (analyze, buffers) of the same request, each time, the explain
> shows the same volume
> > of reads. again and again.
> > If I check with pg_buffercache, the set of objects stay the same,
> WITHOUT the objects of my
> > request, just like if those objects where sticky.
>
> I can't see the plans, so I can only guess.
>
> Perhaps the v16 plan uses a sequential scan on a table that is more than =
a
> quarter of
> shared_buffers in size, so that PostgreSQL uses a ring buffer to read it
> instead of
> blowing out more than a quarter of its buffer cache.
>
> Yours,
> Laurenz Albe
>

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

<div dir=3D"ltr"><div>I didnt=C2=A0know this.</div><div>thanks,</div><div><=
br></div><div><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"=
gmail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr">Marc MILLAS<div>Sen=
ior Architect</div><div>+33607850334</div><div><a href=3D"http://www.mokadb=
.com" target=3D"_blank">www.mokadb.com</a></div><div><br></div></div></div>=
</div></div></div><br></div><br><div class=3D"gmail_quote gmail_quote_conta=
iner"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, Apr 26, 2025 at 12:46=
=E2=80=AFAM Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">la=
urenz.albe@cybertec.at</a>&gt; wrote:<br></div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex">On Fri, 2025-04-25 at 15:42 +0200, Marc Millas wrote:=
<br>
&gt; got something strange to me:<br>
&gt; Same db ie. same data, around 1.2TB,one on pg13, one on pg16<br>
&gt; same 16 GB of shared_buffers,<br>
&gt; I am the single user.<br>
&gt; both have track_io_timing on<br>
&gt; <br>
&gt; on pg13, if I run a big request with explain (analyze,buffers),=C2=A0<=
br>
&gt; I see around 6 GB read<br>
&gt; if I do rerun the very same request, no more read(s), all data in the =
shared buffers cache. fine<br>
&gt; If I check with pg_buffercache what&#39;s in it, I see the biggest tab=
les of my request within<br>
&gt; the biggest users (in number of blocks used). All this is fine.<br>
&gt; <br>
&gt; next, if I do the very same on the pg16 machine, whatever the number o=
f times I rerun the<br>
&gt; explain=C2=A0(analyze, buffers) of the same request, each time, the ex=
plain shows the same volume<br>
&gt; of reads. again and again.<br>
&gt; If I check with pg_buffercache, the set of objects stay the same, WITH=
OUT the objects of my<br>
&gt; request, just like if those objects where sticky.<br>
<br>
I can&#39;t see the plans, so I can only guess.<br>
<br>
Perhaps the v16 plan uses a sequential scan on a table that is more than a =
quarter of<br>
shared_buffers in size, so that PostgreSQL uses a ring buffer to read it in=
stead of<br>
blowing out more than a quarter of its buffer cache.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--000000000000e1cfef0633d4ab4a--