Re: lru_multiplier and backend page write-outs

On Wed, 5 Nov 2008, Peter Schuller wrote:

> In addition, PostgreSQL is not even close to even filling it's buffer
> cache. The buffer cache is configured at 1 GB, and the resident size
> of the PostgreSQL process is only 80-90 MB so far. So even
> independently of any lru multplier setting, delays and whatever else,
> I don't see why any backend would ever have to do its own writeouts in
> order to allocate a page from the buffer cache.

Any buffer that you've accessed recently gets its recent usage count
incremented such that the background writer won't touch it--the current
one only writes things where that count is 0. The only mechanism which
drops that usage count back down again only kicks in once you've used all
the buffers in the cache. You need some pressure to evict buffers that
can't fit anymore before the background writer has any useful role to play
in PostgreSQL 8.3.

At one point I envisioned making it smart enough to try and handle the
scenario you describe--on an idle system, you may very well want to write
out dirty and recently accessed buffers if there's nothing else going on.
But such behavior is counter-productive on a busy system, which is why a
similar mechanism that existed before 8.3 was removed. Making that only
happen when idle requires a metric for what "busy" means, which is tricky
to do given the information available to this particular process.

Short version: if you never fill the buffer cache, buffers_clean will
always be zero, and you'll only see writes by checkpoints and things not
operating with the standard client buffer allocation mechanism. Which
brings us to...

> One theory: Is it the auto vacuum process? Stracing those I've seen
> that they very often to writes directly to disk.

In order to keep it from using up the whole cache with maintenance
overhead, vacuum allocates a 256K ring of buffers and use re-uses ones
from there whenever possible. That will generate buffer_backend writes
when that ring fills but it has more left to scan. Your theory that all
the backend writes are coming from vacuum seems consistant with what
you've described.

You might even want to drop the two background writer parameters you've
tweaked upwards back down closer to their original values. I get the
impression you might have increased those hoping for more background
writer work because you weren't seeing any. If you ever do get to where
your buffer cache is full and the background writer starts doing
something, those could jump from ineffective to wastefully heavy at that
point.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD