Thread: Autovacuum loose ends

Autovacuum loose ends

From
Tom Lane
Date:
I've applied Alvaro's latest integrated-autovacuum patch.  There are
still a number of loose ends to be dealt with before beta, though:

* Not all the functionality of the current contrib code seems to have
made it in.  In particular I noted the "sleep scaling factor" is
missing, as well as the options to use nondefault vacuum_cost_delay
settings.  (I'm not sure how important the sleep scale factor is,
but the vacuum cost options seem pretty critical for practical use.)
There may be other stuff to move over; Matthew or someone more familiar
than I with the contrib version needs to take a look.  (I have refrained
from removing the contrib module until we're sure we have extracted
everything from it.)

* The code does not make a provision to ignore temporary tables.
Although vacuum.c and analyze.c will disregard the request to touch
such tables, it'd probably be better to recognize the situation further
upstream.  In particular it seems that autovacuum will continually throw
ANALYZE requests for a temp table due to lack of stats.

* ANALYZE also refuses to do anything with pg_statistic itself, which
is another case that may need special treatment to avoid useless cycles.

* For that matter I'm unconvinced that it's a good idea to try to force
the pgstat DB to pick up every table in every database.  If there's no
entry it's because the table is not getting modified, and therefore it
seems to me that we can just leave well enough alone.  The code really
is not very good about doing nothing where nothing is called for ;-)

* The code ignores datallowconn and therefore will periodically vacuum
template0.  I've got mixed emotions about this --- it could save
someone's bacon if they failed to properly VACUUM FREEZE a template
database, but in 99.99% of installations it's just wasted cycles.
Maybe it'd make sense to perform XID-wraparound-prevention vacuuming,
but not anything more, in a template DB.  Thoughts?

* Or actually, it would vacuum template0, except that since no regular
backend ever connects to template0, there will be no stats DB entry for
it and so the loop in AutoVacMain will ignore it.  This is definitely
BAD as it means that a database that's not been touched since postmaster
start will never be vacuumed, not even for XID wraparound prevention.
That test needs to be weakened.

* I'm still pretty concerned about the handling of shared catalogs.
AFAICS the current pgstats infrastructure simply gets this wrong,
meaning that shared catalogs will not get adequate vacuuming.  We need
to fix that.

* As Alvaro noted, the default parameter settings need a lookover.
What is in the patch is not what was the default in the contrib module,
but the contrib defaults seem awfully passive.

* The documentation badly needs work.  I committed some minimal
additions to runtime.sgml and catalogs.sgml, but the chapter about
routine maintenance needs a section added about how to use autovac.
        regards, tom lane


Re: Autovacuum loose ends

From
"Matthew T. O'Connor"
Date:
This is great news!  I will do what I can to continue improving the code 
and address these concerns as best I can.  Many of the items below will 
need to be addressed by Alvaro, but I will comment where I think I have 
something useful to say :-)

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:
>
>* Not all the functionality of the current contrib code seems to have
>made it in.  In particular I noted the "sleep scaling factor" is
>missing, as well as the options to use nondefault vacuum_cost_delay
>settings.  (I'm not sure how important the sleep scale factor is,
>but the vacuum cost options seem pretty critical for practical use.)
>There may be other stuff to move over; Matthew or someone more familiar
>than I with the contrib version needs to take a look.  (I have refrained
>from removing the contrib module until we're sure we have extracted
>everything from it.)
>  
>

I will take a look for missing features, thanks for not removing it from 
contrib yet. 

As for the sleep factor I'm not sure it makes sense.  It was initially 
put in as a way to prevent autovacuum from running more than X% of the 
time. However, I think the better answer these days is to use the vacuum 
delay settings.

Speaking of which, I think I mentioned this to Alvaro, but I guess it 
just didn't make it in.  The pg_autovacuum table should have a few 
additional columns that allow setting vacuum delay settings on a per 
table basis.  I also think that there should be GUC settings for the 
default autovacuum delay settings which an admin might want to be 
separate from the system wide default vacuum delay settings.

>* The code does not make a provision to ignore temporary tables.
>Although vacuum.c and analyze.c will disregard the request to touch
>such tables, it'd probably be better to recognize the situation further
>upstream.  In particular it seems that autovacuum will continually throw
>ANALYZE requests for a temp table due to lack of stats.
>  
>

Does the stats system track data about temp tables?  If it doesn't then 
autovacuum won't try to vacuum them.  Will take a look.

>* ANALYZE also refuses to do anything with pg_statistic itself, which
>is another case that may need special treatment to avoid useless cycles.
>  
>

Should be easy enough to tell autovacuum to ignore this table specifically.

>* For that matter I'm unconvinced that it's a good idea to try to force
>the pgstat DB to pick up every table in every database.  If there's no
>entry it's because the table is not getting modified, and therefore it
>seems to me that we can just leave well enough alone.  The code really
>is not very good about doing nothing where nothing is called for ;-)
>  
>

I think in a production environment, this won't be an issue, but in a 
development situation where the postmaster is getting stopped and 
started fairly often, it could be an issue.  Actually, if the stats 
system doesn't reset it's data on postmaster restart, this shouldn't be 
a problem.  Any thoughts on changing this default?

>* The code ignores datallowconn and therefore will periodically vacuum
>template0.  I've got mixed emotions about this --- it could save
>someone's bacon if they failed to properly VACUUM FREEZE a template
>database, but in 99.99% of installations it's just wasted cycles.
>Maybe it'd make sense to perform XID-wraparound-prevention vacuuming,
>but not anything more, in a template DB.  Thoughts?
>  
>

Sounds like a good idea.  Bacon conservation is clearly one of the goals 
of autovacuum.

>* Or actually, it would vacuum template0, except that since no regular
>backend ever connects to template0, there will be no stats DB entry for
>it and so the loop in AutoVacMain will ignore it.  This is definitely
>BAD as it means that a database that's not been touched since postmaster
>start will never be vacuumed, not even for XID wraparound prevention.
>That test needs to be weakened.
>
>* I'm still pretty concerned about the handling of shared catalogs.
>AFAICS the current pgstats infrastructure simply gets this wrong,
>meaning that shared catalogs will not get adequate vacuuming.  We need
>to fix that.
>  
>

This was handled in the contrib version by only vacuuming shared 
catalogs inside template1, however it would then analyze those tables in 
each and every database.  Is there a reason this solution is not 
adequate?  Or perhaps this concept doesn't translate to the integrated 
version?

>* As Alvaro noted, the default parameter settings need a lookover.
>What is in the patch is not what was the default in the contrib module,
>but the contrib defaults seem awfully passive.
>  
>

Alvaro and I talked about this.  I suggested these as the new defaults 
as there seemed to be a consensus that the defaults in the contrib  
version were not very useful for most people.  Hopefully these defaults 
still a bit conservative, but useful.

>* The documentation badly needs work.  I committed some minimal
>additions to runtime.sgml and catalogs.sgml, but the chapter about
>routine maintenance needs a section added about how to use autovac.
>

I promised Alvaro that I would do all the documentation.  I will work on 
it in the next few days now that the patch has been applied. 

Thanks!

Matthew O'Connor



Re: Autovacuum loose ends

From
Alvaro Herrera
Date:
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:

Thanks, and again sorry for the bugs.  The code for shutting the whole
thing down was not easy for me to understand -- I think it should be
better documented.  I can send a src/backend/postmaster/README file if
you think it's worth it; I'd document how to create processes, how to
handle shutdown, and how does the signalling mechanism work.  (It took
me at least an hour to figure the signal handling out, and as you see I
still had gross misunderstadings.)


> * The code does not make a provision to ignore temporary tables.
> Although vacuum.c and analyze.c will disregard the request to touch
> such tables, it'd probably be better to recognize the situation further
> upstream.  In particular it seems that autovacuum will continually throw
> ANALYZE requests for a temp table due to lack of stats.

Oh, is that right?  Actually in the end I forgot about temp tables so I
didn't handle them specially, but now I remember that when I started
looking at Matthew's integration code I thought that temp tables should
be analyzed if they happen to have a lot of new tuples, so that the
planner would have good stats about them.


> * For that matter I'm unconvinced that it's a good idea to try to force
> the pgstat DB to pick up every table in every database.  If there's no
> entry it's because the table is not getting modified, and therefore it
> seems to me that we can just leave well enough alone.  The code really
> is not very good about doing nothing where nothing is called for ;-)

Hmm.  The problem is that the table may merit a first ANALYZE, and in a
second run we need to know that another one is not needed.  How would we
know that, if we don't keep track on it in the pgstat DB?  Keeping no
info about a table seems problematic to me.

Also, remember that there were mentions of changing wraparound Xid to be
kept track of on a per-table basis, instead of per-database (for 8.2 I
assume).  If this happens we will _need_ to check every table.


> * The code ignores datallowconn and therefore will periodically vacuum
> template0.  I've got mixed emotions about this --- it could save
> someone's bacon if they failed to properly VACUUM FREEZE a template
> database, but in 99.99% of installations it's just wasted cycles.
> Maybe it'd make sense to perform XID-wraparound-prevention vacuuming,
> but not anything more, in a template DB.  Thoughts?
> 
> * Or actually, it would vacuum template0, except that since no regular
> backend ever connects to template0, there will be no stats DB entry for
> it and so the loop in AutoVacMain will ignore it.  This is definitely
> BAD as it means that a database that's not been touched since postmaster
> start will never be vacuumed, not even for XID wraparound prevention.
> That test needs to be weakened.

See, that's what I'm talking about :-)  No information about an object
is a problem.  Now, I don't think it's a problem to periodically vacuum
template0, because it will connect to it and quickly realize that no
work is needed.  OTOH I think you can argue that we document that
datallowcon=false databases should be frozen and thus we can just assume
that they don't need vacuuming.  However this doesn't seem safe -- it'll
quickly end up in the "PostgreSQL gotchas" section.

Maybe we could pick the first database with no entry in pgstat, and
process that.  After it's processed, pgstat will have complete data
about it.

Another idea would be keeping a per-database dead tuple counter, or some
other metric, and use that as a parameter in choosing what database to
vacuum.  The current test (last autovac start time) is certainly very
naive.

Yet another idea is to keep track of current Xid as of the last autovac
start, and compare that with the current Xid, in order to check for
wraparound.  Not sure if it's possible to check current Xid without
connecting to a database first.


> * I'm still pretty concerned about the handling of shared catalogs.
> AFAICS the current pgstats infrastructure simply gets this wrong,
> meaning that shared catalogs will not get adequate vacuuming.  We need
> to fix that.

Maybe we can store them in pgstat in a pseudo-database with Oid=0, and
special case them everywhere.  Where do we store pg_autovacuum values?
Or do we dictate that they can only use default parameters from GUC?


> * As Alvaro noted, the default parameter settings need a lookover.
> What is in the patch is not what was the default in the contrib module,
> but the contrib defaults seem awfully passive.

Yeah, the values you saw in the patch were suggested by Matthew.  I had
the contrib module's values originally.


> * The documentation badly needs work.  I committed some minimal
> additions to runtime.sgml and catalogs.sgml, but the chapter about
> routine maintenance needs a section added about how to use autovac.

Matthew is on that, I think.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Right now the sectors on the hard disk run clockwise, but I heard a rumor that
you can squeeze 0.2% more throughput by running them counterclockwise.
It's worth the effort. Recommended."  (Gerry Pourwelle)


Re: Autovacuum loose ends

From
Tom Lane
Date:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Oh, is that right?  Actually in the end I forgot about temp tables so I
> didn't handle them specially, but now I remember that when I started
> looking at Matthew's integration code I thought that temp tables should
> be analyzed if they happen to have a lot of new tuples, so that the
> planner would have good stats about them.

You can't analyze temp tables of other sessions at all (and analyze.c
won't try) because you can't be certain of being able to access their
data.  The valid data might only exist inside the local buffers of the
owning backend.  Same goes for VACUUM --- basically, autovac has to keep
its fingers off temp tables altogether.  (See nearby discussion with
Tom O'Connell for graphic evidence.)

>> * For that matter I'm unconvinced that it's a good idea to try to force
>> the pgstat DB to pick up every table in every database.  If there's no
>> entry it's because the table is not getting modified, and therefore it
>> seems to me that we can just leave well enough alone.  The code really
>> is not very good about doing nothing where nothing is called for ;-)

> Hmm.  The problem is that the table may merit a first ANALYZE, and in a
> second run we need to know that another one is not needed.  How would we
> know that, if we don't keep track on it in the pgstat DB?

If it's being touched at all, then it will get into the pgstat DB
because of the actions of regular backends.  I am questioning the need
for autovac to force things into the pgstat DB when they are evidently
not being used otherwise.

(This argument becomes stronger if we don't reset the stats contents
at postmaster start, which is another open issue.  I think we probably
do need to toss the old stats file after a WAL recovery, but maybe it
need not happen otherwise.)

> Also, remember that there were mentions of changing wraparound Xid to be
> kept track of on a per-table basis, instead of per-database (for 8.2 I
> assume).  If this happens we will _need_ to check every table.

Exactly ... whether it is in pgstat or not.  That does not translate to
a need to force pgstat entries to be made.

>> * Or actually, it would vacuum template0, except that since no regular
>> backend ever connects to template0, there will be no stats DB entry for
>> it and so the loop in AutoVacMain will ignore it.  This is definitely
>> BAD as it means that a database that's not been touched since postmaster
>> start will never be vacuumed, not even for XID wraparound prevention.
>> That test needs to be weakened.

> See, that's what I'm talking about :-)

Yeah, but you're drawing the wrong conclusion.  I'm saying that for a DB
or table that is not present in pgstat, it is reasonable to assume it is
not being used, and so our only responsibility is to prevent XID
wraparound on it --- which we can determine from the pg_database entry
(or pg_class entry if per-table wrap management happens).  We do not need
to force a pgstat entry to be created, and we should not try.

> Another idea would be keeping a per-database dead tuple counter, or some
> other metric, and use that as a parameter in choosing what database to
> vacuum.  The current test (last autovac start time) is certainly very
> naive.

Yeah, keeping per-database totals of the dead tuple counts would help,
and would cost little inside the stats collector AFAICS.

> Not sure if it's possible to check current Xid without
> connecting to a database first.

We could trivially expand the database flat file to include its
datfrozenxid, and any other fields we need from pg_database.

>> * I'm still pretty concerned about the handling of shared catalogs.

> Maybe we can store them in pgstat in a pseudo-database with Oid=0, and
> special case them everywhere.

Yeah, that's what I suggested before.  I haven't thought of any holes in
the idea yet.  The "special casing" shouldn't be hard --- you can just
use the same info set up for the Relation's rd_lockInfo.
        regards, tom lane


Re: Autovacuum loose ends

From
Tom Lane
Date:
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> Speaking of which, I think I mentioned this to Alvaro, but I guess it 
> just didn't make it in.  The pg_autovacuum table should have a few 
> additional columns that allow setting vacuum delay settings on a per 
> table basis.  I also think that there should be GUC settings for the 
> default autovacuum delay settings which an admin might want to be 
> separate from the system wide default vacuum delay settings.

I was thinking GUC settings only; is there a real use-case for
table-specific delay parameters?  ISTM the point of the delay parameters
for autovac is to put a lid on its impact on interactive response.  Seen
in that light, you do not care exactly which table it's hitting at the
moment.

>> * I'm still pretty concerned about the handling of shared catalogs.

> This was handled in the contrib version by only vacuuming shared 
> catalogs inside template1, however it would then analyze those tables in 
> each and every database.  Is there a reason this solution is not 
> adequate?

The problem is that now that we've invented the default postgres
database, it becomes more plausible to think about installations that
haven't got a template1 at all.  I'd prefer a solution that does not
assume the presence of any specific database.  ISTM reasonable to
process the shared catalogs symmetrically in every DB: look to see
if they need vacuuming or not.  The problem (which was also a problem
for the contrib version) is that the stats system fails to maintain
a single set of stats for a shared catalog --- operations get counted
under whichever DB they were issued from.  This means that autovac
will underestimate the need for vacuuming of a shared catalog, since
no matter where it looks from, it will see only a portion of the
true update activity.
        regards, tom lane


Re: Autovacuum loose ends

From
"Matthew T. O'Connor"
Date:
Tom Lane wrote:

>"Matthew T. O'Connor" <matthew@zeut.net> writes:
>  
>
>>Speaking of which, I think I mentioned this to Alvaro, but I guess it 
>>just didn't make it in.  The pg_autovacuum table should have a few 
>>additional columns that allow setting vacuum delay settings on a per 
>>table basis.  I also think that there should be GUC settings for the 
>>default autovacuum delay settings which an admin might want to be 
>>separate from the system wide default vacuum delay settings.
>>    
>>
>
>I was thinking GUC settings only; is there a real use-case for
>table-specific delay parameters?  ISTM the point of the delay parameters
>for autovac is to put a lid on its impact on interactive response.  Seen
>in that light, you do not care exactly which table it's hitting at the
>moment.
>  
>

I was thinking of users that might not want the vacuum delay settings on 
small tables that will normally be vacuumed very quickly.  This isn't a 
very strong argument, but I thought I should mention it.  Also, given 
the projects tenancy towards not giving knobs to users unless we are 
sure they need them, I think GUC only would be OK.

>>This was handled in the contrib version by only vacuuming shared 
>>catalogs inside template1, however it would then analyze those tables in 
>>each and every database.  Is there a reason this solution is not 
>>adequate?
>>    
>>
>
>The problem is that now that we've invented the default postgres
>database, it becomes more plausible to think about installations that
>haven't got a template1 at all.  I'd prefer a solution that does not
>assume the presence of any specific database.  ISTM reasonable to
>process the shared catalogs symmetrically in every DB: look to see
>if they need vacuuming or not.  The problem (which was also a problem
>for the contrib version) is that the stats system fails to maintain
>a single set of stats for a shared catalog --- operations get counted
>under whichever DB they were issued from.  This means that autovac
>will underestimate the need for vacuuming of a shared catalog, since
>no matter where it looks from, it will see only a portion of the
>true update activity.
>

Ok, so without reworking the stats system, I don't see an easy answer to 
this other than autovacuum trying to sum up all the activity it finds in 
all the different databases it looks at, but that seems rather ugly.  
Any thoughts on improving the stats situation here?



Re: Autovacuum loose ends

From
Tom Lane
Date:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Thanks, and again sorry for the bugs.  The code for shutting the whole
> thing down was not easy for me to understand -- I think it should be
> better documented.  I can send a src/backend/postmaster/README file if
> you think it's worth it; I'd document how to create processes, how to
> handle shutdown, and how does the signalling mechanism work.  (It took
> me at least an hour to figure the signal handling out, and as you see I
> still had gross misunderstadings.)

You can if you want, but I don't think that that will ever be
cookie-cutter stuff --- each specialized subjob we've added to date has
had slightly different requirements, and I'd expect the same if we add
more in future.  You have to think about whether they need to connect to
shared memory, whether they can run transactions, which signals they
need to receive, what the postmaster's response should be to either a
normal or non-normal child exit, what the startup and shutdown order
should be, etc.  All of these questions are interrelated ...
        regards, tom lane


Re: Autovacuum loose ends

From
Greg Stark
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:

> I was thinking GUC settings only; is there a real use-case for
> table-specific delay parameters?  ISTM the point of the delay parameters
> for autovac is to put a lid on its impact on interactive response.  Seen
> in that light, you do not care exactly which table it's hitting at the
> moment.

I'm not sure that's true.

ISTM if you have a small table that needs to be vacuumed frequently you
probably don't want it taking longer than necessary to vacuum. It's probably
mostly cached so there wouldn't be much of an i/o hit and even a small sleep
can make a big proportional difference in vacuum run time. You could get into
a situation where it takes longer to vacuum a bunch of such tables than the
frequency you need the vacuuming to taking place.

I think the i/o problem comes when you have large uncached tables. They
probably have a relatively small percentage of the table being updated and so
don't need to be vacuumed frequently. But when they do you need the sleeps to
avoid the i/o problems.

-- 
greg



Re: Autovacuum loose ends

From
Hannu Krosing
Date:
On N, 2005-07-14 at 14:12 -0400, Tom Lane wrote:
> "Matthew T. O'Connor" <matthew@zeut.net> writes:
> > Speaking of which, I think I mentioned this to Alvaro, but I guess it 
> > just didn't make it in.  The pg_autovacuum table should have a few 
> > additional columns that allow setting vacuum delay settings on a per 
> > table basis.  I also think that there should be GUC settings for the 
> > default autovacuum delay settings which an admin might want to be 
> > separate from the system wide default vacuum delay settings.
> 
> I was thinking GUC settings only; is there a real use-case for
> table-specific delay parameters? 

Probably not, unless we also have table-specific load and/or
maintenance-window thresholds above which they are not vacuumed.

Often there are some tables that need to be vacuumed constantly even at
the highest loads (usually small but fast-changing) and some that need
to be vacuumed only at lower activity periods (usually big and changing
at a lower rate).

> ISTM the point of the delay parameters
> for autovac is to put a lid on its impact on interactive response.  Seen
> in that light, you do not care exactly which table it's hitting at the
> moment.

The only difference I can see is if vacuum is hitting the *same* table
as my critical functions or some *other* table. 

If it's hitting the same one, there seems to be larger performance
impact, especially if I'm writing to that table. 

This is just a gut feeling, not anything scientific :)

But I guess that current release of autovacuum can't handle parallel
vacuums anyway, so I just need to do the small/fast vacuums from my own
scripts. 

This should be feasible if I can convince you of safety and usefullness
of my concurrent vacuum patch :)

-- 
Hannu Krosing <hannu@skype.net>



Re: Autovacuum loose ends

From
Ron Mayer
Date:
Tom Lane wrote:
> 
> ISTM the point of the delay parameters
> for autovac is to put a lid on its impact on interactive response.  Seen
> in that light, you do not care exactly which table it's hitting at the
> moment.

Unless the table in question takes a big lock when it's VACUUMed
like tables with GiST indexes do today.

Slowing down one of those vacuums on a larger table has a huge
impact on interactive responses.

With GiST indexes becoming concurrent I assume Vacuum won't lock
anymore on my tables; but I don't know if there are other index
types or condition that might make vacuums take out similar
table-wide locks.
   Ron


Re: Autovacuum loose ends

From
Tom Lane
Date:
Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> Tom Lane wrote:
>> ISTM the point of the delay parameters
>> for autovac is to put a lid on its impact on interactive response.  Seen
>> in that light, you do not care exactly which table it's hitting at the
>> moment.

> Unless the table in question takes a big lock when it's VACUUMed
> like tables with GiST indexes do today.

Well, the issue there is not at the table level, but only while the
individual index is being cleaned.

I suggested a few days ago that we ought not do vacuum delays at all
while processing an index that needs an exclusive lock (this no longer
includes gist, but rtree and to a lesser extent hash still have issues).

