Re: Load distributed checkpoint

On Thu, Dec 07, 2006 at 10:03:05AM -0600, Kevin Grittner wrote:
> When we first switched our web site to PostgreSQL, this was one of our biggest problems.  Queries which normally run
ina few milliseconds were hitting the 20 second limit we impose in our web application.  These were happening in bursts
whichsuggested that they were caused by checkpoints.  We adjusted the background writer configuration and nearly
eliminatedthe problem.
 
>  
>  bgwriter_all_maxpages           | 600
>  bgwriter_all_percent            | 10
>  bgwriter_delay                  | 200
>  bgwriter_lru_maxpages           | 200
>  bgwriter_lru_percent            | 20

Bear in mind that bgwriter settings should be considered in conjunction
with shared_buffer and checkpoint_timeout settings. For example, if you
have 60,000 shared buffers and a 300 second checkpoint interval, those
settings are going to be pretty aggressive.

Generally, I try and configure the all* settings so that you'll get 1
clock-sweep per checkpoint_timeout. It's worked pretty well, but I don't
have any actual tests to back that methodology up.
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)