Re: Question regarding autovacuum

On 8/28/07, Karl Denninger <karl(at)denninger(dot)net> wrote:
>
> Am I correct in that this number will GROW over time? Or is what I see
> right now (with everything running ok) all that the system
> will ever need?

They will grow at first to accomodate your typical load of dead tuples
created between regular vacuums.

Then they should reach a steady state where they will slowly grow as
your activity levels increase.

So it's a good idea to allocate 20 to 50% more than what vacuum
verbose says you'll need for overhead. also keep in mind that vacuum
verbose only tells you what the one db in the server needs. If you
have multiple dbs in your postgresql service, you'll need to run
vacuum verbose on all of them after X time (typical time between your
vacuums) and add the needed free space together to get the total
needed.

> If the latter, then I'm WELL within limits and I guess I need to tune the
> autovacuum parameters to be more aggressive; system views show it IS being
> run.
>
> INFO: free space map contains 5895 pages in 639 relations
> DETAIL: A total of 14976 page slots are in use (including overhead).
> 14976 page slots are required to track all free space.
> Current limits are: 179200 page slots, 1000 relations, using 1115 kB.

Yeah, that looks good. Note that the preferred state for pgsql is to
have 10-25% free space in frequently updated tables, rather than
removing it all with reindex / vacuum full. This keeps the files from
getting fragmented AND keeps the OS from having to constantly allocate
more space for the tables. Just cron up something to run vacuum
verbose everynight and email it to you to peruse over coffee in the
morning, and compare to previous nights. that'll give you an idea of
how you're fsm is holding up.