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. skre...@la-z-boy.com -----Original Message----- From: Scott Marlowe [mailto:scott.marl...@gmail.com] Sent: Monday, January 05, 2009 11:35 AM To: Scot Kreienkamp Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Vacuum problems On Mon, Jan 5, 2009 at 7:46 AM, Scot Kreienkamp <skre...@la-z-boy.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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
