Re: tuning bgwriter in 8.4.2

On Feb 17, 2010, at 3:41 PM, Greg Smith wrote:

> Ben Chobot wrote:
>> As I understand things, assuming I don't keep updating the same pages then buffers_backend should be a small
percentageof buffers_alloc, and buffers_clean should be larger than it is compared to buffers_checkpoint. Is my
understandingcorrect? 
>
> Sure; your buffers_clean is really low relative to the totals.  You should take a snapshot now that you've fixed
bgwriter_lru_maxpages,with a timestamp, and then another sometime later to get really useful numbers.  A diff only
consideringthe current setup and with a time interval to go along with it is much more useful than the aggregate
numbershere (patch to make that easier already in 9.0:
http://blog.2ndquadrant.com/en/2010/01/measuring-postgresql-checkpoin.html)  Keep taking regular snapshots with a
timestamp:
>
> select current_timestamp,* from pg_stat_bgwriter;
>
> So you can compute a diff to measure what's changing as you go.
>
> The only explanation I can offer is that your workload might be really bursty.  The method used for estimating how
muchthe cleaner should do is most likely to break down when the load comes in narrow spikes.  The main way to improve
responsein that situation is by decreasing the interval, so it kicks in and does the "what's happened during the last
<n>ms?" computations more often.  Right now, a burst that lasts less than 200ms can be completely missed, if the system
wasmostly idle before that. 
>
> You can try lowering bgwriter_delay and proportionally decreasing bgwriter_lru_maxpages to make response time to
burstworkloads better.  In your situation, I'd try make the writer wake up 4X as often, only do 1/4 as much maximum
workas it currently does each time, and doubling the multiplier too; see if things move in the right direction, and
maybekeep going from there afterwards. 

Thanks for the suggestions Greg. I'll monitor it closely over the next few days, but it doesn't really seem to have
changedmuch so far. Is there a way to tell if I really am just keeping the same few pages dirty throughout every
checkpoint?I wouldn't have expected that, but given our application I suppose it is possible.