On Mon, Jan 5, 2009 at 7:46 AM, Scot Kreienkamp <SKreien@xxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
