Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> You can easily quantify if the BGW is aggressive enough. Buffers
> leave the cache three ways, and they each show up as separate
> counts in pg_stat_bgwriter: buffers_checkpoint, buffers_clean
> (the BGW), and buffers_backend (the queries). Cranking it up
> further tends to shift writes out of buffers_backend, which are
> the ones you want to avoid, toward buffers_clean instead. If
> buffers_backend is already low on a percentage basis compared to
> the other two, there's little benefit in trying to make the BGW do
> more.
Here are the values from our two largest and busiest systems (where
we found the pg_xlog placement to matter so much). It looks to me
like a more aggressive bgwriter would help, yes?
cir=> select * from pg_stat_bgwriter ;
-[ RECORD 1 ]------+------------
checkpoints_timed | 125996
checkpoints_req | 16932
buffers_checkpoint | 342972024
buffers_clean | 343634920
maxwritten_clean | 9928
buffers_backend | 575589056
buffers_alloc | 52397855471
cir=> select * from pg_stat_bgwriter ;
-[ RECORD 1 ]------+------------
checkpoints_timed | 125992
checkpoints_req | 16840
buffers_checkpoint | 260358442
buffers_clean | 474768152
maxwritten_clean | 9778
buffers_backend | 565837397
buffers_alloc | 71463873477
Current settings:
bgwriter_delay = '200ms'
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4
Any suggestions on how far to push it?
-Kevin