Re: autovacuum issue after upgrade to 9.0.1

On Tue, Mar 15, 2011 at 02:38:37PM -0400, George Woodring wrote:
> We recently upgraded from 8.3.something to 9.0.1. With 9.0.1, we have a
> huge spike in vacuums every 8 days only on one of our DB servers.

Is the one affected DB server part of a group of servers you would expect to
behave similarly (same schema, similar transaction rate, etc), or is it fairly
different from other servers not exhibiting the problem?

Did you upgrade via pg_upgrade, or dump+reload?

> We go
> from approx 20 vacuums every 5 minutes to 350 per 5 minutes. This lasts for
> several hours, then stops. I have attached a graph that shows the
> occurrence. I am assuming that it needs to vacuum all of my tables to avoid
> some sort of wrap around counter.

Yes. From http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html:

VACUUM normally skips pages that don't have any dead row versions, but those
pages might still have row versions with old XID values. To ensure all old
XIDs have been replaced by FrozenXID, a scan of the whole table is
needed. vacuum_freeze_table_age controls when VACUUM does that: a whole table
sweep is forced if the table hasn't been fully scanned for
vacuum_freeze_table_age minus vacuum_freeze_min_age transactions. Setting it
to 0 forces VACUUM to always scan all pages, effectively ignoring the
visibility map.

> I am wondering what is the best way to
> make autovacuum spread this out so it will not be quite a big of a hit. I
> we did not see this with 8.3 and I kept the setting the same after the
> upgrade.

Stagger manual VACUUMs of every table across a period of eight days, running
them like "SET vacuum_freeze_table_age = 0; VACUUM sometable;". You'll only
need to do this once. Having done so, pg_class.relfrozenxid will no longer be
clustered in a narrow range. From then, autovacuum will spread out these
full-table VACUUMs according to the pattern you set into motion.

Also consider increasing vacuum_freeze_table_age and autovacuum_freeze_max_age
to enlarge the period of these full-table VACUUMs.

You wouldn't have seen this with 8.3, because the partial-table VACUUM
optimization appeared starting in 8.4.

nm