Index grows huge, possible leakage?
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?
As a side note, the space requirement is actually 'ok' it's just
that performance gets terrible once the indecies reach such huge
sizes.
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."
PostgreSQL hasn't a rewritten storage managent, this is a normal case.
Regards
XuYifeng
----- Original Message -----
From: "Alfred Perlstein" <bright@wintelcom.net>
To: <pgsql-hackers@postgresql.org>
Cc: "Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
Sent: Friday, February 02, 2001 7:34 AM
Subject: [HACKERS] Index grows huge, possible leakage?
Show quoted text
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 tablesHowever 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?As a side note, the space requirement is actually 'ok' it's just
that performance gets terrible once the indecies reach such huge
sizes.--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."
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 tablesHowever 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.
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.
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...
Vadim
Import Notes
Resolved by subject fallback
* 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 tablesHowever 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 likeIndex _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."