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

2020-05-16 Thread Ranier Vilela
Em sex., 15 de mai. de 2020 às 18:53, Alvaro Herrera < alvhe...@2ndquadrant.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

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

2020-05-16 Thread Ranier Vilela
Em sex., 15 de mai. de 2020 às 18:53, Alvaro Herrera < alvhe...@2ndquadrant.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

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

2020-05-15 Thread Alvaro Herrera
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

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

2020-04-10 Thread James Coleman
On Thu, Apr 9, 2020 at 10:08 PM Peter Geoghegan wrote: > > On Thu, Apr 9, 2020 at 6:47 PM James Coleman wrote: > > I believe the write pattern to this table likely looks like: > > - INSERT > > - UPDATE > > - DELETE > > for every row. But tomorrow I can do some more digging if needed. > > The pg_s

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

2020-04-09 Thread Kyotaro Horiguchi
At Fri, 10 Apr 2020 12:32:31 +0900, Masahiko Sawada wrote in > On Fri, 10 Apr 2020 at 04:05, Peter Geoghegan wrote: > > > > On Wed, Apr 8, 2020 at 10:56 PM Masahiko Sawada > > wrote: > > > Here is the reproducer: > > > > What version of Postgres did you notice the actual customer issue on? > >

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

2020-04-09 Thread Masahiko Sawada
On Fri, 10 Apr 2020 at 04:05, Peter Geoghegan wrote: > > On Wed, Apr 8, 2020 at 10:56 PM Masahiko Sawada > wrote: > > Here is the reproducer: > > What version of Postgres did you notice the actual customer issue on? > I ask because I wonder if the work on B-Tree indexes in Postgres 12 > affects t

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

2020-04-09 Thread Peter Geoghegan
On Thu, Apr 9, 2020 at 6:47 PM James Coleman wrote: > I believe the write pattern to this table likely looks like: > - INSERT > - UPDATE > - DELETE > for every row. But tomorrow I can do some more digging if needed. The pg_stats.null_frac for the column/index might be interesting here. I believe

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

2020-04-09 Thread James Coleman
On Thu, Apr 9, 2020 at 8:32 PM Peter Geoghegan wrote: > > On Thu, Apr 9, 2020 at 5:25 PM Peter Geoghegan wrote: > > Was this a low cardinality index in the way I describe? If it was, > > then we can hope (and maybe even verify) that the Postgres 12 work > > noticeably ameliorates the problem. > >

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

2020-04-09 Thread Peter Geoghegan
On Thu, Apr 9, 2020 at 5:25 PM Peter Geoghegan wrote: > Was this a low cardinality index in the way I describe? If it was, > then we can hope (and maybe even verify) that the Postgres 12 work > noticeably ameliorates the problem. What I really meant was an index where hundreds or even thousands o

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

2020-04-09 Thread Peter Geoghegan
On Thu, Apr 9, 2020 at 1:37 PM James Coleman wrote: > We saw the issue on our PG11 clusters. The specific index we noticed > in the wal dump (I don't think we confirmed if there were others) as > one on a `created_at` column, to give you an idea of cardinality. You tend to get a lot of problems w

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

2020-04-09 Thread James Coleman
On Thu, Apr 9, 2020 at 3:05 PM Peter Geoghegan wrote: > > On Wed, Apr 8, 2020 at 10:56 PM Masahiko Sawada > wrote: > > Here is the reproducer: > > What version of Postgres did you notice the actual customer issue on? > I ask because I wonder if the work on B-Tree indexes in Postgres 12 > affects

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

2020-04-09 Thread Peter Geoghegan
On Wed, Apr 8, 2020 at 10:56 PM Masahiko Sawada wrote: > Here is the reproducer: What version of Postgres did you notice the actual customer issue on? I ask because I wonder if the work on B-Tree indexes in Postgres 12 affects the precise behavior you get here with real world workloads. It probab

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

2020-04-09 Thread Alvaro Herrera
On 2020-Apr-09, Masahiko Sawada wrote: > The inner test in the comment "found the item" never tests the item > for being dead. So maybe we can add !ItemIdIsDead(iid) to that > condition. But there still is a race condition of recording multiple > FPIs can happen. Maybe a better solution is to chan

Multiple FPI_FOR_HINT for the same block during killing btree index items

2020-04-08 Thread Masahiko Sawada
Hi all, During investigating the issue our customer had, I realized that _bt_killitems() can record the same FPI pages multiple times simultaneously. This can happen when several concurrent index scans are processing pages that contain killable tuples. Because killing index items could be performe