470,602 Members | 1,731 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,602 developers. It's quick & easy.

Fwd: Indexes and Tables: Growth and Treatment

Matthew,

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

Nov 23 '05 #1
0 1030

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Steve_CA | last post: by
9 posts views Thread by Igor | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.