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

* Robert Haas (robertmhaas@gmail.com) wrote:
> 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.

Ehhh.  No.  If it's a hot page that we've been holding in *our* cache
long enough, the kernel will happily evict it as 'cold' from *its*
cache, leading to...

> 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.

Exactly this.

> Of course, the overhead of repeated clock sweeps to push down the
> usage counts isn't a great thing either.  I'm not saying that isn't a
> problem.  But I think bad decisions about what to evict are also a
> problem.

Using a bit more CPU here and there, particularly if it's done in a
background worker, or ideally multiple background workers (for each
buffer pool) would be much better than evicting a hot page that isn't in
the kernel's buffer either 'cause we've held on to it long enough that
the kernel thinks it's cold.
Thanks,
    Stephen