On Jan 5, 2008 5:38 AM, Ed L. <
pgsql@bluepolka.net> wrote:
We need some advice on how to handle some large table autovacuum
issues. One of our 8.1.2 autovacuums is launching a DB-wide
vacuum on our 270GB database to prevent xid wrap-around, but is
getting hung-up and/or bogged down for hours on a 40gb table and
taking the server performance down with it, apparently due to an
IO bottleneck. The autovac child process becomes completely
unresponsive to SIGTERM/SIGINT; only a sigkill restart with
disabling the autovac daemon gets us back to adequate
performance for now.
Looks like you haven't been vacuuming for a while , have you? because it seems the autovac was disabled but was invoked forcefully to avoid wraparound. If infact the wraparound happens you will lose data. When autovacuum is processing a table it wouldn't take more time than what a normal vacuum would take.
What might help you really is a temporary increase in maint work memory, whats your current setting? how much RAM do you have?, if you can afford more memory, increase it to significantly high value to help speed up the vacuum process. I understand it might impact some other system activity but you need a vacuum and fast, before you lose all data.
You need to get rid of dead rows first and then have a healthy vacuuming schedule , either a daily cron job or autovac, setup as your workload . Prevention is ofcourse better than the cure. ;)
Thanks,
--
Usama Munir Dar
http://www.linkedin.com/in/usamadarConsultant Architect
Cell:+92 321 5020666
Skype: usamadar