Vacuum verbose output? - Mailing list pgsql-general

From Julian Scarfe
Subject Vacuum verbose output?
Date
Msg-id 054001c2bbb1$c86bf900$0700a8c0@Wilbur
Whole thread Raw
Responses time to restore a database
Re: Vacuum verbose output?
Re: Vacuum verbose output?
List pgsql-general
Is there any documentation on the output of VACUUM VERBOSE please?

I've found some mention from Tom of some of the fields in a previous message
here...

---------------------------------------------------------------

Looking at the code:

Tup # tuples remaining after vacuum
Vac # tuples removed by vacuum
Keep # dead tuples kept because some xact can still see 'em
Unused # unused item pointers

"Keep" is included in the "Tup" total.

The unused item pointers may get recycled later; it looks like that
hasn't been determined yet at the point where these stats are printed.

---------------------------------------------------------------

but I'd be interested to understand more of

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.

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.

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):

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.
NOTICE:  Pages 342: Changed 2, Empty 0; Tup 8747: Vac 609, Keep 0, UnUsed
5350.
NOTICE:  Pages 342: Changed 1, Empty 0; Tup 8443: Vac 732, Keep 0, UnUsed
5572.
NOTICE:  Pages 342: Changed 0, Empty 0; Tup 8325: Vac 838, Keep 0, UnUsed
5614.
NOTICE:  Pages 342: Changed 4, Empty 0; Tup 8197: Vac 868, Keep 0, UnUsed
5729.
NOTICE:  Pages 353: Changed 1, Empty 0; Tup 9159: Vac 413, Keep 0, UnUsed
5508.
NOTICE:  Pages 354: Changed 1, Empty 0; Tup 8780: Vac 765, Keep 0, UnUsed
5552.
NOTICE:  Pages 354: Changed 1, Empty 0; Tup 8335: Vac 1179, Keep 0, UnUsed
5597.
NOTICE:  Pages 354: Changed 1, Empty 0; Tup 8746: Vac 349, Keep 0, UnUsed
6016.
NOTICE:  Pages 361: Changed 1, Empty 0; Tup 9084: Vac 691, Keep 0, UnUsed
5515.
NOTICE:  Pages 361: Changed 2, Empty 0; Tup 8632: Vac 899, Keep 0, UnUsed
5785.
NOTICE:  Pages 361: Changed 0, Empty 0; Tup 8229: Vac 1111, Keep 0, UnUsed
5981.
NOTICE:  Pages 361: Changed 2, Empty 0; Tup 8563: Vac 329, Keep 0, UnUsed
6429.
NOTICE:  Pages 363: Changed 4, Empty 0; Tup 8992: Vac 822, Keep 0, UnUsed
5580.
NOTICE:  Pages 363: Changed 1, reaped 341, Empty 0, New 0; Tup 8411: Vac
1136, Keep/VTL 0/0, UnUsed 5851, MinLen 115, MaxLen 804; Re-using:
Free/Avail. Space 428616/426644; EndEmpty/Avail. Pages 0/327.
NOTICE:  Pages 351: Changed 1, reaped 340, Empty 0, New 0; Tup 7987: Vac
1297, Keep/VTL 0/0, UnUsed 5583, MinLen 115, MaxLen 804; Re-using:
Free/Avail. Space 477064/475712; EndEmpty/Avail. Pages 0/321.
NOTICE:  Pages 337: Changed 0, Empty 0; Tup 8334: Vac 690, Keep 0, UnUsed
5481.

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?

Thanks

Julian Scarfe





pgsql-general by date:

Previous
From: "Aurangzeb M. Agha"
Date:
Subject: Re: PostgreSQL on Windows
Next
From: "Peter Gibbs"
Date:
Subject: Re: How to ALTER COLUMN to set a constraint of not null?