Re: Simple postgresql.conf wizard

On Wed, 2008-12-03 at 22:17 -0300, Alvaro Herrera wrote:
> Gregory Stark escribió:
> > "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
>
> > > I don't think at any time I have said to my self, I am going to set this
> > > parameter low so I don't fill up my disk. If I am saying that to myself
> > > I have either greatly underestimated the hardware for the task. Consider
> > > that we are quarreling over what amounts to a nominal amount of hard
> > > drive space, 1000 checkpoint_segments = 1.6G of space. My phone has more
> > > capacity than that.
> >
> > Well my phone has 16G of RAM, why not 10000 ?
>
> I don't think the disk space used is the only consideration here. You
> also have to keep recovery time in mind. If you set it to 1000,
> recovery would take way too long.

Well certainly but the original argument that came back was, (from
Robert Haas):

"
It seems unlikely that you would want 256 MB of checkpoint segments on
a database that is only 100 MB (or even 500 MB). But you might very
well want that on a database that is 1 TB.
"

My whole point is that:

1. It seems unlikely that you would hit 256MB of checkpoint segments on
a 100MB database before checkpoint_timeout and if you did, you certainly
did need them. (the checkpoint segments)

2. "taking up space" is such a minute concern in comparison to the
potential benefit.

Recovery is certainly a consideration but let's be realistic it is the
last consideration because it is the least likely to happen. What is
more likely to happen is IO spikes because we are recycling logs too
much.

I know we have some other facilities to deal with that now too but it
doesn't completely negate the problem and in my opinion, increasing the
checkpoint_segments provides no perceivable downside in production use
but does provide significant perceivable upside.

Joshua D. Drake

--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997