"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> When we first went to PostgreSQL our biggest problem was that dirty buffers
> would accumulate in shared memory until a checkpoint, and then overrun the
> controllers cache. This would cause disk reads to queue up behind the
> writes, and queries which normally ran in a millisecond or two were timing
> out at our renderers' 20 second limit. The problem went away completely when
> we used a very aggressive background writer configuration, to put the dirty
> pages in front of the OS file system right away, so that its algorithms and
> the controller cache could deal with things before they got out of hand.
Sounds like a tailor-mode use case for precisely what Heikki was complaining
about. He couldn't find a case in 8.3 where tuning the bgwriter to be more
aggressive helped at all.
With the load distributed checkpoints I think the symptoms would be different
but the disease may still be there. Since checkpoints will try not to swamp
your i/o bandwidth any longer you shouldn't get these terrible spikes.
However the theory with bgwriter is that setting it to be very aggressive will
reduce the response time even outside the checkpoints by avoiding the need for
individual backends to evict dirty pages. So it would be interesting to know
with 8.3 whether the average response time even outside of checkpoints is
reduced by having a more aggressive bgwriter policy.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com
