Re: Vacuum Full - Mailing list pgsql-admin

Hi Rafael,

On Wed, Apr 01, 2009 at 06:51:00PM -0300, Rafael Domiciano wrote:

> > > I have some doubts about Vacuum Full. There We go:
> > > 1) The Only thing that Vacuum Full (Only Full, not Analyze) is to clean
> > > "dead space" on the disc, and reorganize the relation at the physical
> > level?
> > > If it's true, so doing this may speed up select's, while the Postgres
> > will
> > > going to do less hit in the disc. I'm right?
> >
> > Generally speaking, vacuum full should not normally be needed.
> > Regular vacuuming should free up enough free space that the table
> > reaches an equilibrium where it has some small percentage of available
> > space (5 to 15% or so) and stay there.
>
> So, why Vacuum Full should not nomally be needed? Vacuum Analyze is enough
> for Maintenance Base?

You should normally be using autovacuum, possibly slightly tuned, to
keep everything nice.

> One more question, If I understood Vacuum Full it's similar to Defrag Tool
> like Defrag Windows. So, like defraging Windows, it's speed up a little (the
> disc is going to read the blocks faster) , Vacuuming Full Postgres should
> have the same behavior?!

VACUUM FULL should be renamed to "I totally bloated my database, please
fix it" or something like that. It is a very intrusive operation
(prevents write access to the table IIRC), so it's nothing for regular
operation. If you need VACUUM FULL, something went wrong before.

Maybe a command name like "UNBLOAT DATABASE" or "REBUILD TABLES"
would be more suited. @Developers: I'm serious. People perceive VACUUM
FULL as just a more intense version of VACUUM.

> > > 2) Doing only Vacuum Analyze I have a enlargement of the parameter
> > > max_fsm_pages. Does it have any impact in the operation?
> > > Should I increment the value in the conf to be so large than the Vacuum
> > > Verbose shows me?
> >
> > If the needed fsm settings need to keep increasing then something is wrong.
>
> The fsm_pages values incresead from 120000 to 320000, and now is around that
> every day.
> On more question, the postgres.conf max_fsm_pages is set to 150000, and
> every vacuum hint me to increase this parameter. Does it have any impact in
> the normal operation of Postgres?

Yes, it takes memory (6 bytes per page). If you have max_fsm_pages too
low, you'll get a bloated database because Postgres cannot keep track of
which pages are useable in a relation - it needs to append at the end!
Then you'll need VACUUM FULL sooner or later.

I've heard a rule of thumb: 65536 pages per GB of data.

> > > 3) There are differences in performace of Vacuum Full between versions
> > 8.1.4
> > > and 8.3.7?
> > > As soon as possible we are going to migrate the Postgres to 8.3.7, just
> > > waiting the finish of the tests of the software in the new version.
> >
> > Yeah, 8.3 is faster.
> >
> > > 4) The right way to run Vacuum and Reindex is: Vacuum and Reindex or
> > Reindex
> > > and Vacuum?
> > > Running Vacuum I have a Index Bloat, right?! So I have to run Reindex
> > afet
> > > Vacuum?!
> > > If true I'll change the script to make first Vacuum and then Reindex.
> >
> > yep, vacuum full, then reindex.

If matters are really worse, it's faster to pg_dump, drop/create and
pg_restore. You'll get a nice fresh database afterwards with no bloat.
I've seen a database perform VACUUM FULL on pg_largeobject for two
weeks! (Only about 200 GB or so of data.)

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de