Skip site navigation (1) Skip section navigation (2)

Re: Vacuum stats interpreted?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Boes <jboes(at)nexcerpt(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Vacuum stats interpreted?
Date: 2003-11-26 23:51:33
Message-ID: 18578.1069890693@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
Jeff Boes <jboes(at)nexcerpt(dot)com> writes:
> At some point in time, tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
>> UnUsed is the number of empty line-pointer slots.  At 4 bytes apiece,
>> this would have to vastly exceed the number of live tuples before you
>> should worry much.

> For which values of "vastly"? I have a small table (1-2k rows) which has a ratio
> of UnUsed:Tuples of 50-500.

That sounds like a lot to me too.  You should probably VACUUM FULL and
then try to increase the frequency of regular vacuums to cut down on
the accumulation of deadwood.

> The table in question has a ratio of about 10 or 11:1.

It did?  I saw about 1:1:

>> INFO:  Pages 3886: Changed 0, Empty 0; Tup 180175: Vac 122, Keep 26437, UnUsed 135721.

which is why I didn't feel a need to panic about it.

> For some tables (not this one), we find that it significantly improves
> performance (of non-indexed queries) to pg_dump and reload the table
> periodically. I've been asked to try to quantify (from these vacuum numbers)
> when we can predict that a dump-and-reload would be valuable.

For non-indexed scans I would think that the fraction of free space
(hence, useless I/O) would be the number you are after.  VACUUM does not
really offer this, but see contrib/pgstattuple.

			regards, tom lane

In response to

pgsql-admin by date

Next:From: Rudi StarcevicDate: 2003-11-27 04:51:04
Subject: Re: pg_lo_import alternative ...
Previous:From: Michal ZaborowskiDate: 2003-11-26 22:58:46
Subject: Re: Size on Disk

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group