Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile

On Fri, Jun 1, 2012 at 2:06 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Fri, Jun 1, 2012 at 10:51 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Fri, Jun 1, 2012 at 8:47 AM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
>>
>>> We'd drain the unpin queue whenever we don't expect a PinBuffer() request
>>> to happen for a while. Returning to the main loop is an obvious such place,
>>> but there might be others.
>>
>> However, on a workload like pgbench -S, dropping the pin when you
>> return to the main loop would render the optimization useless.
>
> But do we need the optimization on a workload like pgbench -S?  You
> did some pretty massive scalability tests on that front, and I don't
> think this problem was identified in them.

I don't know if I mentioned it on list, but I believe there is an
effect, and I believe it will get stronger as we continue to make
improvements in other areas, and as we continue to ramp up the number
of cores. Even now, you can get s_lock taking >1% of the CPU time,
which is not trivial, and not all of that is from buffer-pinning, but
it's in the mix, so I'm kind of reluctant to accept a solution that
involves assuming that the problem always has to occur within a
transaction rather than across transactions. I think it's a dead-end
anyway on other grounds: we ought to be trying to find ways to hold
buffer pins for less time, not more time.

Another thought is that if the problem is limited to the root index
block, then we could consider less general solutions, like allowing
backends to cache the root index block and sending some kind of
invalidation when it gets split. But I don't know that the root index
block is the only place where we can have this problem, so I'm a
little nervous about that angle, too. Still, it might be worth some
research.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company