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)


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

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.



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

http://archives.postgresql.org

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

regards, tom lane

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

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

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)

---------------------------(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

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

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

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)

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

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

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

http://archives.postgresql.org

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)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


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

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

"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

---------------------------(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