Re: Time to up bgwriter_lru_maxpages?

On 2/3/17 6:20 PM, Andres Freund wrote:
>> - 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?

No, I mean the non-default BufferAccessStrategy's.

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

Right.

> seqscans, copy, vacuum - those won't be affected in either case.

Yeah, my idea there is that if there was a separate process responsible 
for the clock sweep then perhaps instead of ring buffers we could simply 
use different initial values for usage_count to control how likely 
things were to get evicted. Trying to do that with all the backends 
needing to run the clock would presumably be a really bad idea though.

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

Good idea.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)