From: | "'Alfred Perlstein'" <bright(at)wintelcom(dot)net> |
---|---|
To: | "Mikheev, Vadim" <vmikheev(at)SECTORBASE(dot)COM> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: RE: Index grows huge, possible leakage? |
Date: | 2001-02-05 20:52:05 |
Message-ID: | 20010205125205.Z26076@fw.wintelcom.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
* 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."
From | Date | Subject | |
---|---|---|---|
Next Message | John Anderson | 2001-02-05 21:28:53 | building libpgtcl for pgaccess under win32, and other stories |
Previous Message | Peter Eisentraut | 2001-02-05 20:26:23 | Re: pg_ctl wish list |