At 11:23 AM 18/12/2002 +0100, Roel Rozendaal - IC&S wrote:
The problem is that postgresql keeps on eating disk space
Have a look at this link:
http://www.rhyme.com.au/manuals/pgsql-7.3/postmaster-tuning-software.html
it is an addition to the PG docs that I wrote recently and which has not
yet been added to CVS. We had the same problem, and this describes the
cause & solution. If you like, I can help in a more detailed fashion offline.
I would like to ask the postgresql experts on this list some advice: how
to reclaim disk space?
Only solution is VACUUM full or backup/restore. However, the above link
describes how to at least stop the growth. The problems with having a
too-big database are that VACUUM takes a long time, sequential scans take
longer and disk space is wasted. It is worth scheduling downtime to fix,
but only after some statistics are collected.
For now, each night a VACUUM ANALYZE runs on the database. Any chance i
can see where exactly postgresql is growing?
VACUUM VERBOSE gives more detailed information (see above link). Also,
select relname, relpages from pg_class
will give you relation sizes. But VACUUM VERBOSE is more likely to give
more useful information.
Would re-building the indices help?
Unlikely, unless there are other problems.
The database is not growing (only POP is run), about 20000 messages are
delivered each day.
This means that the most likely problem is as above. Try:
VACUUM VERBOSE messageblks
This will probably show a very large 'Unused' value in the MESSAGEBLKS or
PG_TOAST_nnnnnnn table, where nnnnnnn is the OID of the MESSAGEBLKS table.
To collect useful data, run VACUUM VERBOSE every few hours and keep the
logs; run it often enough to collect a representative usage pattern
(dbmail-maintenance, day/nigh etc). Once you have that, I can give more
specific advice about the best settings (or you can use the link above to
work this out for yourself).
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
