Re: large table vacuum issues

On Jan 5, 2008 5:38 AM, Ed L. <pgsql(at)bluepolka(dot)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/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar