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





Tue, 14 Jan 2003 09:46:16 -0000
"Julian Scarfe" <julian.scarfe@ntlworld.com>
Vacuum verbose output?
hello,


is it possible to estimate the time to restore a database from a dump ?
the dump is  85 Mo, the base is 850 Mo, we have 512 Mo RAM and a 
pentium 3 1.13 GHz.
we are running for about 16 hours (20:00 CPU time) but it's really 
slow, and the database is actually 365 Mo restored.


cheers,
pascal.

Tue, 14 Jan 2003 12:19:39 +0100
pascal =?iso-8859-1?Q?b=E9rest?= <lists@magnetophone.com>
time to restore a database
"Julian Scarfe" <julian.scarfe@ntlworld.com> writes:
> 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.

What do you have the FSM parameters in postgresql.conf set to?  My guess
is you need to increase 'em.

			regards, tom lane

Tue, 14 Jan 2003 10:42:33 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Vacuum verbose output? 
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



14 Jan 2003 10:54:29 -0500
Robert Treat <xzilla@users.sourceforge.net>
Re: Vacuum verbose output?
On 14/1/03 15:42, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

> "Julian Scarfe" <julian.scarfe@ntlworld.com> writes:
>> 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.
> 
> What do you have the FSM parameters in postgresql.conf set to?  My guess
> is you need to increase 'em.

Currently set at the defaults, max_fsm_relations is 100, max_fsm_pages is
10000.

What are the implications/effects of increasing them?

