Subject: Re: Extension Enhancement: Buffer Invalidation in pg_buffercache

From: jian he <[email-masked]>
Date: Mon, 3 Jul 2023 23:46:26 +0800

On Mon, Jul 3, 2023 at 4:26 PM Palak Chaturvedi
<[email-masked]> wrote:
>
> Hi Thomas,
> Thank you for your suggestions. I have added the sql in the meson
> build as well.
>
> On Sat, 1 Jul 2023 at 03:39, Thomas Munro <[email-masked]> wrote:
> >
> > On Fri, Jun 30, 2023 at 10:47 PM Palak Chaturvedi
> > <[email-masked]> wrote:
> > > pgbench=# select count(pg_buffercache_invalidate(bufferid)) from
> > > pg_buffercache where relfilenode =
> > > pg_relation_filenode('pgbench_accounts'::regclass);
> >
> > Hi Palak,
> >
> > Thanks for working on this!  I think this will be very useful for
> > testing existing workloads but also for testing future work on
> > prefetching with AIO (and DIO), work on putting SLRUs (or anything
> > else) into the buffer pool, nearby proposals for caching buffer
> > mapping information, etc etc.
> >
> > Palak and I talked about this idea a bit last week (stimulated by a
> > recent thread[1], but the topic has certainly come up before), and we
> > discussed some different ways one could specify which pages are
> > dropped.  For example, perhaps the pg_prewarm extension could have an
> > 'unwarm' option instead.  I personally thought the buffer ID-based
> > approach was quite good because it's extremely simple, while giving
> > the user the full power of SQL to say which buffers.   Half a table?
> > Visibility map?  Everything?  Root page of an index?  I think that's
> > probably better than something that requires more code and
> > complication but is less flexible in the end.  It feels like the right
> > level of rawness for something primarily of interest to hackers and
> > advanced users.  I don't think it matters that there is a window
> > between selecting a buffer ID and invalidating it, for the intended
> > use cases.  That's my vote, anyway, let's see if others have other
> > ideas...
> >
> > We also talked a bit about how one might control the kernel page cache
> > in more fine-grained ways for testing purposes, but it seems like the
> > pgfincore project has that covered with its pgfadvise_willneed() and
> > pgfadvise_dontneed().  IMHO that project could use more page-oriented
> > operations (instead of just counts and coarse grains operations) but
> > that's something that could be material for patches to send to the
> > extension maintainers.  This work, in contrast, is more tangled up
> > with bufmgr.c internals, so it feels like this feature belongs in a
> > core contrib module.
> >
> > Some initial thoughts on the patch:
> >
> > I wonder if we should include a simple exercise in
> > contrib/pg_buffercache/sql/pg_buffercache.sql.  One problem is that
> > it's not guaranteed to succeed in general.  It doesn't wait for pins
> > to go away, and it doesn't retry cleaning dirty buffers after one
> > attempt, it just returns false, which I think is probably the right
> > approach, but it makes the behaviour too non-deterministic for simple
> > tests.  Perhaps it's enough to include an exercise where we call it a
> > few times to hit a couple of cases, but not verify what effect it has.
> >
> > It should be restricted by role, but I wonder which role it should be.
> > Testing for superuser is now out of fashion.
> >
> > Where the Makefile mentions 1.4--1.5.sql, the meson.build file needs
> > to do the same.  That's because PostgreSQL is currently in transition
> > from autoconf/gmake to meson/ninja[2], so for now we have to maintain
> > both build systems.  That's why it fails to build in some CI tasks[3].
> > You can enable CI in your own GitHub account if you want to run test
> > builds on several operating systems, see [4] for info.
> >
> > [1] https://www.postgresql.org/message-id/flat/CAFSGpE3y_oMK1uHhcHxGxBxs%2BKrjMMdGrE%2B6HHOu0vttVET0UQ%40mail.gmail.com
> > [2] https://wiki.postgresql.org/wiki/Meson
> > [3] http://cfbot.cputube.org/palak-chaturvedi.html
> > [4] https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/tools/ci/README;hb=HEAD

newbie question:
quote from: https://www.interdb.jp/pg/pgsql08.html
>
> Pinned: When the corresponding buffer pool slot stores a page and any PostgreSQL processes are accessing the page (i.e. refcount and usage_count are greater than or equal to 1), the state of this buffer descriptor is pinned.
> Unpinned: When the corresponding buffer pool slot stores a page but no PostgreSQL processes are accessing the page (i.e. usage_count is greater than or equal to 1, but refcount is 0), the state of this buffer descriptor is unpinned.

So do you need to check BUF_STATE_GET_REFCOUNT(buf_state) and
BUF_STATE_GET_USAGECOUNT(state)?

---

From: Palak Chaturvedi <[email-masked]>
Date: Tue, 4 Jul 2023 11:38:04 +0530

hi,
I don't think we need to check the usage count. Because we are
clearing all the buffers that are not pinned.
Checking the usage count is for buffer replacement since we are not
replacing it does not matter.