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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
