Re: Vacuum problems

Scott,

Would the "alter user postgres set statement_timeout=0;" be a permanent
change? I ask because our application is using that for its login to
the database. (No lectures please, I inherited the system that way. I
already read the riot act to our developers about that.) If so I'll
have to set it back after the vacuum is done.

FYI, when I inherited the system it was doing nightly vacuum fulls. It
was that way for several months. If that causes bloated indexes, then
that's fairly likely a problem I have. Sounds like I should quit
running vacuum fulls altogether except maybe once or twice per year.

I'll try adding a reindex for tonight's vacuum run.

Thanks,

Scot Kreienkamp
La-Z-Boy Inc.
skreien(at)la-z-boy(dot)com

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Monday, January 05, 2009 11:35 AM
To: Scot Kreienkamp
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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.