On Thu, Jun 10, 2010 at 12:49 PM, Janning <ml@planwerk6.de> wrote:
> 1. With raising checkpoint_timeout, is there any downgrade other than slower
> after-crash recovery?
Depends on how busy your DB is, and how many checkpoint segments you
have. All the timeout does is say, "if we have not done a checkpoint
this long, do it now." So, if you run out of checkpoint segments, a
checkpoint will be run. How often you fill those segments depends on
how much writing you do and how many of them you have.
>
> 2. Is there a way to calculate the after-crash recovery time with a certain
> checkpoint_timeout? How long would be approx. for a checkpoint_timeout of 60
> minutes?
It depends on the number of segments with data that needs to be pushed
to permanent table storage. So it is not so much dependent on the
timeout but the number of segments and how much writing you do, as
above.
>
> 3. Is it sane to set checkpoint_timeout to 120min or even to 600min?
I don't think so :-)
>
> 4. We have checkpoint_completion_target set to 0.9. The checkpoint is always
> ready much earlier. Is it ought to be like this? I thought the work would be
> spread across 90% of 60 minutes? Instead it takes only 10 Minutes for the
> checkpoint to be finished.
I use the following:
checkpoint_segments = 64
checkpoint_timeout = 15min
checkpoint_completion_target = 0.8
on a server with 20GB RAM and very big fast disks with Pg 8.3.
You should also investigate the bgwriter, which pushes dirty pages to
disk. Perhaps being more aggressive with that will reduce your need to
checkpoint.
Greg Smith has the best info on tuning these parameters that I have
found: http://www.westnet.com/~gsmith/gregsmith/content/postgresql/index.htm