Search Postgresql Archives

Re: autovacuum issue after upgrade to 9.0.1

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux