Autovacuum on by default?

Is it time to turn on autovacuum by default in 8.2? I know
we wanted to be on the side of caution with 8.1, but perhaps
we should evaluate the experiences now. Comments?

FWIW, the win32 installer has enalbed autovacuum by default already in
8.1. So it's definitly received a fair amount of testing from those
users (except those that turned it off, of course)

//Magnus

Peter,

Is it time to turn on autovacuum by default in 8.2? I know we wanted to
be on the side of caution with 8.1, but perhaps we should evaluate the
experiences now. Comments?

I'm in favor of this, but do we want to turn on vacuum_delay by default
as well?

--Josh

Peter Eisentraut wrote:

Is it time to turn on autovacuum by default in 8.2? I know we wanted to
be on the side of caution with 8.1, but perhaps we should evaluate the
experiences now. Comments?

Would be fine by me, but I'm curious to see what the community has to
say. A few comments:

Autovacuum can cause unpredictable performance issues, that is if it
vacuums in the middle of a busy day and people don't want that, of
course they turn it off easy enough, but they might be surprised.

I haven't played with CVS HEAD much, but I think the logging issue has
been addressed no? That is my single biggest gripe with the 8.1
autovacuum is that it's very hard to see if it's actually done anything
without having to turn up the logging significantly.

The remaining big ticket items for autovacuum are the maintenance window
that Alvaro and I have just been discussing, and multiple concurrent
vacuum, (possibly you could add the continued reduction of vacuum impact
but that just a constant thing). Do we think it's worth waiting for
either of these two features prior to turning on autovacuum by default?

Matt

On Thu, 2006-08-17 at 18:32 +0200, Peter Eisentraut wrote:

Is it time to turn on autovacuum by default in 8.2? I know we wanted to
be on the side of caution with 8.1, but perhaps we should evaluate the
experiences now. Comments?

I would say yes.

I use it on 2 databases over the 200GB mark without any difficulties.
One is OLTP and the other acts more like a data warehouse.

The defaults could be a little more aggressive for both vacuum and
analyze scale_factor settings; 10% and 5% respectively.

--

Matthew T. O'Connor wrote:

Peter Eisentraut wrote:

Is it time to turn on autovacuum by default in 8.2? I know we wanted to
be on the side of caution with 8.1, but perhaps we should evaluate the
experiences now. Comments?

Would be fine by me, but I'm curious to see what the community has to
say. A few comments:

Autovacuum can cause unpredictable performance issues, that is if it
vacuums in the middle of a busy day and people don't want that, of
course they turn it off easy enough, but they might be surprised.

I haven't played with CVS HEAD much, but I think the logging issue has
been addressed no? That is my single biggest gripe with the 8.1
autovacuum is that it's very hard to see if it's actually done anything
without having to turn up the logging significantly.

This has not been addressed, except that pg_stat_activity now shows
autovacuum. Someone was going to work on per-module log output, but it
wasn't completed for 8.2. Does pg_stat_activity now show the table
being vacuumed?

The remaining big ticket items for autovacuum are the maintenance window
that Alvaro and I have just been discussing, and multiple concurrent
vacuum, (possibly you could add the continued reduction of vacuum impact
but that just a constant thing). Do we think it's worth waiting for
either of these two features prior to turning on autovacuum by default?

Probably not. It can always be turned off by people who don't want it
on.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

Would be fine by me, but I'm curious to see what the community has to
say. A few comments:

Autovacuum can cause unpredictable performance issues, that is if it
vacuums in the middle of a busy day and people don't want that, of
course they turn it off easy enough, but they might be surprised.

I haven't played with CVS HEAD much, but I think the logging issue has
been addressed no? That is my single biggest gripe with the 8.1
autovacuum is that it's very hard to see if it's actually done anything
without having to turn up the logging significantly.

This has not been addressed, except that pg_stat_activity now shows
autovacuum. Someone was going to work on per-module log output, but it
wasn't completed for 8.2. Does pg_stat_activity now show the table
being vacuumed?

Hmm... I though it had, not the full blown per-module log output stuff,
but just a simple reorgainzing of the log levels for autovacuum
messages. That is lowering the level for:
LOG: autovacuum: processing database "foo"
and increasing the log level when autovacuum actually fires off a VACUUM
or ANALYZE command.

Matthew T. O'Connor wrote:

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

Would be fine by me, but I'm curious to see what the community has to
say. A few comments:

Autovacuum can cause unpredictable performance issues, that is if it
vacuums in the middle of a busy day and people don't want that, of
course they turn it off easy enough, but they might be surprised.

I haven't played with CVS HEAD much, but I think the logging issue has
been addressed no? That is my single biggest gripe with the 8.1
autovacuum is that it's very hard to see if it's actually done anything
without having to turn up the logging significantly.

This has not been addressed, except that pg_stat_activity now shows
autovacuum. Someone was going to work on per-module log output, but it
wasn't completed for 8.2. Does pg_stat_activity now show the table
being vacuumed?

Hmm... I though it had, not the full blown per-module log output stuff,
but just a simple reorgainzing of the log levels for autovacuum
messages. That is lowering the level for:
LOG: autovacuum: processing database "foo"

Yes, this was done.

and increasing the log level when autovacuum actually fires off a VACUUM
or ANALYZE command.

This was not done because the logging control only for autovacuum was
going to be added. Right now, if you want to see the vacuum activity,
you end up seeing debug stuff too (very ugly).

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Rod Taylor wrote:

The defaults could be a little more aggressive for both vacuum and
analyze scale_factor settings; 10% and 5% respectively.

I would agree with this, not sure of 10%/5% are right, but the general
feedback I have heard is that while the defaults in 8.1 are much better
than the ones in the contrib module, they are still not aggressive enough.

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

and increasing the log level when autovacuum actually fires off a VACUUM
or ANALYZE command.

This was not done because the logging control only for autovacuum was
going to be added. Right now, if you want to see the vacuum activity,
you end up seeing debug stuff too (very ugly).

Any chance we can make this change before release? I think it's very
important to be able to look through the logs and *know* that you tables
are getting vacuumed or not.

Matthew T. O'Connor wrote:

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

and increasing the log level when autovacuum actually fires off a VACUUM
or ANALYZE command.

This was not done because the logging control only for autovacuum was
going to be added. Right now, if you want to see the vacuum activity,
you end up seeing debug stuff too (very ugly).

Any chance we can make this change before release? I think it's very
important to be able to look through the logs and *know* that you tables
are getting vacuumed or not.

Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now
show exactly what autovacuum is doing (and if it doesn't, let's fix it).
I think that is the best solution to the monitoring problem, rather than
throwing lines in the server logs.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

and increasing the log level when autovacuum actually fires off a VACUUM
or ANALYZE command.

This was not done because the logging control only for autovacuum was
going to be added. Right now, if you want to see the vacuum activity,
you end up seeing debug stuff too (very ugly).

Any chance we can make this change before release? I think it's very
important to be able to look through the logs and *know* that you tables
are getting vacuumed or not.

Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now
show exactly what autovacuum is doing (and if it doesn't, let's fix it).
I think that is the best solution to the monitoring problem, rather than
throwing lines in the server logs.

Well, the problem is that it shows what it's *currently* doing, but it
doesn't let you know what has happened in the last day or whatever. It
can't answer "has table foo been vacuumed recently?" or "what tables
haven't been vacuumed at all during this week?"

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

Any chance we can make this change before release? I think it's very
important to be able to look through the logs and *know* that you tables
are getting vacuumed or not.

Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now
show exactly what autovacuum is doing (and if it doesn't, let's fix it).
I think that is the best solution to the monitoring problem, rather than
throwing lines in the server logs.

I'm not sure I agree with this. I can use the pg_stat_activity table to
see if autovacuum is doing something right now, but what I want to be
able to do is look through my logs and see that table_foo hasn't been
vacuumed since last week, or that table_bar has been vacuumed 7 times
today. Can I do that just with the stat system alone?

Alvaro Herrera wrote:

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

and increasing the log level when autovacuum actually fires off a
VACUUM or ANALYZE command.

This was not done because the logging control only for autovacuum
was going to be added. Right now, if you want to see the vacuum
activity, you end up seeing debug stuff too (very ugly).

Any chance we can make this change before release? I think it's
very important to be able to look through the logs and *know* that
you tables are getting vacuumed or not.

Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now
show exactly what autovacuum is doing (and if it doesn't, let's fix
it). I think that is the best solution to the monitoring problem,
rather than throwing lines in the server logs.

Well, the problem is that it shows what it's *currently* doing, but it
doesn't let you know what has happened in the last day or whatever.
It can't answer "has table foo been vacuumed recently?" or "what
tables haven't been vacuumed at all during this week?"

I added last vacuum and last analyze (both auto and manual) dates in the 8.2
tree.....

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 512-248-2683 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893

Josh Berkus wrote:

Is it time to turn on autovacuum by default in 8.2? I know we wanted
to be on the side of caution with 8.1, but perhaps we should evaluate
the experiences now. Comments?

I'm in favor of this, but do we want to turn on vacuum_delay by default
as well?

I thought about this, might be a good idea as it will mitigate the
impact of vacuums, however it will also slow down vacuums, I'm a bit
concerned that it won't be able to keep up on really large database, or
that it'll fall really far behind after vacuuming a big table.

Also, if we do enable it, what is a good default?

On Thu, Aug 17, 2006 at 12:41:57PM -0400, Matthew T. O'Connor wrote:

Peter Eisentraut wrote:

