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

On Thu, Apr 17, 2014 at 10:18:43AM -0400, Robert Haas wrote:
> I also believe this to be the case on first principles and my own
> experiments. Suppose you have a workload that fits inside
> shared_buffers. All of the usage counts will converge to 5. Then,
> somebody accesses a table that is not cached, so something's got to be
> evicted. Because all the usage counts are the same, the eviction at
> this point is completely indiscriminate. We're just as likely to kick
> out a btree root page or a visibility map page as we are to kick out a
> random heap page, even though the former have probably been accessed
> several orders of magnitude more often. That's clearly bad. On
> systems that are not too heavily loaded it doesn't matter too much
> because we just fault the page right back in from the OS pagecache.
> But I've done pgbench runs where such decisions lead to long stalls,
> because the page has to be brought back in from disk, and there's a
> long I/O queue; or maybe just because the kernel thinks PostgreSQL is
> issuing too many I/O requests and makes some of them wait to cool
> things down.

I understand now. If there is no memory pressure, every buffer gets the
max usage count, and when a new buffer comes in, it isn't the max so it
is swiftly removed until the clock sweep has time to decrement the old
buffers. Decaying buffers when there is no memory pressure creates
additional overhead and gets into timing issues of when to decay.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +