Here's some more feedback on our use of pg_autovaccum. It's clear that
it's working and that it's helping, but even after increasing our
max_fsm_pages substantially (to in excess of what vacuum verbose
suggests is needed), we're still seeing pretty a rapid increase in disk
usage.
It used to be that nightly reindexing helped substantially, but am I
wrong in thinking that the frequency of dynamic analysis is helping
keep index size down?
Anyway, the bottom line is that it still seems like a vacuum full on a
periodic basis is necessary to keep growth rates in check. We track
both table and index size, and our max table grows pretty rapidly (over
the course of a few days) from about 4 MB to about 11 MB with
pg_autovacuum running. After a vacuum full, it returns to about 4 MB.
Are there any other postgresql.conf parameters that I should be looking
at as far as allowing pg_autovacuum to work more effective in terms of
disk reclamation? Is it possible that my max_fsm_pages is still too
low?
Thanks!
-tfo
Begin forwarded message:
From: Tom Lane <tg*@sss.pgh.pa.us>
Date: July 13, 2004 6:58:24 PM CDT
To: "Thomas F.O'Connell" <tf*@sitening.com>
Cc: Scott Holdren <sc***@holdren.com>, pg***********@postgreSQL.org
Subject: Re: [GENERAL] Indexes and Tables: Growth and Treatment
(Modified by Thomas F. O'Connell)
"Thomas F.O'Connell" <tf*@sitening.com> writes:It's a high turnover database, in that the applications that use it
perform thousands of inserts, updates, and deletes on a daily basis.We're seeing about 5-10 GB of increased disk space used on a daily
basis if a vacuum (full) or reindexdb is not performed. We were doing
one vacuum analyze full a week with nightly vacuum analyzes.
Try hourly vacuums. If that doesn't stem the tide, make it more often
(or try autovacuum). Also make sure that your FSM settings are large
enough; if they're not then no amount of plain vacuuming will keep you
out of trouble.
With sufficiently frequent plain vacuums you really shouldn't need
vacuum full at all.
I can't recommend an analyze frequency on what you've told us.
regards, tom lane
