Re: moving pg_xlog -- yeah, it's worth it!

Greg Smith <greg@2ndquadrant.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