Re: Multiple FPI_FOR_HINT for the same block during killing btree index items

From: Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com>, Peter Geoghegan <pg(at)bowt(dot)ie>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Multiple FPI_FOR_HINT for the same block during killing btree index items
Date: 2020-05-16 19:32:46
Message-ID: CAEudQAq9h=LMcaGnZi8fKkBiXFe7rSAVLMK6VD88jFJgkb-P3w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Em sex., 15 de mai. de 2020 às 18:53, Alvaro Herrera <
alvherre(at)2ndquadrant(dot)com> escreveu:

> On 2020-Apr-10, Masahiko Sawada wrote:
>
> > Okay. I think only adding the check would also help with reducing the
> > likelihood. How about the changes for the current HEAD I've attached?
>
> Pushed this to all branches. (Branches 12 and older obviously needed an
> adjustment.) Thanks!
>
> > Related to this behavior on btree indexes, this can happen even on
> > heaps during searching heap tuples. To reduce the likelihood of that
> > more generally I wonder if we can acquire a lock on buffer descriptor
> > right before XLogSaveBufferForHint() and set a flag to the buffer
> > descriptor that indicates that we're about to log FPI for hint bit so
> > that concurrent process can be aware of that.
>
> I'm not sure how that helps; the other process would have to go back and
> redo their whole operation from scratch in order to find out whether
> there's still something alive that needs killing.
>
> I think you need to acquire the exclusive lock sooner: if, when scanning
> the page, you find a killable item, *then* upgrade the lock to exclusive
> and restart the scan. This means that we'll have to wait for any other
> process that's doing the scan, and they will all give up their share
> lock to wait for the exclusive lock they need. So the one that gets it
> first will do all the killing, log the page, then release the lock. At
> that point the other processes will wake up and see that items have been
> killed, so they will return having done nothing.
>
Regarding the block, I disagree in part, because in the worst case,
the block can be requested in the last item analyzed, leading to redo all
the work from the beginning.
If we are in _bt_killitems it is because there is a high probability that
there will be items to be deleted,
why not request the block soon, if this meets the conditions?

1. XLogHintBitIsNeeded ()
2.! AutoVacuumingActive ()
3. New exclusive configuration variable option to activate the lock?

Masahiko reported that it occurs only when (autovacuum_enabled = off);

regards,
Ranier Vilela

Attachment Content-Type Size
avoid_killing_btree_items_already_dead_v2.patch application/octet-stream 2.5 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Euler Taveira 2020-05-16 19:44:13 Re: [PATCH] Fix pg_dump --no-tablespaces for the custom format
Previous Message Tom Lane 2020-05-16 19:31:07 Re: [PATCH] Fix pg_dump --no-tablespaces for the custom format