Re: pg_xlog becomes extremely large during CREATE INDEX

Vivek Khera <khera@kcilink.com> writes:
> "TL" == Tom Lane <tgl@sss.pgh.pa.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.

            regards, tom lane