Re: lowering impact of checkpoints

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, but
these settings are barely doing anything; they aren't even in the right
ballpark for a system that's doing 600-2000 TPS. You need to be careful
here because making the background writer run too often can result in
things running slower all the time, and in some cases it can even make the
checkpoints worse.

I would suggest changing these parameters one at a time, in the following
order, and see what happens to the number of slow queries at checkpoint
time after each change:

bgwriter_lru_maxpages: increase to 500
bgwriter_lru_percent: increase to 5
bgwriter_all_maxpage: increase to 250
bgwriter_all_percent: increase to 2

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. Here is a message I'd suggest reading carefully
from someone who went through the process you're starting now and ended up
with a much more aggressive set of settings even than these:

http://archives.postgresql.org/pgsql-hackers/2006-12/msg00383.php

As Kevin suggests there, one thing that varies a bit based on the exact
work you're doing is the ratio between how heavily you balance the all vs.
lru weighting. His final settings use the all writer a bit more heavily
than I'd normally recommend, but with his particular system that worked
out well.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD