Autovacuum loose ends
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
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
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)
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
"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
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?
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
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>
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
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
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
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)
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
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 start giving 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
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:
Ok, here's a patch that deals with some of this:
- The stat collector is modified so as to keep shared relations separate
from regular ones. Also, backends sends messages separately.
Autovacuum takes advantage of this, so it correctly identifies the
appropiate time to operate on a shared relation, irrespective of the
database where they were modified. Note however that it uses each
database's pg_autovacuum settings. This means it could be vacuumed
sooner in one database than another, but I don't think it's a problem.
- Temp tables are completely ignored.
- pg_statistic is completely ignored.
- 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.
Some comments:
- Now that we have a real Xid wraparound check, we could go back to
having any table with no stat entry ignored, which was the original
coding. There's no danger of wraparound, and there'd be no work done
to a table that doesn't have any activity. We have to consider what
happens at stat reset -- AFAICS there's no problem, because as soon as
the table sees some activity, it will be picked up by pgstat.
However, it would be bad if stats are reset right after some heavy
activity on a table. Maybe the only thing we need is documentation.
- datallowcon is still ignored. Now it's safe to do so, because we have
a real Xid wraparound check. Changing it requires extending the
pg_database flat-file (should be fairly easy).
- There are stat messages emitted for a database-wide vacuum, just like
any other. This means that all tables in the database would end up in
pgstat; and also all databases, including those with datallowconn = false.
This may not be good. I'm not sure what exactly to do about it. Do
we want to disallow such stats? Disable message sending (or
collecting) in some circumstances?
- 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)
Attachments:
autovac-two-1.patchtext/plain; charset=us-asciiDownload
Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.210
diff -c -r1.210 xlog.c
*** src/backend/access/transam/xlog.c 23 Jul 2005 15:31:16 -0000 1.210
--- src/backend/access/transam/xlog.c 24 Jul 2005 17:55:51 -0000
***************
*** 465,471 ****
TimeLineID endTLI,
uint32 endLogId, uint32 endLogSeg);
static void WriteControlFile(void);
- static void ReadControlFile(void);
static char *str_time(time_t tnow);
static void issue_xlog_fsync(void);
--- 465,470 ----
***************
*** 3383,3390 ****
errmsg("could not close control file: %m")));
}
! static void
! ReadControlFile(void)
{
pg_crc32 crc;
int fd;
--- 3382,3394 ----
errmsg("could not close control file: %m")));
}
! /*
! * Read and verify the control file, filling the ControlFile struct.
! *
! * If nextXid is not NULL, the latest Checkpoint's nextXid is returned.
! */
! void
! ReadControlFile(TransactionId *nextXid)
{
pg_crc32 crc;
int fd;
***************
*** 3525,3530 ****
--- 3529,3537 ----
ControlFile->lc_ctype),
errhint("It looks like you need to initdb or install locale support.")));
+ if (PointerIsValid(nextXid))
+ *nextXid = ControlFile->checkPointCopy.nextXid;
+
/* Make the fixed locale settings visible as GUC variables, too */
SetConfigOption("lc_collate", ControlFile->lc_collate,
PGC_INTERNAL, PGC_S_OVERRIDE);
***************
*** 3650,3656 ****
* for the reasons why).
*/
if (!IsBootstrapProcessingMode())
! ReadControlFile();
}
/*
--- 3657,3663 ----
* for the reasons why).
*/
if (!IsBootstrapProcessingMode())
! ReadControlFile(NULL);
}
/*
***************
*** 4232,4238 ****
* Note: in most control paths, *ControlFile is already valid and we need
* not do ReadControlFile() here, but might as well do it to be sure.
*/
! ReadControlFile();
if (ControlFile->logSeg == 0 ||
ControlFile->state < DB_SHUTDOWNED ||
--- 4239,4245 ----
* Note: in most control paths, *ControlFile is already valid and we need
* not do ReadControlFile() here, but might as well do it to be sure.
*/
! ReadControlFile(NULL);
if (ControlFile->logSeg == 0 ||
ControlFile->state < DB_SHUTDOWNED ||
Index: src/backend/commands/analyze.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/commands/analyze.c,v
retrieving revision 1.87
diff -c -r1.87 analyze.c
*** src/backend/commands/analyze.c 14 Jul 2005 05:13:39 -0000 1.87
--- src/backend/commands/analyze.c 22 Jul 2005 01:08:51 -0000
***************
*** 317,323 ****
* a zero-column table.
*/
if (!vacstmt->vacuum)
! pgstat_report_analyze(RelationGetRelid(onerel), 0, 0);
vac_close_indexes(nindexes, Irel, AccessShareLock);
relation_close(onerel, AccessShareLock);
--- 317,324 ----
* a zero-column table.
*/
if (!vacstmt->vacuum)
! pgstat_report_analyze(RelationGetRelid(onerel), onerel->rd_rel->relisshared,
! 0, 0);
vac_close_indexes(nindexes, Irel, AccessShareLock);
relation_close(onerel, AccessShareLock);
***************
*** 436,443 ****
}
/* report results to the stats collector, too */
! pgstat_report_analyze(RelationGetRelid(onerel), totalrows,
! totaldeadrows);
}
/* Done with indexes */
--- 437,444 ----
}
/* report results to the stats collector, too */
! pgstat_report_analyze(RelationGetRelid(onerel), onerel->rd_rel->relisshared,
! totalrows, totaldeadrows);
}
/* Done with indexes */
Index: src/backend/commands/vacuum.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.311
diff -c -r1.311 vacuum.c
*** src/backend/commands/vacuum.c 14 Jul 2005 05:13:39 -0000 1.311
--- src/backend/commands/vacuum.c 24 Jul 2005 15:34:23 -0000
***************
*** 41,46 ****
--- 41,47 ----
#include "tcop/pquery.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+ #include "utils/flatfiles.h"
#include "utils/fmgroids.h"
#include "utils/inval.h"
#include "utils/lsyscache.h"
***************
*** 712,718 ****
* vac_update_dbstats() -- update statistics for one database
*
* Update the whole-database statistics that are kept in its pg_database
! * row.
*
* We violate no-overwrite semantics here by storing new values for the
* statistics columns directly into the tuple that's already on the page.
--- 713,719 ----
* vac_update_dbstats() -- update statistics for one database
*
* Update the whole-database statistics that are kept in its pg_database
! * row, and the flat-file copy of pg_database.
*
* We violate no-overwrite semantics here by storing new values for the
* statistics columns directly into the tuple that's already on the page.
***************
*** 721,728 ****
*
* This routine is shared by full and lazy VACUUM. Note that it is only
* applied after a database-wide VACUUM operation.
- *
- * Note that we don't bother to update the flat-file copy of pg_database.
*/
static void
vac_update_dbstats(Oid dbid,
--- 722,727 ----
***************
*** 768,773 ****
--- 767,775 ----
heap_endscan(scan);
heap_close(relation, RowExclusiveLock);
+
+ /* Mark the flat-file for update at commit */
+ database_file_update_needed();
}
***************
*** 1165,1172 ****
vacrelstats->rel_tuples, vacrelstats->hasindex);
/* report results to the stats collector, too */
! pgstat_report_vacuum(RelationGetRelid(onerel), vacstmt->analyze,
! vacrelstats->rel_tuples);
}
--- 1167,1174 ----
vacrelstats->rel_tuples, vacrelstats->hasindex);
/* report results to the stats collector, too */
! pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared,
! vacstmt->analyze, vacrelstats->rel_tuples);
}
Index: src/backend/commands/vacuumlazy.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.55
diff -c -r1.55 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c 14 Jul 2005 05:13:40 -0000 1.55
--- src/backend/commands/vacuumlazy.c 22 Jul 2005 01:08:51 -0000
***************
*** 182,189 ****
hasindex);
/* report results to the stats collector, too */
! pgstat_report_vacuum(RelationGetRelid(onerel), vacstmt->analyze,
! vacrelstats->rel_tuples);
}
--- 182,189 ----
hasindex);
/* report results to the stats collector, too */
! pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared,
! vacstmt->analyze, vacrelstats->rel_tuples);
}
Index: src/backend/libpq/hba.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/libpq/hba.c,v
retrieving revision 1.144
diff -c -r1.144 hba.c
*** src/backend/libpq/hba.c 28 Jun 2005 22:16:45 -0000 1.144
--- src/backend/libpq/hba.c 22 Jul 2005 01:08:51 -0000
***************
*** 39,44 ****
--- 39,45 ----
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
+ #define atoxid(x) ((TransactionId) strtoul((x), NULL, 10))
/* Max size of username ident server can return */
#define IDENT_USERNAME_MAX 512
***************
*** 1002,1014 ****
* dbname: gets database name (must be of size NAMEDATALEN bytes)
* dboid: gets database OID
* dbtablespace: gets database's default tablespace's OID
*
* This is not much related to the other functions in hba.c, but we put it
* here because it uses the next_token() infrastructure.
*/
bool
read_pg_database_line(FILE *fp, char *dbname,
! Oid *dboid, Oid *dbtablespace)
{
char buf[MAX_TOKEN];
--- 1003,1017 ----
* dbname: gets database name (must be of size NAMEDATALEN bytes)
* dboid: gets database OID
* dbtablespace: gets database's default tablespace's OID
+ * dbfrozenxid: get database's frozen Xid
*
* This is not much related to the other functions in hba.c, but we put it
* here because it uses the next_token() infrastructure.
*/
bool
read_pg_database_line(FILE *fp, char *dbname,
! Oid *dboid, Oid *dbtablespace,
! TransactionId *dbfrozenxid)
{
char buf[MAX_TOKEN];
***************
*** 1027,1036 ****
if (!isdigit((unsigned char) buf[0]))
elog(FATAL, "bad data in flat pg_database file");
*dbtablespace = atooid(buf);
- /* discard datfrozenxid */
next_token(fp, buf, sizeof(buf));
if (!isdigit((unsigned char) buf[0]))
elog(FATAL, "bad data in flat pg_database file");
/* expect EOL next */
if (next_token(fp, buf, sizeof(buf)))
elog(FATAL, "bad data in flat pg_database file");
--- 1030,1039 ----
if (!isdigit((unsigned char) buf[0]))
elog(FATAL, "bad data in flat pg_database file");
*dbtablespace = atooid(buf);
next_token(fp, buf, sizeof(buf));
if (!isdigit((unsigned char) buf[0]))
elog(FATAL, "bad data in flat pg_database file");
+ *dbfrozenxid = atoxid(buf);
/* expect EOL next */
if (next_token(fp, buf, sizeof(buf)))
elog(FATAL, "bad data in flat pg_database file");
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.1
diff -c -r1.1 autovacuum.c
*** src/backend/postmaster/autovacuum.c 14 Jul 2005 05:13:40 -0000 1.1
--- src/backend/postmaster/autovacuum.c 24 Jul 2005 17:31:32 -0000
***************
*** 23,29 ****
--- 23,31 ----
#include "access/genam.h"
#include "access/heapam.h"
+ #include "access/xlog.h"
#include "catalog/indexing.h"
+ #include "catalog/namespace.h"
#include "catalog/pg_autovacuum.h"
#include "catalog/pg_database.h"
#include "commands/vacuum.h"
***************
*** 41,46 ****
--- 43,49 ----
#include "tcop/tcopprot.h"
#include "utils/flatfiles.h"
#include "utils/fmgroids.h"
+ #include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/ps_status.h"
#include "utils/relcache.h"
***************
*** 68,74 ****
--- 71,79 ----
{
Oid oid;
char *name;
+ TransactionId frozenxid;
PgStat_StatDBEntry *entry;
+ int32 age;
} autovac_dbase;
***************
*** 76,83 ****
static pid_t autovac_forkexec(void);
#endif
NON_EXEC_STATIC void AutoVacMain(int argc, char *argv[]);
! static void autovac_check_wraparound(void);
! static void do_autovacuum(PgStat_StatDBEntry *dbentry);
static List *autovac_get_database_list(void);
static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
Form_pg_class classForm, Form_pg_autovacuum avForm,
--- 81,87 ----
static pid_t autovac_forkexec(void);
#endif
NON_EXEC_STATIC void AutoVacMain(int argc, char *argv[]);
! static void do_autovacuum(PgStat_StatDBEntry *dbentry, bool whole_db);
static List *autovac_get_database_list(void);
static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
Form_pg_class classForm, Form_pg_autovacuum avForm,
***************
*** 194,200 ****
--- 198,206 ----
ListCell *cell;
List *dblist;
autovac_dbase *db;
+ bool whole_db = false;
sigjmp_buf local_sigjmp_buf;
+ TransactionId nextXid;
/* we are a postmaster subprocess now */
IsUnderPostmaster = true;
***************
*** 269,289 ****
/*
* Choose a database to connect to. We pick the database that was least
! * recently auto-vacuumed.
*
* XXX This could be improved if we had more info about whether it needs
* vacuuming before connecting to it. Perhaps look through the pgstats
* data for the database's tables?
- *
- * XXX it is NOT good that we totally ignore databases that have no
- * pgstats entry ...
*/
db = NULL;
foreach(cell, dblist)
{
! autovac_dbase *tmp = lfirst(cell);
tmp->entry = pgstat_fetch_stat_dbentry(tmp->oid);
if (!tmp->entry)
continue;
--- 275,327 ----
/*
* Choose a database to connect to. We pick the database that was least
! * recently auto-vacuumed, or one that needs database-wide vacuum (to
! * prevent Xid wraparound-related data loss.)
*
* XXX This could be improved if we had more info about whether it needs
* vacuuming before connecting to it. Perhaps look through the pgstats
* data for the database's tables?
*/
db = NULL;
+ /*
+ * Get the next Xid that was current as of the last checkpoint.
+ * We will use it to determine whether databases are about to need
+ * database-wide vacuums.
+ */
+ ReadControlFile(&nextXid);
+
foreach(cell, dblist)
{
! autovac_dbase *tmp = lfirst(cell);
! bool this_whole_db;
+ /*
+ * We decide to vacuum a little earlier than when vacuum.c's
+ * vac_truncate_clog() would decide start giving warnings. We
+ * look for the database that most urgently needs a database-wide
+ * vacuum. As soon as one is found, any other database is
+ * ignored.
+ */
+ tmp->age = (int32) (nextXid - tmp->frozenxid);
+ this_whole_db = (tmp->age > (int32) ((MaxTransactionId >> 3) * 3));
+
+ if (whole_db || this_whole_db)
+ {
+ if (!this_whole_db)
+ continue;
+ if (!db || tmp->age > db->age)
+ {
+ db = tmp;
+ whole_db = true;
+ }
+ continue;
+ }
+
+ /*
+ * Skip databases with no pgstat entry; it means it hasn't seen
+ * any activity.
+ */
tmp->entry = pgstat_fetch_stat_dbentry(tmp->oid);
if (!tmp->entry)
continue;
***************
*** 316,322 ****
/*
* And do an appropriate amount of work on it
*/
! do_autovacuum(db->entry);
}
/* One iteration done, go away */
--- 354,360 ----
/*
* And do an appropriate amount of work on it
*/
! do_autovacuum(db->entry, whole_db);
}
/* One iteration done, go away */
***************
*** 338,343 ****
--- 376,382 ----
FILE *db_file;
Oid db_id;
Oid db_tablespace;
+ TransactionId db_frozenxid;
filename = database_getflatfilename();
db_file = AllocateFile(filename, "r");
***************
*** 346,352 ****
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m", filename)));
! while (read_pg_database_line(db_file, thisname, &db_id, &db_tablespace))
{
autovac_dbase *db;
--- 385,392 ----
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m", filename)));
! while (read_pg_database_line(db_file, thisname, &db_id, &db_tablespace,
! &db_frozenxid))
{
autovac_dbase *db;
***************
*** 354,359 ****
--- 394,400 ----
db->oid = db_id;
db->name = pstrdup(thisname);
+ db->frozenxid = db_frozenxid;
/* this gets set later */
db->entry = NULL;
***************
*** 369,374 ****
--- 410,421 ----
/*
* Process a database.
*
+ * If whole_db is true, the database is processed as a whole, and the
+ * dbentry parameter is ignored. If it's false, dbentry must hold a valid
+ * pointer to the database entry in the stats databases' hash table, and
+ * it will be used to determine whether vacuum or analyze is needed on a
+ * table per table basis.
+ *
* Note that test_rel_for_autovac generates two separate lists, one for
* vacuum and other for analyze. This is to facilitate processing all
* analyzes first, and then all vacuums.
***************
*** 377,383 ****
* order not to ignore shutdown commands for too long.
*/
static void
! do_autovacuum(PgStat_StatDBEntry *dbentry)
{
Relation classRel,
avRel;
--- 424,430 ----
* order not to ignore shutdown commands for too long.
*/
static void
! do_autovacuum(PgStat_StatDBEntry *dbentry, bool whole_db)
{
Relation classRel,
avRel;
***************
*** 387,392 ****
--- 434,441 ----
*analyze_tables = NIL;
MemoryContext AutovacMemCxt;
+ Assert(dbentry != NULL || whole_db);
+
/* Memory context where cross-transaction state is stored */
AutovacMemCxt = AllocSetContextCreate(TopMemoryContext,
"Autovacuum context",
***************
*** 405,485 ****
*/
MemoryContextSwitchTo(AutovacMemCxt);
! /*
! * If this database is old enough to need a whole-database VACUUM,
! * don't bother checking each table. If that happens, this function
! * will issue the VACUUM command and won't return.
! */
! autovac_check_wraparound();
!
! CHECK_FOR_INTERRUPTS();
!
! classRel = heap_open(RelationRelationId, AccessShareLock);
! avRel = heap_open(AutovacuumRelationId, AccessShareLock);
!
! relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL);
!
! /* Scan pg_class looking for tables to vacuum */
! while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
{
! Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
! Form_pg_autovacuum avForm = NULL;
! PgStat_StatTabEntry *tabentry;
! SysScanDesc avScan;
! HeapTuple avTup;
! ScanKeyData entry[1];
! Oid relid;
!
! /* Skip non-table entries. */
! /* XXX possibly allow RELKIND_TOASTVALUE entries here too? */
! if (classForm->relkind != RELKIND_RELATION)
! continue;
!
! relid = HeapTupleGetOid(tuple);
!
! /* See if we have a pg_autovacuum entry for this relation. */
! ScanKeyInit(&entry[0],
! Anum_pg_autovacuum_vacrelid,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(relid));
!
! avScan = systable_beginscan(avRel, AutovacuumRelidIndexId, true,
! SnapshotNow, 1, entry);
!
! avTup = systable_getnext(avScan);
!
! if (HeapTupleIsValid(avTup))
! avForm = (Form_pg_autovacuum) GETSTRUCT(avTup);
!
! tabentry = hash_search(dbentry->tables, &relid,
! HASH_FIND, NULL);
! test_rel_for_autovac(relid, tabentry, classForm, avForm,
! &vacuum_tables, &analyze_tables);
! systable_endscan(avScan);
! }
! heap_endscan(relScan);
! heap_close(avRel, AccessShareLock);
! heap_close(classRel, AccessShareLock);
! CHECK_FOR_INTERRUPTS();
! /*
! * Perform operations on collected tables.
! */
! if (analyze_tables)
! autovacuum_do_vac_analyze(analyze_tables, false);
! CHECK_FOR_INTERRUPTS();
! /* get back to proper context */
! MemoryContextSwitchTo(AutovacMemCxt);
! if (vacuum_tables)
! autovacuum_do_vac_analyze(vacuum_tables, true);
/* Finally close out the last transaction. */
CommitTransactionCommand();
--- 454,548 ----
*/
MemoryContextSwitchTo(AutovacMemCxt);
! if (whole_db)
{
! elog(DEBUG2, "autovacuum: VACUUM ANALYZE whole database");
! autovacuum_do_vac_analyze(NIL, true);
! }
! else
! {
! /* Get the stat database entry where pgstat stores shared relations */
! PgStat_StatDBEntry *shared = pgstat_fetch_stat_dbentry(InvalidOid);
! CHECK_FOR_INTERRUPTS();
! classRel = heap_open(RelationRelationId, AccessShareLock);
! avRel = heap_open(AutovacuumRelationId, AccessShareLock);
! relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL);
!
! /* Scan pg_class looking for tables to vacuum */
! while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
! {
! Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
! Form_pg_autovacuum avForm = NULL;
! PgStat_StatTabEntry *tabentry;
! SysScanDesc avScan;
! HeapTuple avTup;
! ScanKeyData entry[1];
! Oid relid;
! Oid nspOid;
!
! /* Skip non-table entries. */
! /* XXX possibly allow RELKIND_TOASTVALUE entries here too? */
! if (classForm->relkind != RELKIND_RELATION)
! continue;
!
! relid = HeapTupleGetOid(tuple);
!
! /* Skip temp tables (i.e. those in temp namespaces) */
! nspOid = get_rel_namespace(relid);
! if (isTempNamespace(nspOid))
! continue;
!
! /* See if we have a pg_autovacuum entry for this relation. */
! ScanKeyInit(&entry[0],
! Anum_pg_autovacuum_vacrelid,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(relid));
!
! avScan = systable_beginscan(avRel, AutovacuumRelidIndexId, true,
! SnapshotNow, 1, entry);
!
! avTup = systable_getnext(avScan);
!
! if (HeapTupleIsValid(avTup))
! avForm = (Form_pg_autovacuum) GETSTRUCT(avTup);
!
! if (classForm->relisshared)
! tabentry = hash_search(shared->tables, &relid,
! HASH_FIND, NULL);
! else
! tabentry = hash_search(dbentry->tables, &relid,
! HASH_FIND, NULL);
!
! test_rel_for_autovac(relid, tabentry, classForm, avForm,
! &vacuum_tables, &analyze_tables);
!
! systable_endscan(avScan);
! }
!
! heap_endscan(relScan);
! heap_close(avRel, AccessShareLock);
! heap_close(classRel, AccessShareLock);
! CHECK_FOR_INTERRUPTS();
! /*
! * Perform operations on collected tables.
! */
! if (analyze_tables)
! autovacuum_do_vac_analyze(analyze_tables, false);
! CHECK_FOR_INTERRUPTS();
! /* get back to proper context */
! MemoryContextSwitchTo(AutovacMemCxt);
! if (vacuum_tables)
! autovacuum_do_vac_analyze(vacuum_tables, true);
! }
/* Finally close out the last transaction. */
CommitTransactionCommand();
***************
*** 607,615 ****
}
else if (anltuples > anlthresh)
{
! elog(DEBUG2, "will ANALYZE %s",
! RelationGetRelationName(rel));
! *analyze_tables = lappend_oid(*analyze_tables, relid);
}
RelationClose(rel);
--- 670,682 ----
}
else if (anltuples > anlthresh)
{
! /* ANALYZE refuses to work with pg_statistics */
! if (relid != StatisticRelationId)
! {
! elog(DEBUG2, "will ANALYZE %s",
! RelationGetRelationName(rel));
! *analyze_tables = lappend_oid(*analyze_tables, relid);
! }
}
RelationClose(rel);
***************
*** 646,706 ****
}
/*
- * autovac_check_wraparound
- * Check database Xid wraparound
- *
- * Check pg_database to see if the last database-wide VACUUM was too long ago,
- * and issue one now if so. If this comes to pass, we do not return, as there
- * is no point in checking individual tables -- they will all get vacuumed
- * anyway.
- */
- static void
- autovac_check_wraparound(void)
- {
- Relation relation;
- ScanKeyData entry[1];
- HeapScanDesc scan;
- HeapTuple tuple;
- Form_pg_database dbform;
- int32 age;
- bool whole_db;
-
- relation = heap_open(DatabaseRelationId, AccessShareLock);
-
- /* Must use a heap scan, since there's no syscache for pg_database */
- ScanKeyInit(&entry[0],
- ObjectIdAttributeNumber,
- BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(MyDatabaseId));
-
- scan = heap_beginscan(relation, SnapshotNow, 1, entry);
-
- tuple = heap_getnext(scan, ForwardScanDirection);
-
- if (!HeapTupleIsValid(tuple))
- elog(ERROR, "could not find tuple for database %u", MyDatabaseId);
-
- dbform = (Form_pg_database) GETSTRUCT(tuple);
-
- /*
- * We decide to vacuum at the same point where vacuum.c's
- * vac_truncate_clog() would decide to start giving warnings.
- */
- age = (int32) (GetTopTransactionId() - dbform->datfrozenxid);
- whole_db = (age > (int32) ((MaxTransactionId >> 3) * 3));
-
- heap_endscan(scan);
- heap_close(relation, AccessShareLock);
-
- if (whole_db)
- {
- elog(LOG, "autovacuum: VACUUM ANALYZE whole database");
- autovacuum_do_vac_analyze(NIL, true);
- proc_exit(0);
- }
- }
-
- /*
* AutoVacuumingActive
* Check GUC vars and report whether the autovacuum process should be
* running.
--- 713,718 ----
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.101
diff -c -r1.101 pgstat.c
*** src/backend/postmaster/pgstat.c 24 Jul 2005 00:33:28 -0000 1.101
--- src/backend/postmaster/pgstat.c 24 Jul 2005 17:55:54 -0000
***************
*** 119,130 ****
static bool pgStatRunningInCollector = FALSE;
! static int pgStatTabstatAlloc = 0;
! static int pgStatTabstatUsed = 0;
! static PgStat_MsgTabstat **pgStatTabstatMessages = NULL;
#define TABSTAT_QUANTUM 4 /* we alloc this many at a time */
static int pgStatXactCommit = 0;
static int pgStatXactRollback = 0;
--- 119,141 ----
static bool pgStatRunningInCollector = FALSE;
! /*
! * Place where backends store per-table info to be sent to the collector.
! * We store shared relations separately from non-shared ones, to be able to
! * send them in separate messages.
! */
! typedef struct TabStatArray
! {
! int tsa_alloc; /* num allocated */
! int tsa_used; /* num actually used */
! PgStat_MsgTabstat **tsa_messages; /* the array itself */
! } TabStatArray;
#define TABSTAT_QUANTUM 4 /* we alloc this many at a time */
+ static TabStatArray RegularTabStat;
+ static TabStatArray SharedTabStat;
+
static int pgStatXactCommit = 0;
static int pgStatXactRollback = 0;
***************
*** 182,187 ****
--- 193,199 ----
static void pgstat_recv_vacuum(PgStat_MsgVacuum *msg, int len);
static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
+ extern void display_statfile_contents(void);
/* ------------------------------------------------------------
* Public functions called from postmaster follow
***************
*** 614,619 ****
--- 626,632 ----
if (pgStatSock < 0)
return;
+ /* can't use pgstat_setheader() because it's not called in a backend */
MemSet(&(msg.m_hdr), 0, sizeof(msg.m_hdr));
msg.m_hdr.m_type = PGSTAT_MTYPE_BETERM;
msg.m_hdr.m_procpid = pid;
***************
*** 684,690 ****
* ---------
*/
void
! pgstat_report_vacuum(Oid tableoid, bool analyze, PgStat_Counter tuples)
{
PgStat_MsgVacuum msg;
--- 697,704 ----
* ---------
*/
void
! pgstat_report_vacuum(Oid tableoid, bool shared, bool analyze,
! PgStat_Counter tuples)
{
PgStat_MsgVacuum msg;
***************
*** 692,698 ****
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_VACUUM);
! msg.m_databaseid = MyDatabaseId;
msg.m_tableoid = tableoid;
msg.m_analyze = analyze;
msg.m_tuples = tuples;
--- 706,715 ----
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_VACUUM);
! if (shared)
! msg.m_databaseid = InvalidOid;
! else
! msg.m_databaseid = MyDatabaseId;
msg.m_tableoid = tableoid;
msg.m_analyze = analyze;
msg.m_tuples = tuples;
***************
*** 706,712 ****
* --------
*/
void
! pgstat_report_analyze(Oid tableoid, PgStat_Counter livetuples,
PgStat_Counter deadtuples)
{
PgStat_MsgAnalyze msg;
--- 723,729 ----
* --------
*/
void
! pgstat_report_analyze(Oid tableoid, bool shared, PgStat_Counter livetuples,
PgStat_Counter deadtuples)
{
PgStat_MsgAnalyze msg;
***************
*** 715,721 ****
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_ANALYZE);
! msg.m_databaseid = MyDatabaseId;
msg.m_tableoid = tableoid;
msg.m_live_tuples = livetuples;
msg.m_dead_tuples = deadtuples;
--- 732,741 ----
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_ANALYZE);
! if (shared)
! msg.m_databaseid = InvalidOid;
! else
! msg.m_databaseid = MyDatabaseId;
msg.m_tableoid = tableoid;
msg.m_live_tuples = livetuples;
msg.m_dead_tuples = deadtuples;
***************
*** 784,790 ****
pgstat_collect_blocklevel))
{
/* Not reporting stats, so just flush whatever we have */
! pgStatTabstatUsed = 0;
return;
}
--- 804,811 ----
pgstat_collect_blocklevel))
{
/* Not reporting stats, so just flush whatever we have */
! RegularTabStat.tsa_used = 0;
! SharedTabStat.tsa_used = 0;
return;
}
***************
*** 792,800 ****
* For each message buffer used during the last query set the header
* fields and send it out.
*/
! for (i = 0; i < pgStatTabstatUsed; i++)
{
! PgStat_MsgTabstat *tsmsg = pgStatTabstatMessages[i];
int n;
int len;
--- 813,821 ----
* For each message buffer used during the last query set the header
* fields and send it out.
*/
! for (i = 0; i < RegularTabStat.tsa_used; i++)
{
! PgStat_MsgTabstat *tsmsg = RegularTabStat.tsa_messages[i];
int n;
int len;
***************
*** 811,818 ****
tsmsg->m_databaseid = MyDatabaseId;
pgstat_send(tsmsg, len);
}
! pgStatTabstatUsed = 0;
}
--- 832,857 ----
tsmsg->m_databaseid = MyDatabaseId;
pgstat_send(tsmsg, len);
}
+ RegularTabStat.tsa_used = 0;
+
+ /* Ditto, for shared relations */
+ for (i = 0; i < SharedTabStat.tsa_used; i++)
+ {
+ PgStat_MsgTabstat *tsmsg = SharedTabStat.tsa_messages[i];
+ int n;
+ int len;
! n = tsmsg->m_nentries;
! len = offsetof(PgStat_MsgTabstat, m_entry[0]) +
! n * sizeof(PgStat_TableEntry);
!
! /* We don't report transaction commit/abort here */
!
! pgstat_setheader(&tsmsg->m_hdr, PGSTAT_MTYPE_TABSTAT);
! tsmsg->m_databaseid = InvalidOid;
! pgstat_send(tsmsg, len);
! }
! SharedTabStat.tsa_used = 0;
}
***************
*** 1045,1081 ****
}
/*
! * Create or enlarge the pgStatTabstatMessages array
*/
static void
! more_tabstat_space(void)
{
PgStat_MsgTabstat *newMessages;
PgStat_MsgTabstat **msgArray;
! int newAlloc = pgStatTabstatAlloc + TABSTAT_QUANTUM;
int i;
/* Create (another) quantum of message buffers */
newMessages = (PgStat_MsgTabstat *)
MemoryContextAllocZero(TopMemoryContext,
sizeof(PgStat_MsgTabstat) * TABSTAT_QUANTUM);
/* Create or enlarge the pointer array */
! if (pgStatTabstatMessages == NULL)
msgArray = (PgStat_MsgTabstat **)
MemoryContextAlloc(TopMemoryContext,
sizeof(PgStat_MsgTabstat *) * newAlloc);
else
msgArray = (PgStat_MsgTabstat **)
! repalloc(pgStatTabstatMessages,
sizeof(PgStat_MsgTabstat *) * newAlloc);
for (i = 0; i < TABSTAT_QUANTUM; i++)
! msgArray[pgStatTabstatAlloc + i] = newMessages++;
! pgStatTabstatMessages = msgArray;
! pgStatTabstatAlloc = newAlloc;
! Assert(pgStatTabstatUsed < pgStatTabstatAlloc);
}
/* ----------
--- 1084,1124 ----
}
/*
! * Enlarge a TabStatArray
*/
static void
! more_tabstat_space(TabStatArray *tsarr)
{
PgStat_MsgTabstat *newMessages;
PgStat_MsgTabstat **msgArray;
! int newAlloc;
int i;
+ AssertArg(PointerIsValid(tsarr));
+
+ newAlloc = tsarr->tsa_alloc + TABSTAT_QUANTUM;
+
/* Create (another) quantum of message buffers */
newMessages = (PgStat_MsgTabstat *)
MemoryContextAllocZero(TopMemoryContext,
sizeof(PgStat_MsgTabstat) * TABSTAT_QUANTUM);
/* Create or enlarge the pointer array */
! if (tsarr->tsa_messages == NULL)
msgArray = (PgStat_MsgTabstat **)
MemoryContextAlloc(TopMemoryContext,
sizeof(PgStat_MsgTabstat *) * newAlloc);
else
msgArray = (PgStat_MsgTabstat **)
! repalloc(tsarr->tsa_messages,
sizeof(PgStat_MsgTabstat *) * newAlloc);
for (i = 0; i < TABSTAT_QUANTUM; i++)
! msgArray[tsarr->tsa_alloc + i] = newMessages++;
! tsarr->tsa_messages = msgArray;
! tsarr->tsa_alloc = newAlloc;
! Assert(tsarr->tsa_used < tsarr->tsa_alloc);
}
/* ----------
***************
*** 1092,1097 ****
--- 1135,1141 ----
{
Oid rel_id = rel->rd_id;
PgStat_TableEntry *useent;
+ TabStatArray *tsarr;
PgStat_MsgTabstat *tsmsg;
int mb;
int i;
***************
*** 1112,1123 ****
return;
}
/*
* Search the already-used message slots for this relation.
*/
! for (mb = 0; mb < pgStatTabstatUsed; mb++)
{
! tsmsg = pgStatTabstatMessages[mb];
for (i = tsmsg->m_nentries; --i >= 0;)
{
--- 1156,1169 ----
return;
}
+ tsarr = rel->rd_rel->relisshared ? &SharedTabStat : &RegularTabStat;
+
/*
* Search the already-used message slots for this relation.
*/
! for (mb = 0; mb < tsarr->tsa_used; mb++)
{
! tsmsg = tsarr->tsa_messages[mb];
for (i = tsmsg->m_nentries; --i >= 0;)
{
***************
*** 1146,1159 ****
/*
* If we ran out of message buffers, we just allocate more.
*/
! if (pgStatTabstatUsed >= pgStatTabstatAlloc)
! more_tabstat_space();
/*
* Use the first entry of the next message buffer.
*/
! mb = pgStatTabstatUsed++;
! tsmsg = pgStatTabstatMessages[mb];
tsmsg->m_nentries = 1;
useent = &tsmsg->m_entry[0];
MemSet(useent, 0, sizeof(PgStat_TableEntry));
--- 1192,1205 ----
/*
* If we ran out of message buffers, we just allocate more.
*/
! if (tsarr->tsa_used >= tsarr->tsa_alloc)
! more_tabstat_space(tsarr);
/*
* Use the first entry of the next message buffer.
*/
! mb = tsarr->tsa_used++;
! tsmsg = tsarr->tsa_messages[mb];
tsmsg->m_nentries = 1;
useent = &tsmsg->m_entry[0];
MemSet(useent, 0, sizeof(PgStat_TableEntry));
***************
*** 1183,1195 ****
* message buffer used without slots, causing the next report to tell
* new xact-counters.
*/
! if (pgStatTabstatAlloc == 0)
! more_tabstat_space();
! if (pgStatTabstatUsed == 0)
{
! pgStatTabstatUsed++;
! pgStatTabstatMessages[0]->m_nentries = 0;
}
}
--- 1229,1241 ----
* message buffer used without slots, causing the next report to tell
* new xact-counters.
*/
! if (RegularTabStat.tsa_alloc == 0)
! more_tabstat_space(&RegularTabStat);
! if (RegularTabStat.tsa_used == 0)
{
! RegularTabStat.tsa_used++;
! RegularTabStat.tsa_messages[0]->m_nentries = 0;
}
}
***************
*** 1215,1227 ****
* message buffer used without slots, causing the next report to tell
* new xact-counters.
*/
! if (pgStatTabstatAlloc == 0)
! more_tabstat_space();
! if (pgStatTabstatUsed == 0)
{
! pgStatTabstatUsed++;
! pgStatTabstatMessages[0]->m_nentries = 0;
}
}
--- 1261,1273 ----
* message buffer used without slots, causing the next report to tell
* new xact-counters.
*/
! if (RegularTabStat.tsa_alloc == 0)
! more_tabstat_space(&RegularTabStat);
! if (RegularTabStat.tsa_used == 0)
{
! RegularTabStat.tsa_used++;
! RegularTabStat.tsa_messages[0]->m_nentries = 0;
}
}
***************
*** 2387,2393 ****
* pgstat_read_statsfile() -
*
* Reads in an existing statistics collector and initializes the
! * databases hash table (who's entries point to the tables hash tables)
* and the current backend table.
* ----------
*/
--- 2433,2439 ----
* pgstat_read_statsfile() -
*
* Reads in an existing statistics collector and initializes the
! * databases' hash table (whose entries point to the tables' hash tables)
* and the current backend table.
* ----------
*/
Index: src/backend/postmaster/postmaster.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.460
diff -c -r1.460 postmaster.c
*** src/backend/postmaster/postmaster.c 21 Jul 2005 03:56:11 -0000 1.460
--- src/backend/postmaster/postmaster.c 24 Jul 2005 17:54:31 -0000
***************
*** 1164,1176 ****
/*
* Wait for something to happen.
*
! * We wait at most one minute, to ensure that the other background
! * tasks handled below get done even when no requests are
! * arriving.
*/
memcpy((char *) &rmask, (char *) &readmask, sizeof(fd_set));
! timeout.tv_sec = 60;
timeout.tv_usec = 0;
PG_SETMASK(&UnBlockSig);
--- 1164,1176 ----
/*
* Wait for something to happen.
*
! * We wait at most one minute, or the minimum autovacuum delay, to
! * ensure that the other background tasks handled below get done
! * even when no requests are arriving.
*/
memcpy((char *) &rmask, (char *) &readmask, sizeof(fd_set));
! timeout.tv_sec = Min(60, autovacuum_naptime);
timeout.tv_usec = 0;
PG_SETMASK(&UnBlockSig);
Index: src/backend/utils/init/postinit.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/init/postinit.c,v
retrieving revision 1.153
diff -c -r1.153 postinit.c
*** src/backend/utils/init/postinit.c 14 Jul 2005 05:13:41 -0000 1.153
--- src/backend/utils/init/postinit.c 24 Jul 2005 15:50:28 -0000
***************
*** 78,83 ****
--- 78,84 ----
char *filename;
FILE *db_file;
char thisname[NAMEDATALEN];
+ TransactionId frozenxid;
filename = database_getflatfilename();
db_file = AllocateFile(filename, "r");
***************
*** 86,92 ****
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m", filename)));
! while (read_pg_database_line(db_file, thisname, db_id, db_tablespace))
{
if (strcmp(thisname, name) == 0)
{
--- 87,94 ----
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m", filename)));
! while (read_pg_database_line(db_file, thisname, db_id, db_tablespace,
! &frozenxid))
{
if (strcmp(thisname, name) == 0)
{
***************
*** 170,179 ****
/*
* Also check that the database is currently allowing connections.
* (We do not enforce this in standalone mode, however, so that there is
! * a way to recover from "UPDATE pg_database SET datallowconn = false;")
*/
dbform = (Form_pg_database) GETSTRUCT(tup);
! if (IsUnderPostmaster && !dbform->datallowconn)
ereport(FATAL,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("database \"%s\" is not currently accepting connections",
--- 172,182 ----
/*
* Also check that the database is currently allowing connections.
* (We do not enforce this in standalone mode, however, so that there is
! * a way to recover from "UPDATE pg_database SET datallowconn = false;".
! * We do not enforce it for the autovacuum process either.)
*/
dbform = (Form_pg_database) GETSTRUCT(tup);
! if (IsUnderPostmaster && !IsAutoVacuumProcess() && !dbform->datallowconn)
ereport(FATAL,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("database \"%s\" is not currently accepting connections",
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.277
diff -c -r1.277 guc.c
*** src/backend/utils/misc/guc.c 23 Jul 2005 21:05:47 -0000 1.277
--- src/backend/utils/misc/guc.c 24 Jul 2005 17:55:56 -0000
***************
*** 1412,1423 ****
},
{
{"autovacuum_naptime", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Time to sleep between autovacuum runs, in seconds."),
NULL
},
&autovacuum_naptime,
! 60, 0, INT_MAX, NULL, NULL
},
{
{"autovacuum_vacuum_threshold", PGC_SIGHUP, AUTOVACUUM,
--- 1412,1427 ----
},
{
+ /*
+ * Note we set an artificial minimum of 1 sec, so that the
+ * postmaster's main loop does not turn into busy-wait.
+ */
{"autovacuum_naptime", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Time to sleep between autovacuum runs, in seconds."),
NULL
},
&autovacuum_naptime,
! 60, 1, INT_MAX, NULL, NULL
},
{
{"autovacuum_vacuum_threshold", PGC_SIGHUP, AUTOVACUUM,
Index: src/include/pgstat.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/pgstat.h,v
retrieving revision 1.33
diff -c -r1.33 pgstat.h
*** src/include/pgstat.h 14 Jul 2005 05:13:43 -0000 1.33
--- src/include/pgstat.h 22 Jul 2005 01:08:51 -0000
***************
*** 384,393 ****
extern void pgstat_report_activity(const char *what);
extern void pgstat_report_tabstat(void);
extern void pgstat_report_autovac(void);
! extern void pgstat_report_vacuum(Oid tableoid, bool analyze,
PgStat_Counter tuples);
! extern void pgstat_report_analyze(Oid tableoid, PgStat_Counter livetuples,
! PgStat_Counter deadtuples);
extern int pgstat_vacuum_tabstat(void);
extern void pgstat_reset_counters(void);
--- 384,393 ----
extern void pgstat_report_activity(const char *what);
extern void pgstat_report_tabstat(void);
extern void pgstat_report_autovac(void);
! extern void pgstat_report_vacuum(Oid tableoid, bool shared, bool analyze,
PgStat_Counter tuples);
! extern void pgstat_report_analyze(Oid tableoid, bool shared,
! PgStat_Counter livetuples, PgStat_Counter deadtuples);
extern int pgstat_vacuum_tabstat(void);
extern void pgstat_reset_counters(void);
Index: src/include/access/xlog.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/access/xlog.h,v
retrieving revision 1.66
diff -c -r1.66 xlog.h
*** src/include/access/xlog.h 4 Jul 2005 04:51:52 -0000 1.66
--- src/include/access/xlog.h 23 Jul 2005 02:04:33 -0000
***************
*** 155,160 ****
--- 155,161 ----
extern void xlog_redo(XLogRecPtr lsn, XLogRecord *record);
extern void xlog_desc(char *buf, uint8 xl_info, char *rec);
+ extern void ReadControlFile(TransactionId *nextXid);
extern void UpdateControlFile(void);
extern int XLOGShmemSize(void);
extern void XLOGShmemInit(void);
Index: src/include/libpq/hba.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/libpq/hba.h,v
retrieving revision 1.38
diff -c -r1.38 hba.h
*** src/include/libpq/hba.h 28 Jun 2005 05:09:13 -0000 1.38
--- src/include/libpq/hba.h 22 Jul 2005 01:08:51 -0000
***************
*** 37,42 ****
extern int hba_getauthmethod(hbaPort *port);
extern int authident(hbaPort *port);
extern bool read_pg_database_line(FILE *fp, char *dbname,
! Oid *dboid, Oid *dbtablespace);
#endif /* HBA_H */
--- 37,43 ----
extern int hba_getauthmethod(hbaPort *port);
extern int authident(hbaPort *port);
extern bool read_pg_database_line(FILE *fp, char *dbname,
! Oid *dboid, Oid *dbtablespace,
! TransactionId *dbfrozenxid);
#endif /* HBA_H */
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
- pg_statistic is completely ignored.
... pg_statistic still needs vacuuming, surely. It's only ANALYZE
that you can/should skip for it.
- 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.
Hmm, I wonder whether the minimum shouldn't be 10. Or even 60.
- Now that we have a real Xid wraparound check, we could go back to
having any table with no stat entry ignored, which was the original
coding. There's no danger of wraparound, and there'd be no work done
to a table that doesn't have any activity.
Agreed.
We have to consider what
happens at stat reset -- AFAICS there's no problem, because as soon as
the table sees some activity, it will be picked up by pgstat.
However, it would be bad if stats are reset right after some heavy
activity on a table. Maybe the only thing we need is documentation.
What's the use-case for having the stat reset feature at all?
- datallowcon is still ignored. Now it's safe to do so, because we have
a real Xid wraparound check. Changing it requires extending the
pg_database flat-file (should be fairly easy).
I think this is all right, as long as a database that shows no stats
traffic is only connected to when it needs to be vacuumed for XID wrap
prevention purposes.
- There are stat messages emitted for a database-wide vacuum, just like
any other. This means that all tables in the database would end up in
pgstat; and also all databases, including those with datallowconn = false.
This may not be good. I'm not sure what exactly to do about it. Do
we want to disallow such stats? Disable message sending (or
collecting) in some circumstances?
Needs thought...
regards, tom lane
On Sun, Jul 24, 2005 at 02:33:38PM -0400, Tom Lane wrote:
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
- pg_statistic is completely ignored.
... pg_statistic still needs vacuuming, surely. It's only ANALYZE
that you can/should skip for it.
Sorry, yes, it's ignored only for analyze.
- 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.Hmm, I wonder whether the minimum shouldn't be 10. Or even 60.
It's ok with me. What do other people think?
We have to consider what
happens at stat reset -- AFAICS there's no problem, because as soon as
the table sees some activity, it will be picked up by pgstat.
However, it would be bad if stats are reset right after some heavy
activity on a table. Maybe the only thing we need is documentation.What's the use-case for having the stat reset feature at all?
I don't know. Maybe the people who added it can tell?
- There are stat messages emitted for a database-wide vacuum, just like
any other. This means that all tables in the database would end up in
pgstat; and also all databases, including those with datallowconn = false.
This may not be good. I'm not sure what exactly to do about it. Do
we want to disallow such stats? Disable message sending (or
collecting) in some circumstances?Needs thought...
Ok.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"I call it GNU/Linux. Except the GNU/ is silent." (Ben Reiter)
We have to consider what
happens at stat reset -- AFAICS there's no problem, because as soon as
the table sees some activity, it will be picked up by pgstat.
However, it would be bad if stats are reset right after some heavy
activity on a table. Maybe the only thing we need is documentation.What's the use-case for having the stat reset feature at all?
I believe I was the root cause of the pg_stat_reset() function. The
idea at the time was that if you decide to do a round of index
optimisation, you want to be able to search for unused indexes and
heavily seq. scanned tables.
If you reset the stats you have 'clean' data to work with. For
instance, you can get 24 hours of clean stats data.
Chris
Alvaro Herrera wrote:
On Sun, Jul 24, 2005 at 02:33:38PM -0400, Tom Lane wrote:
Hmm, I wonder whether the minimum shouldn't be 10. Or even 60.
It's ok with me. What do other people think?
Effectiely, this is going to be the minimum amount of "down time" for
autovacuum between checking databases, right? So if the minimum is 10
seconds, and there I have six databases, then it will check each
database at most once per minute? If so, then I'm not sure what I think
if I have a few hundred databases, 10s might be too long.
What's the use-case for having the stat reset feature at all?
I don't know. Maybe the people who added it can tell?
I don't know either, but this brings up another question. Stats
wraparound. The n_tup_ins/upd/del columns in the stats system are
defined as bigint, what happens when the total number of upd for example
exceeds the capacity for bigint, or overflows to negative, anyone have
any idea?
Matt
"Matthew T. O'Connor" <matthew@zeut.net> writes:
I don't know either, but this brings up another question. Stats
wraparound. The n_tup_ins/upd/del columns in the stats system are
defined as bigint, what happens when the total number of upd for example
exceeds the capacity for bigint, or overflows to negative, anyone have
any idea?
We'll all be safely dead, for one thing ;-)
At one update per nanosecond, it'd take approximately 300 years to wrap
a 64-bit counter. Somehow I don't have a problem with the idea that
Postgres would need to be rebooted that often. We'd want to fix the
32-bit nature of XIDs long before 64-bit stats counters get to be a
real-world issue ...
regards, tom lane
Tom Lane wrote:
"Matthew T. O'Connor" <matthew@zeut.net> writes:
I don't know either, but this brings up another question. Stats
wraparound.We'll all be safely dead, for one thing ;-)
At one update per nanosecond, it'd take approximately 300 years to wrap
a 64-bit counter. Somehow I don't have a problem with the idea that
Postgres would need to be rebooted that often. We'd want to fix the
32-bit nature of XIDs long before 64-bit stats counters get to be a
real-world issue ...
*sigh* Sorry, I should have done a little math before I asked that
question.....
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)
Attachments:
autovac-two-2.patchtext/plain; charset=us-asciiDownload
Index: access/transam/xlog.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.210
diff -c -r1.210 xlog.c
*** access/transam/xlog.c 23 Jul 2005 15:31:16 -0000 1.210
--- access/transam/xlog.c 24 Jul 2005 17:55:51 -0000
***************
*** 465,471 ****
TimeLineID endTLI,
uint32 endLogId, uint32 endLogSeg);
static void WriteControlFile(void);
- static void ReadControlFile(void);
static char *str_time(time_t tnow);
static void issue_xlog_fsync(void);
--- 465,470 ----
***************
*** 3383,3390 ****
errmsg("could not close control file: %m")));
}
! static void
! ReadControlFile(void)
{
pg_crc32 crc;
int fd;
--- 3382,3394 ----
errmsg("could not close control file: %m")));
}
! /*
! * Read and verify the control file, filling the ControlFile struct.
! *
! * If nextXid is not NULL, the latest Checkpoint's nextXid is returned.
! */
! void
! ReadControlFile(TransactionId *nextXid)
{
pg_crc32 crc;
int fd;
***************
*** 3525,3530 ****
--- 3529,3537 ----
ControlFile->lc_ctype),
errhint("It looks like you need to initdb or install locale support.")));
+ if (PointerIsValid(nextXid))
+ *nextXid = ControlFile->checkPointCopy.nextXid;
+
/* Make the fixed locale settings visible as GUC variables, too */
SetConfigOption("lc_collate", ControlFile->lc_collate,
PGC_INTERNAL, PGC_S_OVERRIDE);
***************
*** 3650,3656 ****
* for the reasons why).
*/
if (!IsBootstrapProcessingMode())
! ReadControlFile();
}
/*
--- 3657,3663 ----
* for the reasons why).
*/
if (!IsBootstrapProcessingMode())
! ReadControlFile(NULL);
}
/*
***************
*** 4232,4238 ****
* Note: in most control paths, *ControlFile is already valid and we need
* not do ReadControlFile() here, but might as well do it to be sure.
*/
! ReadControlFile();
if (ControlFile->logSeg == 0 ||
ControlFile->state < DB_SHUTDOWNED ||
--- 4239,4245 ----
* Note: in most control paths, *ControlFile is already valid and we need
* not do ReadControlFile() here, but might as well do it to be sure.
*/
! ReadControlFile(NULL);
if (ControlFile->logSeg == 0 ||
ControlFile->state < DB_SHUTDOWNED ||
Index: commands/analyze.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/commands/analyze.c,v
retrieving revision 1.87
diff -c -r1.87 analyze.c
*** commands/analyze.c 14 Jul 2005 05:13:39 -0000 1.87
--- commands/analyze.c 26 Jul 2005 03:51:45 -0000
***************
*** 317,323 ****
* a zero-column table.
*/
if (!vacstmt->vacuum)
! pgstat_report_analyze(RelationGetRelid(onerel), 0, 0);
vac_close_indexes(nindexes, Irel, AccessShareLock);
relation_close(onerel, AccessShareLock);
--- 317,325 ----
* a zero-column table.
*/
if (!vacstmt->vacuum)
! pgstat_report_analyze(RelationGetRelid(onerel),
! onerel->rd_rel->relisshared,
! 0, 0);
vac_close_indexes(nindexes, Irel, AccessShareLock);
relation_close(onerel, AccessShareLock);
***************
*** 436,443 ****
}
/* report results to the stats collector, too */
! pgstat_report_analyze(RelationGetRelid(onerel), totalrows,
! totaldeadrows);
}
/* Done with indexes */
--- 438,446 ----
}
/* report results to the stats collector, too */
! pgstat_report_analyze(RelationGetRelid(onerel),
! onerel->rd_rel->relisshared,
! totalrows, totaldeadrows);
}
/* Done with indexes */
Index: commands/vacuum.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.311
diff -c -r1.311 vacuum.c
*** commands/vacuum.c 14 Jul 2005 05:13:39 -0000 1.311
--- commands/vacuum.c 24 Jul 2005 15:34:23 -0000
***************
*** 41,46 ****
--- 41,47 ----
#include "tcop/pquery.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+ #include "utils/flatfiles.h"
#include "utils/fmgroids.h"
#include "utils/inval.h"
#include "utils/lsyscache.h"
***************
*** 712,718 ****
* vac_update_dbstats() -- update statistics for one database
*
* Update the whole-database statistics that are kept in its pg_database
! * row.
*
* We violate no-overwrite semantics here by storing new values for the
* statistics columns directly into the tuple that's already on the page.
--- 713,719 ----
* vac_update_dbstats() -- update statistics for one database
*
* Update the whole-database statistics that are kept in its pg_database
! * row, and the flat-file copy of pg_database.
*
* We violate no-overwrite semantics here by storing new values for the
* statistics columns directly into the tuple that's already on the page.
***************
*** 721,728 ****
*
* This routine is shared by full and lazy VACUUM. Note that it is only
* applied after a database-wide VACUUM operation.
- *
- * Note that we don't bother to update the flat-file copy of pg_database.
*/
static void
vac_update_dbstats(Oid dbid,
--- 722,727 ----
***************
*** 768,773 ****
--- 767,775 ----
heap_endscan(scan);
heap_close(relation, RowExclusiveLock);
+
+ /* Mark the flat-file for update at commit */
+ database_file_update_needed();
}
***************
*** 1165,1172 ****
vacrelstats->rel_tuples, vacrelstats->hasindex);
/* report results to the stats collector, too */
! pgstat_report_vacuum(RelationGetRelid(onerel), vacstmt->analyze,
! vacrelstats->rel_tuples);
}
--- 1167,1174 ----
vacrelstats->rel_tuples, vacrelstats->hasindex);
/* report results to the stats collector, too */
! pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared,
! vacstmt->analyze, vacrelstats->rel_tuples);
}
Index: commands/vacuumlazy.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.55
diff -c -r1.55 vacuumlazy.c
*** commands/vacuumlazy.c 14 Jul 2005 05:13:40 -0000 1.55
--- commands/vacuumlazy.c 22 Jul 2005 01:08:51 -0000
***************
*** 182,189 ****
hasindex);
/* report results to the stats collector, too */
! pgstat_report_vacuum(RelationGetRelid(onerel), vacstmt->analyze,
! vacrelstats->rel_tuples);
}
--- 182,189 ----
hasindex);
/* report results to the stats collector, too */
! pgstat_report_vacuum(RelationGetRelid(onerel), onerel->rd_rel->relisshared,
! vacstmt->analyze, vacrelstats->rel_tuples);
}
Index: libpq/hba.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/libpq/hba.c,v
retrieving revision 1.144
diff -c -r1.144 hba.c
*** libpq/hba.c 28 Jun 2005 22:16:45 -0000 1.144
--- libpq/hba.c 22 Jul 2005 01:08:51 -0000
***************
*** 39,44 ****
--- 39,45 ----
#define atooid(x) ((Oid) strtoul((x), NULL, 10))
+ #define atoxid(x) ((TransactionId) strtoul((x), NULL, 10))
/* Max size of username ident server can return */
#define IDENT_USERNAME_MAX 512
***************
*** 1002,1014 ****
* dbname: gets database name (must be of size NAMEDATALEN bytes)
* dboid: gets database OID
* dbtablespace: gets database's default tablespace's OID
*
* This is not much related to the other functions in hba.c, but we put it
* here because it uses the next_token() infrastructure.
*/
bool
read_pg_database_line(FILE *fp, char *dbname,
! Oid *dboid, Oid *dbtablespace)
{
char buf[MAX_TOKEN];
--- 1003,1017 ----
* dbname: gets database name (must be of size NAMEDATALEN bytes)
* dboid: gets database OID
* dbtablespace: gets database's default tablespace's OID
+ * dbfrozenxid: get database's frozen Xid
*
* This is not much related to the other functions in hba.c, but we put it
* here because it uses the next_token() infrastructure.
*/
bool
read_pg_database_line(FILE *fp, char *dbname,
! Oid *dboid, Oid *dbtablespace,
! TransactionId *dbfrozenxid)
{
char buf[MAX_TOKEN];
***************
*** 1027,1036 ****
if (!isdigit((unsigned char) buf[0]))
elog(FATAL, "bad data in flat pg_database file");
*dbtablespace = atooid(buf);
- /* discard datfrozenxid */
next_token(fp, buf, sizeof(buf));
if (!isdigit((unsigned char) buf[0]))
elog(FATAL, "bad data in flat pg_database file");
/* expect EOL next */
if (next_token(fp, buf, sizeof(buf)))
elog(FATAL, "bad data in flat pg_database file");
--- 1030,1039 ----
if (!isdigit((unsigned char) buf[0]))
elog(FATAL, "bad data in flat pg_database file");
*dbtablespace = atooid(buf);
next_token(fp, buf, sizeof(buf));
if (!isdigit((unsigned char) buf[0]))
elog(FATAL, "bad data in flat pg_database file");
+ *dbfrozenxid = atoxid(buf);
/* expect EOL next */
if (next_token(fp, buf, sizeof(buf)))
elog(FATAL, "bad data in flat pg_database file");
Index: postmaster/Makefile
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/Makefile,v
retrieving revision 1.21
diff -c -r1.21 Makefile
*** postmaster/Makefile 14 Jul 2005 05:13:40 -0000 1.21
--- postmaster/Makefile 25 Jul 2005 23:02:00 -0000
***************
*** 17,22 ****
--- 17,25 ----
all: SUBSYS.o
+ statdump: statdump.c
+ $(CC) $(CFLAGS) -I$(top_srcdir)/src/include -I$(top_builddir)/src/include $<
+
SUBSYS.o: $(OBJS)
$(LD) $(LDREL) $(LDOUT) SUBSYS.o $(OBJS)
Index: postmaster/autovacuum.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.1
diff -c -r1.1 autovacuum.c
*** postmaster/autovacuum.c 14 Jul 2005 05:13:40 -0000 1.1
--- postmaster/autovacuum.c 26 Jul 2005 04:02:19 -0000
***************
*** 23,29 ****
--- 23,31 ----
#include "access/genam.h"
#include "access/heapam.h"
+ #include "access/xlog.h"
#include "catalog/indexing.h"
+ #include "catalog/namespace.h"
#include "catalog/pg_autovacuum.h"
#include "catalog/pg_database.h"
#include "commands/vacuum.h"
***************
*** 41,46 ****
--- 43,49 ----
#include "tcop/tcopprot.h"
#include "utils/flatfiles.h"
#include "utils/fmgroids.h"
+ #include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/ps_status.h"
#include "utils/relcache.h"
***************
*** 68,74 ****
--- 71,79 ----
{
Oid oid;
char *name;
+ TransactionId frozenxid;
PgStat_StatDBEntry *entry;
+ int32 age;
} autovac_dbase;
***************
*** 76,83 ****
static pid_t autovac_forkexec(void);
#endif
NON_EXEC_STATIC void AutoVacMain(int argc, char *argv[]);
! static void autovac_check_wraparound(void);
! static void do_autovacuum(PgStat_StatDBEntry *dbentry);
static List *autovac_get_database_list(void);
static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
Form_pg_class classForm, Form_pg_autovacuum avForm,
--- 81,87 ----
static pid_t autovac_forkexec(void);
#endif
NON_EXEC_STATIC void AutoVacMain(int argc, char *argv[]);
! static void do_autovacuum(bool whole_db, PgStat_StatDBEntry *dbentry);
static List *autovac_get_database_list(void);
static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
Form_pg_class classForm, Form_pg_autovacuum avForm,
***************
*** 194,200 ****
--- 198,206 ----
ListCell *cell;
List *dblist;
autovac_dbase *db;
+ bool whole_db = false;
sigjmp_buf local_sigjmp_buf;
+ TransactionId nextXid;
/* we are a postmaster subprocess now */
IsUnderPostmaster = true;
***************
*** 269,289 ****
/*
* Choose a database to connect to. We pick the database that was least
! * recently auto-vacuumed.
*
* XXX This could be improved if we had more info about whether it needs
* vacuuming before connecting to it. Perhaps look through the pgstats
! * data for the database's tables?
! *
! * XXX it is NOT good that we totally ignore databases that have no
! * pgstats entry ...
*/
db = NULL;
foreach(cell, dblist)
{
! autovac_dbase *tmp = lfirst(cell);
tmp->entry = pgstat_fetch_stat_dbentry(tmp->oid);
if (!tmp->entry)
continue;
--- 275,335 ----
/*
* Choose a database to connect to. We pick the database that was least
! * recently auto-vacuumed, or one that needs database-wide vacuum (to
! * prevent Xid wraparound-related data loss.)
! *
! * Note that a database with no stats entry is not considered, except
! * for Xid wraparound purposes. The theory is that if no one has ever
! * connected to it since the stats were last initialized, it doesn't
! * need vacuuming.
*
* XXX This could be improved if we had more info about whether it needs
* vacuuming before connecting to it. Perhaps look through the pgstats
! * data for the database's tables? One idea is to keep track of the
! * number of new and dead tuples per database in pgstats. However it
! * isn't clear how to construct a metric that measures that and not
! * cause starvation for less busy databases.
*/
db = NULL;
+ /*
+ * Get the next Xid that was current as of the last checkpoint.
+ * We will use it to determine whether databases are about to need
+ * database-wide vacuums.
+ */
+ ReadControlFile(&nextXid);
+
foreach(cell, dblist)
{
! autovac_dbase *tmp = lfirst(cell);
! bool this_whole_db;
!
! /*
! * We
! * look for the database that most urgently needs a database-wide
! * vacuum. As soon as one is found, any other database is
! * ignored. We decide to vacuum a little earlier than when vacuum.c's
! * vac_truncate_clog() would decide start giving warnings.
! */
! tmp->age = (int32) (nextXid - tmp->frozenxid);
! this_whole_db = (tmp->age > (int32) ((MaxTransactionId >> 3) * 3));
!
! if (whole_db || this_whole_db)
! {
! if (!this_whole_db)
! continue;
! if (!db || tmp->age > db->age)
! {
! db = tmp;
! whole_db = true;
! }
! continue;
! }
+ /*
+ * Skip a database with no pgstat entry; it means it hasn't seen
+ * any activity.
+ */
tmp->entry = pgstat_fetch_stat_dbentry(tmp->oid);
if (!tmp->entry)
continue;
***************
*** 316,322 ****
/*
* And do an appropriate amount of work on it
*/
! do_autovacuum(db->entry);
}
/* One iteration done, go away */
--- 362,368 ----
/*
* And do an appropriate amount of work on it
*/
! do_autovacuum(whole_db, db->entry);
}
/* One iteration done, go away */
***************
*** 338,343 ****
--- 384,390 ----
FILE *db_file;
Oid db_id;
Oid db_tablespace;
+ TransactionId db_frozenxid;
filename = database_getflatfilename();
db_file = AllocateFile(filename, "r");
***************
*** 346,352 ****
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m", filename)));
! while (read_pg_database_line(db_file, thisname, &db_id, &db_tablespace))
{
autovac_dbase *db;
--- 393,400 ----
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m", filename)));
! while (read_pg_database_line(db_file, thisname, &db_id, &db_tablespace,
! &db_frozenxid))
{
autovac_dbase *db;
***************
*** 354,359 ****
--- 402,408 ----
db->oid = db_id;
db->name = pstrdup(thisname);
+ db->frozenxid = db_frozenxid;
/* this gets set later */
db->entry = NULL;
***************
*** 369,374 ****
--- 418,429 ----
/*
* Process a database.
*
+ * If whole_db is true, the database is processed as a whole, and the
+ * dbentry parameter is ignored. If it's false, dbentry must hold a valid
+ * pointer to the database entry in the stats databases' hash table, and
+ * it will be used to determine whether vacuum or analyze is needed on a
+ * table per table basis.
+ *
* Note that test_rel_for_autovac generates two separate lists, one for
* vacuum and other for analyze. This is to facilitate processing all
* analyzes first, and then all vacuums.
***************
*** 377,383 ****
* order not to ignore shutdown commands for too long.
*/
static void
! do_autovacuum(PgStat_StatDBEntry *dbentry)
{
Relation classRel,
avRel;
--- 432,438 ----
* order not to ignore shutdown commands for too long.
*/
static void
! do_autovacuum(bool whole_db, PgStat_StatDBEntry *dbentry)
{
Relation classRel,
avRel;
***************
*** 387,392 ****
--- 442,449 ----
*analyze_tables = NIL;
MemoryContext AutovacMemCxt;
+ Assert(whole_db || PointerIsValid(dbentry));
+
/* Memory context where cross-transaction state is stored */
AutovacMemCxt = AllocSetContextCreate(TopMemoryContext,
"Autovacuum context",
***************
*** 405,485 ****
*/
MemoryContextSwitchTo(AutovacMemCxt);
! /*
! * If this database is old enough to need a whole-database VACUUM,
! * don't bother checking each table. If that happens, this function
! * will issue the VACUUM command and won't return.
! */
! autovac_check_wraparound();
!
! CHECK_FOR_INTERRUPTS();
!
! classRel = heap_open(RelationRelationId, AccessShareLock);
! avRel = heap_open(AutovacuumRelationId, AccessShareLock);
!
! relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL);
!
! /* Scan pg_class looking for tables to vacuum */
! while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
{
! Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
! Form_pg_autovacuum avForm = NULL;
! PgStat_StatTabEntry *tabentry;
! SysScanDesc avScan;
! HeapTuple avTup;
! ScanKeyData entry[1];
! Oid relid;
!
! /* Skip non-table entries. */
! /* XXX possibly allow RELKIND_TOASTVALUE entries here too? */
! if (classForm->relkind != RELKIND_RELATION)
! continue;
!
! relid = HeapTupleGetOid(tuple);
!
! /* See if we have a pg_autovacuum entry for this relation. */
! ScanKeyInit(&entry[0],
! Anum_pg_autovacuum_vacrelid,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(relid));
!
! avScan = systable_beginscan(avRel, AutovacuumRelidIndexId, true,
! SnapshotNow, 1, entry);
!
! avTup = systable_getnext(avScan);
!
! if (HeapTupleIsValid(avTup))
! avForm = (Form_pg_autovacuum) GETSTRUCT(avTup);
!
! tabentry = hash_search(dbentry->tables, &relid,
! HASH_FIND, NULL);
! test_rel_for_autovac(relid, tabentry, classForm, avForm,
! &vacuum_tables, &analyze_tables);
! systable_endscan(avScan);
! }
! heap_endscan(relScan);
! heap_close(avRel, AccessShareLock);
! heap_close(classRel, AccessShareLock);
! CHECK_FOR_INTERRUPTS();
! /*
! * Perform operations on collected tables.
! */
! if (analyze_tables)
! autovacuum_do_vac_analyze(analyze_tables, false);
! CHECK_FOR_INTERRUPTS();
! /* get back to proper context */
! MemoryContextSwitchTo(AutovacMemCxt);
! if (vacuum_tables)
! autovacuum_do_vac_analyze(vacuum_tables, true);
/* Finally close out the last transaction. */
CommitTransactionCommand();
--- 462,556 ----
*/
MemoryContextSwitchTo(AutovacMemCxt);
! if (whole_db)
{
! elog(DEBUG2, "autovacuum: VACUUM ANALYZE whole database");
! autovacuum_do_vac_analyze(NIL, true);
! }
! else
! {
! /* the hash entry where pgstat stores shared relations */
! PgStat_StatDBEntry *shared = pgstat_fetch_stat_dbentry(InvalidOid);
! CHECK_FOR_INTERRUPTS();
! classRel = heap_open(RelationRelationId, AccessShareLock);
! avRel = heap_open(AutovacuumRelationId, AccessShareLock);
! relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL);
!
! /* Scan pg_class looking for tables to vacuum */
! while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
! {
! Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
! Form_pg_autovacuum avForm = NULL;
! PgStat_StatTabEntry *tabentry;
! SysScanDesc avScan;
! HeapTuple avTup;
! ScanKeyData entry[1];
! Oid relid;
! Oid nspOid;
!
! /* Skip non-table entries. */
! /* XXX possibly allow RELKIND_TOASTVALUE entries here too? */
! if (classForm->relkind != RELKIND_RELATION)
! continue;
!
! relid = HeapTupleGetOid(tuple);
!
! /* Skip temp tables (i.e. those in temp namespaces) */
! nspOid = get_rel_namespace(relid);
! if (isTempNamespace(nspOid))
! continue;
!
! /* See if we have a pg_autovacuum entry for this relation. */
! ScanKeyInit(&entry[0],
! Anum_pg_autovacuum_vacrelid,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(relid));
!
! avScan = systable_beginscan(avRel, AutovacuumRelidIndexId, true,
! SnapshotNow, 1, entry);
!
! avTup = systable_getnext(avScan);
!
! if (HeapTupleIsValid(avTup))
! avForm = (Form_pg_autovacuum) GETSTRUCT(avTup);
!
! if (classForm->relisshared && PointerIsValid(shared))
! tabentry = hash_search(shared->tables, &relid,
! HASH_FIND, NULL);
! else
! tabentry = hash_search(dbentry->tables, &relid,
! HASH_FIND, NULL);
!
! test_rel_for_autovac(relid, tabentry, classForm, avForm,
! &vacuum_tables, &analyze_tables);
!
! systable_endscan(avScan);
! }
!
! heap_endscan(relScan);
! heap_close(avRel, AccessShareLock);
! heap_close(classRel, AccessShareLock);
! CHECK_FOR_INTERRUPTS();
! /*
! * Perform operations on collected tables.
! */
! if (analyze_tables)
! autovacuum_do_vac_analyze(analyze_tables, false);
! CHECK_FOR_INTERRUPTS();
! /* get back to proper context */
! MemoryContextSwitchTo(AutovacMemCxt);
! if (vacuum_tables)
! autovacuum_do_vac_analyze(vacuum_tables, true);
! }
/* Finally close out the last transaction. */
CommitTransactionCommand();
***************
*** 503,509 ****
* analyze. This is asymmetric to the VACUUM case.
*
* A table whose pg_autovacuum.enabled value is false, is automatically
! * skipped. Thus autovacuum can be disabled for specific tables.
*
* A table whose vac_base_thresh value is <0 takes the base value from the
* autovacuum_vacuum_threshold GUC variable. Similarly, a vac_scale_factor
--- 574,582 ----
* analyze. This is asymmetric to the VACUUM case.
*
* A table whose pg_autovacuum.enabled value is false, is automatically
! * skipped. Thus autovacuum can be disabled for specific tables. Also,
! * when the stats collector does not have data about a table, it will be
! * skipped.
*
* A table whose vac_base_thresh value is <0 takes the base value from the
* autovacuum_vacuum_threshold GUC variable. Similarly, a vac_scale_factor
***************
*** 533,558 ****
/* User disabled it in pg_autovacuum? */
if (avForm && !avForm->enabled)
return;
rel = RelationIdGetRelation(relid);
/* The table was recently dropped? */
! if (rel == NULL)
! return;
!
! /* Not found in stat hash? */
! if (tabentry == NULL)
! {
! /*
! * Analyze this table. It will emit a stat message for the
! * collector that will initialize the entry for the next time
! * around, so we won't have to guess again.
! */
! elog(DEBUG2, "table %s not known to stat system, will ANALYZE",
! RelationGetRelationName(rel));
! *analyze_tables = lappend_oid(*analyze_tables, relid);
! RelationClose(rel);
return;
- }
reltuples = rel->rd_rel->reltuples;
vactuples = tabentry->n_dead_tuples;
--- 606,623 ----
/* User disabled it in pg_autovacuum? */
if (avForm && !avForm->enabled)
return;
+ /*
+ * Skip a table not found in stat hash. If it's not acted upon,
+ * there's no need to vacuum it. (Note that database-level check
+ * will take care of Xid wraparound.)
+ */
+ if (!PointerIsValid(tabentry))
+ return;
rel = RelationIdGetRelation(relid);
/* The table was recently dropped? */
! if (!PointerIsValid(rel))
return;
reltuples = rel->rd_rel->reltuples;
vactuples = tabentry->n_dead_tuples;
***************
*** 607,615 ****
}
else if (anltuples > anlthresh)
{
! elog(DEBUG2, "will ANALYZE %s",
! RelationGetRelationName(rel));
! *analyze_tables = lappend_oid(*analyze_tables, relid);
}
RelationClose(rel);
--- 672,684 ----
}
else if (anltuples > anlthresh)
{
! /* ANALYZE refuses to work with pg_statistics */
! if (relid != StatisticRelationId)
! {
! elog(DEBUG2, "will ANALYZE %s",
! RelationGetRelationName(rel));
! *analyze_tables = lappend_oid(*analyze_tables, relid);
! }
}
RelationClose(rel);
***************
*** 646,706 ****
}
/*
- * autovac_check_wraparound
- * Check database Xid wraparound
- *
- * Check pg_database to see if the last database-wide VACUUM was too long ago,
- * and issue one now if so. If this comes to pass, we do not return, as there
- * is no point in checking individual tables -- they will all get vacuumed
- * anyway.
- */
- static void
- autovac_check_wraparound(void)
- {
- Relation relation;
- ScanKeyData entry[1];
- HeapScanDesc scan;
- HeapTuple tuple;
- Form_pg_database dbform;
- int32 age;
- bool whole_db;
-
- relation = heap_open(DatabaseRelationId, AccessShareLock);
-
- /* Must use a heap scan, since there's no syscache for pg_database */
- ScanKeyInit(&entry[0],
- ObjectIdAttributeNumber,
- BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(MyDatabaseId));
-
- scan = heap_beginscan(relation, SnapshotNow, 1, entry);
-
- tuple = heap_getnext(scan, ForwardScanDirection);
-
- if (!HeapTupleIsValid(tuple))
- elog(ERROR, "could not find tuple for database %u", MyDatabaseId);
-
- dbform = (Form_pg_database) GETSTRUCT(tuple);
-
- /*
- * We decide to vacuum at the same point where vacuum.c's
- * vac_truncate_clog() would decide to start giving warnings.
- */
- age = (int32) (GetTopTransactionId() - dbform->datfrozenxid);
- whole_db = (age > (int32) ((MaxTransactionId >> 3) * 3));
-
- heap_endscan(scan);
- heap_close(relation, AccessShareLock);
-
- if (whole_db)
- {
- elog(LOG, "autovacuum: VACUUM ANALYZE whole database");
- autovacuum_do_vac_analyze(NIL, true);
- proc_exit(0);
- }
- }
-
- /*
* AutoVacuumingActive
* Check GUC vars and report whether the autovacuum process should be
* running.
--- 715,720 ----
Index: postmaster/pgstat.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.101
diff -c -r1.101 pgstat.c
*** postmaster/pgstat.c 24 Jul 2005 00:33:28 -0000 1.101
--- postmaster/pgstat.c 26 Jul 2005 04:05:14 -0000
***************
*** 119,130 ****
static bool pgStatRunningInCollector = FALSE;
! static int pgStatTabstatAlloc = 0;
! static int pgStatTabstatUsed = 0;
! static PgStat_MsgTabstat **pgStatTabstatMessages = NULL;
#define TABSTAT_QUANTUM 4 /* we alloc this many at a time */
static int pgStatXactCommit = 0;
static int pgStatXactRollback = 0;
--- 119,141 ----
static bool pgStatRunningInCollector = FALSE;
! /*
! * Place where backends store per-table info to be sent to the collector.
! * We store shared relations separately from non-shared ones, to be able to
! * send them in separate messages.
! */
! typedef struct TabStatArray
! {
! int tsa_alloc; /* num allocated */
! int tsa_used; /* num actually used */
! PgStat_MsgTabstat **tsa_messages; /* the array itself */
! } TabStatArray;
#define TABSTAT_QUANTUM 4 /* we alloc this many at a time */
+ static TabStatArray RegularTabStat = { 0, 0, NULL };
+ static TabStatArray SharedTabStat = { 0, 0, NULL };
+
static int pgStatXactCommit = 0;
static int pgStatXactRollback = 0;
***************
*** 158,164 ****
static void pgstat_die(SIGNAL_ARGS);
static void pgstat_beshutdown_hook(int code, Datum arg);
! static PgStat_StatDBEntry *pgstat_get_db_entry(Oid databaseid);
static int pgstat_add_backend(PgStat_MsgHdr *msg);
static void pgstat_sub_backend(int procpid);
static void pgstat_drop_database(Oid databaseid);
--- 169,175 ----
static void pgstat_die(SIGNAL_ARGS);
static void pgstat_beshutdown_hook(int code, Datum arg);
! static PgStat_StatDBEntry *pgstat_get_db_entry(Oid databaseid, bool create);
static int pgstat_add_backend(PgStat_MsgHdr *msg);
static void pgstat_sub_backend(int procpid);
static void pgstat_drop_database(Oid databaseid);
***************
*** 614,619 ****
--- 625,631 ----
if (pgStatSock < 0)
return;
+ /* can't use pgstat_setheader() because it's not called in a backend */
MemSet(&(msg.m_hdr), 0, sizeof(msg.m_hdr));
msg.m_hdr.m_type = PGSTAT_MTYPE_BETERM;
msg.m_hdr.m_procpid = pid;
***************
*** 684,690 ****
* ---------
*/
void
! pgstat_report_vacuum(Oid tableoid, bool analyze, PgStat_Counter tuples)
{
PgStat_MsgVacuum msg;
--- 696,703 ----
* ---------
*/
void
! pgstat_report_vacuum(Oid tableoid, bool shared, bool analyze,
! PgStat_Counter tuples)
{
PgStat_MsgVacuum msg;
***************
*** 692,698 ****
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_VACUUM);
! msg.m_databaseid = MyDatabaseId;
msg.m_tableoid = tableoid;
msg.m_analyze = analyze;
msg.m_tuples = tuples;
--- 705,711 ----
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_VACUUM);
! msg.m_databaseid = shared ? InvalidOid : MyDatabaseId;
msg.m_tableoid = tableoid;
msg.m_analyze = analyze;
msg.m_tuples = tuples;
***************
*** 706,712 ****
* --------
*/
void
! pgstat_report_analyze(Oid tableoid, PgStat_Counter livetuples,
PgStat_Counter deadtuples)
{
PgStat_MsgAnalyze msg;
--- 719,725 ----
* --------
*/
void
! pgstat_report_analyze(Oid tableoid, bool shared, PgStat_Counter livetuples,
PgStat_Counter deadtuples)
{
PgStat_MsgAnalyze msg;
***************
*** 715,721 ****
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_ANALYZE);
! msg.m_databaseid = MyDatabaseId;
msg.m_tableoid = tableoid;
msg.m_live_tuples = livetuples;
msg.m_dead_tuples = deadtuples;
--- 728,734 ----
return;
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_ANALYZE);
! msg.m_databaseid = shared ? InvalidOid : MyDatabaseId;
msg.m_tableoid = tableoid;
msg.m_live_tuples = livetuples;
msg.m_dead_tuples = deadtuples;
***************
*** 784,790 ****
pgstat_collect_blocklevel))
{
/* Not reporting stats, so just flush whatever we have */
! pgStatTabstatUsed = 0;
return;
}
--- 797,804 ----
pgstat_collect_blocklevel))
{
/* Not reporting stats, so just flush whatever we have */
! RegularTabStat.tsa_used = 0;
! SharedTabStat.tsa_used = 0;
return;
}
***************
*** 792,800 ****
* For each message buffer used during the last query set the header
* fields and send it out.
*/
! for (i = 0; i < pgStatTabstatUsed; i++)
{
! PgStat_MsgTabstat *tsmsg = pgStatTabstatMessages[i];
int n;
int len;
--- 806,814 ----
* For each message buffer used during the last query set the header
* fields and send it out.
*/
! for (i = 0; i < RegularTabStat.tsa_used; i++)
{
! PgStat_MsgTabstat *tsmsg = RegularTabStat.tsa_messages[i];
int n;
int len;
***************
*** 811,818 ****
tsmsg->m_databaseid = MyDatabaseId;
pgstat_send(tsmsg, len);
}
! pgStatTabstatUsed = 0;
}
--- 825,850 ----
tsmsg->m_databaseid = MyDatabaseId;
pgstat_send(tsmsg, len);
}
+ RegularTabStat.tsa_used = 0;
+
+ /* Ditto, for shared relations */
+ for (i = 0; i < SharedTabStat.tsa_used; i++)
+ {
+ PgStat_MsgTabstat *tsmsg = SharedTabStat.tsa_messages[i];
+ int n;
+ int len;
! n = tsmsg->m_nentries;
! len = offsetof(PgStat_MsgTabstat, m_entry[0]) +
! n * sizeof(PgStat_TableEntry);
!
! /* We don't report transaction commit/abort here */
!
! pgstat_setheader(&tsmsg->m_hdr, PGSTAT_MTYPE_TABSTAT);
! tsmsg->m_databaseid = InvalidOid;
! pgstat_send(tsmsg, len);
! }
! SharedTabStat.tsa_used = 0;
}
***************
*** 1045,1081 ****
}
/*
! * Create or enlarge the pgStatTabstatMessages array
*/
static void
! more_tabstat_space(void)
{
PgStat_MsgTabstat *newMessages;
PgStat_MsgTabstat **msgArray;
! int newAlloc = pgStatTabstatAlloc + TABSTAT_QUANTUM;
int i;
/* Create (another) quantum of message buffers */
newMessages = (PgStat_MsgTabstat *)
MemoryContextAllocZero(TopMemoryContext,
sizeof(PgStat_MsgTabstat) * TABSTAT_QUANTUM);
/* Create or enlarge the pointer array */
! if (pgStatTabstatMessages == NULL)
msgArray = (PgStat_MsgTabstat **)
MemoryContextAlloc(TopMemoryContext,
sizeof(PgStat_MsgTabstat *) * newAlloc);
else
msgArray = (PgStat_MsgTabstat **)
! repalloc(pgStatTabstatMessages,
sizeof(PgStat_MsgTabstat *) * newAlloc);
for (i = 0; i < TABSTAT_QUANTUM; i++)
! msgArray[pgStatTabstatAlloc + i] = newMessages++;
! pgStatTabstatMessages = msgArray;
! pgStatTabstatAlloc = newAlloc;
! Assert(pgStatTabstatUsed < pgStatTabstatAlloc);
}
/* ----------
--- 1077,1117 ----
}
/*
! * Enlarge a TabStatArray
*/
static void
! more_tabstat_space(TabStatArray *tsarr)
{
PgStat_MsgTabstat *newMessages;
PgStat_MsgTabstat **msgArray;
! int newAlloc;
int i;
+ AssertArg(PointerIsValid(tsarr));
+
+ newAlloc = tsarr->tsa_alloc + TABSTAT_QUANTUM;
+
/* Create (another) quantum of message buffers */
newMessages = (PgStat_MsgTabstat *)
MemoryContextAllocZero(TopMemoryContext,
sizeof(PgStat_MsgTabstat) * TABSTAT_QUANTUM);
/* Create or enlarge the pointer array */
! if (tsarr->tsa_messages == NULL)
msgArray = (PgStat_MsgTabstat **)
MemoryContextAlloc(TopMemoryContext,
sizeof(PgStat_MsgTabstat *) * newAlloc);
else
msgArray = (PgStat_MsgTabstat **)
! repalloc(tsarr->tsa_messages,
sizeof(PgStat_MsgTabstat *) * newAlloc);
for (i = 0; i < TABSTAT_QUANTUM; i++)
! msgArray[tsarr->tsa_alloc + i] = newMessages++;
! tsarr->tsa_messages = msgArray;
! tsarr->tsa_alloc = newAlloc;
! Assert(tsarr->tsa_used < tsarr->tsa_alloc);
}
/* ----------
***************
*** 1092,1097 ****
--- 1128,1134 ----
{
Oid rel_id = rel->rd_id;
PgStat_TableEntry *useent;
+ TabStatArray *tsarr;
PgStat_MsgTabstat *tsmsg;
int mb;
int i;
***************
*** 1112,1123 ****
return;
}
/*
* Search the already-used message slots for this relation.
*/
! for (mb = 0; mb < pgStatTabstatUsed; mb++)
{
! tsmsg = pgStatTabstatMessages[mb];
for (i = tsmsg->m_nentries; --i >= 0;)
{
--- 1149,1162 ----
return;
}
+ tsarr = rel->rd_rel->relisshared ? &SharedTabStat : &RegularTabStat;
+
/*
* Search the already-used message slots for this relation.
*/
! for (mb = 0; mb < tsarr->tsa_used; mb++)
{
! tsmsg = tsarr->tsa_messages[mb];
for (i = tsmsg->m_nentries; --i >= 0;)
{
***************
*** 1146,1159 ****
/*
* If we ran out of message buffers, we just allocate more.
*/
! if (pgStatTabstatUsed >= pgStatTabstatAlloc)
! more_tabstat_space();
/*
* Use the first entry of the next message buffer.
*/
! mb = pgStatTabstatUsed++;
! tsmsg = pgStatTabstatMessages[mb];
tsmsg->m_nentries = 1;
useent = &tsmsg->m_entry[0];
MemSet(useent, 0, sizeof(PgStat_TableEntry));
--- 1185,1198 ----
/*
* If we ran out of message buffers, we just allocate more.
*/
! if (tsarr->tsa_used >= tsarr->tsa_alloc)
! more_tabstat_space(tsarr);
/*
* Use the first entry of the next message buffer.
*/
! mb = tsarr->tsa_used++;
! tsmsg = tsarr->tsa_messages[mb];
tsmsg->m_nentries = 1;
useent = &tsmsg->m_entry[0];
MemSet(useent, 0, sizeof(PgStat_TableEntry));
***************
*** 1183,1195 ****
* message buffer used without slots, causing the next report to tell
* new xact-counters.
*/
! if (pgStatTabstatAlloc == 0)
! more_tabstat_space();
! if (pgStatTabstatUsed == 0)
{
! pgStatTabstatUsed++;
! pgStatTabstatMessages[0]->m_nentries = 0;
}
}
--- 1222,1234 ----
* message buffer used without slots, causing the next report to tell
* new xact-counters.
*/
! if (RegularTabStat.tsa_alloc == 0)
! more_tabstat_space(&RegularTabStat);
! if (RegularTabStat.tsa_used == 0)
{
! RegularTabStat.tsa_used++;
! RegularTabStat.tsa_messages[0]->m_nentries = 0;
}
}
***************
*** 1215,1227 ****
* message buffer used without slots, causing the next report to tell
* new xact-counters.
*/
! if (pgStatTabstatAlloc == 0)
! more_tabstat_space();
! if (pgStatTabstatUsed == 0)
{
! pgStatTabstatUsed++;
! pgStatTabstatMessages[0]->m_nentries = 0;
}
}
--- 1254,1266 ----
* message buffer used without slots, causing the next report to tell
* new xact-counters.
*/
! if (RegularTabStat.tsa_alloc == 0)
! more_tabstat_space(&RegularTabStat);
! if (RegularTabStat.tsa_used == 0)
{
! RegularTabStat.tsa_used++;
! RegularTabStat.tsa_messages[0]->m_nentries = 0;
}
}
***************
*** 2107,2124 ****
/*
* Lookup the hash table entry for the specified database. If no hash
! * table entry exists, initialize it.
*/
static PgStat_StatDBEntry *
! pgstat_get_db_entry(Oid databaseid)
{
PgStat_StatDBEntry *result;
bool found;
/* Lookup or create the hash table entry for this database */
result = (PgStat_StatDBEntry *) hash_search(pgStatDBHash,
&databaseid,
! HASH_ENTER, &found);
/* If not found, initialize the new one. */
if (!found)
--- 2146,2168 ----
/*
* Lookup the hash table entry for the specified database. If no hash
! * table entry exists, initialize it, if the create parameter is true.
! * Else, return NULL.
*/
static PgStat_StatDBEntry *
! pgstat_get_db_entry(Oid databaseid, bool create)
{
PgStat_StatDBEntry *result;
bool found;
+ HASHACTION action = create ? HASH_ENTER : HASH_FIND;
/* Lookup or create the hash table entry for this database */
result = (PgStat_StatDBEntry *) hash_search(pgStatDBHash,
&databaseid,
! action, &found);
!
! if (!create && !found)
! return NULL;
/* If not found, initialize the new one. */
if (!found)
***************
*** 2387,2393 ****
* pgstat_read_statsfile() -
*
* Reads in an existing statistics collector and initializes the
! * databases hash table (who's entries point to the tables hash tables)
* and the current backend table.
* ----------
*/
--- 2431,2437 ----
* pgstat_read_statsfile() -
*
* Reads in an existing statistics collector and initializes the
! * databases' hash table (whose entries point to the tables' hash tables)
* and the current backend table.
* ----------
*/
***************
*** 2738,2751 ****
PgStat_StatDBEntry *dbentry;
/*
! * Lookup the database in the hashtable.
! *
! * XXX this creates the entry if it doesn't exist. Is this a problem? (We
! * could leak an entry if we send an autovac message and the database is
! * later destroyed, _and_ the messages are rearranged. Doesn't seem very
! * likely though.) Not sure what to do about it.
*/
! dbentry = pgstat_get_db_entry(msg->m_databaseid);
/*
* Store the last autovacuum time in the database entry.
--- 2782,2797 ----
PgStat_StatDBEntry *dbentry;
/*
! * Lookup the database in the hashtable. Don't create the entry if it
! * doesn't exist, because autovacuum may be processing a template
! * database. If this isn't the case, the database is most likely to
! * have an entry already. (If it doesn't, not much harm is done
! * anyway -- it'll get created as soon as somebody actually uses
! * the database.)
*/
! dbentry = pgstat_get_db_entry(msg->m_databaseid, false);
! if (dbentry == NULL)
! return;
/*
* Store the last autovacuum time in the database entry.
***************
*** 2765,2772 ****
PgStat_StatDBEntry *dbentry;
PgStat_StatTabEntry *tabentry;
bool found;
! dbentry = pgstat_get_db_entry(msg->m_databaseid);
tabentry = hash_search(dbentry->tables, &(msg->m_tableoid),
HASH_ENTER, &found);
--- 2811,2829 ----
PgStat_StatDBEntry *dbentry;
PgStat_StatTabEntry *tabentry;
bool found;
+ bool create;
+
+ /*
+ * If we don't know about the database, ignore the message, because it
+ * may be autovacuum processing a template database. If the message
+ * is for database InvalidOid, don't ignore it, because we are getting
+ * a message from vacuuming a shared relation.
+ */
+ create = msg->m_databaseid == InvalidOid;
! dbentry = pgstat_get_db_entry(msg->m_databaseid, create);
! if (dbentry == NULL)
! return;
tabentry = hash_search(dbentry->tables, &(msg->m_tableoid),
HASH_ENTER, &found);
***************
*** 2819,2825 ****
PgStat_StatTabEntry *tabentry;
bool found;
! dbentry = pgstat_get_db_entry(msg->m_databaseid);
tabentry = hash_search(dbentry->tables, &(msg->m_tableoid),
HASH_ENTER, &found);
--- 2876,2887 ----
PgStat_StatTabEntry *tabentry;
bool found;
! /*
! * Note that we do create the database entry here, as opposed to what
! * we do on AutovacStart and Vacuum messages. This is because
! * autovacuum never executes ANALYZE on template databases.
! */
! dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
tabentry = hash_search(dbentry->tables, &(msg->m_tableoid),
HASH_ENTER, &found);
***************
*** 2902,2908 ****
if (pgstat_add_backend(&msg->m_hdr) < 0)
return;
! dbentry = pgstat_get_db_entry(msg->m_databaseid);
/*
* If the database is marked for destroy, this is a delayed UDP packet
--- 2964,2970 ----
if (pgstat_add_backend(&msg->m_hdr) < 0)
return;
! dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
/*
* If the database is marked for destroy, this is a delayed UDP packet
***************
*** 2994,3000 ****
if (pgstat_add_backend(&msg->m_hdr) < 0)
return;
! dbentry = pgstat_get_db_entry(msg->m_databaseid);
/*
* If the database is marked for destroy, this is a delayed UDP packet
--- 3056,3062 ----
if (pgstat_add_backend(&msg->m_hdr) < 0)
return;
! dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
/*
* If the database is marked for destroy, this is a delayed UDP packet
***************
*** 3037,3043 ****
/*
* Lookup the database in the hashtable.
*/
! dbentry = pgstat_get_db_entry(msg->m_databaseid);
/*
* Mark the database for destruction.
--- 3099,3105 ----
/*
* Lookup the database in the hashtable.
*/
! dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
/*
* Mark the database for destruction.
***************
*** 3067,3073 ****
/*
* Lookup the database in the hashtable.
*/
! dbentry = pgstat_get_db_entry(msg->m_databaseid);
/*
* We simply throw away all the database's table entries by
--- 3129,3135 ----
/*
* Lookup the database in the hashtable.
*/
! dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
/*
* We simply throw away all the database's table entries by
Index: postmaster/postmaster.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.460
diff -c -r1.460 postmaster.c
*** postmaster/postmaster.c 21 Jul 2005 03:56:11 -0000 1.460
--- postmaster/postmaster.c 24 Jul 2005 17:54:31 -0000
***************
*** 1164,1176 ****
/*
* Wait for something to happen.
*
! * We wait at most one minute, to ensure that the other background
! * tasks handled below get done even when no requests are
! * arriving.
*/
memcpy((char *) &rmask, (char *) &readmask, sizeof(fd_set));
! timeout.tv_sec = 60;
timeout.tv_usec = 0;
PG_SETMASK(&UnBlockSig);
--- 1164,1176 ----
/*
* Wait for something to happen.
*
! * We wait at most one minute, or the minimum autovacuum delay, to
! * ensure that the other background tasks handled below get done
! * even when no requests are arriving.
*/
memcpy((char *) &rmask, (char *) &readmask, sizeof(fd_set));
! timeout.tv_sec = Min(60, autovacuum_naptime);
timeout.tv_usec = 0;
PG_SETMASK(&UnBlockSig);
Index: utils/init/postinit.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/init/postinit.c,v
retrieving revision 1.153
diff -c -r1.153 postinit.c
*** utils/init/postinit.c 14 Jul 2005 05:13:41 -0000 1.153
--- utils/init/postinit.c 24 Jul 2005 15:50:28 -0000
***************
*** 78,83 ****
--- 78,84 ----
char *filename;
FILE *db_file;
char thisname[NAMEDATALEN];
+ TransactionId frozenxid;
filename = database_getflatfilename();
db_file = AllocateFile(filename, "r");
***************
*** 86,92 ****
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m", filename)));
! while (read_pg_database_line(db_file, thisname, db_id, db_tablespace))
{
if (strcmp(thisname, name) == 0)
{
--- 87,94 ----
(errcode_for_file_access(),
errmsg("could not open file \"%s\": %m", filename)));
! while (read_pg_database_line(db_file, thisname, db_id, db_tablespace,
! &frozenxid))
{
if (strcmp(thisname, name) == 0)
{
***************
*** 170,179 ****
/*
* Also check that the database is currently allowing connections.
* (We do not enforce this in standalone mode, however, so that there is
! * a way to recover from "UPDATE pg_database SET datallowconn = false;")
*/
dbform = (Form_pg_database) GETSTRUCT(tup);
! if (IsUnderPostmaster && !dbform->datallowconn)
ereport(FATAL,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("database \"%s\" is not currently accepting connections",
--- 172,182 ----
/*
* Also check that the database is currently allowing connections.
* (We do not enforce this in standalone mode, however, so that there is
! * a way to recover from "UPDATE pg_database SET datallowconn = false;".
! * We do not enforce it for the autovacuum process either.)
*/
dbform = (Form_pg_database) GETSTRUCT(tup);
! if (IsUnderPostmaster && !IsAutoVacuumProcess() && !dbform->datallowconn)
ereport(FATAL,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("database \"%s\" is not currently accepting connections",
Index: utils/misc/guc.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.277
diff -c -r1.277 guc.c
*** utils/misc/guc.c 23 Jul 2005 21:05:47 -0000 1.277
--- utils/misc/guc.c 24 Jul 2005 17:55:56 -0000
***************
*** 1412,1423 ****
},
{
{"autovacuum_naptime", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Time to sleep between autovacuum runs, in seconds."),
NULL
},
&autovacuum_naptime,
! 60, 0, INT_MAX, NULL, NULL
},
{
{"autovacuum_vacuum_threshold", PGC_SIGHUP, AUTOVACUUM,
--- 1412,1427 ----
},
{
+ /*
+ * Note we set an artificial minimum of 1 sec, so that the
+ * postmaster's main loop does not turn into busy-wait.
+ */
{"autovacuum_naptime", PGC_SIGHUP, AUTOVACUUM,
gettext_noop("Time to sleep between autovacuum runs, in seconds."),
NULL
},
&autovacuum_naptime,
! 60, 1, INT_MAX, NULL, NULL
},
{
{"autovacuum_vacuum_threshold", PGC_SIGHUP, AUTOVACUUM,
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.
On Mon, Jul 25, 2005 at 09:31:15AM +0800, Christopher Kings-Lynne wrote:
We have to consider what
happens at stat reset -- AFAICS there's no problem, because as soon as
the table sees some activity, it will be picked up by pgstat.
However, it would be bad if stats are reset right after some heavy
activity on a table. Maybe the only thing we need is documentation.What's the use-case for having the stat reset feature at all?
I believe I was the root cause of the pg_stat_reset() function. The
idea at the time was that if you decide to do a round of index
optimisation, you want to be able to search for unused indexes and
heavily seq. scanned tables.If you reset the stats you have 'clean' data to work with. For
instance, you can get 24 hours of clean stats data.
Ok, so there's a reason for having a manual stat-reset. However what's
the rationale for cleaning stats at postmaster start? In fact I think
it's actively bad because you lose any data you had before postmaster
stop/crash.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"I personally became interested in Linux while I was dating an English major
who wouldn't know an operating system if it walked up and bit him."
(Val Henson)
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
Ok, so there's a reason for having a manual stat-reset. However what's
the rationale for cleaning stats at postmaster start? In fact I think
it's actively bad because you lose any data you had before postmaster
stop/crash.
We probably *should* drop the stats file if any WAL replay activity
occurs, because the stats file could be out of sync with reality
--- this is particularly important in a PITR recovery situation,
where the stats file is likely to be WAY out of sync. (Maybe only
clobber it in PITR mode?)I agree that the argument for doing it in a normal restart is pretty
weak.
regards, tom lane
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 19073Bruce 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 19073On 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)
Attachments:
autovac-three-1.patchtext/plain; charset=us-asciiDownload
Index: doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.109
diff -c -r2.109 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 26 Jul 2005 16:38:25 -0000 2.109
--- doc/src/sgml/catalogs.sgml 31 Jul 2005 20:19:20 -0000
***************
*** 1187,1192 ****
--- 1187,1206 ----
<entry>Multiplier for reltuples to add to
<structfield>anl_base_thresh</></entry>
</row>
+
+ <row>
+ <entry><structfield>vac_cost_delay</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry></entry>
+ <entry>Custom <variable>vacuum_cost_delay</> parameter</entry>
+ </row>
+
+ <row>
+ <entry><structfield>vac_cost_limit</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry></entry>
+ <entry>Custom <variable>vacuum_cost_limit</> parameter</entry>
+ </row>
</tbody>
</tgroup>
</table>
***************
*** 1207,1213 ****
<para>
Any of the numerical fields can contain <literal>-1</> (or indeed
any negative value) to indicate that the system-wide default should
! be used for this particular value.
</para>
</sect1>
--- 1221,1231 ----
<para>
Any of the numerical fields can contain <literal>-1</> (or indeed
any negative value) to indicate that the system-wide default should
! be used for this particular value. Observe that the
! <structfield>vac_cost_delay</> variable inherits its default value from the
! <varname>autovacuum_vacuum_cost_delay</> configuration parameter,
! or from <varname>vacuum_cost_delay</> if the former is set to a negative
! value. The same applies to <structfield>vac_cost_limit</>.
</para>
</sect1>
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.341
diff -c -r1.341 runtime.sgml
*** doc/src/sgml/runtime.sgml 30 Jul 2005 17:15:35 -0000 1.341
--- doc/src/sgml/runtime.sgml 31 Jul 2005 23:22:11 -0000
***************
*** 3399,3404 ****
--- 3399,3436 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
+ <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the default value that will be applied to each
+ <command>VACUUM</> operation, for tables that do not have
+ a default value set in <structname>pg_autovacuum</>. If a
+ negative value is specified (like the default value of -1),
+ the <varname>vacuum_cost_delay</> value will be applied instead.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-autovacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
+ <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the default value that will be applied to each
+ <command>VACUUM</> operation, for tables that do not have
+ a default value set in <structname>pg_autovacuum</>. If a
+ negative value is specified (like the default value of -1),
+ the <varname>vacuum_cost_limit</> value will be applied instead.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.214
diff -c -r1.214 xlog.c
*** src/backend/access/transam/xlog.c 30 Jul 2005 14:15:44 -0000 1.214
--- src/backend/access/transam/xlog.c 31 Jul 2005 21:36:45 -0000
***************
*** 33,38 ****
--- 33,39 ----
#include "catalog/catversion.h"
#include "catalog/pg_control.h"
#include "miscadmin.h"
+ #include "pgstat.h"
#include "postmaster/bgwriter.h"
#include "storage/bufpage.h"
#include "storage/fd.h"
***************
*** 48,54 ****
/*
! * Becauase O_DIRECT bypasses the kernel buffers, and because we never
* read those buffers except during crash recovery, it is a win to use
* it in all cases where we sync on each write(). We could allow O_DIRECT
* with fsync(), but because skipping the kernel buffer forces writes out
--- 49,55 ----
/*
! * Because O_DIRECT bypasses the kernel buffers, and because we never
* read those buffers except during crash recovery, it is a win to use
* it in all cases where we sync on each write(). We could allow O_DIRECT
* with fsync(), but because skipping the kernel buffer forces writes out
***************
*** 4544,4549 ****
--- 4545,4555 ----
} while (record != NULL && recoveryContinue);
/*
+ * Reset pgstat data, because it may be invalid after recovery.
+ */
+ pgstat_reset_all();
+
+ /*
* end of main redo apply loop
*/
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.2
diff -c -r1.2 autovacuum.c
*** src/backend/postmaster/autovacuum.c 29 Jul 2005 19:30:04 -0000 1.2
--- src/backend/postmaster/autovacuum.c 31 Jul 2005 22:45:53 -0000
***************
*** 57,62 ****
--- 57,65 ----
int autovacuum_anl_thresh;
double autovacuum_anl_scale;
+ int autovacuum_vac_cost_delay;
+ int autovacuum_vac_cost_limit;
+
/* Flag to tell if we are in the autovacuum daemon process */
static bool am_autovacuum = false;
***************
*** 74,79 ****
--- 77,89 ----
int32 age;
} autovac_dbase;
+ /* struct to keep track of tables to vacuum */
+ typedef struct autovac_table
+ {
+ Oid relid;
+ int vacuum_cost_delay;
+ int vacuum_cost_limit;
+ } autovac_table;
#ifdef EXEC_BACKEND
static pid_t autovac_forkexec(void);
***************
*** 84,90 ****
static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
Form_pg_class classForm, Form_pg_autovacuum avForm,
List **vacuum_tables, List **analyze_tables);
! static void autovacuum_do_vac_analyze(List *relids, bool dovacuum);
/*
--- 94,101 ----
static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
Form_pg_class classForm, Form_pg_autovacuum avForm,
List **vacuum_tables, List **analyze_tables);
! static void autovacuum_do_vac_analyze(List *relids, bool doanalyze,
! bool dovacuum, bool full);
/*
***************
*** 469,476 ****
if (whole_db)
{
! elog(DEBUG2, "autovacuum: VACUUM ANALYZE whole database");
! autovacuum_do_vac_analyze(NIL, true);
}
else
{
--- 480,487 ----
if (whole_db)
{
! elog(DEBUG2, "autovacuum: VACUUM FULL whole database");
! autovacuum_do_vac_analyze(NIL, false, true, true);
}
else
{
***************
*** 545,559 ****
*/
if (analyze_tables)
! autovacuum_do_vac_analyze(analyze_tables, false);
CHECK_FOR_INTERRUPTS();
- /* get back to proper context */
- MemoryContextSwitchTo(AutovacMemCxt);
-
if (vacuum_tables)
! autovacuum_do_vac_analyze(vacuum_tables, true);
}
/* Finally close out the last transaction. */
--- 556,585 ----
*/
if (analyze_tables)
! autovacuum_do_vac_analyze(analyze_tables, true, false, false);
CHECK_FOR_INTERRUPTS();
if (vacuum_tables)
! {
! ListCell *cell;
!
!
! foreach(cell, vacuum_tables)
! {
! autovac_table *tab = lfirst(cell);
!
! /* get back to proper context */
! MemoryContextSwitchTo(AutovacMemCxt);
!
! /* Set the cost vacuum parameters for this table */
! VacuumCostDelay = tab->vacuum_cost_delay;
! VacuumCostLimit = tab->vacuum_cost_limit;
!
! autovacuum_do_vac_analyze(list_make1_oid(tab->relid), true,
! true, true);
! }
! }
}
/* Finally close out the last transaction. */
***************
*** 606,611 ****
--- 632,640 ----
/* number of vacuum (resp. analyze) tuples at this time */
float4 vactuples,
anltuples;
+ /* cost-based vacuum delay parameters */
+ int vac_cost_limit;
+ int vac_cost_delay;
/* User disabled it in pg_autovacuum? */
if (avForm && !avForm->enabled)
***************
*** 645,650 ****
--- 674,687 ----
autovacuum_anl_scale : avForm->anl_scale_factor;
anl_base_thresh = (avForm->anl_base_thresh < 0) ?
autovacuum_anl_thresh : avForm->anl_base_thresh;
+
+ vac_cost_limit = (avForm->vac_cost_limit < 0) ?
+ (autovacuum_vac_cost_limit < 0) ? VacuumCostLimit :
+ autovacuum_vac_cost_limit : avForm->vac_cost_limit;
+
+ vac_cost_delay = (avForm->vac_cost_delay < 0) ?
+ (autovacuum_vac_cost_delay < 0) ? VacuumCostDelay :
+ autovacuum_vac_cost_delay : avForm->vac_cost_delay;
}
else
{
***************
*** 653,658 ****
--- 690,701 ----
anl_scale_factor = autovacuum_anl_scale;
anl_base_thresh = autovacuum_anl_thresh;
+
+ vac_cost_limit = (autovacuum_vac_cost_limit < 0) ?
+ VacuumCostLimit : autovacuum_vac_cost_limit;
+
+ vac_cost_delay = (autovacuum_vac_cost_delay < 0) ?
+ VacuumCostDelay : autovacuum_vac_cost_delay;
}
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
***************
*** 671,679 ****
/* Determine if this table needs vacuum or analyze. */
if (vactuples > vacthresh)
{
elog(DEBUG2, "will VACUUM ANALYZE %s",
RelationGetRelationName(rel));
! *vacuum_tables = lappend_oid(*vacuum_tables, relid);
}
else if (anltuples > anlthresh)
{
--- 714,730 ----
/* Determine if this table needs vacuum or analyze. */
if (vactuples > vacthresh)
{
+ autovac_table *tab = (autovac_table *)
+ palloc(sizeof(autovac_table));
+
elog(DEBUG2, "will VACUUM ANALYZE %s",
RelationGetRelationName(rel));
!
! tab->relid = relid;
! tab->vacuum_cost_limit = vac_cost_limit;
! tab->vacuum_cost_delay = vac_cost_delay;
!
! *vacuum_tables = lappend(*vacuum_tables, tab);
}
else if (anltuples > anlthresh)
{
***************
*** 691,702 ****
/*
* autovacuum_do_vac_analyze
! * Vacuum or analyze a list of tables; or all tables if relids = NIL
*
* We must be in AutovacMemCxt when this routine is called.
*/
static void
! autovacuum_do_vac_analyze(List *relids, bool dovacuum)
{
VacuumStmt *vacstmt = makeNode(VacuumStmt);
--- 742,754 ----
/*
* autovacuum_do_vac_analyze
! * Vacuum and/or analyze a list of tables; or all tables if relids = NIL
*
* We must be in AutovacMemCxt when this routine is called.
*/
static void
! autovacuum_do_vac_analyze(List *relids, bool doanalyze, bool dovacuum,
! bool full)
{
VacuumStmt *vacstmt = makeNode(VacuumStmt);
***************
*** 709,722 ****
/* Set up command parameters */
vacstmt->vacuum = dovacuum;
! vacstmt->full = false;
! vacstmt->analyze = true;
vacstmt->freeze = false;
vacstmt->verbose = false;
vacstmt->relation = NULL; /* all tables, or not used if relids != NIL */
vacstmt->va_cols = NIL;
vacuum(vacstmt, relids);
}
/*
--- 761,776 ----
/* Set up command parameters */
vacstmt->vacuum = dovacuum;
! vacstmt->full = full;
! vacstmt->analyze = doanalyze;
vacstmt->freeze = false;
vacstmt->verbose = false;
vacstmt->relation = NULL; /* all tables, or not used if relids != NIL */
vacstmt->va_cols = NIL;
vacuum(vacstmt, relids);
+
+ pfree(vacstmt);
}
/*
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.102
diff -c -r1.102 pgstat.c
*** src/backend/postmaster/pgstat.c 29 Jul 2005 19:30:04 -0000 1.102
--- src/backend/postmaster/pgstat.c 31 Jul 2005 21:36:07 -0000
***************
*** 99,105 ****
* ----------
*/
bool pgstat_collect_startcollector = true;
! bool pgstat_collect_resetonpmstart = true;
bool pgstat_collect_querystring = false;
bool pgstat_collect_tuplelevel = false;
bool pgstat_collect_blocklevel = false;
--- 99,105 ----
* ----------
*/
bool pgstat_collect_startcollector = true;
! bool pgstat_collect_resetonpmstart = false;
bool pgstat_collect_querystring = false;
bool pgstat_collect_tuplelevel = false;
bool pgstat_collect_blocklevel = false;
***************
*** 236,242 ****
* statistics on postmaster start, simply remove the stats file.
*/
if (!pgstat_collect_startcollector || pgstat_collect_resetonpmstart)
! unlink(PGSTAT_STAT_FILENAME);
/*
* Nothing else required if collector will not get started
--- 236,242 ----
* statistics on postmaster start, simply remove the stats file.
*/
if (!pgstat_collect_startcollector || pgstat_collect_resetonpmstart)
! pgstat_reset_all();
/*
* Nothing else required if collector will not get started
***************
*** 455,460 ****
--- 455,472 ----
pgstat_collect_blocklevel = false;
}
+ /*
+ * pgstat_reset_all() -
+ *
+ * Remove the stats file. This is used on server start if the
+ * stats_reset_on_server_start feature is enabled, or if WAL
+ * recovery is needed after a crash.
+ */
+ void
+ pgstat_reset_all(void)
+ {
+ unlink(PGSTAT_STAT_FILENAME);
+ }
#ifdef EXEC_BACKEND
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.280
diff -c -r1.280 guc.c
*** src/backend/utils/misc/guc.c 30 Jul 2005 15:17:20 -0000 1.280
--- src/backend/utils/misc/guc.c 31 Jul 2005 21:15:36 -0000
***************
*** 672,678 ****
NULL
},
&pgstat_collect_resetonpmstart,
! true, NULL, NULL
},
{
{"stats_command_string", PGC_SUSET, STATS_COLLECTOR,
--- 672,678 ----
NULL
},
&pgstat_collect_resetonpmstart,
! false, NULL, NULL
},
{
{"stats_command_string", PGC_SUSET, STATS_COLLECTOR,
***************
*** 1161,1166 ****
--- 1161,1184 ----
},
{
+ {"autovacuum_vacuum_cost_delay", PGC_USERSET, AUTOVACUUM,
+ gettext_noop("Vacuum cost delay in milliseconds, for autovacuum."),
+ NULL
+ },
+ &autovacuum_vac_cost_delay,
+ -1, -1, 1000, NULL, NULL
+ },
+
+ {
+ {"autovacuum_vacuum_cost_limit", PGC_USERSET, AUTOVACUUM,
+ gettext_noop("Vacuum cost amount available before napping, for autovacuum."),
+ NULL
+ },
+ &autovacuum_vac_cost_limit,
+ -1, -1, 10000, NULL, NULL
+ },
+
+ {
{"max_files_per_process", PGC_POSTMASTER, RESOURCES_KERNEL,
gettext_noop("Sets the maximum number of simultaneously open files for each server process."),
NULL
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.155
diff -c -r1.155 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample 30 Jul 2005 15:17:20 -0000 1.155
--- src/backend/utils/misc/postgresql.conf.sample 31 Jul 2005 21:16:43 -0000
***************
*** 287,293 ****
#stats_command_string = off
#stats_block_level = off
#stats_row_level = off
! #stats_reset_on_server_start = on
#---------------------------------------------------------------------------
--- 287,293 ----
#stats_command_string = off
#stats_block_level = off
#stats_row_level = off
! #stats_reset_on_server_start = off
#---------------------------------------------------------------------------
***************
*** 300,305 ****
--- 300,309 ----
#autovacuum_analyze_threshold = 500 # min # of tuple updates before analyze
#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before vacuum
#autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before analyze
+ #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for autovac
+ # negative means use vacuum_cost_delay
+ #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for autovac
+ # negative means use vacuum_cost_limit
#---------------------------------------------------------------------------
Index: src/include/pgstat.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/pgstat.h,v
retrieving revision 1.34
diff -c -r1.34 pgstat.h
*** src/include/pgstat.h 29 Jul 2005 19:30:09 -0000 1.34
--- src/include/pgstat.h 31 Jul 2005 21:32:59 -0000
***************
*** 367,372 ****
--- 367,373 ----
extern void pgstat_init(void);
extern int pgstat_start(void);
extern void pgstat_beterm(int pid);
+ extern void pgstat_reset_all(void);
#ifdef EXEC_BACKEND
extern void PgstatBufferMain(int argc, char *argv[]);
Index: src/include/catalog/pg_autovacuum.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/catalog/pg_autovacuum.h,v
retrieving revision 1.1
diff -c -r1.1 pg_autovacuum.h
*** src/include/catalog/pg_autovacuum.h 14 Jul 2005 05:13:42 -0000 1.1
--- src/include/catalog/pg_autovacuum.h 31 Jul 2005 23:26:27 -0000
***************
*** 34,39 ****
--- 34,41 ----
float4 vac_scale_factor; /* reltuples scaling factor */
int4 anl_base_thresh; /* base threshold value */
float4 anl_scale_factor; /* reltuples scaling factor */
+ int4 vac_cost_delay; /* vacuum cost-based delay */
+ int4 vac_cost_limit; /* vacuum cost limit */
} FormData_pg_autovacuum;
/* ----------------
***************
*** 47,59 ****
* compiler constants for pg_autovacuum
* ----------------
*/
! #define Natts_pg_autovacuum 6
#define Anum_pg_autovacuum_vacrelid 1
#define Anum_pg_autovacuum_enabled 2
#define Anum_pg_autovacuum_vac_base_thresh 3
#define Anum_pg_autovacuum_vac_scale_factor 4
#define Anum_pg_autovacuum_anl_base_thresh 5
#define Anum_pg_autovacuum_anl_scale_factor 6
/* There are no preloaded tuples in pg_autovacuum.h */
--- 49,63 ----
* compiler constants for pg_autovacuum
* ----------------
*/
! #define Natts_pg_autovacuum 8
#define Anum_pg_autovacuum_vacrelid 1
#define Anum_pg_autovacuum_enabled 2
#define Anum_pg_autovacuum_vac_base_thresh 3
#define Anum_pg_autovacuum_vac_scale_factor 4
#define Anum_pg_autovacuum_anl_base_thresh 5
#define Anum_pg_autovacuum_anl_scale_factor 6
+ #define Anum_pg_autovacuum_vac_cost_delay 7
+ #define Anum_pg_autovacuum_vac_cost_limit 8
/* There are no preloaded tuples in pg_autovacuum.h */
Index: src/include/postmaster/autovacuum.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/postmaster/autovacuum.h,v
retrieving revision 1.1
diff -c -r1.1 autovacuum.h
*** src/include/postmaster/autovacuum.h 14 Jul 2005 05:13:43 -0000 1.1
--- src/include/postmaster/autovacuum.h 31 Jul 2005 17:32:12 -0000
***************
*** 21,26 ****
--- 21,28 ----
extern double autovacuum_vac_scale;
extern int autovacuum_anl_thresh;
extern double autovacuum_anl_scale;
+ extern int autovacuum_vac_cost_delay;
+ extern int autovacuum_vac_cost_limit;
/* Status inquiry functions */
extern bool AutoVacuumingActive(void);
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)
Attachments:
autovac-three-2.patchtext/plain; charset=us-asciiDownload
Index: doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.110
diff -c -r2.110 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 31 Jul 2005 17:19:16 -0000 2.110
--- doc/src/sgml/catalogs.sgml 7 Aug 2005 19:03:22 -0000
***************
*** 1197,1202 ****
--- 1197,1216 ----
<entry>Multiplier for reltuples to add to
<structfield>anl_base_thresh</></entry>
</row>
+
+ <row>
+ <entry><structfield>vac_cost_delay</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry></entry>
+ <entry>Custom <variable>vacuum_cost_delay</> parameter</entry>
+ </row>
+
+ <row>
+ <entry><structfield>vac_cost_limit</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry></entry>
+ <entry>Custom <variable>vacuum_cost_limit</> parameter</entry>
+ </row>
</tbody>
</tgroup>
</table>
***************
*** 1217,1223 ****
<para>
Any of the numerical fields can contain <literal>-1</> (or indeed
any negative value) to indicate that the system-wide default should
! be used for this particular value.
</para>
</sect1>
--- 1231,1241 ----
<para>
Any of the numerical fields can contain <literal>-1</> (or indeed
any negative value) to indicate that the system-wide default should
! be used for this particular value. Observe that the
! <structfield>vac_cost_delay</> variable inherits its default value from the
! <varname>autovacuum_vacuum_cost_delay</> configuration parameter,
! or from <varname>vacuum_cost_delay</> if the former is set to a negative
! value. The same applies to <structfield>vac_cost_limit</>.
</para>
</sect1>
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.341
diff -c -r1.341 runtime.sgml
*** doc/src/sgml/runtime.sgml 30 Jul 2005 17:15:35 -0000 1.341
--- doc/src/sgml/runtime.sgml 31 Jul 2005 23:22:11 -0000
***************
*** 3399,3404 ****
--- 3399,3436 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
+ <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the default value that will be applied to each
+ <command>VACUUM</> operation, for tables that do not have
+ a default value set in <structname>pg_autovacuum</>. If a
+ negative value is specified (like the default value of -1),
+ the <varname>vacuum_cost_delay</> value will be applied instead.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-autovacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
+ <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the default value that will be applied to each
+ <command>VACUUM</> operation, for tables that do not have
+ a default value set in <structname>pg_autovacuum</>. If a
+ negative value is specified (like the default value of -1),
+ the <varname>vacuum_cost_limit</> value will be applied instead.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.214
diff -c -r1.214 xlog.c
*** src/backend/access/transam/xlog.c 30 Jul 2005 14:15:44 -0000 1.214
--- src/backend/access/transam/xlog.c 31 Jul 2005 21:36:45 -0000
***************
*** 33,38 ****
--- 33,39 ----
#include "catalog/catversion.h"
#include "catalog/pg_control.h"
#include "miscadmin.h"
+ #include "pgstat.h"
#include "postmaster/bgwriter.h"
#include "storage/bufpage.h"
#include "storage/fd.h"
***************
*** 48,54 ****
/*
! * Becauase O_DIRECT bypasses the kernel buffers, and because we never
* read those buffers except during crash recovery, it is a win to use
* it in all cases where we sync on each write(). We could allow O_DIRECT
* with fsync(), but because skipping the kernel buffer forces writes out
--- 49,55 ----
/*
! * Because O_DIRECT bypasses the kernel buffers, and because we never
* read those buffers except during crash recovery, it is a win to use
* it in all cases where we sync on each write(). We could allow O_DIRECT
* with fsync(), but because skipping the kernel buffer forces writes out
***************
*** 4544,4549 ****
--- 4545,4555 ----
} while (record != NULL && recoveryContinue);
/*
+ * Reset pgstat data, because it may be invalid after recovery.
+ */
+ pgstat_reset_all();
+
+ /*
* end of main redo apply loop
*/
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.2
diff -c -r1.2 autovacuum.c
*** src/backend/postmaster/autovacuum.c 29 Jul 2005 19:30:04 -0000 1.2
--- src/backend/postmaster/autovacuum.c 8 Aug 2005 16:57:46 -0000
***************
*** 27,32 ****
--- 27,33 ----
#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/pg_autovacuum.h"
+ #include "catalog/pg_database.h"
#include "commands/vacuum.h"
#include "libpq/hba.h"
#include "libpq/pqsignal.h"
***************
*** 57,62 ****
--- 58,66 ----
int autovacuum_anl_thresh;
double autovacuum_anl_scale;
+ int autovacuum_vac_cost_delay;
+ int autovacuum_vac_cost_limit;
+
/* Flag to tell if we are in the autovacuum daemon process */
static bool am_autovacuum = false;
***************
*** 64,69 ****
--- 68,76 ----
static time_t last_autovac_start_time = 0;
static time_t last_autovac_stop_time = 0;
+ /* Memory context for long-lived data */
+ static MemoryContext AutovacMemCxt;
+
/* struct to keep list of candidate databases for vacuum */
typedef struct autovac_dbase
{
***************
*** 74,90 ****
int32 age;
} autovac_dbase;
#ifdef EXEC_BACKEND
static pid_t autovac_forkexec(void);
#endif
NON_EXEC_STATIC void AutoVacMain(int argc, char *argv[]);
! static void do_autovacuum(bool whole_db, PgStat_StatDBEntry *dbentry);
static List *autovac_get_database_list(void);
static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
Form_pg_class classForm, Form_pg_autovacuum avForm,
List **vacuum_tables, List **analyze_tables);
! static void autovacuum_do_vac_analyze(List *relids, bool dovacuum);
/*
--- 81,107 ----
int32 age;
} autovac_dbase;
+ /* struct to keep track of tables to vacuum */
+ typedef struct autovac_table
+ {
+ Oid relid;
+ int vacuum_cost_delay;
+ int vacuum_cost_limit;
+ } autovac_table;
+
#ifdef EXEC_BACKEND
static pid_t autovac_forkexec(void);
#endif
NON_EXEC_STATIC void AutoVacMain(int argc, char *argv[]);
! static void process_whole_db(void);
! static void do_autovacuum(PgStat_StatDBEntry *dbentry);
static List *autovac_get_database_list(void);
static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
Form_pg_class classForm, Form_pg_autovacuum avForm,
List **vacuum_tables, List **analyze_tables);
! static void autovacuum_do_vac_analyze(List *relids, bool doanalyze,
! bool dovacuum, bool freeze);
/*
***************
*** 363,372 ****
set_ps_display(db->name);
ereport(LOG,
(errmsg("autovacuum: processing database \"%s\"", db->name)));
/*
! * And do an appropriate amount of work on it
*/
! do_autovacuum(whole_db, db->entry);
}
/* One iteration done, go away */
--- 380,400 ----
set_ps_display(db->name);
ereport(LOG,
(errmsg("autovacuum: processing database \"%s\"", db->name)));
+
+ /* Create the memory context where cross-transaction state is stored */
+ AutovacMemCxt = AllocSetContextCreate(TopMemoryContext,
+ "Autovacuum context",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
/*
! * And do an appropriate amount of work
*/
! if (whole_db)
! process_whole_db();
! else
! do_autovacuum(db->entry);
}
/* One iteration done, go away */
***************
*** 421,433 ****
}
/*
! * Process a database.
*
! * If whole_db is true, the database is processed as a whole, and the
! * dbentry parameter is ignored. If it's false, dbentry must be a valid
! * pointer to the database entry in the stats databases' hash table, and
! * it will be used to determine whether vacuum or analyze is needed on a
! * per-table basis.
*
* Note that test_rel_for_autovac generates two separate lists, one for
* vacuum and other for analyze. This is to facilitate processing all
--- 449,511 ----
}
/*
! * Process a whole database. If it's a template database or is disallowing
! * connection by means of datallowconn=false, then issue a VACUUM FREEZE.
! * Else use a plain VACUUM.
! */
! static void
! process_whole_db(void)
! {
! Relation dbRel;
! ScanKeyData entry[1];
! HeapScanDesc scan;
! HeapTuple tup;
! Form_pg_database dbForm;
! bool freeze;
!
! /* Start a transaction so our commands have one to play into. */
! StartTransactionCommand();
!
! dbRel = heap_open(DatabaseRelationId, AccessShareLock);
!
! /* Must use a heap scan, since there's no syscache for pg_database */
! ScanKeyInit(&entry[0],
! ObjectIdAttributeNumber,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(MyDatabaseId));
!
! scan = heap_beginscan(dbRel, SnapshotNow, 1, entry);
!
! tup = heap_getnext(scan, ForwardScanDirection);
!
! if (!HeapTupleIsValid(tup))
! elog(ERROR, "could not find tuple for database %u", MyDatabaseId);
!
! dbForm = (Form_pg_database) GETSTRUCT(tup);
!
! if (!dbForm->datallowconn || dbForm->datistemplate)
! freeze = true;
! else
! freeze = false;
!
! heap_endscan(scan);
! heap_close(dbRel, AccessShareLock);
!
! elog(DEBUG2, "autovacuum: VACUUM%s whole database",
! (freeze) ? " FREEZE" : "");
!
! autovacuum_do_vac_analyze(NIL, false, true, freeze);
!
! /* Finally close out the last transaction. */
! CommitTransactionCommand();
! }
!
! /*
! * Process a database table per table
*
! * dbentry must be a valid pointer to the database entry in the stats
! * databases' hash table, and it will be used to determine whether vacuum or
! * analyze is needed on a per-table basis.
*
* Note that test_rel_for_autovac generates two separate lists, one for
* vacuum and other for analyze. This is to facilitate processing all
***************
*** 437,443 ****
* order not to ignore shutdown commands for too long.
*/
static void
! do_autovacuum(bool whole_db, PgStat_StatDBEntry *dbentry)
{
Relation classRel,
avRel;
--- 515,521 ----
* order not to ignore shutdown commands for too long.
*/
static void
! do_autovacuum(PgStat_StatDBEntry *dbentry)
{
Relation classRel,
avRel;
***************
*** 445,460 ****
HeapScanDesc relScan;
List *vacuum_tables = NIL,
*analyze_tables = NIL;
! MemoryContext AutovacMemCxt;
!
! Assert(whole_db || PointerIsValid(dbentry));
!
! /* Memory context where cross-transaction state is stored */
! AutovacMemCxt = AllocSetContextCreate(TopMemoryContext,
! "Autovacuum context",
! ALLOCSET_DEFAULT_MINSIZE,
! ALLOCSET_DEFAULT_INITSIZE,
! ALLOCSET_DEFAULT_MAXSIZE);
/* Start a transaction so our commands have one to play into. */
StartTransactionCommand();
--- 523,529 ----
HeapScanDesc relScan;
List *vacuum_tables = NIL,
*analyze_tables = NIL;
! PgStat_StatDBEntry *shared;
/* Start a transaction so our commands have one to play into. */
StartTransactionCommand();
***************
*** 467,559 ****
*/
MemoryContextSwitchTo(AutovacMemCxt);
! if (whole_db)
! {
! elog(DEBUG2, "autovacuum: VACUUM ANALYZE whole database");
! autovacuum_do_vac_analyze(NIL, true);
! }
! else
! {
! /* the hash entry where pgstat stores shared relations */
! PgStat_StatDBEntry *shared = pgstat_fetch_stat_dbentry(InvalidOid);
! classRel = heap_open(RelationRelationId, AccessShareLock);
! avRel = heap_open(AutovacuumRelationId, AccessShareLock);
! relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL);
! /* Scan pg_class looking for tables to vacuum */
! while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
! {
! Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
! Form_pg_autovacuum avForm = NULL;
! PgStat_StatTabEntry *tabentry;
! SysScanDesc avScan;
! HeapTuple avTup;
! ScanKeyData entry[1];
! Oid relid;
!
! /* Skip non-table entries. */
! /* XXX possibly allow RELKIND_TOASTVALUE entries here too? */
! if (classForm->relkind != RELKIND_RELATION)
! continue;
! /*
! * Skip temp tables (i.e. those in temp namespaces). We cannot
! * safely process other backends' temp tables.
! */
! if (isTempNamespace(classForm->relnamespace))
! continue;
! relid = HeapTupleGetOid(tuple);
! /* See if we have a pg_autovacuum entry for this relation. */
! ScanKeyInit(&entry[0],
! Anum_pg_autovacuum_vacrelid,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(relid));
! avScan = systable_beginscan(avRel, AutovacuumRelidIndexId, true,
! SnapshotNow, 1, entry);
! avTup = systable_getnext(avScan);
! if (HeapTupleIsValid(avTup))
! avForm = (Form_pg_autovacuum) GETSTRUCT(avTup);
! if (classForm->relisshared && PointerIsValid(shared))
! tabentry = hash_search(shared->tables, &relid,
! HASH_FIND, NULL);
! else
! tabentry = hash_search(dbentry->tables, &relid,
! HASH_FIND, NULL);
! test_rel_for_autovac(relid, tabentry, classForm, avForm,
! &vacuum_tables, &analyze_tables);
! systable_endscan(avScan);
! }
! heap_endscan(relScan);
! heap_close(avRel, AccessShareLock);
! heap_close(classRel, AccessShareLock);
! CHECK_FOR_INTERRUPTS();
! /*
! * Perform operations on collected tables.
! */
! if (analyze_tables)
! autovacuum_do_vac_analyze(analyze_tables, false);
! CHECK_FOR_INTERRUPTS();
! /* get back to proper context */
! MemoryContextSwitchTo(AutovacMemCxt);
! if (vacuum_tables)
! autovacuum_do_vac_analyze(vacuum_tables, true);
}
/* Finally close out the last transaction. */
--- 536,632 ----
*/
MemoryContextSwitchTo(AutovacMemCxt);
! /* The database hash where pgstat keeps shared relations */
! shared = pgstat_fetch_stat_dbentry(InvalidOid);
! classRel = heap_open(RelationRelationId, AccessShareLock);
! avRel = heap_open(AutovacuumRelationId, AccessShareLock);
! relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL);
! /* Scan pg_class looking for tables to vacuum */
! while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
! {
! Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
! Form_pg_autovacuum avForm = NULL;
! PgStat_StatTabEntry *tabentry;
! SysScanDesc avScan;
! HeapTuple avTup;
! ScanKeyData entry[1];
! Oid relid;
!
! /* Skip non-table entries. */
! /* XXX possibly allow RELKIND_TOASTVALUE entries here too? */
! if (classForm->relkind != RELKIND_RELATION)
! continue;
! /*
! * Skip temp tables (i.e. those in temp namespaces). We cannot
! * safely process other backends' temp tables.
! */
! if (isTempNamespace(classForm->relnamespace))
! continue;
! relid = HeapTupleGetOid(tuple);
! /* See if we have a pg_autovacuum entry for this relation. */
! ScanKeyInit(&entry[0],
! Anum_pg_autovacuum_vacrelid,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(relid));
! avScan = systable_beginscan(avRel, AutovacuumRelidIndexId, true,
! SnapshotNow, 1, entry);
! avTup = systable_getnext(avScan);
! if (HeapTupleIsValid(avTup))
! avForm = (Form_pg_autovacuum) GETSTRUCT(avTup);
! if (classForm->relisshared && PointerIsValid(shared))
! tabentry = hash_search(shared->tables, &relid,
! HASH_FIND, NULL);
! else
! tabentry = hash_search(dbentry->tables, &relid,
! HASH_FIND, NULL);
! test_rel_for_autovac(relid, tabentry, classForm, avForm,
! &vacuum_tables, &analyze_tables);
! systable_endscan(avScan);
! }
! heap_endscan(relScan);
! heap_close(avRel, AccessShareLock);
! heap_close(classRel, AccessShareLock);
! CHECK_FOR_INTERRUPTS();
! /*
! * Perform operations on collected tables.
! */
! if (analyze_tables)
! autovacuum_do_vac_analyze(analyze_tables, true, false, false);
! CHECK_FOR_INTERRUPTS();
! if (vacuum_tables)
! {
! ListCell *cell;
!
!
! foreach(cell, vacuum_tables)
! {
! autovac_table *tab = lfirst(cell);
! /* Set the cost vacuum parameters for this table */
! VacuumCostDelay = tab->vacuum_cost_delay;
! VacuumCostLimit = tab->vacuum_cost_limit;
!
! autovacuum_do_vac_analyze(list_make1_oid(tab->relid), true,
! true, true);
! }
}
/* Finally close out the last transaction. */
***************
*** 606,611 ****
--- 679,687 ----
/* number of vacuum (resp. analyze) tuples at this time */
float4 vactuples,
anltuples;
+ /* cost-based vacuum delay parameters */
+ int vac_cost_limit;
+ int vac_cost_delay;
/* User disabled it in pg_autovacuum? */
if (avForm && !avForm->enabled)
***************
*** 645,650 ****
--- 721,734 ----
autovacuum_anl_scale : avForm->anl_scale_factor;
anl_base_thresh = (avForm->anl_base_thresh < 0) ?
autovacuum_anl_thresh : avForm->anl_base_thresh;
+
+ vac_cost_limit = (avForm->vac_cost_limit < 0) ?
+ (autovacuum_vac_cost_limit < 0) ? VacuumCostLimit :
+ autovacuum_vac_cost_limit : avForm->vac_cost_limit;
+
+ vac_cost_delay = (avForm->vac_cost_delay < 0) ?
+ (autovacuum_vac_cost_delay < 0) ? VacuumCostDelay :
+ autovacuum_vac_cost_delay : avForm->vac_cost_delay;
}
else
{
***************
*** 653,658 ****
--- 737,748 ----
anl_scale_factor = autovacuum_anl_scale;
anl_base_thresh = autovacuum_anl_thresh;
+
+ vac_cost_limit = (autovacuum_vac_cost_limit < 0) ?
+ VacuumCostLimit : autovacuum_vac_cost_limit;
+
+ vac_cost_delay = (autovacuum_vac_cost_delay < 0) ?
+ VacuumCostDelay : autovacuum_vac_cost_delay;
}
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
***************
*** 668,679 ****
RelationGetRelationName(rel),
vactuples, vacthresh, anltuples, anlthresh);
/* Determine if this table needs vacuum or analyze. */
if (vactuples > vacthresh)
{
elog(DEBUG2, "will VACUUM ANALYZE %s",
RelationGetRelationName(rel));
! *vacuum_tables = lappend_oid(*vacuum_tables, relid);
}
else if (anltuples > anlthresh)
{
--- 758,779 ----
RelationGetRelationName(rel),
vactuples, vacthresh, anltuples, anlthresh);
+ Assert(CurrentMemoryContext == AutovacMemCxt);
+
/* Determine if this table needs vacuum or analyze. */
if (vactuples > vacthresh)
{
+ autovac_table *tab = (autovac_table *)
+ palloc(sizeof(autovac_table));
+
elog(DEBUG2, "will VACUUM ANALYZE %s",
RelationGetRelationName(rel));
!
! tab->relid = relid;
! tab->vacuum_cost_limit = vac_cost_limit;
! tab->vacuum_cost_delay = vac_cost_delay;
!
! *vacuum_tables = lappend(*vacuum_tables, tab);
}
else if (anltuples > anlthresh)
{
***************
*** 691,704 ****
/*
* autovacuum_do_vac_analyze
! * Vacuum or analyze a list of tables; or all tables if relids = NIL
! *
! * We must be in AutovacMemCxt when this routine is called.
*/
static void
! autovacuum_do_vac_analyze(List *relids, bool dovacuum)
{
! VacuumStmt *vacstmt = makeNode(VacuumStmt);
/*
* Point QueryContext to the autovac memory context to fake out the
--- 791,812 ----
/*
* autovacuum_do_vac_analyze
! * Vacuum and/or analyze a list of tables; or all tables if relids = NIL
*/
static void
! autovacuum_do_vac_analyze(List *relids, bool doanalyze, bool dovacuum,
! bool freeze)
{
! VacuumStmt *vacstmt;
! MemoryContext old_cxt;
!
! /*
! * The node must survive transaction boundaries, so make sure we create it
! * in a long-lived context
! */
! old_cxt = MemoryContextSwitchTo(AutovacMemCxt);
!
! vacstmt = makeNode(VacuumStmt);
/*
* Point QueryContext to the autovac memory context to fake out the
***************
*** 710,722 ****
/* Set up command parameters */
vacstmt->vacuum = dovacuum;
vacstmt->full = false;
! vacstmt->analyze = true;
! vacstmt->freeze = false;
vacstmt->verbose = false;
vacstmt->relation = NULL; /* all tables, or not used if relids != NIL */
vacstmt->va_cols = NIL;
vacuum(vacstmt, relids);
}
/*
--- 818,833 ----
/* Set up command parameters */
vacstmt->vacuum = dovacuum;
vacstmt->full = false;
! vacstmt->analyze = doanalyze;
! vacstmt->freeze = freeze;
vacstmt->verbose = false;
vacstmt->relation = NULL; /* all tables, or not used if relids != NIL */
vacstmt->va_cols = NIL;
vacuum(vacstmt, relids);
+
+ pfree(vacstmt);
+ MemoryContextSwitchTo(old_cxt);
}
/*
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.102
diff -c -r1.102 pgstat.c
*** src/backend/postmaster/pgstat.c 29 Jul 2005 19:30:04 -0000 1.102
--- src/backend/postmaster/pgstat.c 31 Jul 2005 21:36:07 -0000
***************
*** 99,105 ****
* ----------
*/
bool pgstat_collect_startcollector = true;
! bool pgstat_collect_resetonpmstart = true;
bool pgstat_collect_querystring = false;
bool pgstat_collect_tuplelevel = false;
bool pgstat_collect_blocklevel = false;
--- 99,105 ----
* ----------
*/
bool pgstat_collect_startcollector = true;
! bool pgstat_collect_resetonpmstart = false;
bool pgstat_collect_querystring = false;
bool pgstat_collect_tuplelevel = false;
bool pgstat_collect_blocklevel = false;
***************
*** 236,242 ****
* statistics on postmaster start, simply remove the stats file.
*/
if (!pgstat_collect_startcollector || pgstat_collect_resetonpmstart)
! unlink(PGSTAT_STAT_FILENAME);
/*
* Nothing else required if collector will not get started
--- 236,242 ----
* statistics on postmaster start, simply remove the stats file.
*/
if (!pgstat_collect_startcollector || pgstat_collect_resetonpmstart)
! pgstat_reset_all();
/*
* Nothing else required if collector will not get started
***************
*** 455,460 ****
--- 455,472 ----
pgstat_collect_blocklevel = false;
}
+ /*
+ * pgstat_reset_all() -
+ *
+ * Remove the stats file. This is used on server start if the
+ * stats_reset_on_server_start feature is enabled, or if WAL
+ * recovery is needed after a crash.
+ */
+ void
+ pgstat_reset_all(void)
+ {
+ unlink(PGSTAT_STAT_FILENAME);
+ }
#ifdef EXEC_BACKEND
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.280
diff -c -r1.280 guc.c
*** src/backend/utils/misc/guc.c 30 Jul 2005 15:17:20 -0000 1.280
--- src/backend/utils/misc/guc.c 31 Jul 2005 21:15:36 -0000
***************
*** 672,678 ****
NULL
},
&pgstat_collect_resetonpmstart,
! true, NULL, NULL
},
{
{"stats_command_string", PGC_SUSET, STATS_COLLECTOR,
--- 672,678 ----
NULL
},
&pgstat_collect_resetonpmstart,
! false, NULL, NULL
},
{
{"stats_command_string", PGC_SUSET, STATS_COLLECTOR,
***************
*** 1161,1166 ****
--- 1161,1184 ----
},
{
+ {"autovacuum_vacuum_cost_delay", PGC_USERSET, AUTOVACUUM,
+ gettext_noop("Vacuum cost delay in milliseconds, for autovacuum."),
+ NULL
+ },
+ &autovacuum_vac_cost_delay,
+ -1, -1, 1000, NULL, NULL
+ },
+
+ {
+ {"autovacuum_vacuum_cost_limit", PGC_USERSET, AUTOVACUUM,
+ gettext_noop("Vacuum cost amount available before napping, for autovacuum."),
+ NULL
+ },
+ &autovacuum_vac_cost_limit,
+ -1, -1, 10000, NULL, NULL
+ },
+
+ {
{"max_files_per_process", PGC_POSTMASTER, RESOURCES_KERNEL,
gettext_noop("Sets the maximum number of simultaneously open files for each server process."),
NULL
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.155
diff -c -r1.155 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample 30 Jul 2005 15:17:20 -0000 1.155
--- src/backend/utils/misc/postgresql.conf.sample 31 Jul 2005 21:16:43 -0000
***************
*** 287,293 ****
#stats_command_string = off
#stats_block_level = off
#stats_row_level = off
! #stats_reset_on_server_start = on
#---------------------------------------------------------------------------
--- 287,293 ----
#stats_command_string = off
#stats_block_level = off
#stats_row_level = off
! #stats_reset_on_server_start = off
#---------------------------------------------------------------------------
***************
*** 300,305 ****
--- 300,309 ----
#autovacuum_analyze_threshold = 500 # min # of tuple updates before analyze
#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before vacuum
#autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before analyze
+ #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for autovac
+ # negative means use vacuum_cost_delay
+ #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for autovac
+ # negative means use vacuum_cost_limit
#---------------------------------------------------------------------------
Index: src/include/pgstat.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/pgstat.h,v
retrieving revision 1.34
diff -c -r1.34 pgstat.h
*** src/include/pgstat.h 29 Jul 2005 19:30:09 -0000 1.34
--- src/include/pgstat.h 31 Jul 2005 21:32:59 -0000
***************
*** 367,372 ****
--- 367,373 ----
extern void pgstat_init(void);
extern int pgstat_start(void);
extern void pgstat_beterm(int pid);
+ extern void pgstat_reset_all(void);
#ifdef EXEC_BACKEND
extern void PgstatBufferMain(int argc, char *argv[]);
Index: src/include/catalog/pg_autovacuum.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/catalog/pg_autovacuum.h,v
retrieving revision 1.1
diff -c -r1.1 pg_autovacuum.h
*** src/include/catalog/pg_autovacuum.h 14 Jul 2005 05:13:42 -0000 1.1
--- src/include/catalog/pg_autovacuum.h 31 Jul 2005 23:26:27 -0000
***************
*** 34,39 ****
--- 34,41 ----
float4 vac_scale_factor; /* reltuples scaling factor */
int4 anl_base_thresh; /* base threshold value */
float4 anl_scale_factor; /* reltuples scaling factor */
+ int4 vac_cost_delay; /* vacuum cost-based delay */
+ int4 vac_cost_limit; /* vacuum cost limit */
} FormData_pg_autovacuum;
/* ----------------
***************
*** 47,59 ****
* compiler constants for pg_autovacuum
* ----------------
*/
! #define Natts_pg_autovacuum 6
#define Anum_pg_autovacuum_vacrelid 1
#define Anum_pg_autovacuum_enabled 2
#define Anum_pg_autovacuum_vac_base_thresh 3
#define Anum_pg_autovacuum_vac_scale_factor 4
#define Anum_pg_autovacuum_anl_base_thresh 5
#define Anum_pg_autovacuum_anl_scale_factor 6
/* There are no preloaded tuples in pg_autovacuum.h */
--- 49,63 ----
* compiler constants for pg_autovacuum
* ----------------
*/
! #define Natts_pg_autovacuum 8
#define Anum_pg_autovacuum_vacrelid 1
#define Anum_pg_autovacuum_enabled 2
#define Anum_pg_autovacuum_vac_base_thresh 3
#define Anum_pg_autovacuum_vac_scale_factor 4
#define Anum_pg_autovacuum_anl_base_thresh 5
#define Anum_pg_autovacuum_anl_scale_factor 6
+ #define Anum_pg_autovacuum_vac_cost_delay 7
+ #define Anum_pg_autovacuum_vac_cost_limit 8
/* There are no preloaded tuples in pg_autovacuum.h */
Index: src/include/postmaster/autovacuum.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/postmaster/autovacuum.h,v
retrieving revision 1.1
diff -c -r1.1 autovacuum.h
*** src/include/postmaster/autovacuum.h 14 Jul 2005 05:13:43 -0000 1.1
--- src/include/postmaster/autovacuum.h 31 Jul 2005 17:32:12 -0000
***************
*** 21,26 ****
--- 21,28 ----
extern double autovacuum_vac_scale;
extern int autovacuum_anl_thresh;
extern double autovacuum_anl_scale;
+ extern int autovacuum_vac_cost_delay;
+ extern int autovacuum_vac_cost_limit;
/* Status inquiry functions */
extern bool AutoVacuumingActive(void);
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)
Attachments:
autovac-three-3.patchtext/plain; charset=us-asciiDownload
Index: doc/src/sgml/catalogs.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.110
diff -c -r2.110 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 31 Jul 2005 17:19:16 -0000 2.110
--- doc/src/sgml/catalogs.sgml 7 Aug 2005 19:03:22 -0000
***************
*** 1197,1202 ****
--- 1197,1216 ----
<entry>Multiplier for reltuples to add to
<structfield>anl_base_thresh</></entry>
</row>
+
+ <row>
+ <entry><structfield>vac_cost_delay</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry></entry>
+ <entry>Custom <variable>vacuum_cost_delay</> parameter</entry>
+ </row>
+
+ <row>
+ <entry><structfield>vac_cost_limit</structfield></entry>
+ <entry><type>integer</type></entry>
+ <entry></entry>
+ <entry>Custom <variable>vacuum_cost_limit</> parameter</entry>
+ </row>
</tbody>
</tgroup>
</table>
***************
*** 1217,1223 ****
<para>
Any of the numerical fields can contain <literal>-1</> (or indeed
any negative value) to indicate that the system-wide default should
! be used for this particular value.
</para>
</sect1>
--- 1231,1241 ----
<para>
Any of the numerical fields can contain <literal>-1</> (or indeed
any negative value) to indicate that the system-wide default should
! be used for this particular value. Observe that the
! <structfield>vac_cost_delay</> variable inherits its default value from the
! <varname>autovacuum_vacuum_cost_delay</> configuration parameter,
! or from <varname>vacuum_cost_delay</> if the former is set to a negative
! value. The same applies to <structfield>vac_cost_limit</>.
</para>
</sect1>
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.341
diff -c -r1.341 runtime.sgml
*** doc/src/sgml/runtime.sgml 30 Jul 2005 17:15:35 -0000 1.341
--- doc/src/sgml/runtime.sgml 31 Jul 2005 23:22:11 -0000
***************
*** 3399,3404 ****
--- 3399,3436 ----
</listitem>
</varlistentry>
+ <varlistentry id="guc-autovacuum-vacuum-cost-delay" xreflabel="autovacuum_vacuum_cost_delay">
+ <term><varname>autovacuum_vacuum_cost_delay</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_delay</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the default value that will be applied to each
+ <command>VACUUM</> operation, for tables that do not have
+ a default value set in <structname>pg_autovacuum</>. If a
+ negative value is specified (like the default value of -1),
+ the <varname>vacuum_cost_delay</> value will be applied instead.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-autovacuum-cost-limit" xreflabel="autovacuum_vacuum_cost_limit">
+ <term><varname>autovacuum_vacuum_cost_limit</varname> (<type>integer</type>)</term>
+ <indexterm>
+ <primary><varname>autovacuum_vacuum_cost_limit</> configuration parameter</primary>
+ </indexterm>
+ <listitem>
+ <para>
+ Specifies the default value that will be applied to each
+ <command>VACUUM</> operation, for tables that do not have
+ a default value set in <structname>pg_autovacuum</>. If a
+ negative value is specified (like the default value of -1),
+ the <varname>vacuum_cost_limit</> value will be applied instead.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
Index: src/backend/access/transam/xlog.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.214
diff -c -r1.214 xlog.c
*** src/backend/access/transam/xlog.c 30 Jul 2005 14:15:44 -0000 1.214
--- src/backend/access/transam/xlog.c 31 Jul 2005 21:36:45 -0000
***************
*** 33,38 ****
--- 33,39 ----
#include "catalog/catversion.h"
#include "catalog/pg_control.h"
#include "miscadmin.h"
+ #include "pgstat.h"
#include "postmaster/bgwriter.h"
#include "storage/bufpage.h"
#include "storage/fd.h"
***************
*** 48,54 ****
/*
! * Becauase O_DIRECT bypasses the kernel buffers, and because we never
* read those buffers except during crash recovery, it is a win to use
* it in all cases where we sync on each write(). We could allow O_DIRECT
* with fsync(), but because skipping the kernel buffer forces writes out
--- 49,55 ----
/*
! * Because O_DIRECT bypasses the kernel buffers, and because we never
* read those buffers except during crash recovery, it is a win to use
* it in all cases where we sync on each write(). We could allow O_DIRECT
* with fsync(), but because skipping the kernel buffer forces writes out
***************
*** 4544,4549 ****
--- 4545,4555 ----
} while (record != NULL && recoveryContinue);
/*
+ * Reset pgstat data, because it may be invalid after recovery.
+ */
+ pgstat_reset_all();
+
+ /*
* end of main redo apply loop
*/
Index: src/backend/libpq/hba.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/libpq/hba.c,v
retrieving revision 1.146
diff -c -r1.146 hba.c
*** src/backend/libpq/hba.c 29 Jul 2005 19:30:04 -0000 1.146
--- src/backend/libpq/hba.c 10 Aug 2005 02:48:20 -0000
***************
*** 1001,1013 ****
* dboid: gets database OID
* dbtablespace: gets database's default tablespace's OID
* dbfrozenxid: gets database's frozen XID
*
* This is not much related to the other functions in hba.c, but we put it
* here because it uses the next_token() infrastructure.
*/
bool
read_pg_database_line(FILE *fp, char *dbname, Oid *dboid,
! Oid *dbtablespace, TransactionId *dbfrozenxid)
{
char buf[MAX_TOKEN];
--- 1001,1015 ----
* dboid: gets database OID
* dbtablespace: gets database's default tablespace's OID
* dbfrozenxid: gets database's frozen XID
+ * dbvacuumxid: gets database's vacuum XID
*
* This is not much related to the other functions in hba.c, but we put it
* here because it uses the next_token() infrastructure.
*/
bool
read_pg_database_line(FILE *fp, char *dbname, Oid *dboid,
! Oid *dbtablespace, TransactionId *dbfrozenxid,
! TransactionId *dbvacuumxid)
{
char buf[MAX_TOKEN];
***************
*** 1030,1035 ****
--- 1032,1041 ----
if (!isdigit((unsigned char) buf[0]))
elog(FATAL, "bad data in flat pg_database file");
*dbfrozenxid = atoxid(buf);
+ next_token(fp, buf, sizeof(buf));
+ if (!isdigit((unsigned char) buf[0]))
+ elog(FATAL, "bad data in flat pg_database file");
+ *dbvacuumxid = atoxid(buf);
/* expect EOL next */
if (next_token(fp, buf, sizeof(buf)))
elog(FATAL, "bad data in flat pg_database file");
Index: src/backend/postmaster/autovacuum.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.2
diff -c -r1.2 autovacuum.c
*** src/backend/postmaster/autovacuum.c 29 Jul 2005 19:30:04 -0000 1.2
--- src/backend/postmaster/autovacuum.c 10 Aug 2005 02:46:52 -0000
***************
*** 27,32 ****
--- 27,33 ----
#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/pg_autovacuum.h"
+ #include "catalog/pg_database.h"
#include "commands/vacuum.h"
#include "libpq/hba.h"
#include "libpq/pqsignal.h"
***************
*** 57,62 ****
--- 58,66 ----
int autovacuum_anl_thresh;
double autovacuum_anl_scale;
+ int autovacuum_vac_cost_delay;
+ int autovacuum_vac_cost_limit;
+
/* Flag to tell if we are in the autovacuum daemon process */
static bool am_autovacuum = false;
***************
*** 64,91 ****
static time_t last_autovac_start_time = 0;
static time_t last_autovac_stop_time = 0;
/* struct to keep list of candidate databases for vacuum */
typedef struct autovac_dbase
{
Oid oid;
char *name;
TransactionId frozenxid;
PgStat_StatDBEntry *entry;
int32 age;
} autovac_dbase;
#ifdef EXEC_BACKEND
static pid_t autovac_forkexec(void);
#endif
NON_EXEC_STATIC void AutoVacMain(int argc, char *argv[]);
! static void do_autovacuum(bool whole_db, PgStat_StatDBEntry *dbentry);
static List *autovac_get_database_list(void);
static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
Form_pg_class classForm, Form_pg_autovacuum avForm,
List **vacuum_tables, List **analyze_tables);
! static void autovacuum_do_vac_analyze(List *relids, bool dovacuum);
!
/*
* Main entry point for autovacuum controller process.
--- 68,108 ----
static time_t last_autovac_start_time = 0;
static time_t last_autovac_stop_time = 0;
+ /* Memory context for long-lived data */
+ static MemoryContext AutovacMemCxt;
+
/* struct to keep list of candidate databases for vacuum */
typedef struct autovac_dbase
{
Oid oid;
char *name;
TransactionId frozenxid;
+ TransactionId vacuumxid;
PgStat_StatDBEntry *entry;
int32 age;
} autovac_dbase;
+ /* struct to keep track of tables to vacuum */
+ typedef struct autovac_table
+ {
+ Oid relid;
+ int vacuum_cost_delay;
+ int vacuum_cost_limit;
+ } autovac_table;
+
#ifdef EXEC_BACKEND
static pid_t autovac_forkexec(void);
#endif
NON_EXEC_STATIC void AutoVacMain(int argc, char *argv[]);
! static void process_whole_db(void);
! static void do_autovacuum(PgStat_StatDBEntry *dbentry);
static List *autovac_get_database_list(void);
static void test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
Form_pg_class classForm, Form_pg_autovacuum avForm,
List **vacuum_tables, List **analyze_tables);
! static void autovacuum_do_vac_analyze(List *relids, bool doanalyze,
! bool dovacuum, bool freeze);
/*
* Main entry point for autovacuum controller process.
***************
*** 302,307 ****
--- 319,326 ----
{
autovac_dbase *tmp = lfirst(cell);
bool this_whole_db;
+ int32 freeze_age,
+ vacuum_age;
/*
* We look for the database that most urgently needs a database-wide
***************
*** 310,317 ****
* decide to start giving warnings. If any such db is found, we
* ignore all other dbs.
*/
! tmp->age = (int32) (nextXid - tmp->frozenxid);
! this_whole_db = (tmp->age > (int32) ((MaxTransactionId >> 3) * 3 - 100000));
if (whole_db || this_whole_db)
{
if (!this_whole_db)
--- 329,340 ----
* decide to start giving warnings. If any such db is found, we
* ignore all other dbs.
*/
! freeze_age = (int32) (nextXid - tmp->frozenxid);
! vacuum_age = (int32) (nextXid - tmp->vacuumxid);
! tmp->age = Max(freeze_age, vacuum_age);
!
! this_whole_db = (tmp->age >
! (int32) ((MaxTransactionId >> 3) * 3 - 100000));
if (whole_db || this_whole_db)
{
if (!this_whole_db)
***************
*** 363,372 ****
set_ps_display(db->name);
ereport(LOG,
(errmsg("autovacuum: processing database \"%s\"", db->name)));
/*
! * And do an appropriate amount of work on it
*/
! do_autovacuum(whole_db, db->entry);
}
/* One iteration done, go away */
--- 386,406 ----
set_ps_display(db->name);
ereport(LOG,
(errmsg("autovacuum: processing database \"%s\"", db->name)));
+
+ /* Create the memory context where cross-transaction state is stored */
+ AutovacMemCxt = AllocSetContextCreate(TopMemoryContext,
+ "Autovacuum context",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+
/*
! * And do an appropriate amount of work
*/
! if (whole_db)
! process_whole_db();
! else
! do_autovacuum(db->entry);
}
/* One iteration done, go away */
***************
*** 389,394 ****
--- 423,429 ----
Oid db_id;
Oid db_tablespace;
TransactionId db_frozenxid;
+ TransactionId db_vacuumxid;
filename = database_getflatfilename();
db_file = AllocateFile(filename, "r");
***************
*** 398,404 ****
errmsg("could not open file \"%s\": %m", filename)));
while (read_pg_database_line(db_file, thisname, &db_id,
! &db_tablespace, &db_frozenxid))
{
autovac_dbase *db;
--- 433,440 ----
errmsg("could not open file \"%s\": %m", filename)));
while (read_pg_database_line(db_file, thisname, &db_id,
! &db_tablespace, &db_frozenxid,
! &db_vacuumxid))
{
autovac_dbase *db;
***************
*** 407,412 ****
--- 443,449 ----
db->oid = db_id;
db->name = pstrdup(thisname);
db->frozenxid = db_frozenxid;
+ db->vacuumxid = db_vacuumxid;
/* these get set later: */
db->entry = NULL;
db->age = 0;
***************
*** 421,433 ****
}
/*
! * Process a database.
*
! * If whole_db is true, the database is processed as a whole, and the
! * dbentry parameter is ignored. If it's false, dbentry must be a valid
! * pointer to the database entry in the stats databases' hash table, and
! * it will be used to determine whether vacuum or analyze is needed on a
! * per-table basis.
*
* Note that test_rel_for_autovac generates two separate lists, one for
* vacuum and other for analyze. This is to facilitate processing all
--- 458,520 ----
}
/*
! * Process a whole database. If it's a template database or is disallowing
! * connection by means of datallowconn=false, then issue a VACUUM FREEZE.
! * Else use a plain VACUUM.
! */
! static void
! process_whole_db(void)
! {
! Relation dbRel;
! ScanKeyData entry[1];
! HeapScanDesc scan;
! HeapTuple tup;
! Form_pg_database dbForm;
! bool freeze;
!
! /* Start a transaction so our commands have one to play into. */
! StartTransactionCommand();
!
! dbRel = heap_open(DatabaseRelationId, AccessShareLock);
!
! /* Must use a heap scan, since there's no syscache for pg_database */
! ScanKeyInit(&entry[0],
! ObjectIdAttributeNumber,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(MyDatabaseId));
!
! scan = heap_beginscan(dbRel, SnapshotNow, 1, entry);
!
! tup = heap_getnext(scan, ForwardScanDirection);
!
! if (!HeapTupleIsValid(tup))
! elog(ERROR, "could not find tuple for database %u", MyDatabaseId);
!
! dbForm = (Form_pg_database) GETSTRUCT(tup);
!
! if (!dbForm->datallowconn || dbForm->datistemplate)
! freeze = true;
! else
! freeze = false;
!
! heap_endscan(scan);
! heap_close(dbRel, AccessShareLock);
!
! elog(DEBUG2, "autovacuum: VACUUM%s whole database",
! (freeze) ? " FREEZE" : "");
!
! autovacuum_do_vac_analyze(NIL, false, true, freeze);
!
! /* Finally close out the last transaction. */
! CommitTransactionCommand();
! }
!
! /*
! * Process a database table per table
*
! * dbentry must be a valid pointer to the database entry in the stats
! * databases' hash table, and it will be used to determine whether vacuum or
! * analyze is needed on a per-table basis.
*
* Note that test_rel_for_autovac generates two separate lists, one for
* vacuum and other for analyze. This is to facilitate processing all
***************
*** 437,443 ****
* order not to ignore shutdown commands for too long.
*/
static void
! do_autovacuum(bool whole_db, PgStat_StatDBEntry *dbentry)
{
Relation classRel,
avRel;
--- 524,530 ----
* order not to ignore shutdown commands for too long.
*/
static void
! do_autovacuum(PgStat_StatDBEntry *dbentry)
{
Relation classRel,
avRel;
***************
*** 445,460 ****
HeapScanDesc relScan;
List *vacuum_tables = NIL,
*analyze_tables = NIL;
! MemoryContext AutovacMemCxt;
!
! Assert(whole_db || PointerIsValid(dbentry));
!
! /* Memory context where cross-transaction state is stored */
! AutovacMemCxt = AllocSetContextCreate(TopMemoryContext,
! "Autovacuum context",
! ALLOCSET_DEFAULT_MINSIZE,
! ALLOCSET_DEFAULT_INITSIZE,
! ALLOCSET_DEFAULT_MAXSIZE);
/* Start a transaction so our commands have one to play into. */
StartTransactionCommand();
--- 532,538 ----
HeapScanDesc relScan;
List *vacuum_tables = NIL,
*analyze_tables = NIL;
! PgStat_StatDBEntry *shared;
/* Start a transaction so our commands have one to play into. */
StartTransactionCommand();
***************
*** 467,559 ****
*/
MemoryContextSwitchTo(AutovacMemCxt);
! if (whole_db)
! {
! elog(DEBUG2, "autovacuum: VACUUM ANALYZE whole database");
! autovacuum_do_vac_analyze(NIL, true);
! }
! else
! {
! /* the hash entry where pgstat stores shared relations */
! PgStat_StatDBEntry *shared = pgstat_fetch_stat_dbentry(InvalidOid);
! classRel = heap_open(RelationRelationId, AccessShareLock);
! avRel = heap_open(AutovacuumRelationId, AccessShareLock);
! relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL);
! /* Scan pg_class looking for tables to vacuum */
! while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
! {
! Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
! Form_pg_autovacuum avForm = NULL;
! PgStat_StatTabEntry *tabentry;
! SysScanDesc avScan;
! HeapTuple avTup;
! ScanKeyData entry[1];
! Oid relid;
!
! /* Skip non-table entries. */
! /* XXX possibly allow RELKIND_TOASTVALUE entries here too? */
! if (classForm->relkind != RELKIND_RELATION)
! continue;
! /*
! * Skip temp tables (i.e. those in temp namespaces). We cannot
! * safely process other backends' temp tables.
! */
! if (isTempNamespace(classForm->relnamespace))
! continue;
! relid = HeapTupleGetOid(tuple);
! /* See if we have a pg_autovacuum entry for this relation. */
! ScanKeyInit(&entry[0],
! Anum_pg_autovacuum_vacrelid,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(relid));
! avScan = systable_beginscan(avRel, AutovacuumRelidIndexId, true,
! SnapshotNow, 1, entry);
! avTup = systable_getnext(avScan);
! if (HeapTupleIsValid(avTup))
! avForm = (Form_pg_autovacuum) GETSTRUCT(avTup);
! if (classForm->relisshared && PointerIsValid(shared))
! tabentry = hash_search(shared->tables, &relid,
! HASH_FIND, NULL);
! else
! tabentry = hash_search(dbentry->tables, &relid,
! HASH_FIND, NULL);
! test_rel_for_autovac(relid, tabentry, classForm, avForm,
! &vacuum_tables, &analyze_tables);
! systable_endscan(avScan);
! }
! heap_endscan(relScan);
! heap_close(avRel, AccessShareLock);
! heap_close(classRel, AccessShareLock);
! CHECK_FOR_INTERRUPTS();
! /*
! * Perform operations on collected tables.
! */
- if (analyze_tables)
- autovacuum_do_vac_analyze(analyze_tables, false);
! CHECK_FOR_INTERRUPTS();
! /* get back to proper context */
! MemoryContextSwitchTo(AutovacMemCxt);
! if (vacuum_tables)
! autovacuum_do_vac_analyze(vacuum_tables, true);
}
/* Finally close out the last transaction. */
--- 545,641 ----
*/
MemoryContextSwitchTo(AutovacMemCxt);
! /* The database hash where pgstat keeps shared relations */
! shared = pgstat_fetch_stat_dbentry(InvalidOid);
! classRel = heap_open(RelationRelationId, AccessShareLock);
! avRel = heap_open(AutovacuumRelationId, AccessShareLock);
! relScan = heap_beginscan(classRel, SnapshotNow, 0, NULL);
! /* Scan pg_class looking for tables to vacuum */
! while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL)
! {
! Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
! Form_pg_autovacuum avForm = NULL;
! PgStat_StatTabEntry *tabentry;
! SysScanDesc avScan;
! HeapTuple avTup;
! ScanKeyData entry[1];
! Oid relid;
!
! /* Skip non-table entries. */
! /* XXX possibly allow RELKIND_TOASTVALUE entries here too? */
! if (classForm->relkind != RELKIND_RELATION)
! continue;
! /*
! * Skip temp tables (i.e. those in temp namespaces). We cannot
! * safely process other backends' temp tables.
! */
! if (isTempNamespace(classForm->relnamespace))
! continue;
! relid = HeapTupleGetOid(tuple);
! /* See if we have a pg_autovacuum entry for this relation. */
! ScanKeyInit(&entry[0],
! Anum_pg_autovacuum_vacrelid,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(relid));
! avScan = systable_beginscan(avRel, AutovacuumRelidIndexId, true,
! SnapshotNow, 1, entry);
! avTup = systable_getnext(avScan);
! if (HeapTupleIsValid(avTup))
! avForm = (Form_pg_autovacuum) GETSTRUCT(avTup);
! if (classForm->relisshared && PointerIsValid(shared))
! tabentry = hash_search(shared->tables, &relid,
! HASH_FIND, NULL);
! else
! tabentry = hash_search(dbentry->tables, &relid,
! HASH_FIND, NULL);
! test_rel_for_autovac(relid, tabentry, classForm, avForm,
! &vacuum_tables, &analyze_tables);
! systable_endscan(avScan);
! }
! heap_endscan(relScan);
! heap_close(avRel, AccessShareLock);
! heap_close(classRel, AccessShareLock);
! CHECK_FOR_INTERRUPTS();
! /*
! * Perform operations on collected tables.
! */
!
! if (analyze_tables)
! autovacuum_do_vac_analyze(analyze_tables, true, false, false);
!
! CHECK_FOR_INTERRUPTS();
!
! if (vacuum_tables)
! {
! ListCell *cell;
! foreach(cell, vacuum_tables)
! {
! autovac_table *tab = lfirst(cell);
! /* Set the cost vacuum parameters for this table */
! VacuumCostDelay = tab->vacuum_cost_delay;
! VacuumCostLimit = tab->vacuum_cost_limit;
! autovacuum_do_vac_analyze(list_make1_oid(tab->relid), true,
! true, true);
! }
}
/* Finally close out the last transaction. */
***************
*** 606,611 ****
--- 688,696 ----
/* number of vacuum (resp. analyze) tuples at this time */
float4 vactuples,
anltuples;
+ /* cost-based vacuum delay parameters */
+ int vac_cost_limit;
+ int vac_cost_delay;
/* User disabled it in pg_autovacuum? */
if (avForm && !avForm->enabled)
***************
*** 645,650 ****
--- 730,743 ----
autovacuum_anl_scale : avForm->anl_scale_factor;
anl_base_thresh = (avForm->anl_base_thresh < 0) ?
autovacuum_anl_thresh : avForm->anl_base_thresh;
+
+ vac_cost_limit = (avForm->vac_cost_limit < 0) ?
+ (autovacuum_vac_cost_limit < 0) ? VacuumCostLimit :
+ autovacuum_vac_cost_limit : avForm->vac_cost_limit;
+
+ vac_cost_delay = (avForm->vac_cost_delay < 0) ?
+ (autovacuum_vac_cost_delay < 0) ? VacuumCostDelay :
+ autovacuum_vac_cost_delay : avForm->vac_cost_delay;
}
else
{
***************
*** 653,658 ****
--- 746,757 ----
anl_scale_factor = autovacuum_anl_scale;
anl_base_thresh = autovacuum_anl_thresh;
+
+ vac_cost_limit = (autovacuum_vac_cost_limit < 0) ?
+ VacuumCostLimit : autovacuum_vac_cost_limit;
+
+ vac_cost_delay = (autovacuum_vac_cost_delay < 0) ?
+ VacuumCostDelay : autovacuum_vac_cost_delay;
}
vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
***************
*** 668,679 ****
RelationGetRelationName(rel),
vactuples, vacthresh, anltuples, anlthresh);
/* Determine if this table needs vacuum or analyze. */
if (vactuples > vacthresh)
{
elog(DEBUG2, "will VACUUM ANALYZE %s",
RelationGetRelationName(rel));
! *vacuum_tables = lappend_oid(*vacuum_tables, relid);
}
else if (anltuples > anlthresh)
{
--- 767,788 ----
RelationGetRelationName(rel),
vactuples, vacthresh, anltuples, anlthresh);
+ Assert(CurrentMemoryContext == AutovacMemCxt);
+
/* Determine if this table needs vacuum or analyze. */
if (vactuples > vacthresh)
{
+ autovac_table *tab = (autovac_table *)
+ palloc(sizeof(autovac_table));
+
elog(DEBUG2, "will VACUUM ANALYZE %s",
RelationGetRelationName(rel));
!
! tab->relid = relid;
! tab->vacuum_cost_limit = vac_cost_limit;
! tab->vacuum_cost_delay = vac_cost_delay;
!
! *vacuum_tables = lappend(*vacuum_tables, tab);
}
else if (anltuples > anlthresh)
{
***************
*** 691,704 ****
/*
* autovacuum_do_vac_analyze
! * Vacuum or analyze a list of tables; or all tables if relids = NIL
! *
! * We must be in AutovacMemCxt when this routine is called.
*/
static void
! autovacuum_do_vac_analyze(List *relids, bool dovacuum)
{
! VacuumStmt *vacstmt = makeNode(VacuumStmt);
/*
* Point QueryContext to the autovac memory context to fake out the
--- 800,821 ----
/*
* autovacuum_do_vac_analyze
! * Vacuum and/or analyze a list of tables; or all tables if relids = NIL
*/
static void
! autovacuum_do_vac_analyze(List *relids, bool doanalyze, bool dovacuum,
! bool freeze)
{
! VacuumStmt *vacstmt;
! MemoryContext old_cxt;
!
! /*
! * The node must survive transaction boundaries, so make sure we create it
! * in a long-lived context
! */
! old_cxt = MemoryContextSwitchTo(AutovacMemCxt);
!
! vacstmt = makeNode(VacuumStmt);
/*
* Point QueryContext to the autovac memory context to fake out the
***************
*** 710,722 ****
/* Set up command parameters */
vacstmt->vacuum = dovacuum;
vacstmt->full = false;
! vacstmt->analyze = true;
! vacstmt->freeze = false;
vacstmt->verbose = false;
vacstmt->relation = NULL; /* all tables, or not used if relids != NIL */
vacstmt->va_cols = NIL;
vacuum(vacstmt, relids);
}
/*
--- 827,842 ----
/* Set up command parameters */
vacstmt->vacuum = dovacuum;
vacstmt->full = false;
! vacstmt->analyze = doanalyze;
! vacstmt->freeze = freeze;
vacstmt->verbose = false;
vacstmt->relation = NULL; /* all tables, or not used if relids != NIL */
vacstmt->va_cols = NIL;
vacuum(vacstmt, relids);
+
+ pfree(vacstmt);
+ MemoryContextSwitchTo(old_cxt);
}
/*
Index: src/backend/postmaster/pgstat.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.102
diff -c -r1.102 pgstat.c
*** src/backend/postmaster/pgstat.c 29 Jul 2005 19:30:04 -0000 1.102
--- src/backend/postmaster/pgstat.c 31 Jul 2005 21:36:07 -0000
***************
*** 99,105 ****
* ----------
*/
bool pgstat_collect_startcollector = true;
! bool pgstat_collect_resetonpmstart = true;
bool pgstat_collect_querystring = false;
bool pgstat_collect_tuplelevel = false;
bool pgstat_collect_blocklevel = false;
--- 99,105 ----
* ----------
*/
bool pgstat_collect_startcollector = true;
! bool pgstat_collect_resetonpmstart = false;
bool pgstat_collect_querystring = false;
bool pgstat_collect_tuplelevel = false;
bool pgstat_collect_blocklevel = false;
***************
*** 236,242 ****
* statistics on postmaster start, simply remove the stats file.
*/
if (!pgstat_collect_startcollector || pgstat_collect_resetonpmstart)
! unlink(PGSTAT_STAT_FILENAME);
/*
* Nothing else required if collector will not get started
--- 236,242 ----
* statistics on postmaster start, simply remove the stats file.
*/
if (!pgstat_collect_startcollector || pgstat_collect_resetonpmstart)
! pgstat_reset_all();
/*
* Nothing else required if collector will not get started
***************
*** 455,460 ****
--- 455,472 ----
pgstat_collect_blocklevel = false;
}
+ /*
+ * pgstat_reset_all() -
+ *
+ * Remove the stats file. This is used on server start if the
+ * stats_reset_on_server_start feature is enabled, or if WAL
+ * recovery is needed after a crash.
+ */
+ void
+ pgstat_reset_all(void)
+ {
+ unlink(PGSTAT_STAT_FILENAME);
+ }
#ifdef EXEC_BACKEND
Index: src/backend/utils/init/flatfiles.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/init/flatfiles.c,v
retrieving revision 1.13
diff -c -r1.13 flatfiles.c
*** src/backend/utils/init/flatfiles.c 28 Jul 2005 22:27:02 -0000 1.13
--- src/backend/utils/init/flatfiles.c 9 Aug 2005 16:39:46 -0000
***************
*** 208,219 ****
char *datname;
Oid datoid;
Oid dattablespace;
! TransactionId datfrozenxid;
datname = NameStr(dbform->datname);
datoid = HeapTupleGetOid(tuple);
dattablespace = dbform->dattablespace;
datfrozenxid = dbform->datfrozenxid;
/*
* Identify the oldest datfrozenxid, ignoring databases that are not
--- 208,221 ----
char *datname;
Oid datoid;
Oid dattablespace;
! TransactionId datfrozenxid,
! datvacuumxid;
datname = NameStr(dbform->datname);
datoid = HeapTupleGetOid(tuple);
dattablespace = dbform->dattablespace;
datfrozenxid = dbform->datfrozenxid;
+ datvacuumxid = dbform->datvacuumxid;
/*
* Identify the oldest datfrozenxid, ignoring databases that are not
***************
*** 248,254 ****
* for forensic purposes.
*/
fputs_quote(datname, fp);
! fprintf(fp, " %u %u %u\n", datoid, dattablespace, datfrozenxid);
}
heap_endscan(scan);
--- 250,257 ----
* for forensic purposes.
*/
fputs_quote(datname, fp);
! fprintf(fp, " %u %u %u %u\n", datoid, dattablespace, datfrozenxid,
! datvacuumxid);
}
heap_endscan(scan);
Index: src/backend/utils/init/postinit.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/init/postinit.c,v
retrieving revision 1.155
diff -c -r1.155 postinit.c
*** src/backend/utils/init/postinit.c 31 Jul 2005 17:19:19 -0000 1.155
--- src/backend/utils/init/postinit.c 9 Aug 2005 16:43:27 -0000
***************
*** 78,84 ****
char *filename;
FILE *db_file;
char thisname[NAMEDATALEN];
! TransactionId frozenxid;
filename = database_getflatfilename();
db_file = AllocateFile(filename, "r");
--- 78,84 ----
char *filename;
FILE *db_file;
char thisname[NAMEDATALEN];
! TransactionId dummyxid;
filename = database_getflatfilename();
db_file = AllocateFile(filename, "r");
***************
*** 88,94 ****
errmsg("could not open file \"%s\": %m", filename)));
while (read_pg_database_line(db_file, thisname, db_id,
! db_tablespace, &frozenxid))
{
if (strcmp(thisname, name) == 0)
{
--- 88,95 ----
errmsg("could not open file \"%s\": %m", filename)));
while (read_pg_database_line(db_file, thisname, db_id,
! db_tablespace, &dummyxid,
! &dummyxid))
{
if (strcmp(thisname, name) == 0)
{
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.280
diff -c -r1.280 guc.c
*** src/backend/utils/misc/guc.c 30 Jul 2005 15:17:20 -0000 1.280
--- src/backend/utils/misc/guc.c 31 Jul 2005 21:15:36 -0000
***************
*** 672,678 ****
NULL
},
&pgstat_collect_resetonpmstart,
! true, NULL, NULL
},
{
{"stats_command_string", PGC_SUSET, STATS_COLLECTOR,
--- 672,678 ----
NULL
},
&pgstat_collect_resetonpmstart,
! false, NULL, NULL
},
{
{"stats_command_string", PGC_SUSET, STATS_COLLECTOR,
***************
*** 1161,1166 ****
--- 1161,1184 ----
},
{
+ {"autovacuum_vacuum_cost_delay", PGC_USERSET, AUTOVACUUM,
+ gettext_noop("Vacuum cost delay in milliseconds, for autovacuum."),
+ NULL
+ },
+ &autovacuum_vac_cost_delay,
+ -1, -1, 1000, NULL, NULL
+ },
+
+ {
+ {"autovacuum_vacuum_cost_limit", PGC_USERSET, AUTOVACUUM,
+ gettext_noop("Vacuum cost amount available before napping, for autovacuum."),
+ NULL
+ },
+ &autovacuum_vac_cost_limit,
+ -1, -1, 10000, NULL, NULL
+ },
+
+ {
{"max_files_per_process", PGC_POSTMASTER, RESOURCES_KERNEL,
gettext_noop("Sets the maximum number of simultaneously open files for each server process."),
NULL
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.155
diff -c -r1.155 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample 30 Jul 2005 15:17:20 -0000 1.155
--- src/backend/utils/misc/postgresql.conf.sample 31 Jul 2005 21:16:43 -0000
***************
*** 287,293 ****
#stats_command_string = off
#stats_block_level = off
#stats_row_level = off
! #stats_reset_on_server_start = on
#---------------------------------------------------------------------------
--- 287,293 ----
#stats_command_string = off
#stats_block_level = off
#stats_row_level = off
! #stats_reset_on_server_start = off
#---------------------------------------------------------------------------
***************
*** 300,305 ****
--- 300,309 ----
#autovacuum_analyze_threshold = 500 # min # of tuple updates before analyze
#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before vacuum
#autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before analyze
+ #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for autovac
+ # negative means use vacuum_cost_delay
+ #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for autovac
+ # negative means use vacuum_cost_limit
#---------------------------------------------------------------------------
Index: src/include/pgstat.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/pgstat.h,v
retrieving revision 1.34
diff -c -r1.34 pgstat.h
*** src/include/pgstat.h 29 Jul 2005 19:30:09 -0000 1.34
--- src/include/pgstat.h 31 Jul 2005 21:32:59 -0000
***************
*** 367,372 ****
--- 367,373 ----
extern void pgstat_init(void);
extern int pgstat_start(void);
extern void pgstat_beterm(int pid);
+ extern void pgstat_reset_all(void);
#ifdef EXEC_BACKEND
extern void PgstatBufferMain(int argc, char *argv[]);
Index: src/include/catalog/pg_autovacuum.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/catalog/pg_autovacuum.h,v
retrieving revision 1.1
diff -c -r1.1 pg_autovacuum.h
*** src/include/catalog/pg_autovacuum.h 14 Jul 2005 05:13:42 -0000 1.1
--- src/include/catalog/pg_autovacuum.h 31 Jul 2005 23:26:27 -0000
***************
*** 34,39 ****
--- 34,41 ----
float4 vac_scale_factor; /* reltuples scaling factor */
int4 anl_base_thresh; /* base threshold value */
float4 anl_scale_factor; /* reltuples scaling factor */
+ int4 vac_cost_delay; /* vacuum cost-based delay */
+ int4 vac_cost_limit; /* vacuum cost limit */
} FormData_pg_autovacuum;
/* ----------------
***************
*** 47,59 ****
* compiler constants for pg_autovacuum
* ----------------
*/
! #define Natts_pg_autovacuum 6
#define Anum_pg_autovacuum_vacrelid 1
#define Anum_pg_autovacuum_enabled 2
#define Anum_pg_autovacuum_vac_base_thresh 3
#define Anum_pg_autovacuum_vac_scale_factor 4
#define Anum_pg_autovacuum_anl_base_thresh 5
#define Anum_pg_autovacuum_anl_scale_factor 6
/* There are no preloaded tuples in pg_autovacuum.h */
--- 49,63 ----
* compiler constants for pg_autovacuum
* ----------------
*/
! #define Natts_pg_autovacuum 8
#define Anum_pg_autovacuum_vacrelid 1
#define Anum_pg_autovacuum_enabled 2
#define Anum_pg_autovacuum_vac_base_thresh 3
#define Anum_pg_autovacuum_vac_scale_factor 4
#define Anum_pg_autovacuum_anl_base_thresh 5
#define Anum_pg_autovacuum_anl_scale_factor 6
+ #define Anum_pg_autovacuum_vac_cost_delay 7
+ #define Anum_pg_autovacuum_vac_cost_limit 8
/* There are no preloaded tuples in pg_autovacuum.h */
Index: src/include/libpq/hba.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/libpq/hba.h,v
retrieving revision 1.39
diff -c -r1.39 hba.h
*** src/include/libpq/hba.h 29 Jul 2005 19:30:08 -0000 1.39
--- src/include/libpq/hba.h 9 Aug 2005 16:41:50 -0000
***************
*** 37,42 ****
extern int hba_getauthmethod(hbaPort *port);
extern int authident(hbaPort *port);
extern bool read_pg_database_line(FILE *fp, char *dbname, Oid *dboid,
! Oid *dbtablespace, TransactionId *dbfrozenxid);
#endif /* HBA_H */
--- 37,43 ----
extern int hba_getauthmethod(hbaPort *port);
extern int authident(hbaPort *port);
extern bool read_pg_database_line(FILE *fp, char *dbname, Oid *dboid,
! Oid *dbtablespace, TransactionId *dbfrozenxid,
! TransactionId *dbvacuumxid);
#endif /* HBA_H */
Index: src/include/postmaster/autovacuum.h
===================================================================
RCS file: /home/alvherre/cvs/pgsql/src/include/postmaster/autovacuum.h,v
retrieving revision 1.1
diff -c -r1.1 autovacuum.h
*** src/include/postmaster/autovacuum.h 14 Jul 2005 05:13:43 -0000 1.1
--- src/include/postmaster/autovacuum.h 31 Jul 2005 17:32:12 -0000
***************
*** 21,26 ****
--- 21,28 ----
extern double autovacuum_vac_scale;
extern int autovacuum_anl_thresh;
extern double autovacuum_anl_scale;
+ extern int autovacuum_vac_cost_delay;
+ extern int autovacuum_vac_cost_limit;
/* Status inquiry functions */
extern bool AutoVacuumingActive(void);
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.
I thought I'd run a couple of tests to see if it would be helpful
against CVS from Aug 3, 2005.
Here's a run with autovacuum turned off:
http://www.testing.osdl.org/projects/dbt2dev/results/dev4-015/42/
5186.55 notpm
Autvacuum on with default settings:
http://www.testing.osdl.org/projects/dbt2dev/results/dev4-015/38/
5462.23 notpm
Would it help more to try a series of parameter changes?
Mark
On Fri, Aug 12, 2005 at 10:49:43AM -0700, Mark Wong wrote:
I thought I'd run a couple of tests to see if it would be helpful
against CVS from Aug 3, 2005.Here's a run with autovacuum turned off:
http://www.testing.osdl.org/projects/dbt2dev/results/dev4-015/42/
5186.55 notpmAutvacuum on with default settings:
http://www.testing.osdl.org/projects/dbt2dev/results/dev4-015/38/
5462.23 notpm
Just noticed what seems to be a bug: in
http://www.testing.osdl.org/projects/dbt2dev/results/dev4-015/42/db/index_info.input
plot "index_info.data" using 1:2 title "i_customer" with lines, \
"index_info.data" using 1:2 title "i_orders" with lines, \
"index_info.data" using 1:3 title "pk_customer" with lines, \
"index_info.data" using 1:4 title "pk_district" with lines, \
"index_info.data" using 1:5 title "pk_item" with lines, \
"index_info.data" using 1:6 title "pk_new_order" with lines, \
"index_info.data" using 1:7 title "pk_order_line" with lines, \
"index_info.data" using 1:8 title "pk_orders" with lines, \
"index_info.data" using 1:9 title "pk_stock" with lines, \
"index_info.data" using 1:11 title "pk_warehouse" with lines
Notice how the subindexes are wrong ... I think it should be 1:3 for
i_orders, no? Apparently indexes_scan.data has the same problem.
It called my attention that the pk_warehouse index seems to have a very
different usage in both runs in index_info, but in indexes_scan they
seem similar.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Las mujeres son como hondas: mientras m�s resistencia tienen,
m�s lejos puedes llegar con ellas" (Jonas Nightingale, Leap of Faith)
On Fri, 12 Aug 2005 17:49:41 -0400
Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On Fri, Aug 12, 2005 at 10:49:43AM -0700, Mark Wong wrote:
I thought I'd run a couple of tests to see if it would be helpful
against CVS from Aug 3, 2005.Here's a run with autovacuum turned off:
http://www.testing.osdl.org/projects/dbt2dev/results/dev4-015/42/
5186.55 notpmAutvacuum on with default settings:
http://www.testing.osdl.org/projects/dbt2dev/results/dev4-015/38/
5462.23 notpmJust noticed what seems to be a bug: in
http://www.testing.osdl.org/projects/dbt2dev/results/dev4-015/42/db/index_info.input
plot "index_info.data" using 1:2 title "i_customer" with lines, \
"index_info.data" using 1:2 title "i_orders" with lines, \
"index_info.data" using 1:3 title "pk_customer" with lines, \
"index_info.data" using 1:4 title "pk_district" with lines, \
"index_info.data" using 1:5 title "pk_item" with lines, \
"index_info.data" using 1:6 title "pk_new_order" with lines, \
"index_info.data" using 1:7 title "pk_order_line" with lines, \
"index_info.data" using 1:8 title "pk_orders" with lines, \
"index_info.data" using 1:9 title "pk_stock" with lines, \
"index_info.data" using 1:11 title "pk_warehouse" with linesNotice how the subindexes are wrong ... I think it should be 1:3 for
i_orders, no? Apparently indexes_scan.data has the same problem.
Whoops! I think I fixed it for real now and the charts should be
updated now. It was broken slightly more previously.
It called my attention that the pk_warehouse index seems to have a very
different usage in both runs in index_info, but in indexes_scan they
seem similar.
Thanks,
Mark
On Fri, Aug 12, 2005 at 03:16:04PM -0700, Mark Wong wrote:
On Fri, 12 Aug 2005 17:49:41 -0400
Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:Notice how the subindexes are wrong ... I think it should be 1:3 for
i_orders, no? Apparently indexes_scan.data has the same problem.Whoops! I think I fixed it for real now and the charts should be
updated now. It was broken slightly more previously.
Hmm, did you fix the 42 case only? The other one is broken too ...
Also, it seems the "tran_lock.out" file captured wrong input -- I think
you mean "WHERE transactionid IS NULL" in the query instead of "WHERE
transaction IS NULL".
I wonder what the big down-spikes (?) at minutes ~45 and ~85 correspond
to. Are those checkpoints? The IO vmstat chart would indicate that, I
think.
Anyway, it's interesting to see the performance go up with autovacuum
on. I certainly didn't expect that in this kind of test.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"No necesitamos banderas
No reconocemos fronteras" (Jorge Gonz�lez)
On Fri, 12 Aug 2005 18:42:09 -0400
Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On Fri, Aug 12, 2005 at 03:16:04PM -0700, Mark Wong wrote:
On Fri, 12 Aug 2005 17:49:41 -0400
Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:Notice how the subindexes are wrong ... I think it should be 1:3 for
i_orders, no? Apparently indexes_scan.data has the same problem.Whoops! I think I fixed it for real now and the charts should be
updated now. It was broken slightly more previously.Hmm, did you fix the 42 case only? The other one is broken too ...
The other dev4-015 cases should be fixed too.
Also, it seems the "tran_lock.out" file captured wrong input -- I think
you mean "WHERE transactionid IS NULL" in the query instead of "WHERE
transaction IS NULL".
Hmm, ok I can try that in a future test run. I'm not very familiar with
this table, what's the difference between transaction and transactionid?
I wonder what the big down-spikes (?) at minutes ~45 and ~85 correspond
to. Are those checkpoints? The IO vmstat chart would indicate that, I
think.
That's correct, those should be checkpoints.
Anyway, it's interesting to see the performance go up with autovacuum
on. I certainly didn't expect that in this kind of test.
I think in Mary's case it was hurting, but she's running the workload
dramatically different. I think she was planning to revisit that after
we sort out what's going on with the grouped WAL writes.
Mark
On Fri, Aug 12, 2005 at 03:49:57PM -0700, Mark Wong wrote:
On Fri, 12 Aug 2005 18:42:09 -0400
Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Also, it seems the "tran_lock.out" file captured wrong input -- I think
you mean "WHERE transactionid IS NULL" in the query instead of "WHERE
transaction IS NULL".Hmm, ok I can try that in a future test run. I'm not very familiar with
this table, what's the difference between transaction and transactionid?
transaction is the Xid of the transaction holding or waiting for the
lock. transactionid is not null in the case where the lock is for a
TransactionId. I guess it depends on what do you want though -- now
that I think about it, capturing only transaction locks is very likely
not what you want.
http://developer.postgresql.org/docs/postgres/view-pg-locks.html
I wonder why do you have that condition though. I don't think
"transaction" can ever be NULL in that view.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Hay dos momentos en la vida de un hombre en los que no deber�a
especular: cuando puede permit�rselo y cuando no puede" (Mark Twain)