Jesse Norell wrote:
Matthew T. O'Connor wrote:
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. :)
Well it's not technically a bug which is why I said "fix" with the
quotes, but it was a recognized deficiency that has been addresses in
7.4. If you look the the 7.4 release notes, it is mentioned there.
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. :(
Let me correct my statement a little, it's not just the big tables that
will get cause problems. It has more to do with the number of updates
than it has do do with the number of rows in the table. The user table
for example is updated every time you login, so it's indexes could get
fairly big... etc...
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.
Yip afraid so. BTW, a goal for PostgreSQL is that VACUUL FULL will just
go away at some point.
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.
You should use pg_autovacuum with 7.4 also.
Matthew
