Re: massive FPI_FOR_HINT load after promote - Mailing list pgsql-hackers

From Masahiko Sawada
Subject Re: massive FPI_FOR_HINT load after promote
Date
Msg-id CA+fd4k5y+pLtS0vyCmOqXZfn_y_ZiUo6e3H5=fF51rtoNnMOrQ@mail.gmail.com
Whole thread Raw
In response to Re: massive FPI_FOR_HINT load after promote  (Alvaro Herrera <alvherre@2ndquadrant.com>)
List pgsql-hackers
On Wed, 12 Aug 2020 at 02:42, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> On 2020-Aug-11, Masahiko Sawada wrote:
>
> > On Tue, 11 Aug 2020 at 07:56, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> > > So if you have some table where tuples gain hint bits in bulk, and
> > > rarely modify the pages afterwards, and promote before those pages are
> > > frozen, then you may end up with a massive amount of pages that will
> > > need hinting after the promote, which can become troublesome.
> >
> > Did the case you observed not use hot standby? I thought the impact of
> > this issue could be somewhat alleviated in hot standby cases since
> > read queries on the hot standby can set hint bits.
>
> Oh, interesting, I didn't know that.  However, it's not 100% true: the
> standby can set the bit in shared buffers, but it does not mark the page
> dirty.  So when the page is evicted, those bits that were set are lost.
> That's not great.  See MarkBufferDirtyHint:

Yeah, you're right.

>
> > > One simple idea to try to forestall this problem would be to modify the
> > > algorithm so that all tuples are scanned and hinted if the page is going
> > > to be dirtied -- then send a single FPI setting bits for all tuples,
> > > instead of just on the first tuple.
> >
> > This idea seems good to me but I'm concerned a bit that the
> > probability of concurrent processes writing FPI for the same page
> > might get higher since concurrent processes could set hint bits at the
> > same time. If it's true, I wonder if we can advertise hint bits are
> > being updated to prevent concurrent FPI writes for the same page.
>
> Hmm, a very good point.  Sounds like we would need to obtain an
> exclusive lock on the page .. but that would be very problematic.
>

I think that when the page is going to be dirty only updating hint
bits on the page and writing FPI need to be performed exclusively. So
perhaps we can add a flag, say BM_UPDATE_HINTBITS, to buffer
descriptor indicating the hint bits are being updated.

Regards,

--
Masahiko Sawada            http://www.2ndQuadrant.com/


PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



pgsql-hackers by date:

Previous
From: Thomas Munro
Date:
Subject: Re: Collation versioning
Next
From: Masahiko Sawada
Date:
Subject: Re: recovering from "found xmin ... from before relfrozenxid ..."