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

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

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

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

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

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

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