Re: Clock sweep not caching enough B-Tree leaf pages?

On Wed, Apr 16, 2014 at 3:22 AM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> It's possible that I've misunderstood what you mean here, but do you
> really think it's likely that everything will be hot, in the event of
> using something like what I've sketched here? I think it's an
> important measure against this general problem that buffers really
> earn the right to be considered hot, so to speak. With my prototype,
> in order for a buffer to become as hard to evict as possible, at a
> minimum it must be *continually* pinned for at least 30 seconds.
> That's actually a pretty tall order. Although, as I said, I wouldn't
> be surprised if it was worth making it possible for buffers to be even
> more difficult to evict than that. It should be extremely difficult to
> evict a root B-Tree page, and to a lesser extent inner pages even
> under a lot of cache pressure, for example. There are lots of
> workloads in which that can happen, and I have a hard time believing
> that it's worth it to evict given the extraordinary difference in
> their utility as compared to a lot of other things. I can imagine a
> huge barrier against evicting what is actually a relatively tiny
> number of pages being worth it.

I'm making a general statement about a property that I think a buffer
eviction algorithm ought to have. I actually didn't say anything
about the algorithm you've chosen one way or the other. Obviously,
you've built in some protections against everything becoming hot, and
that's a good thing as far as it goes. But you also have a greatly
increased risk of everything becoming cold. All you need is a rate of
buffer eviction that circles shared_buffers more often than once every
3 seconds, and everything will gradually cool down until you once
again can't distinguish which stuff is hot from which stuff isn't.

> I don't want to dismiss what you're saying about heating and cooling
> being unrelated, but I don't find the conclusion that not everything
> can be hot obvious. Maybe "heat" should be relative rather than
> absolute, and maybe that's actually what you meant. There is surely
> some workload where buffer access actually is perfectly uniform, and
> what do you do there? What "temperature" are those buffers?

Obviously, some value lower than the maximum and higher than the
minimum. If they're all at max temperature and then a new buffer (a
btree room or vm page, for example) comes along and is much hotter,
there's no room on the scale left to express that. If they're all at
min temperature and then a new buffer comes along that is just used
once and thrown out, there's no room left on the scale for that buffer
to emerge as a good candidate for eviction.

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