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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
