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 percentage of buffers_alloc, and buffers_clean should be larger than it is compared to buffers_checkpoint. Is my understanding correct?
>
> 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 considering the current setup and with a time interval to go along with it is much more useful than the aggregate numbers here (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 much the cleaner should do is most likely to break down when the load comes in narrow spikes. The main way to improve response in 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 was mostly idle before that.
>
> You can try lowering bgwriter_delay and proportionally decreasing bgwriter_lru_maxpages to make response time to burst workloads better. In your situation, I'd try make the writer wake up 4X as often, only do 1/4 as much maximum work as it currently does each time, and doubling the multiplier too; see if things move in the right direction, and maybe keep 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 changed much 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.