Re: Vacuum Full is *hideously* slow!

On Sat, Nov 15, 2008 at 2:36 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> Hi.
>
> Per this thread:
> http://archives.postgresql.org/pgsql-general/2008-11/msg00608.php -- I
> think I understood that the time had come for my db to have a VACUUM
> FULL. (Regular autovacuum etc is working)
>
> I know a full vacuum is slow. A simple google search had suggested so.
>
> But I had no idea it would take HOURS! I started the process against a
> 5GB database (about 8.5 million rows in all) and it went on for more
> than an hour. I had to kill the process.

Your machine must have a pretty slow I/O subsystem. Recently our main
production database, which is in the 25Gig range, had a few rogue
queries (updates without where clauses, gotta love'em!) that bloated a
few tables from 300,000 rows to 300,000 live and about 10,000,000 dead
rows. We took our system down late at night for maintenance and
vacuum fulled then reindexed the whole thing, and it took
approximately 20 minutes to run.

Note that you really should take your app offline if you're vacuuming
full any tables, since it locks them anyway, and there's no reason for
users to sit and stare at an hour glass waiting for a response that
isn't coming for quite some time, as well as producing more load on a
db that's already working pretty hard.

> I am now reindexing just to be sure.

Always a good idea after a vacuum full, as a vacuum full can bloat indexes.

> Is this normal? If a vacuum full takes hours or even days then what's
> the point?

Define Normal (sorry, that's a favorite book of mine, I really couldn't resist).

Normal depends a lot on your server and the size of the tables being
vacuumed. Since my main db servers have 12 spindles under the DB and
8 cores to run on, I can run vacuum fulls on several tables at once
and get the time down to about 5 to 8 minutes for the whole db. If
I'm in a hurry. I'm usually not.

If you're running on a single 7200RPM SATA drive don't expect it to
complete a vacuum on a good sized dataset as fast as a lot of drives
under a fast RAID controller. Vacuum is VERY I/O dependent.

> I read here - http://archives.postgresql.org/pgsql-performance/2005-07/msg00375.php
> - that it's better to drop the indices, then vacuum, and then recreate
> the indices. Is this true? This is also a bad decision for production
> servers, but would this be better?

Yep, it is. Since the indexes are likely to get bloated anyway, you
might as well just drop them and recreate them. OR cluster your tables
on your favorite index, which will all free all the dead space plus it
makes the table faster for things ordered by that index.

The real question is what led to you needing vacuum full instead of
regular vacuum, and what are you doing to prevent it in the future.