large table vacuum issues

From: "Ed L(dot)" <pgsql(at)bluepolka(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: large table vacuum issues
Date: 2008-01-05 00:38:31
Message-ID: 200801041738.31579.pgsql@bluepolka.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

We are discussing how to partition the table (difficult due to
existing foreign keys in other tables), and archiving/clearing
data.

Are there any other tricks to get it past this large table for
the time being and still get the xid wraparound fix?

TIA.

Ed

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Clodoaldo 2008-01-05 00:45:09 Performance problem. Could it be related to 8.3-beta4?
Previous Message Webb Sprague 2008-01-04 23:17:33 Re: Getting process id of a connection?