Thread: 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? > 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." >
> 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. 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
* 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."