Re: lowering impact of checkpoints

"Greg Smith" <gsmith(at)gregsmith(dot)com> writes:

> On Tue, 25 Sep 2007, hubert depesz lubaczewski wrote:
>
>> name | setting | unit
>> -----------------------+-----------+--------
>> bgwriter_all_maxpages | 5 |
>> bgwriter_all_percent | 0.333 | [null]
>> bgwriter_delay | 200 | ms
>> bgwriter_lru_maxpages | 5 |
>> bgwriter_lru_percent | 1 | [null]
>
> The background writer can help smooth out checkpoints a bit in 8.2.4,

Not to disagree with anything Greg says here but you should keep in mind that
all of these parameters will have to be retuned from scratch with 8.3 which
has been optimized somewhat for just this problem.

> If these are all positive changes, you might even want to increase these
> further; potentially you could double all of the above and still not have the
> settings high enough, and if that's the case you may have to adjust
> bgwriter_delay downward.

I'm surprised you don't start by suggesting lowering bgwriter_delay for a busy
dedicated system. Does it cause too much wasted cpu work in the "all" cycle in
8.2?

I also wonder if it doesn't make more sense in 8.2 if your goal is to avoid
drop-outs to just give up on the lru cycle entirely and set the delay to
something like 60s and the all_percent to 100. Effectively saying to flush all
dirty buffers once a minute to smooth the checkpoint. I haven't tried doing
anything like that though.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com