hi philip,
I just checked your link and executed the query
select count(*) from pg_class where relkind in ('t','r');
it got me a value of 38; the system has default config of
max_fsm_relations (100) so that shouldn't be the problem either.
regards roel
Philip Warner heeft op woensdag, 18 dec 2002 om 12:22
(Europe/Amsterdam) het volgende geschreven:
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 |/
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
