On Sun, 2012-11-25 at 22:30 -0500, Tom Lane wrote:
> I'd be worried about the case of a lot of sessions touching a lot of
> unrelated tables. This change implies doubling the number of buffers
> that are held pinned by any given query, and the distributed overhead
> from that (eg, adding cycles to searches for free buffers) is what you
> ought to be afraid of.
That's a good point. "Doubling" might be an exaggeration if indexes are
involved, but it's still a concern. The cost of this might be difficult
to measure though.
> Another possibly important point is that reducing the number of
> pin/unpin cycles for a given VM page might actually hurt the chances of
> it being found in shared buffers, because IIRC the usage_count is bumped
> once per pin/unpin. That algorithm is based on the assumption that
> buffer pins are not drastically different in lifespan, but I think you
> just broke that for pins on VM pages.
If doing a bunch of simple key lookups using an index, then the root of
the index page is only pinned once per query, but we expect that to stay
in shared buffers. I see the VM page as about the same: one pin per
query (or maybe a couple for large tables).
I don't see how the lifetime of the pin matters a whole lot in this
case; it's more about the rate of pins/unpins, right?
> I'm not particularly concerned about devising solutions for these
> problems, though, because I think this idea is a loser from the get-go;
> the increase in contention for VM pages is alone going to destroy any
> possible benefit.
Your intuition here is better than mine, but I am still missing
something here. If we keep the buffer pinned, then there will be very
few pin/unpin cycles here, so I don't see where the contention would
come from (any more than there is contention pinning the root of an
index).
Do you still think I need a shared lock here or something? If so, then
index-only scans have a pretty big problem right now, too.
I'll try to quantify some of these effects you've mentioned, and see how
the numbers turn out. I'm worried that I'll need more than 4 cores to
show anything though, so perhaps someone with a many-core box would be
interested to test this out?
Regards,Jeff Davis