If you don't like that, I think you'd pretty much have to invent autovac
delays that are tunable on a *per index* basis, not per table.  That
seems a bit over the top to me; it'd be a nontrivial amount of work to
implement, and there's no evidence that it's better than just removing
the vacuum_delay_point calls in rtree and hash.
        regards, tom lane


Re: Autovacuum loose ends

From
Alvaro Herrera
Date:
On Thu, Jul 14, 2005 at 10:52:56AM -0400, Tom Lane wrote:

Hey,

> * Or actually, it would vacuum template0, except that since no regular
> backend ever connects to template0, there will be no stats DB entry for
> it and so the loop in AutoVacMain will ignore it.  This is definitely
> BAD as it means that a database that's not been touched since postmaster
> start will never be vacuumed, not even for XID wraparound prevention.
> That test needs to be weakened.

I've hacked the whole thing enough that I fixed most of the issues.
However this one I don't know how to handle.  What I need to do is
compare each database's frozen Xid with the current transaction Id.
I can get the frozenxid from the flatfile -- however I don't have
anything with which to compare it.  I tried ReadNewTransactionId(), but
it doesn't work because it tries to acquire a LWLock, which isn't
possible because we don't have a PGPROC before connecting to a database.

I guess I could the Xid from pg_control.  This seems unclean however.
Opinions about doing that?  Better ideas?

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Crear es tan difícil como ser libre" (Elsa Triolet)


Re: Autovacuum loose ends

From
Tom Lane
Date:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I've hacked the whole thing enough that I fixed most of the issues.
> However this one I don't know how to handle.  What I need to do is
> compare each database's frozen Xid with the current transaction Id.
> I can get the frozenxid from the flatfile -- however I don't have
> anything with which to compare it.  I tried ReadNewTransactionId(), but
> it doesn't work because it tries to acquire a LWLock, which isn't
> possible because we don't have a PGPROC before connecting to a database.

> I guess I could the Xid from pg_control.  This seems unclean however.
> Opinions about doing that?  Better ideas?

Getting it from pg_control isn't that bad; the value could be as old as
the last checkpoint, but that should be close enough for this purpose.

The only alternative I can see is for the stats daemon to try to track
recent values of nextXID and include the latest in the stats datafile.
You really wouldn't want to put XID into every stats message, but you
could put it into PgStat_MsgAutovacStart say, so that each autovac run
would seed the XID information for the next run.  On the whole it's not
clear this is cleaner than looking to pg_control.
        regards, tom lane


Re: Autovacuum loose ends

From
Alvaro Herrera
Date:
On Fri, Jul 22, 2005 at 07:37:53PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > I've hacked the whole thing enough that I fixed most of the issues.
> > However this one I don't know how to handle.  What I need to do is
> > compare each database's frozen Xid with the current transaction Id.
> > I can get the frozenxid from the flatfile -- however I don't have
> > anything with which to compare it.  I tried ReadNewTransactionId(), but
> > it doesn't work because it tries to acquire a LWLock, which isn't
> > possible because we don't have a PGPROC before connecting to a database.
> 
> > I guess I could the Xid from pg_control.  This seems unclean however.
> > Opinions about doing that?  Better ideas?
> 
> Getting it from pg_control isn't that bad; the value could be as old as
> the last checkpoint, but that should be close enough for this purpose.

Ok, fair enough.

That makes me wonder however if the test should be heavier.  Right now,
the test is
       /*        * We decide to vacuum at the same point where vacuum.c's        * vac_truncate_clog() would decide
startgiving warnings.        */       age = (int32) (ReadNewTransactionId() - db->frozenxid);       whole_db = (age >
(int32)((MaxTransactionId >> 3) * 3));
 

Now that we are going to test a TransactionId that was current slightly
in the past, maybe it should instead read
       whole_db = (age > (int32) ((MaxTransactionId >> 3) * 4));

so that vac_truncate_clog doesn't start emitting warning just before we
do the vacuum.

-- 
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
FOO MANE PADME HUM