Re: Time to up bgwriter_lru_maxpages?

On 2017-02-03 18:12:48 -0600, Jim Nasby wrote:
> Interesting. Probably kills a couple birds with one stone:
>
> - This should be a lot cheaper for backends then the clock sweep

Right, that's one of the motivations - the current method is pretty much
guaranteed to create the worst cacheline bouncing possible.

> - The ringbuffers in shared buffers can be problematic. One possible way of
> solving that is to get rid of ringbuffers entirely and rely on different
> initial values for usage_count instead, but that's not desirable if it just
> means more clock sweep work for backends.

I'm not quite sure which ringbuffer you're referring to here? If to the
new one, why is it problematic?

> FWIW, I started looking into this stuff because of a customer system where
> shared buffers is currently ~4x larger than the cluster, yet there's a
> non-trivial amount of buffers being written by backends.

That's probably not related to bgwriter then. If there's free pages
available (which there have to be if s_b is bigger than the cluster)
there'll be no clock sweep / victim buffers. I strongly suspect that
you're seeing backend writes due to the write ringbuffers, e.g. by
seqscans, copy, vacuum - those won't be affected in either case.

I'd suggest installing pg_stat_statements and enabling track_io_timings
- that'll tell you which statements triggered backend writes and how
long they took.

Greetings,

Andres Freund