Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile - Mailing list pgsql-hackers

From Merlin Moncure
Subject Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile
Date
Msg-id CAHyXU0zvh7amXfdNMgm8OwinHH6bOgMueczK17K9u7Qb5t+nKA@mail.gmail.com
Whole thread Raw
In response to Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile  (Robert Haas <robertmhaas@gmail.com>)
Responses Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile
List pgsql-hackers
On Thu, May 24, 2012 at 3:35 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, May 24, 2012 at 3:46 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> hm, looking at the code some more, it looks like the whole point of
>> the strategy system is to do that.  ISTM bulk insert type queries
>> would be good candidates for a buffer strategy somehow?
>
> Yep.
>
> commit 85e2cedf985bfecaf43a18ca17433070f439fb0e
> Author: Tom Lane <tgl@sss.pgh.pa.us>
> Date:   Thu Nov 6 20:51:15 2008 +0000
>
>    Improve bulk-insert performance by keeping the current target buffer pinned
>    (but not locked, as that would risk deadlocks).  Also, make it work in a sma
>    ring of buffers to avoid having bulk inserts trash the whole buffer arena.
>
>    Robert Haas, after an idea of Simon Riggs'.
>
> But that doesn't help here, since pgbench is only looking up one row per query.

Wait -- OP's gripe this isn't regarding standard pgbench, but multiple
large concurrent 'insert into foo select...'.  I looked in the code
and it appears that the only bulk insert strategy using operations are
copy, create table as select, and table rewrite operations. Concurrent
INSERT SELECT apparently doesn't get the benefit of a strategy and
should be fighting over the freelist once the pool exhausts.

We don't get to skip wal of course, but we should be able to use a
bulk insert strategy, especially if there was some way of predicting
that a large number of tuples were going to be inserted.  I'm
wondering though of contention on the free list is in fact the OP's
problem.

merlin


pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile
Next
From: Greg Sabino Mullane
Date:
Subject: Re: Backends stalled in 'startup' state: index corruption