Re: RE: Index grows huge, possible leakage?

* Mikheev, Vadim <vmikheev(at)SECTORBASE(dot)COM> [010202 10:39] wrote:
> > After several weeks our idicies grow very large (in one case to
> > 4-5 gigabytes) After droppping and recreating the indecies they
> > shrink back to something more reasonable (500megs same case).
> >
> > We are currently using Vadim's vacuum patches for VLAZY and MMNB,
> > against 7.0.3. We are using a LAZY vacuum on these tables
> >
> > However a normal (non-lazy) vacuum doesn't shrink the index, the
> > only thing that helps reduce the size is dropping and recreating.
> >
> > Is this a bug in 7.0.3? A possible bug in Vadim's patches? Or is
> > this somewhat expected behavior that we have to cope with?
>
> When index is created its pages are filled in full => any insert
> into such pages results in page split - ie in additional page.
> So, it's very easy to get 4Gb from 500Mb.

Well that certainly stinks. :(

> Vacuum was never able to shrink indices - it just removes dead index
> tuples and so allows to re-use space ... if you'll insert the same
> keys.

This doesn't make sense to me, seriously, if the table is locked
during a normal vacuum (not VLAZY), why not have vaccum make a
new index by copying valid index entries into a new index instead
of just vacating slots that aren't used?

> To know does VLAZY work properly or not I would need in vacuum debug
> messages. Did you run vacuum with verbose option or do you have
> postmaster' logs? With LAZY vacuum writes messages like
>
> Index _name_: deleted XXX unfound YYY
>
> YYY supposed to be 0...

With what you explained (indecies normally growing) I don't think
VLAZY is the problem here.

--
-Alfred Perlstein - [bright(at)wintelcom(dot)net|alfred(at)freebsd(dot)org]
"I have the heart of a child; I keep it in a jar on my desk."