Re: pg_xlog becomes extremely large during CREATE INDEX

On Fri, 2004-05-14 at 20:55, Tom Lane wrote:
> Vivek Khera <khera(at)kcilink(dot)com> writes:
> > "TL" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> > TL> ... On looking at the code I see that it doesn't make any
> > TL> attempt to prune future log segments after a decrease in
> > TL> checkpoint_segments, so if a previous misconfiguration had allowed the
> > TL> number of future segments to get really large, that could be the root of
> > TL> the issue.
>
> > Wow... that explains it!
>
> > I bumped up checkpoint segments to 50 for a restore since it made it
> > run way faster. In normal operation I don't need that many so I
> > dropped it back down but it didn't reclaim any space so I figured I
> > might as well keep it at 50...
>
> How long did you wait? I believe the code will prune excess segments as
> they come around to be recycled. It just doesn't kill them immediately.
>
> I think that part of what's going on in Jeff's example is that he's
> looking at the state immediately after a spike in database traffic, and
> not having any patience to see if the system will recover after some
> period with more-normal traffic levels.

It won't ever return to normal levels after the device fills up, the
server panics, and all the databases are inconsistent.

-jwb