Skip site navigation (1) Skip section navigation (2)

Re: Proposal: vacuum and autovacuum parameters to control freezing

From: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgreSQL(dot)org>,"Alvaro Herrera" <alvherre(at)commandprompt(dot)com>,"Simon Riggs" <simon(at)2ndquadrant(dot)com>
Subject: Re: Proposal: vacuum and autovacuum parameters to control freezing
Date: 2006-11-05 09:52:36
Message-ID: 454DB464.3090305@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Tom Lane wrote:

> * It might seem that there's no point in per-table adjustment of
> critical_age, since only the system-wide maximum means anything for
> resource consumption.  I'm not so sure though --- for a really large
> table, the time needed to finish vacuuming it could be significant,
> meaning it would need a lower critical age than other tables.  With the
> current one-process-at-a-time autovac infrastructure, this probably
> isn't very important, but we've been talking about allowing multiple
> parallel autovacuums specifically to deal with the problem of some
> tables being much larger than others.

I think a global critical_age parameter is just fine. If you have one 
huge table that takes a long time to vacuum, just adjust critical_age so 
that there's enough time for the huge table vacuum to finish before 
wrap-around. That means that other smaller tables are vacuumed more 
frequently than would otherwise be necessary, but that's not a big deal 
if the other tables really are much smaller.

> pg_autovacuum.freeze_distance: per-table vacuum_freeze_distance setting
> for autovacuum to use.

Shouldn't this be used for manual vacuums as well?

> I'd propose default values of 200 million for autovacuum_freeze_limit
> and half that for vacuum_freeze_distance, resulting in a maximum pg_clog
> size of 50MB and forced autovacs about every 100 million transactions.

Sounds fine to me.

> One minor point is that while the values of these variables have to have
> sane relationships to each other, the GUC infrastructure doesn't really
> allow us to enforce such a constraint directly (the behavior would be
> too dependent on which variable got set first).  I'd suggest making
> vacuum just silently limit the effective freeze_distance to not more
> than half of the system's autovacuum_freeze_limit, rather than trying
> to enforce any relationship within GUC.

Makes sense.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-hackers by date

Next:From: Dr. Ernst MolitorDate: 2006-11-05 12:39:05
Subject: WITH SYSID dropped
Previous:From: Simon RiggsDate: 2006-11-05 09:21:05
Subject: Re: Proposal: vacuum and autovacuum parameters to control freezing

pgsql-patches by date

Next:From: Heikki LinnakangasDate: 2006-11-05 12:35:37
Subject: Re: WIP patch for tuple freezing issues
Previous:From: Simon RiggsDate: 2006-11-05 09:21:05
Subject: Re: Proposal: vacuum and autovacuum parameters to control freezing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group