SEO

Hackers,

Here is a second attempt at autovacuum integration. Please have a look
at it. Note that this patch automatically creates three new files:

src/backend/postmaster/autovacuum.c
src/include/catalog/pg_autovacuum.h
src/include/postmaster/autovacuum.h


There are the following changes since the previous version:

- Xid wraparound is taken care of, with a database-wide VACUUM when the
limit is too close.

- Manually executing VACUUM or ANALYZE now updates the statistics for
autovacuum.

- EXEC_BACKEND case was relieved of a few bugs -- it now actually works.

- elog(ERROR) is now handled more gracefully; the error is emitted,
instead of a SEGV happening. The end result is the same, however:
a single error takes the process down, and the database will be
vacuumed again only after all other databases are processed.

- Adapted to the new cwd convention.

- My email address is changed, so that I won't be posting the patch
several times hopefully :-)


Note that I didn't make the autovacuum daemon more verbose. Running
postmaster -d2 (or server_log_messages or whatever the option is called,
to DEBUG2 or higher) shows some messages which are, I hope, what Matthew
was expecting. (It this is not the case please let me know.)

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"El sabio habla porque tiene algo que decir;
el tonto, porque tiene que decir algo" (Platon).


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> Here is a second attempt at autovacuum integration.

A few comments:

* I strongly disagree with keeping updatable state in a catalog. In the
first place, that will cause autovac itself to create vacuumable trash.
In the second place, that means you can't modify the internal state kept
by autovacuum without forcing initdb; which is not a good situation,
considering how much change this code is likely to go through.
I think all the updatable state should be kept in the pgstats file.
(Memo to self: let's add a version number header to the pgstats file,
so that we can change its format without requiring an initdb to do so.)
pg_autovacuum should only contain user-settable parameters --- which is
still putting us at nontrivial risk for initdb constraints, but it's way
better than internal state. If you do that, then pg_autovacuum need
only contain entries for tables that have been given non-default
parameter settings (ie, those for which the user wants to override
global settings given in postgresql.conf).

* I'm pretty dubious about adding a syscache for pg_autovacuum.
Where exactly are the repeated fetches going to come from to justify
caching it?

* The signal processing needs re-work; you can't just randomly assign
signal numbers, you need to keep these things largely consistent with
the other subprocesses. In particular, if this thing is going to be
running transactions then it MUST honor SIGALRM (eg, for deadlock
timeout checks) and SIGUSR1 (sinval catchup interrupt), and I don't
think you get to ignore SIGTERM either (may get this from init).
I think it's a pretty bad idea to use SIGUSR2 for something other than
what regular backends use it for, too. (Consider the prospect that for
some reason your PID occurs in pg_listener.) It'd be a good idea to
honor SIGINT with the normal meaning (query cancel, ie, abort
transaction, which in this context would also imply process shutdown)
and use that to shut down the daemon at postmaster stop. In short,
the signal handling had better look a whole lot more like a normal
backend's.

* Speaking of shutdown, you can't stop the bgwriter until you know
that the autovac daemon is dead. In this respect too, it's much more
like a backend than like any of the other support processes. Signal it
when you signal the backends, and don't advance to the bgwriter kill
phase until autovac and all the backends are known gone.

* I see you have an autovac_init function to "annoy the user", but
shouldn't this be checked every time we are about to spawn an autovac
process?

* I don't see any special checks for shared catalogs, which means they
are probably going to be over-vacuumed; or possibly under-vacuumed if
you fail to track the update stats for them in a single place rather
than in each database. It'd probably be a good idea to nominate just
one database to be the place from which shared catalogs are vacuumed;
or treat them as a completely separate special case.

* I have no objection to adding extra entry points to vacuum.c to
simplify the calls to it.

* With respect to comments like:

+ /*
+ * We just skip a table not found on the stat hash. This is
+ * because whatever we do here, there won't be good statistics
+ * next time around, so we would do this same action again. It is
+ * tempting to issue an ANALYZE, but it won't work because ANALYZE
+ * doesn't update the stat system.
+ */

ISTM the entire point of "autovac integration" is to make sure the rest
of the system does what's needed to support autovac. If ANALYZE needs
to send something to the stats system, make it do so. (ISTM it'd be
reasonable for ANALYZE to make and report an estimate of the dead tuple
count, which after all is what you are hoping all the rest of the stats
machinery will derive for you...)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Fri, Jul 08, 2005 at 03:56:25PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Here is a second attempt at autovacuum integration.
>
> A few comments:

Excellent, thanks. I'll be working on fixing all these.

> * I strongly disagree with keeping updatable state in a catalog. In the
> first place, that will cause autovac itself to create vacuumable trash.

Yes, I thought about that too. I think the argument that convinced me
to do otherwise was the possibility of messages being lost in the path
to stat collector. And I thought that it would be possible to change
tuples in-place, like vacuum does for pg_class. I don't have a problem
with changing it though.

The initdb-forcing point is a good one, didn't think of it.

> pg_autovacuum should only contain user-settable parameters --- which is
> still putting us at nontrivial risk for initdb constraints, but it's way
> better than internal state. If you do that, then pg_autovacuum need
> only contain entries for tables that have been given non-default
> parameter settings.

Ok. Do you think it's worth the trouble to have ALTER TABLE commands to
change autovac parameters?

> * The signal processing needs re-work; you can't just randomly assign
> signal numbers, you need to keep these things largely consistent with
> the other subprocesses.

I think I copied this from pgarch.c, which I can see was a really bad
idea. Will fix.

> * I see you have an autovac_init function to "annoy the user", but
> shouldn't this be checked every time we are about to spawn an autovac
> process?

You argued exactly the other way around to Matthew, before 8.0 freeze.
Personally I don't care either way.


> * I don't see any special checks for shared catalogs, which means they
> are probably going to be over-vacuumed; or possibly under-vacuumed if
> you fail to track the update stats for them in a single place rather
> than in each database. It'd probably be a good idea to nominate just
> one database to be the place from which shared catalogs are vacuumed;
> or treat them as a completely separate special case.

Yeah. The problem with this is that any particular database could be
absent from the stat hash. Personally I think we should make the stat
system aware of all databases, whether there has been activity in them
or not. That'd make this problem moot, and we could skip those tables
in any databases except template1.


Anything I omitted I agree with, so I'll fix things up accordingly.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"The Gord often wonders why people threaten never to come back after they've
been told never to return" (www.actsofgord.com)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On Fri, Jul 08, 2005 at 03:56:25PM -0400, Tom Lane wrote:
>> pg_autovacuum should only contain user-settable parameters ---

> Ok. Do you think it's worth the trouble to have ALTER TABLE commands to
> change autovac parameters?

I think we'll want them eventually, but I don't mind if 8.1 ships
without 'em.

>> * I see you have an autovac_init function to "annoy the user", but
>> shouldn't this be checked every time we are about to spawn an autovac
>> process?

> You argued exactly the other way around to Matthew, before 8.0 freeze.

Did I? I've forgotten the reasoning if so. But it's a minor point.

> Yeah. The problem with this is that any particular database could be
> absent from the stat hash. Personally I think we should make the stat
> system aware of all databases, whether there has been activity in them
> or not. That'd make this problem moot, and we could skip those tables
> in any databases except template1.

Hm. There's nothing wrong with having CREATE DATABASE send a message to
pgstats, but that could get lost anyway. A probably more significant
point is that either postgres or template1 is subject to being dropped;
so hardwiring a single database to do it from is likely not gonna work.

It'd be okay to *consider* vacuuming the shared relations on every
cycle, so long as the stats were managed correctly (ie, only one set of
stats kept). This is probably easier to do in the context of the
stats-in-pgstats idea than before. We could have pgstats deliberately
create an entry for "database zero", and put the table stats for shared
relations there. The only real issue is that we have no place to keep
any nondefault parameters for these catalogs (no, I don't wish to invent
pg_shautovacuum to fix that ;-)) so they'd always be autovacuumed with
the global default parameters. This seems acceptable to me.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane wrote:

>Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>
>
>>Ok. Do you think it's worth the trouble to have ALTER TABLE commands to
>>change autovac parameters?
>>
>>
>
>I think we'll want them eventually, but I don't mind if 8.1 ships
>without 'em.
>
>

It might be good to ship 8.1 without them since autovacuum might change
a fair amount between 8.1 and 8.2 (FSM integration, better formula for
vacuum decisions etc...)


>>Yeah. The problem with this is that any particular database could be
>>absent from the stat hash. Personally I think we should make the stat
>>system aware of all databases, whether there has been activity in them
>>or not. That'd make this problem moot, and we could skip those tables
>>in any databases except template1.
>>
>>
>
>Hm. There's nothing wrong with having CREATE DATABASE send a message to
>pgstats, but that could get lost anyway. A probably more significant
>point is that either postgres or template1 is subject to being dropped;
>so hardwiring a single database to do it from is likely not gonna work.
>
>It'd be okay to *consider* vacuuming the shared relations on every
>cycle, so long as the stats were managed correctly (ie, only one set of
>stats kept). This is probably easier to do in the context of the
>stats-in-pgstats idea than before. We could have pgstats deliberately
>create an entry for "database zero", and put the table stats for shared
>relations there. The only real issue is that we have no place to keep
>any nondefault parameters for these catalogs (no, I don't wish to invent
>pg_shautovacuum to fix that ;-)) so they'd always be autovacuumed with
>the global default parameters. This seems acceptable to me.
>

If memory serves from development discussion of the contrib version,
isn't there an issue that while a shared relation need only be vacuumed
in one database in order to reclaim space etc, the analyze statistics
need to be updated in each database. Does that ring a bell or am I way
off base here?



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

"Matthew T. O'Connor" <matthew@zeut.net> writes:
> Tom Lane wrote:
>> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>>> Ok. Do you think it's worth the trouble to have ALTER TABLE commands to
>>> change autovac parameters?
>>
>> I think we'll want them eventually, but I don't mind if 8.1 ships
>> without 'em.
>>
> It might be good to ship 8.1 without them since autovacuum might change
> a fair amount between 8.1 and 8.2 (FSM integration, better formula for
> vacuum decisions etc...)

Yeah, I was thinking the same thing: the more support there is, the
harder it will be to change the parameter set. Leaving it as a catalog
only will send a suitable message that this is still a prototype ...

> If memory serves from development discussion of the contrib version,
> isn't there an issue that while a shared relation need only be vacuumed
> in one database in order to reclaim space etc, the analyze statistics
> need to be updated in each database. Does that ring a bell or am I way
> off base here?

That's a fair point, but I'm not sure how much we care about maintaining
stats for the shared relations. These days the planner should get it
reasonably right about the relation size anyway, and the availability of
pg_statistic data is only a secondary concern; especially since we have
unique indexes on most of the columns people might search on, and the
presence of such an index also clues the planner.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Fri, Jul 08, 2005 at 03:56:25PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > Here is a second attempt at autovacuum integration.
>
> A few comments:

Ok, here is an updated patch. Hopefully I have covered most your
more important observations. Particularly, I changed the shutdown
sequence per your comments, and the pg_autovacuum tuple is optional.

Additional comments:

> * I see you have an autovac_init function to "annoy the user", but
> shouldn't this be checked every time we are about to spawn an autovac
> process?

I didn't do anything about this (i.e. it only happens once). Note that
if we annoy the user because of this, the autovacuum process is
disabled "forever."

> * I don't see any special checks for shared catalogs, which means they
> are probably going to be over-vacuumed; or possibly under-vacuumed if
> you fail to track the update stats for them in a single place rather
> than in each database.

I'm still not doing anything special about shared relations. I think it
would be easy to treat them in a special way.

> * I have no objection to adding extra entry points to vacuum.c to
> simplify the calls to it.

I didn't do it, because it uglified the code. Rather, I added a relid
member to VacuumStmt.

> If ANALYZE needs to send something to the stats system, make it do
> so.

It does, as does VACUUM. I still think we should do something special
on TRUNCATE, maybe send a special message.


Note that I keep track of dead tuples directly in the stats for each
table, rather than keeping track of "last vacuum tuples", which was a
strange concept anyway. It came out being much simpler this way. The
only consideration is that it makes the vacuum case different from
analyze, but I don't see that as a problem.

Also, there are tables for which analyze refuses to run. I'm not sure
what to do about them. The problem is that since ANALYZE doesn't run to
completion, it doesn't emit the stat message, so we try to analyze it
the next time around. I considered sending special messages to the
stats, telling not to analyze the table in the future (vacuum still
works as expected). However I don't see how would we re-enable the
auto-analyze feature in case something happens to the table. There are
two cases: pg_statistics is one, and the other is tables that don't have
any analyzable columns (There is at least one table of this kind in the
regression test, comprising one "box" column.) This may turn out not to
be a problem, since ANALYZE will return very quickly in this case, but
it annoys me anyway.

Finally: I didn't do anything special about TOAST tables yet. I think
this is a separate problem.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
Thou shalt study thy libraries and strive not to reinvent them without
cause, that thy code may be short and readable and thy days pleasant
and productive. (7th Commandment for C Programmers)


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org