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.
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. 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!
This is what I’m running on each table now in my script:
vacuumdb -d $DB -t $TABLE -e -f -z -v -U postgres
As I understand it, the “vacuumdb –a” command basically does the same thing as my script. So why is it timing out while my script finishes in 5 minutes or less? Is the “vacuumdb –a” command doing something that I’m not?
Now that the holiday season is past I will be upgrading to the latest 8.2 release as soon as possible, but I need to get this figured out first unless it’s a bug that the upgrade fixes.
I’d appreciate any advice or explanations you guys can send my way.
Thanks,
Scot Kreienkamp
La-Z-Boy Inc.
skreien@la-z-boy.com
734-242-1444 ext 6379