Re: Vacuum verbose output? - Mailing list pgsql-general

On Tue, 2003-01-14 at 04:46, Julian Scarfe wrote:
> Is there any documentation on the output of VACUUM VERBOSE please?

AFAIK your best bet is to search the mailing archives or look through
the source :-(

>
> NOTICE:  Pages 371: Changed 3, reaped 364, Empty 0, New 0; Tup 8180: Vac
> 1786, Keep/VTL 0/0, UnUsed 6285, MinLen 115, MaxLen 648; Re-using:
> Free/Avail. Space 622460/621672; EndEmpty/Avail. Pages 0/350.
>
> in a little detail.
>

All from memory, probably wrong on a couple, so take this with a grain
of salt.

Pages = the number of files on the system involved in this table.
Changed = the number of pages that had to be modified. Reaped mean the
number of pages that were made compleltely blank, ready for reuse. Empty
= pages that are currently empty, ready for reuse. New = pages that had
to be created as a result of vacuuming?


> The application is a database where the data in the tables are essentially
> write-once-read-many, and a tuple stays active for about 3 hours before
> being deleted/archived.  With a regular, simple VACUUM, the number of Pages
> increases steadily until a VACUUM FULL is performed.  But a write-lock on
> the tables is a real pain, as the entire population mechanism has to be
> suspended, and it backs up very quickly.
>

Sounds like you need to run vacuum at least every 3 hours.

 > In order to understand the space and performance implications of how often
> we do a VACUUM FULL rather than simple VACUUM, I'd like to understand the
> output.
>
> Example (just successive VACUUM output, longer entries are VACUUM FULL):
<snip output>

ISTM that at times your vacuuming regularly and actually recovering
space, but then there are times when your space grows rather quickly.
This is either an indication that you need to time your vacuums better
(to coincide with large batch inserts or deletes) or you need to vacuum
more often, to recover more space between batches.

I should also say that it might also be an indication that your free
space map settings aren't set appropriately, make sure max_fsm_relation
and max_fsm_pages are set high enough. Without looking at your complete
database there's no way to know for sure. (Although max_fsm_relations is
almost assuredly to low).

>
> It grows steadily until a VACUUM FULL is performed, when it shrinks back.
> If we never do a VACUUM FULL, will it just keep on growing, and does
> performance suffer as it does so?
>

The thing with vacuum is at some point you should reach a condition
where space on the disk increases only when the total number of inserts
is greater than the total number of deletions over a given time frame.
If it increases when inserts is less than deletes, your not vacuuming
enough. If you can reach this level of "symbiosis" with regular vacuums,
you should never have to do vacuum full.

Robert Treat