Re: RE: Index grows huge, possible leakage?

* 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."