On Fri, Jun 1, 2012 at 8:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> A potential issue with this line of thinking is that your pin delay
>> queue could get highly pressured by outer portions of the query (as in
>> the OP's case) that will get little or no benefit from the delayed
>> pin. But choosing a sufficiently sized drain queue would work for
>> most reasonable cases assuming 32 isn't enough? Why not something
>> much larger, for example the lesser of 1024, (NBuffers * .25) /
>> max_connections? In other words, for you to get much benefit, you
>> have to pin the buffer sufficiently more than 1/N times among all
>> buffers.
>
> Allowing each backend to pin a large fraction of shared buffers sounds
> like a seriously bad idea to me. That's just going to increase
> thrashing of what remains.
By 'large fraction', you mean 25%? You could always set it lower, say
5%. But if you can be smarter about which buffers to put in I agree:
a smaller queue is better.
> More generally, I don't believe that we have any way to know which
> buffers would be good candidates to keep pinned for a long time.
> Typically, we don't drop the pin in the first place if we know we're
> likely to touch that buffer again soon. btree root pages might be an
> exception, but I'm not even convinced of that one.
Why not (unless Florian's warming concept is a better bet) hook it to
spinlock contention? That's what we're trying to avoid after all.
s_lock can be modified to return if it had to delay. PinBuffer could
watch for that and stick it in the queue. Both Florin's idea (AIUI)
or a s_lock based implementation require you to search your local
queue on every pin/unpin, which i think is the real cost. Robert's
doesn't, although it is a more complicated approach.
merlin