On Fri, Jun 1, 2012 at 2:54 PM, Florian Pflug <fgp@phlo.org> wrote:
> On Jun1, 2012, at 19:51 , Robert Haas wrote:
>> On Fri, Jun 1, 2012 at 8:47 AM, Florian Pflug <fgp@phlo.org> wrote:
>>> A simpler idea would be to collapse UnpinBuffer() / PinBuffer() pairs
>>> by queing UnpinBuffer() requests for a while before actually updating
>>> shared state.
>>
>> So, what happens when somebody wants a cleanup lock on the buffer
>> you've decided to keep pinned? We have this problem already; I'm wary
>> of making it worse.
>
> She has to wait, or skip the cleanup. We already keep buffers pinned for
> potentially a long time - if cursors enter the game even over multiple
> commands, I think. And even without cursors, a nestloop plan can keep
> buffers used by the outer sub-plan pinned while the inner plan is
> executing. So giving the inner plan the chance to do the same seems not
> unreasonable.
Maybe. TBH, I'm unhappy about how easy it is to leave pins floating
around and would like to reduce that further, not increase it.
> Plus, the more refined version of that idea from my reply to Tom only
> delays the unpinning after the buffer was pinned twice within a short
> period of time. After that, it seems to me that the probability of it
> getting pinned again is high enough to warrant slightly penalizing a
> potential cleanup.
>
>>> 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.
>
> Well, we could drain the queue only if the read() from the socket blocks.
I believe that it's going to block frequently on this type of
workload. pgbench isn't fast enough to get the next query over there
by the time postgres is ready for the next query.
> But does pgbench -S really manage to cause significant spinlock contention
> due to buffer pinning/unpinning? I'd expect other things to dominate there..
See previous note. It may not be the biggest effect, but I think it's
in the mix.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company