public inbox for [email protected]
help / color / mirror / Atom feedshared buffers
2+ messages / 2 participants
[nested] [flat]
* shared buffers
@ 2025-04-25 13:42 Marc Millas <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Marc Millas @ 2025-04-25 13:42 UTC (permalink / raw)
To: [email protected] <[email protected]>
hello,
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 of 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.
any idea ?
thanks
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: shared buffers
@ 2025-04-25 22:46 Laurenz Albe <[email protected]>
parent: Marc Millas <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2025-04-25 22:46 UTC (permalink / raw)
To: Marc Millas <[email protected]>; [email protected] <[email protected]>
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 of 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-04-25 22:46 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-25 13:42 shared buffers Marc Millas <[email protected]>
2025-04-25 22:46 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox