Re: tuning bgwriter in 8.4.2

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.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us