Re: postgresql meltdown on PlanetMath.org - Mailing list pgsql-performance

From Tom Lane
Subject Re: postgresql meltdown on PlanetMath.org
Date
Msg-id 6661.1047917465@sss.pgh.pa.us
Whole thread Raw
In response to Re: postgresql meltdown on PlanetMath.org  (Greg Stark <gsstark@mit.edu>)
Responses Re: postgresql meltdown on PlanetMath.org  (Chris Sutton <chris@smoothcorp.com>)
List pgsql-performance
Greg Stark <gsstark@mit.edu> writes:
> Is there any easy way to check an existing table for lost free space?

contrib/pgstattuple gives a pretty good set of statistics.  (I thought
VACUUM VERBOSE printed something about total free space in a table,
but apparently only VACUUM FULL VERBOSE does.  Maybe should change
that.)

> Is there any way vauum could do this check and print a warning suggesting
> using vaccuum full and/or increasing fsm parameters if it finds such?

In CVS tip, a whole-database VACUUM VERBOSE gives info about the free
space map occupancy, eg

INFO:  Free space map: 224 relations, 450 pages stored; 3776 total pages needed.
        Allocated FSM size: 1000 relations + 20000 pages = 178 KB shared mem.

If the "pages needed" number is drastically larger than the allocated
FSM size, you've got a problem.  (I don't think you need to panic if
it's just a little larger, though.  10X bigger would be time to do
something, 2X bigger maybe not.)

            regards, tom lane

pgsql-performance by date:

Previous
From: Greg Stark
Date:
Subject: Re: postgresql meltdown on PlanetMath.org
Next
From: Chris Sutton
Date:
Subject: Re: postgresql meltdown on PlanetMath.org