BTW, one of my tables is much bigger, turning over at a similar rate (though
I'm much less concerned about performance for that one).  Typical VACUUM
FULL looks like:

NOTICE:  Pages 94279: Changed 315, reaped 53522, Empty 0, New 0; Tup
2753324: Vac 7907, Keep/VTL 0/0, UnUsed 277009, MinLen 135, MaxLen 1248;
Re-using: Free/Avail. Space 8569160/3380588; EndEmpty/Avail. Pages 0/5802.

Thanks

Julian


Tue, 14 Jan 2003 16:04:08 +0000
Julian Scarfe <julian.scarfe@ntlworld.com>
Re: Vacuum verbose output? 
Julian Scarfe <julian.scarfe@ntlworld.com> writes:
> On 14/1/03 15:42, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>> What do you have the FSM parameters in postgresql.conf set to?  My guess
>> is you need to increase 'em.

> Currently set at the defaults, max_fsm_relations is 100, max_fsm_pages is
> 10000.

100 is definitely too small for max_fsm_relations; we've bumped that
default to 1000 for the next release.  Given that you've got a
nearly-100k-page table, 10000 for max_fsm_pages is probably much too
small also.

> What are the implications/effects of increasing them?

Shared memory usage gets bigger.  I think the max_fsm_pages multiplier
is 6 bytes per slot, so you could make it a million, or ten, without
stressing modern machines.  I'd try 1000/1000000 for starters.

Don't forget you need a postmaster restart to make these changes take
effect.

			regards, tom lane

Tue, 14 Jan 2003 11:10:30 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Vacuum verbose output? 
Robert Treat <xzilla@users.sourceforge.net> writes:
> 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.  

It's simpler than that: the vacuum frequency determines the amount of
overhead you have to tolerate.  Judging from the numbers Julian quoted
us to start with, he's got the frequency set so that vacuum reclaims
about 10% of the tuples on each run.  That seems like a good ballpark to
me: 10% space overhead is quite reasonable.

If space usage increases when the total amount of live data is staying
about the same, then the problem has got to be that the space getting
reclaimed by vacuum isn't being re-used effectively.  And the only cause
for that, AFAIK, is the FSM settings being too small to keep track of
all the free space in the database.

			regards, tom lane

Tue, 14 Jan 2003 11:19:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Vacuum verbose output? 
On 14/1/03 16:10, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

> Julian Scarfe <julian.scarfe@ntlworld.com> writes:
>> On 14/1/03 15:42, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>>> What do you have the FSM parameters in postgresql.conf set to?  My guess
>>> is you need to increase 'em.
> 
>> Currently set at the defaults, max_fsm_relations is 100, max_fsm_pages is
>> 10000.
> 
> 100 is definitely too small for max_fsm_relations; we've bumped that
> default to 1000 for the next release.  Given that you've got a
> nearly-100k-page table, 10000 for max_fsm_pages is probably much too
> small also.
> 
>> What are the implications/effects of increasing them?
> 
> Shared memory usage gets bigger.  I think the max_fsm_pages multiplier
> is 6 bytes per slot, so you could make it a million, or ten, without
> stressing modern machines.  I'd try 1000/1000000 for starters.

OK, so what's the up-side? :-)

Will this improve performance, or change disk space requirements, or
something else?  What's the rule of thumb, what are the criteria for setting
these parameters?

Unfortunately, I don't have enough of a grasp of the internals to understand
the implications of the changes -- is there a particular section of the doc
that would help me, or do I have to look at the source?  At the moment, I
don't have a good mental model of what's going on.

Thanks again

Julian


Tue, 14 Jan 2003 16:20:36 +0000
Julian Scarfe <julian.scarfe@ntlworld.com>
Re: Vacuum verbose output? 
Robert

Thanks for that.

On 14/1/03 15:54, "Robert Treat" <xzilla@users.sourceforge.net> wrote:

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

We vacuum about every hour, immediately after a large batch delete.  Inserts
are spread across the hour with a couple of peaks.

[FWIW tuple "life" (insert to subsequent delete) is about 3 hours on that
table, about 12-24 hours on other tables where performance is important, and
30 days on the rather larger but less performance critical large archive
tables.]  

I've got limited data at the moment -- I need to collect more to see.

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

OK, that's a helpful way of looking at it.  Thanks.

Julian


Tue, 14 Jan 2003 16:31:12 +0000
Julian Scarfe <julian.scarfe@ntlworld.com>
Re: Vacuum verbose output?
Julian Scarfe <julian.scarfe@ntlworld.com> writes:
> OK, so what's the up-side? :-)

> Will this improve performance, or change disk space requirements, or
> something else?

It'll keep the system from leaking free space in tables, which is what's
causing your tables to bloat.  You need an FSM slot for each page that
has useful free space on it, else the system will forget about that
free space.

> What's the rule of thumb, what are the criteria for setting
> these parameters?

Right at the moment I don't think there is any direct way to discover
how big max_fsm_pages needs to be.  An upper bound is
	select sum(relpages) from pg_class where relkind in ('r','t');
(actually you have to sum over all databases in your installation).
But in most scenarios this is probably overkill, as large tables tend
not to have turnover in every page.

I suppose we should think about adding some more numbers to the VACUUM
output so that one can determine what's happening with the FSM.

			regards, tom lane

Tue, 14 Jan 2003 11:32:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Vacuum verbose output? 
On 14/1/03 16:32, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

> Julian Scarfe <julian.scarfe@ntlworld.com> writes:
>> OK, so what's the up-side? :-)
> 
>> Will this improve performance, or change disk space requirements, or
>> something else?
> 
> It'll keep the system from leaking free space in tables, which is what's
> causing your tables to bloat.  You need an FSM slot for each page that
> has useful free space on it, else the system will forget about that
> free space.
> 
>> What's the rule of thumb, what are the criteria for setting
>> these parameters?
> 
> Right at the moment I don't think there is any direct way to discover
> how big max_fsm_pages needs to be.  An upper bound is
> select sum(relpages) from pg_class where relkind in ('r','t');
> (actually you have to sum over all databases in your installation).
> But in most scenarios this is probably overkill, as large tables tend
> not to have turnover in every page.

In my case, 


Tue, 14 Jan 2003 16:37:56 +0000
Julian Scarfe <julian.scarfe@ntlworld.com>
Re: Vacuum verbose output? 
[Sorry about the false start.]

On 14/1/03 16:32, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

> Julian Scarfe <julian.scarfe@ntlworld.com> writes:
>> OK, so what's the up-side? :-)
> 
>> Will this improve performance, or change disk space requirements, or
>> something else?
> 
> It'll keep the system from leaking free space in tables, which is what's
> causing your tables to bloat.  You need an FSM slot for each page that
> has useful free space on it, else the system will forget about that
> free space.

OK, makes sense.

>> What's the rule of thumb, what are the criteria for setting
>> these parameters?
> 
> Right at the moment I don't think there is any direct way to discover
> how big max_fsm_pages needs to be.  An upper bound is
> select sum(relpages) from pg_class where relkind in ('r','t');
> (actually you have to sum over all databases in your installation).
> But in most scenarios this is probably overkill, as large tables tend
> not to have turnover in every page.

In my case, for this, the only seriously active database

=> select sum(relpages) from pg_class where relkind in ('r','t');
  sum   
--------
 858596
(1 row)

So it looks like your guess of 1000000 is pretty much spot on.

We'll see how that goes.

Thanks for the help

Julian



Tue, 14 Jan 2003 16:42:05 +0000
Julian Scarfe <julian.scarfe@ntlworld.com>
Re: Vacuum verbose output? 
Hi,

On Tue, Jan 14, 2003 at 12:19:39PM +0100, pascal bï¿½rest wrote:
> is it possible to estimate the time to restore a database from a dump ?
> the dump is  85 Mo, the base is 850 Mo, we have 512 Mo RAM and a 
> pentium 3 1.13 GHz.
No idea.

> we are running for about 16 hours (20:00 CPU time) but it's really 
> slow, and the database is actually 365 Mo restored.
Did You disabled fsync for restore?

so long,
-tb
-- 
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

Tue, 14 Jan 2003 18:07:13 +0100
Thomas Beutin <tyrone@laokoon.IN-Berlin.DE>
Re: time to restore a database
At 12:19 PM 1/14/03 +0100, pascal bérest wrote:
>is it possible to estimate the time to restore a database from a dump ?
>the dump is  85 Mo, the base is 850 Mo, we have 512 Mo RAM and a pentium 3 
>1.13 GHz.
>we are running for about 16 hours (20:00 CPU time) but it's really slow, 
>and the database is actually 365 Mo restored.

Well you could look at your iostat values (to check IO bandwidth usage). 
Might give you some clues, you should have a good guesstimate after a few 
minutes into the restore into a test db.

Hope that helps,
Link.


Wed, 15 Jan 2003 01:51:56 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: time to restore a database
On 14 Jan 2003 10:54:29 -0500, Robert Treat
<xzilla@users.sourceforge.net> wrote:
>Changed = the number of pages that had to be modified.

This counts only pages changed due to heap tuple header updates
(FrozenTransactionId, hint bits).  It does not count page changes due
to removal of dead tuples, cf. Julian's log:

| NOTICE:  Pages 342: Changed 0, Empty 0; Tup 8325: Vac 838, Keep 0, UnUsed 5614.
| [...]
| NOTICE:  Pages 361: Changed 0, Empty 0; Tup 8229: Vac 1111, Keep 0, UnUsed 5981.

I don't know whether this is intentional or just an oversight.  If it
is the latter, let me know and I'll send a patch.

Servus
 Manfred

Wed, 15 Jan 2003 10:42:41 +0100
Manfred Koizar <mkoi-pg@aon.at>
Re: Vacuum verbose output?
>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> 100 is definitely too small for max_fsm_relations; we've bumped that
TL> default to 1000 for the next release.  Given that you've got a
TL> nearly-100k-page table, 10000 for max_fsm_pages is probably much too
TL> small also.

>> What are the implications/effects of increasing them?

TL> Shared memory usage gets bigger.  I think the max_fsm_pages multiplier
TL> is 6 bytes per slot, so you could make it a million, or ten, without
TL> stressing modern machines.  I'd try 1000/1000000 for starters.

Just a followup to this tip, Tom...

I implemented this change last week (set max_fsm_relations = 500000),
during my switchover from RAID5 to RAID10.  After my dump/reload and
vacuum analyze, things settled down to a good clip.  And my nightly
vacuum analyze runs have held to a pretty constant time.  Usually
after a full vacuum or reload the nightly vacuums start taking longer
after about 4 or 5 days.  (I also vacuum some tables hourly, every few
hours, etc. depending on activity).

So basically, this one optimization has significantly improved my disk
usage by not making so many empty pages, and keeps performance good by
not having sparse tables.

My largest table has 115M rows in it (two integers and a char(4)).

Thanks for the tip!

-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

28 Jan 2003 11:56:40 -0500
Vivek Khera <khera@kcilink.com>
Re: Vacuum verbose output?