Thread: Re: [HACKERS] Autovacuum loose ends

On Thu, Jul 14, 2005 at 10:52:56AM -0400, Tom Lane wrote:
> I've applied Alvaro's latest integrated-autovacuum patch.  There are
> still a number of loose ends to be dealt with before beta, though:

Updated this patch:

- The stat collector is modified so as to keep shared relations separate
  from regular ones.  Autovacuum understands this.

- Temp tables are completely ignored.

- pg_statistic is ignored for analyze.  It's still candidate for vacuum.

- databases with no stat entry are still ignored, except that they are
  checked for Xid wraparound like any other.  The "oldest" one is chosen
  for vacuum in a particular autovacuum run.

- A database-wide vacuum forces a pg_database flat-file update, so that
  the wraparound check actually works.

- The postmaster's main loop sleeps Min(60, autovacuum_naptime), in
  order to be able to pick naptimes smaller than 60 seconds.  In order
  not to make the loop a busy-wait, I forced a minimum of 1 to that GUC
  var.  Yes, an argument could be made that the minimum could be higher.
  Not sure if we actually want to dictate policy on this.  The minimum
  is there only to prevent the postmaster from using 100% of a CPU the
  whole time.

- Tables with no stat entries are completely ignored.

- The stat collector ignores messages that relate to databases it
  doesn't know about.  This makes it inocuous to issue a database-wide
  vacuum on a template database.  A special case is made for database
  InvalidOid -- an entry for it is created regardless.


Two comments still apply:

- I haven't done anything yet w.r.t. the custom vacuum_delay nor sleep
  scale factor.

- There are still no docs.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Porque Kim no hacia nada, pero, eso sí,
con extraordinario éxito" ("Kim", Kipling)

Alvaro Herrera wrote:

>Two comments still apply:
>
>- I haven't done anything yet w.r.t. the custom vacuum_delay nor sleep
>  scale factor.
>

I don't think we need the sleep scaling factor.  Before we had vacuum
delay settings, it might have been useful as a means of throttling down
the impact of autovacuum, but I think the delay settings are the better
way to go.

As for the custom vacuum_delay settings, Tom Lane's  commented that it
probably wasn't needed.  However I and several others responded saying
that it probably would be useful for certain use cases.  I'm still not
sure how compelling that case is but it seems like a simple addition.
Perhaps this is something we can add for 8.2 if people seem to want it.

>- There are still no docs.
>


I'm on the hook for this and will start working on them next week.

BTW, is there still any reason not to remove the contrib/pg_autovacuum
directory from CVS?

            regards, tom lane

On Fri, Jul 29, 2005 at 11:19:34AM -0400, Tom Lane wrote:
> BTW, is there still any reason not to remove the contrib/pg_autovacuum
> directory from CVS?

I still haven't added custom cost-based delays, but I don't see that as
a showstopper for removing it.  I just went through the CVS log and I
don't see anything else that applies.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos / con todos los humanos acabaré (Bender)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Updated this patch:
> - The stat collector is modified so as to keep shared relations separate
>   from regular ones.  Autovacuum understands this.
>   [etc]

Applied with some fixes --- you had broken the reporting of statistics
for shared tables, for one thing.  Also the patch seemed to be missing
diffs for header files?

It occurs to me that vacuuming to prevent XID wraparound is not the only
reason to do DB-wide vacuums: we also need to keep
pg_database.datvacuumxid from getting too old, else we will have
problems with clog bloat.  We may need to rethink the test used.

            regards, tom lane

On Fri, Jul 29, 2005 at 03:33:09PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Updated this patch:
> > - The stat collector is modified so as to keep shared relations separate
> >   from regular ones.  Autovacuum understands this.
> >   [etc]
>
> Applied with some fixes --- you had broken the reporting of statistics
> for shared tables, for one thing.

Oops :-(  Didn't notice that.

> Also the patch seemed to be missing diffs for header files?

Damn, I generated the diff from within src/backend instead of the root
:-(  Sorry for the inconvenience.

> It occurs to me that vacuuming to prevent XID wraparound is not the only
> reason to do DB-wide vacuums: we also need to keep
> pg_database.datvacuumxid from getting too old, else we will have
> problems with clog bloat.  We may need to rethink the test used.

Hmm.  Will see about it.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"La Primavera ha venido. Nadie sabe como ha sido" (A. Machado)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>> Also the patch seemed to be missing diffs for header files?

> Damn, I generated the diff from within src/backend instead of the root
> :-(  Sorry for the inconvenience.

No problem --- reverse-engineering the changes to function declarations
was simple enough.  But did you have any other changes outside
src/backend?

            regards, tom lane

On Fri, Jul 29, 2005 at 05:46:11PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> >> Also the patch seemed to be missing diffs for header files?
>
> > Damn, I generated the diff from within src/backend instead of the root
> > :-(  Sorry for the inconvenience.
>
> No problem --- reverse-engineering the changes to function declarations
> was simple enough.  But did you have any other changes outside
> src/backend?

Nope, that was it.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)

Alvaro Herrera wrote:

> I still haven't added custom cost-based delays, but I don't see that as
> a showstopper for removing it.  I just went through the CVS log and I
> don't see anything else that applies.

I think you should at least add an autovacuum specific value for
"vacuum_cost_delay" because it turns cost-based vacuum delay on or off. I
believe not many will have vacuum_cost_delay enabled in postgresql.conf, but
will want to enable it for autovacuum.
At least I do.

Best Regards,
Michael Paesold

"Michael Paesold" <mpaesold@gmx.at> writes:
> Alvaro Herrera wrote:
>> I still haven't added custom cost-based delays, but I don't see that as
>> a showstopper for removing it.  I just went through the CVS log and I
>> don't see anything else that applies.

> I think you should at least add an autovacuum specific value for
> "vacuum_cost_delay" because it turns cost-based vacuum delay on or off.

It occurs to me that you could have that today, using the knowledge that
the autovac daemon runs as the bootstrap user: use ALTER USER SET to
attach user-specific vacuum delay settings to that role.  This is a
pretty bletcherous solution, because (a) it requires knowledge of an
undocumented implementation detail and (b) it would interfere with using
that role for normal manual maintenance.  So I agree that a few extra
GUC settings would be better.  But we could get away without 'em.

Along the same lines, it was suggested that we need a way to disable
stats gathering on a per-database basis.  We already have it: you can
use ALTER DATABASE SET to control stats_row_level and stats_block_level
that way.  Neither of the above two objections apply to this usage, so
I think we can mark off that wishlist item as "done".  (Of course, the
soon-to-appear autovac documentation had better mention this trick.)

            regards, tom lane

Tom Lane wrote:
> "Michael Paesold" <mpaesold@gmx.at> writes:
> > Alvaro Herrera wrote:
> >> I still haven't added custom cost-based delays, but I don't see that as
> >> a showstopper for removing it.  I just went through the CVS log and I
> >> don't see anything else that applies.
>
> > I think you should at least add an autovacuum specific value for
> > "vacuum_cost_delay" because it turns cost-based vacuum delay on or off.
>
> It occurs to me that you could have that today, using the knowledge that
> the autovac daemon runs as the bootstrap user: use ALTER USER SET to
> attach user-specific vacuum delay settings to that role.  This is a
> pretty bletcherous solution, because (a) it requires knowledge of an
> undocumented implementation detail and (b) it would interfere with using
> that role for normal manual maintenance.  So I agree that a few extra
> GUC settings would be better.  But we could get away without 'em.
>
> Along the same lines, it was suggested that we need a way to disable
> stats gathering on a per-database basis.  We already have it: you can
> use ALTER DATABASE SET to control stats_row_level and stats_block_level
> that way.  Neither of the above two objections apply to this usage, so
> I think we can mark off that wishlist item as "done".  (Of course, the
> soon-to-appear autovac documentation had better mention this trick.)

I am thinking we should move ahead with what we have now, suggest the
work-arounds, and thensee what use-cases we have for it for later
releases.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>> I think you should at least add an autovacuum specific value for
>>> "vacuum_cost_delay" because it turns cost-based vacuum delay on or off.

> I am thinking we should move ahead with what we have now, suggest the
> work-arounds, and thensee what use-cases we have for it for later
> releases.

I think it's absolutely unquestionable that there is a use-case for
running autovac with different vacuum-delay settings than you would
want to apply to manually issued vacuums.  We don't need to wait for
field experience on that one; we already have it with the contrib
version.

            regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >>> I think you should at least add an autovacuum specific value for
> >>> "vacuum_cost_delay" because it turns cost-based vacuum delay on or off.
>
> > I am thinking we should move ahead with what we have now, suggest the
> > work-arounds, and thensee what use-cases we have for it for later
> > releases.
>
> I think it's absolutely unquestionable that there is a use-case for
> running autovac with different vacuum-delay settings than you would
> want to apply to manually issued vacuums.  We don't need to wait for
> field experience on that one; we already have it with the contrib
> version.

So do we need to add new GUC variables?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Sat, Jul 30, 2005 at 10:57:15AM -0400, Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > >>> I think you should at least add an autovacuum specific value for
> > >>> "vacuum_cost_delay" because it turns cost-based vacuum delay on or off.
> >
> > > I am thinking we should move ahead with what we have now, suggest the
> > > work-arounds, and thensee what use-cases we have for it for later
> > > releases.
> >
> > I think it's absolutely unquestionable that there is a use-case for
> > running autovac with different vacuum-delay settings than you would
> > want to apply to manually issued vacuums.  We don't need to wait for
> > field experience on that one; we already have it with the contrib
> > version.
>
> So do we need to add new GUC variables?

I was thinking in a GUC var for global setting, and a column in
pg_autovacuum for individual, per table setting.  Just one, for the
vacuum_cost_limit parameter; I don't think we really need settable cost
parameters.

A case could be made for setting the vacuum_cost_delay parameter as
well.  Thoughts?

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Es filósofo el que disfruta con los enigmas" (G. Coli)

On Fri, Jul 29, 2005 at 03:33:09PM -0400, Tom Lane wrote:

> It occurs to me that vacuuming to prevent XID wraparound is not the only
> reason to do DB-wide vacuums: we also need to keep
> pg_database.datvacuumxid from getting too old, else we will have
> problems with clog bloat.  We may need to rethink the test used.

I was unable to come up with a reasonable test for this.  How would we
determine what is "too old"?  Of course, I could pick any number from
thin air, if that was what you were thinking.  Going forward (8.2) I
think this should also be handled on a table per table basis, just like
the freeze Xid limit.

OTOH I just saw this comment in createdb():

    /*
     * Normally we mark the new database with the same datvacuumxid and
     * datfrozenxid as the source.  However, if the source is not allowing
     * connections then we assume it is fully frozen, and we can set the
     * current transaction ID as the xid limits.  This avoids immediately
     * starting to generate warnings after cloning template0.
     */

This means that if the user manages to unfreeze a database, disallow
connections, and later use it as a template, we could suffer Xid-
wraparound data loss in the new database.  Should we rethink this?
Sadly, the only interface for disallowing connections is to manually
update pg_database, so it's impossible to raise a warning about it, or
something; and it's quite likely that people will disallow connections
without reading the proper documentation.  (They do such things all the
time).

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"La experiencia nos dice que el hombre peló millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarían al hombre" (Ijon Tichy)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On Fri, Jul 29, 2005 at 03:33:09PM -0400, Tom Lane wrote:
>> It occurs to me that vacuuming to prevent XID wraparound is not the only
>> reason to do DB-wide vacuums: we also need to keep
>> pg_database.datvacuumxid from getting too old, else we will have
>> problems with clog bloat.  We may need to rethink the test used.

> I was unable to come up with a reasonable test for this.  How would we
> determine what is "too old"?

Well, it depends what you think is too much space for pg_clog.  If we
just follow the standard anti-wrap policy, we'll vacuum at least once
every half billion transactions, so pg_clog could be expected to grow
to about 125Mb, which maybe isn't a problem these days.

> OTOH I just saw this comment in createdb():

>     /*
>      * Normally we mark the new database with the same datvacuumxid and
>      * datfrozenxid as the source.  However, if the source is not allowing
>      * connections then we assume it is fully frozen, and we can set the
>      * current transaction ID as the xid limits.  This avoids immediately
>      * starting to generate warnings after cloning template0.
>      */

> This means that if the user manages to unfreeze a database, disallow
> connections, and later use it as a template, we could suffer Xid-
> wraparound data loss in the new database.  Should we rethink this?

I don't think so.  Fooling with a template database is risky in any
case, and the fact that autovacuum might save your bacon (if you are
running autovacuum) doesn't make it less so.

BTW, it strikes me that there is one serious error in the current
autovac logic: it does VACUUM ANALYZE rather than merely VACUUM
when doing XID-wrap protection.  This means that it actively introduces
unfrozen tuples into template databases, which is A Bad Move.  We
should just VACUUM, instead.

> Sadly, the only interface for disallowing connections is to manually
> update pg_database,

As of now, we have a documented way of disallowing connections that
doesn't involve messing with datallowconn, so this argument seems a
lot weaker than it might have awhile back.

            regards, tom lane

Here is another patch for autovacuum:

- vacuum_cost_delay and vacuum_cost_limit can be set per table, as well
  as globally with autovacuum_vacuum_cost_{limit,delay}

- pgstat is reset if recovery is required

- pgstat reset at postmaster start is disabled by default

- Xid-wraparound VACUUM is now FULL without ANALYZE


Note that because of the cost parameters, I changed the vacuum call in a
per-table call instead of passing a list of Oids.  This could be changed
by having two separate lists, one which uses the default values and
other for the rest, but it hardly seems worth the trouble.

(This patch requires catversion bump.)

On Sun, Jul 31, 2005 at 03:15:35PM -0400, Tom Lane wrote:

> BTW, it strikes me that there is one serious error in the current
> autovac logic: it does VACUUM ANALYZE rather than merely VACUUM
> when doing XID-wrap protection.  This means that it actively introduces
> unfrozen tuples into template databases, which is A Bad Move.  We
> should just VACUUM, instead.

True.  Changed in the attached patch.


I think this completes our expectations for 8.1, doesn't it?  Now we
only need the documentation.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)

Alvaro Herrera wrote:

> Here is another patch for autovacuum:
...
> - Xid-wraparound VACUUM is now FULL without ANALYZE

Am I right in my assumption that this VACUUM FULL can happen for any
database, not just a template database?

I think this is a bad idea. Vacuum full is not an option for our and many
other production databases. I suggest that should be a plain VACUUM.

Otherwise I think you have done great job finally integrating auto vacuum
into the backend.

Best Regards,
Michael Paesold

On Mon, Aug 01, 2005 at 09:55:11AM +0200, Michael Paesold wrote:
> Alvaro Herrera wrote:
>
> >Here is another patch for autovacuum:
> ...
> >- Xid-wraparound VACUUM is now FULL without ANALYZE
>
> Am I right in my assumption that this VACUUM FULL can happen for any
> database, not just a template database?

Ah, right.  I think it would be OK if we made it FULL only for
datallowcon=false databases.  OTOH maybe it's not worth at all making
the distinction and we should continue using the previous policy of
issuing only non-FULL VACUUM.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Having your biases confirmed independently is how scientific progress is
made, and hence made our great society what it is today" (Mary Gardiner)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>>> - Xid-wraparound VACUUM is now FULL without ANALYZE
>>
>> Am I right in my assumption that this VACUUM FULL can happen for any
>> database, not just a template database?

> Ah, right.  I think it would be OK if we made it FULL only for
> datallowcon=false databases.  OTOH maybe it's not worth at all making
> the distinction and we should continue using the previous policy of
> issuing only non-FULL VACUUM.

Strikes me as a waste of cycles: the one database *least* likely to be
in need of a VACUUM FULL is template0.

What we perhaps should consider is VACUUM FREEZE, not FULL, when hitting
a template database --- this would maximize the interval before needing
to do it again.

            regards, tom lane

On Sun, Jul 31, 2005 at 07:36:36PM -0400, Alvaro Herrera wrote:

Updated this patch:

> - vacuum_cost_delay and vacuum_cost_limit can be set per table, as well
>   as globally with autovacuum_vacuum_cost_{limit,delay}
>
> - pgstat is reset if recovery is required
>
> - pgstat reset at postmaster start is disabled by default

- Xid-wraparound VACUUM is now FREEZE without ANALYZE, iff the database
  has datallowconn=false or datistemplate=true

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"No hay cielo posible sin hundir nuestras raíces
 en la profundidad de la tierra"                        (Malucha Pinto)

On Fri, Jul 29, 2005 at 03:33:09PM -0400, Tom Lane wrote:

> It occurs to me that vacuuming to prevent XID wraparound is not the only
> reason to do DB-wide vacuums: we also need to keep
> pg_database.datvacuumxid from getting too old, else we will have
> problems with clog bloat.  We may need to rethink the test used.

Hmm.  I have a patch for this, but now that it's ready, I wonder if it's
really needed.  If I understand vacuum_set_xid_limits() correctly, it's
very difficult for the vacuumxid to be far behind the freeze limit.  And
in the case it's actually behind, then there's nothing we can do -- the
only way out is for the user to end the long-running transaction.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar
al alfarero y ver qué formas se pueden sacar del otro" (C. Halloway en
La Feria de las Tinieblas, R. Bradbury)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Hmm.  I have a patch for this, but now that it's ready, I wonder if it's
> really needed.  If I understand vacuum_set_xid_limits() correctly, it's
> very difficult for the vacuumxid to be far behind the freeze limit.

Umm ... they can be close together, or a billion XIDs apart, depending
on whether the FREEZE option was used.

            regards, tom lane

On Tue, Aug 09, 2005 at 11:24:40PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Hmm.  I have a patch for this, but now that it's ready, I wonder if it's
> > really needed.  If I understand vacuum_set_xid_limits() correctly, it's
> > very difficult for the vacuumxid to be far behind the freeze limit.
>
> Umm ... they can be close together, or a billion XIDs apart, depending
> on whether the FREEZE option was used.

Sorry, my point was that vacuumxid is generally going to be higher than
freeze-xid, and where it isn't, a simple vacuum can't fix it.

But now that I think about it, maybe the point is that if a long-running
transaction (a billon-transactions old transaction?) was running when
the last database-wide vacuum was run, then vacuumxid is going to be
older than freeze-xid, so we may need a database-wide vacuum to fix that
even though the freeze-xid is not old enough.

Is that right?

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
We take risks not to escape from life, but to prevent life escaping from us.

Updated this patch again:

> > - vacuum_cost_delay and vacuum_cost_limit can be set per table, as well
> >   as globally with autovacuum_vacuum_cost_{limit,delay}
> >
> > - pgstat is reset if recovery is required
> >
> > - pgstat reset at postmaster start is disabled by default
>
> - Xid-wraparound VACUUM is now FREEZE without ANALYZE, iff the database
>   has datallowconn=false or datistemplate=true

- A database-wide vacuum is also issued if the vacuumxid is found to be
  very old.

Note that I had to add datvacuumxid to the pg_database flat file.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Ciencias políticas es la ciencia de entender por qué
 los políticos actúan como lo hacen"  (netfunny.com)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Updated this patch again:

> - vacuum_cost_delay and vacuum_cost_limit can be set per table, as well
> as globally with autovacuum_vacuum_cost_{limit,delay}
>
> - pgstat is reset if recovery is required
>
> - pgstat reset at postmaster start is disabled by default
>
> - Xid-wraparound VACUUM is now FREEZE without ANALYZE, iff the database
> has datallowconn=false or datistemplate=true
>
> - A database-wide vacuum is also issued if the vacuumxid is found to be
>   very old.

Applied with minor tweaks --- mostly, fixing it so the custom cost
settings are applied for ANALYZE as well as VACUUM.

            regards, tom lane

On Thu, Aug 11, 2005 at 05:13:15PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Updated this patch again:
>
> Applied with minor tweaks --- mostly, fixing it so the custom cost
> settings are applied for ANALYZE as well as VACUUM.

Ok, cool, thanks.  I think this completes the autovacuum work I wanted
to do for 8.1.  AFAIK the only thing that we are still badly missing is
the documentation update.  (Matthew, if you are too busy to write it,
please let me know so I know I have to tackle it.)

For 8.2 my first priority (autovac-related) is to eliminate, or at least
alleviate, the need for database-wide vacuums, by keeping track of Xid
wraparound issues on a per-relation basis.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
Este mail se entrega garantizadamente 100% libre de sarcasmo.