Fwd: Indexes and Tables: Growth and Treatment - Mailing list pgsql-general

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:


<excerpt><bold><color><param>0000,0000,0000</param>From:
</color></bold>Tom Lane <<tgl@sss.pgh.pa.us>

<bold><color><param>0000,0000,0000</param>Date: </color></bold>July
13, 2004 6:58:24 PM CDT

<bold><color><param>0000,0000,0000</param>To: </color></bold>"Thomas
F.O'Connell" <<tfo@sitening.com>

<bold><color><param>0000,0000,0000</param>Cc: </color></bold>Scott
Holdren <<scott@holdren.com>, pgsql-general@postgreSQL.org

<bold><color><param>0000,0000,0000</param>Subject: </color>Re:
[GENERAL] Indexes and Tables: Growth and Treatment (Modified by Thomas
F. O'Connell)

</bold>

"Thomas F.O'Connell" <<tfo@sitening.com> writes:

<excerpt>It's a high turnover database, in that the applications that
use it

perform thousands of inserts, updates, and deletes on a daily basis.

</excerpt>

<excerpt>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.

</excerpt>

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</excerpt>
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 <tgl@sss.pgh.pa.us>
> Date: July 13, 2004 6:58:24 PM CDT
> To: "Thomas F.O'Connell" <tfo@sitening.com>
> Cc: Scott Holdren <scott@holdren.com>, pgsql-general@postgreSQL.org
> Subject: Re: [GENERAL] Indexes and Tables: Growth and Treatment
> (Modified by Thomas F. O'Connell)
>
> "Thomas F.O'Connell" <tfo@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