Is it time to turn on autovacuum by default in 8.2? I know we wanted to
be on the side of caution with 8.1, but perhaps we should evaluate the
experiences now. Comments?

Would be fine by me, but I'm curious to see what the community has to
say. A few comments:

Autovacuum can cause unpredictable performance issues, that is if it
vacuums in the middle of a busy day and people don't want that, of
course they turn it off easy enough, but they might be surprised.

Anyone in that situation needs to be more hands-on with the database
anyway. I think the big target for autovac is beginners who otherwise
would bloat themselves silly (of course, it's also very useful far
beyond beginners, but by that time an admin should have some clue about
what they're doing).

And +1 on Rod's suggestion to make it more aggressive. I always drop the
scale factor to at least 0.2 and 0.1 (though 0.1 and 0.05 don't seem
unreasonable), and typically drop the thresholds to 200 and 100 (though
again, lower is probably warrented).

Actually, on a table small enough for the thresholds to kick in it's
going to be extremely fast to vacuum anyway, and the table is probably
either static or changing very rapidly. I'm wondering if maybe they
should just default to 0?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Alvaro Herrera wrote:

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

and increasing the log level when autovacuum actually fires off a VACUUM
or ANALYZE command.

This was not done because the logging control only for autovacuum was
going to be added. Right now, if you want to see the vacuum activity,
you end up seeing debug stuff too (very ugly).

Any chance we can make this change before release? I think it's very
important to be able to look through the logs and *know* that you tables
are getting vacuumed or not.

Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now
show exactly what autovacuum is doing (and if it doesn't, let's fix it).
I think that is the best solution to the monitoring problem, rather than
throwing lines in the server logs.

Well, the problem is that it shows what it's *currently* doing, but it
doesn't let you know what has happened in the last day or whatever. It
can't answer "has table foo been vacuumed recently?" or "what tables
haven't been vacuumed at all during this week?"

True, but it seems strange to use the server logs for this type of
information. If anything, perhaps we should allow an optional file name
to put that information, or update pg_class with the most recent vacuum
time, or even better append that information to a system table.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Larry Rosenman wrote:

Alvaro Herrera wrote:

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

and increasing the log level when autovacuum actually fires off a
VACUUM or ANALYZE command.

This was not done because the logging control only for autovacuum
was going to be added. Right now, if you want to see the vacuum
activity, you end up seeing debug stuff too (very ugly).

Any chance we can make this change before release? I think it's
very important to be able to look through the logs and *know* that
you tables are getting vacuumed or not.

Agreed. I just IM'ed Alvaro and he says pg_stat_activity should now
show exactly what autovacuum is doing (and if it doesn't, let's fix
it). I think that is the best solution to the monitoring problem,
rather than throwing lines in the server logs.

Well, the problem is that it shows what it's *currently* doing, but it
doesn't let you know what has happened in the last day or whatever.
It can't answer "has table foo been vacuumed recently?" or "what
tables haven't been vacuumed at all during this week?"

I added last vacuum and last analyze (both auto and manual) dates in the 8.2
tree.....

Ah, true, I forgot about that.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Bruce Momjian wrote:

Larry Rosenman wrote:

Alvaro Herrera wrote:

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

Bruce Momjian wrote:

Matthew T. O'Connor wrote:

and increasing the log level when autovacuum actually fires off
a VACUUM or ANALYZE command.

This was not done because the logging control only for autovacuum
was going to be added. Right now, if you want to see the vacuum
activity, you end up seeing debug stuff too (very ugly).

Any chance we can make this change before release? I think it's
very important to be able to look through the logs and *know* that
you tables are getting vacuumed or not.

Agreed. I just IM'ed Alvaro and he says pg_stat_activity should
now show exactly what autovacuum is doing (and if it doesn't,
let's fix it). I think that is the best solution to the monitoring
problem, rather than throwing lines in the server logs.

Well, the problem is that it shows what it's *currently* doing, but
it doesn't let you know what has happened in the last day or
whatever. It can't answer "has table foo been vacuumed recently?"
or "what tables haven't been vacuumed at all during this week?"

I added last vacuum and last analyze (both auto and manual) dates in
the 8.2 tree.....

Uh, where? I don't see it in pg_class, pg_database, or pg_autovacuum.
Is it somewhere else?

pg_stat_*_tables

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 512-248-2683 E-Mail: ler@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893

Larry Rosenman wrote:

Alvaro Herrera wrote:

Bruce Momjian wrote:

Well, the problem is that it shows what it's *currently* doing, but it
doesn't let you know what has happened in the last day or whatever.
It can't answer "has table foo been vacuumed recently?" or "what
tables haven't been vacuumed at all during this week?"

I added last vacuum and last analyze (both auto and manual) dates in the 8.2
tree.....

Hmm... well that does address most of my issue. Doesn't tell me that
table_foo is getting vacuumed constantly, but it does tell me that it
was vacuumed recently. Might be good enough.