Greg Williamson <gwilliamson39(at)yahoo(dot)com> wrote:
> I've got an 8.4.11 system that I am relatively new to and I am
> seeing multiple autovac processes kick off on several of the
> largest tables at once and it is causing pain.
>
> Are there any suggestions to
>
> a) quickly relieve the immediate pain
Set autovacuum_cost_limit to a smaller value. (Change the
postgresql.conf file and do a reload.) I would start by cutting the
current value in half.
> and
>
> b) prevent such issues in the future (other than going to manual
> vacuuming on a schedule).
If it is suddenly doing this on lots of big tables at once, it seems
somewhat likely that you've hit the transaction wraparound
protection threshold. Because the vacuum necessary for this can be
painful, and they tend to come at the worst possible time (the more
your workload looks like a really heavy OLTP workload at any given
moment, the higher the probability that this is about to happen), I
always follow a bulk load (like from restoring pg_dump output) with
a VACUUM FREEZE ANALYZE.
You might also want to consider running off-hours vacuums to
supplement autovacuum. Upgrading to a more recent version of
PostgreSQL is likely to help some, too.
-Kevin