* Mikheev, Vadim <vmikheev@SECTORBASE.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@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."