Обсуждение: autovacuum issue after upgrade to 9.0.1

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.  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.  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.

Dear George,

  Do you see this issue on 9.0.3, the current and the recommended 9.x version?

Best,
Aleksey

On Tue, Mar 15, 2011 at 11:38 AM, George Woodring
<george.woodring@iglass.net> 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.  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.  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.
> Here are my autovacuum settings:
> autovacuum = on
> log_autovacuum_min_duration = 0
> autovacuum_max_workers = 5
> autovacuum_naptime = 1min
> autovacuum_vacuum_threshold = 100
> autovacuum_analyze_threshold = 100
> autovacuum_vacuum_scale_factor = 0.1
> autovacuum_analyze_scale_factor = 0.05
> autovacuum_freeze_max_age = 200000000
> autovacuum_vacuum_cost_delay = 20ms
> autovacuum_vacuum_cost_limit = -1
>
> Any suggestions would be appreciated.
> Thanks,
> George Woodring
>
> --
> iGLASS Networks
> www.iglass.net
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>

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

I was able to upgrade the machine on Wednesday to 9.0.3 and we saw the spike on Thursday, right on the 8 day schedule.  I will keep my eye out next Friday to see if it happens again.  This will have the whole period on the new version.

--
iGLASS Networks
www.iglass.net