Re: pg_xlog becomes extremely large during CREATE INDEX

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.

regards, tom lane