Re: Vacuum problems

On Mon, Jan 5, 2009 at 7:46 AM, Scot Kreienkamp <SKreien(at)la-z-boy(dot)com> wrote:
> Hi everyone…
>
>
>
> I have a database that is currently about 25 gigs on my primary DB server
> running Postgres 8.2.9, and two others that are less than 1 gig apiece. The
> DB server is a quad proc, quad core, 64 gigs of memory, 5 drive RAID5 array,
> so it has plenty of horsepower. Until about three weeks ago I was running a
> nightly vacuum analyze and a vacuum full analyze once per week.

Did you have a compelling reason for running vacuum full? It's
generally discouraged unless you've got a usage pattern that demands
it. If you are running vacuum full you likely have bloated indexes,
so you might need to reindex the db as well.

> This is what I was running for the vacuum full command:
>
> vacuumdb -a -e -f -z -v -U postgres
>
>
>
> The nightly vacuums have been working flawlessly, but about three weeks ago
> the vacuum full started failing. It was taking about 5-10 minutes normally,
> but all of a sudden it started hitting the command timeout that I have set,
> which is at 60 minutes.

Since I assume vacuum is running under the superuser account you can try this:

alter user postgres set statement_timeout=0;

To give it all the time it needs to finish.

> I thought that it may be a corrupt table or a large
> amount of content had been deleted from a database, so I built a script to
> loop through each database and run a vacuum full analyze on each table
> individually thinking I would find my problem table. The script finished in
> 5 minutes!

It might be that the previous vacuum full cleaned up enough stuff that
the next one ran faster. But again, vacuum full is usually a bad idea
as regular maintenance.