Matthew T. O'Connor wrote:
> Jesse Norell wrote:
>
> > Hello,
> >
> > We've been dealing with this issue quite recently (even with some
> > assistance from Dan in #dbmail{,-dev} channels). The below
> > recommendations are right on par with what I've found for pgsql
> > maintenance (ie. sufficiently high fsm settings, and frequent
> > vacuuming), but there also seems to be a bug in postgres that
> > continues to grow the disk usage without bounds (well, the to the
> > limits of your hardware :). It supposedly may be fixed in 7.4,
> > but we haven't gotten to upgrade yet to test that. We're running
> > 7.3.2, and apparently it's been an issue that's been around quite
> > a while.
>
> I assume the "fix" you are talking about is that starting with
> PostgreSQL 7.4 Vacuum reclaims index space. That resolves the major
> remaining file bloat problem with PostgreSQL. As of 7.4 as long as your
> FSM setting is high enough, and you vacuum often enough, you shouldn't
> have any problems with unbounded file growth. If you do, please let the
> people on the postgresql mailing lists know about it, they would like to
> look at it.
I did a little looking, but as of yet I've not found anywhere discussing
the issue that identifies it as a bug. With no concrete evidence, my
comments are based on the remarks of someone who, at a linux conference,
allegedly witnessed some guys from the pgsql booth reply "Yeah, right!"
when asked if they were ever going to fix a known dump/load problem (don't
know the date, though), and also the fact that our experience sure seems
to corroborate the theory. :)
> As for working around this in 7.3, to reclaim space in youn indexes, you
> have to periodically re-index your big tables. If you add that to your
> maintenace routines it should help alot. But better yet would be to
> upgrade to 7.4, there are lots o'improvements.
We do regularly drop an rebuild indexes on messages, but it looks like
we do not on messageblks. There is one index on messageblks(message_idnr)
and the primary key, both of which are bigint's (8 bytes). So with
max(messageblk_idnr) at < 45million, that should account for about 720MB,
which is quite a bit, but no-where near 10GB. I believe all the other
indexes (on users, aliases, pbsp, etc.) are both much smaller and will
reuse the keys, so shouldn't need to be recreated. Unfortunately, it's
too late right now to drop all indexes and see just how much space that
did free up. :(
<snipp>
> Again, in 7.3 even VACUUM FULL does not reclaim index space, so next
> time instead of doing a complete reload you might try doing a VACUUM
> FULL and a reindex on all the tables. Also, since it sounds like the
> VACUUM FULL only reclaims some of the space it sounds like either bloat
> or perhaps if you did this while the system was live, VACUUM might not
> have been able to get the exclusive lock it needs, so it may have
> skipped some tables, you can verify this by running VACUUM VERBOSE.
> Also, regular VACUUM doesn't require an exclusive lock.
We did VACUUM FULL under the live system. Where it eventually succeeded
with no errors, I didn't know it was possible to not have done it's job.
> Also, pg_autovacuum that is in 7.4 contrib should work against a 7.3
> PostgreSQL server. If you really can't upgrace to 7.4 you might want to
> try running pg_autovacuum against your 7.3 database, again you will
> still need to reindex periodically but it should help.
We'll try that till we can upgrade to 7.4. Having done a dump/load,
we'll probably be fine on our current schedule for about another year,
but maybe with reindexing *everything* it'll help even more.
> Hope some of that helps.
Definitely, thanks! This should probably be in #dbmail, not #dbmail-dev,
but I hope it may help others, too.
Jesse
--
Jesse Norell
[EMAIL PROTECTED] is not my email address;
change "administrator" to my first name.
--
