Обсуждение: Autovacuum on by default?

Поиск
Список
Период
Сортировка

Autovacuum on by default?

От
Peter Eisentraut
Дата:
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?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Autovacuum on by default?

От
"Magnus Hagander"
Дата:
> 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


Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
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


Re: Autovacuum on by default?

От
Rod Taylor
Дата:
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.

-- 



Re: Autovacuum on by default?

От
Bruce Momjian
Дата:
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. +


Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
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.




Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
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.





Re: Autovacuum on by default?

От
Bruce Momjian
Дата:
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. +


Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
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.



Re: Autovacuum on by default?

От
Josh Berkus
Дата:
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



Re: Autovacuum on by default?

От
Bruce Momjian
Дата:
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. +


Re: Autovacuum on by default?

От
Alvaro Herrera
Дата:
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.


Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
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?




Re: Autovacuum on by default?

От
"Larry Rosenman"
Дата:
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



Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
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?


Re: Autovacuum on by default?

От
"Jim C. Nasby"
Дата:
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


Re: Autovacuum on by default?

От
Bruce Momjian
Дата:
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. +


Re: Autovacuum on by default?

От
Alvaro Herrera
Дата:
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.


Re: Autovacuum on by default?

От
"Larry Rosenman"
Дата:
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



Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
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.



Re: Autovacuum on by default?

От
Bruce Momjian
Дата:
Larry Rosenman wrote:
> 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

Oh, great.  I had forgotten about that.  Thanks, so we are OK with
monitoring autovacuum for 8.2.

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Autovacuum on by default?

От
Alvaro Herrera
Дата:
Matthew T. O'Connor wrote:
> 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?

Actually Larry just reminded us that you can use pg_stat_all_tables to
see that information.  However I'm testing it and it doesn't seem to
work for all tables ... strange.  I'll have a look.

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


Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
Jim C. Nasby wrote:
> On Thu, Aug 17, 2006 at 12:41:57PM -0400, 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.
>>     
>
> 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).
>   

Fair enough, also if we turn on the delay setting by default, it will 
help address this.

> 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).
>   

OK.

> 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?
>   
I assume you are suggesting that the base value be 0?  Well for one 
thing if the table doesn't have any rows that will result in constant 
vacuuming of that table, so it needs to be greater than 0.  For a small 
table, say 100 rows, there usually isn'tn much performance impact if the 
table if 50% dead space, so I think the base values you suggest are OK, 
but they shouldn't be 0.





Re: Autovacuum on by default?

От
Alvaro Herrera
Дата:
Matthew T. O'Connor wrote:
> Jim C. Nasby wrote:

> >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?
> >  
> I assume you are suggesting that the base value be 0?  Well for one 
> thing if the table doesn't have any rows that will result in constant 
> vacuuming of that table, so it needs to be greater than 0.  For a small 
> table, say 100 rows, there usually isn'tn much performance impact if the 
> table if 50% dead space, so I think the base values you suggest are OK, 
> but they shouldn't be 0.

Actually Tom suggested some time ago that we should get rid of the base
value completely, i.e. make it 0 forever.

A row with 0 tables would not show any activity in pgstats, so it would
not be vacuumed constantly.  Only once after it's truncated.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
Alvaro Herrera wrote:
> Matthew T. O'Connor wrote:
>   
>> I assume you are suggesting that the base value be 0?  Well for one 
>> thing if the table doesn't have any rows that will result in constant 
>> vacuuming of that table, so it needs to be greater than 0.  For a small 
>> table, say 100 rows, there usually isn'tn much performance impact if the 
>> table if 50% dead space, so I think the base values you suggest are OK, 
>> but they shouldn't be 0.
>>     
>
> Actually Tom suggested some time ago that we should get rid of the base
> value completely, i.e. make it 0 forever.
>
> A row with 0 tables would not show any activity in pgstats, so it would
> not be vacuumed constantly.  Only once after it's truncated.

OK, forgot that.  Well I put it in originally as a way to give more 
flexability to the calculation, if I want a tabled vacuumed every 100 
updates, then I can set the scaling factor to 0 and the base value to 
100, but maybe that's not really needed.  It would simplify things if we 
got rid of it.




Re: Autovacuum on by default?

От
"Jim C. Nasby"
Дата:
On Thu, Aug 17, 2006 at 01:29:57PM -0400, Matthew T. O'Connor wrote:
> 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.
Probably a better idea is to turn on autovacuum_delay instead of
vacuum_delay.

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

I did some limited testing on one clients system and 10ms seemed a good
value for their fairly decent drive array. How that would translate on a
slower machine (which is what I'm more concerned with) I don't know.
Maybe 20ms would be better?

I also discovered on that system that upping vacuum_cost_limit and
vacuum_cost_page_dirty to 300 and 30 was a better setting. I suspect
that might partly be due to vacuum_cost_page_miss being pretty high.
That number makes sense for a page that actually comes off the disk, but
with so many folks still using 10% of memory for shared_buffers I think
it should be lower (5?), since a lot of page misses will come out of the
kernel cache anyway.

Of course if we had some way to determine if a page came out of the OS
cache...
-- 
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


Re: Autovacuum on by default?

От
"Jim C. Nasby"
Дата:
On Thu, Aug 17, 2006 at 01:47:37PM -0400, Matthew T. O'Connor wrote:
> Alvaro Herrera wrote:
> >Matthew T. O'Connor wrote:
> >  
> >>I assume you are suggesting that the base value be 0?  Well for one 
> >>thing if the table doesn't have any rows that will result in constant 
> >>vacuuming of that table, so it needs to be greater than 0.  For a small 
> >>table, say 100 rows, there usually isn'tn much performance impact if the 
> >>table if 50% dead space, so I think the base values you suggest are OK, 
> >>but they shouldn't be 0.
> >>    
> >
> >Actually Tom suggested some time ago that we should get rid of the base
> >value completely, i.e. make it 0 forever.
> >
> >A row with 0 tables would not show any activity in pgstats, so it would
> >not be vacuumed constantly.  Only once after it's truncated.
> 
> OK, forgot that.  Well I put it in originally as a way to give more 
> flexability to the calculation, if I want a tabled vacuumed every 100 
> updates, then I can set the scaling factor to 0 and the base value to 
> 100, but maybe that's not really needed.  It would simplify things if we 
> got rid of it.

I think it makes more sense in the per-table settings (which I can't
remember if we actually have yet). For example, on a frequently update
table that you know should always be small, you might well want to set
it to scaling factor 0 and base of 20 or whatever.
-- 
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


Re: Autovacuum on by default?

От
Tom Lane
Дата:
Bruce Momjian <bruce@momjian.us> writes:
> 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.

How do you figure that?  The point of logging what's done is so that you
can find out what autovac has been doing, not what it's doing right now.
        regards, tom lane


Re: Autovacuum on by default?

От
Alvaro Herrera
Дата:
Alvaro Herrera wrote:
> Matthew T. O'Connor wrote:
> > 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?
> 
> Actually Larry just reminded us that you can use pg_stat_all_tables to
> see that information.  However I'm testing it and it doesn't seem to
> work for all tables ... strange.  I'll have a look.

Nevermind -- it's just that if you vacuum a table which you haven't
touched (insert, update, delete) since the last stats reset, then the
vacuum info isn't recorded because we refuse to create the pgstat entry
for the table.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: Autovacuum on by default?

От
"Jim C. Nasby"
Дата:
On Thu, Aug 17, 2006 at 03:17:07PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > 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.
> 
> How do you figure that?  The point of logging what's done is so that you
> can find out what autovac has been doing, not what it's doing right now.

Well, the big complaint has been "I have no way to know if autovac has
actually been vacuuming table X", which the pg_stat changes address. But
it would still be very handy to be able to do things like get stats from
autovac (which presumably you can do if you crank the logging verbosity
up high enough). Personally, I'd love the ability to have autovac (or
heck, ever regular vac) log statistics on a per-relation basis to a
table for later analysis. I realize there's a lot of transactionality
issues there, but I'd happily pay the cost of having autovac keep a
seperate logging connection open to be able to get this info.
-- 
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


Re: Autovacuum on by default?

От
"Larry Rosenman"
Дата:
Alvaro Herrera wrote:
> Alvaro Herrera wrote:
>> Matthew T. O'Connor wrote:
>>> 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? 
>> 
>> Actually Larry just reminded us that you can use pg_stat_all_tables
>> to see that information.  However I'm testing it and it doesn't seem
>> to work for all tables ... strange.  I'll have a look.
> 
> Nevermind -- it's just that if you vacuum a table which you haven't
> touched (insert, update, delete) since the last stats reset, then the
> vacuum info isn't recorded because we refuse to create the pgstat
> entry for the table.

Do I need to write a Doc patch for that?  It seemed consistent with other
functions of the 
same class when I did the date patch. 



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



Re: Autovacuum on by default?

От
Alvaro Herrera
Дата:
Larry Rosenman wrote:
> Alvaro Herrera wrote:
> > Alvaro Herrera wrote:
> >> Matthew T. O'Connor wrote:
> >>> 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? 
> >> 
> >> Actually Larry just reminded us that you can use pg_stat_all_tables
> >> to see that information.  However I'm testing it and it doesn't seem
> >> to work for all tables ... strange.  I'll have a look.
> > 
> > Nevermind -- it's just that if you vacuum a table which you haven't
> > touched (insert, update, delete) since the last stats reset, then the
> > vacuum info isn't recorded because we refuse to create the pgstat
> > entry for the table.
> 
> Do I need to write a Doc patch for that?  It seemed consistent with other
> functions of the 
> same class when I did the date patch. 

I'm not sure.  It just makes sense overall ... the fact that the
collector chooses to discard some info should be documented somewhere I
think (maybe it already is, I don't know), but not specifically for the
vacuum times.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: Autovacuum on by default?

От
Bruce Momjian
Дата:
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > 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.
> 
> How do you figure that?  The point of logging what's done is so that you
> can find out what autovac has been doing, not what it's doing right now.

I don't think the server logs is the place to record history autovacuum
activity.  I am not saying we might not need that functionality, but not
in the server logs, and I think others seem to agree.

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Autovacuum on by default?

От
Tom Lane
Дата:
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> How do you figure that?  The point of logging what's done is so that you
>> can find out what autovac has been doing, not what it's doing right now.

> I don't think the server logs is the place to record history autovacuum
> activity.  I am not saying we might not need that functionality, but not
> in the server logs, and I think others seem to agree.

Um, so what do you think the server log *is* for, if not a historical
activity record?  This position would certainly surprise a lot of people
who use the log that way ...
        regards, tom lane


Re: Autovacuum on by default?

От
Bruce Momjian
Дата:
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Tom Lane wrote:
> >> How do you figure that?  The point of logging what's done is so that you
> >> can find out what autovac has been doing, not what it's doing right now.
> 
> > I don't think the server logs is the place to record history autovacuum
> > activity.  I am not saying we might not need that functionality, but not
> > in the server logs, and I think others seem to agree.
> 
> Um, so what do you think the server log *is* for, if not a historical
> activity record?  This position would certainly surprise a lot of people
> who use the log that way ...

It is by default for unusual activity.  It can be for normal activity
using the proper GUC settings, but we don't have a way to control that
just for autovacuum yet, and given what we have in 8.2, I don't see a
need to add more until users say they need more.

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Autovacuum on by default?

От
Peter Eisentraut
Дата:
Bruce Momjian wrote:
> It is by default for unusual activity.  It can be for normal activity
> using the proper GUC settings, but we don't have a way to control
> that just for autovacuum yet, and given what we have in 8.2, I don't
> see a need to add more until users say they need more.

Right, the server log should tend to being empty if there is nothing 
unusual happening.  I recall that someone once complained about every 
checkpoint run making an entry; and every autovacuum run making one 
would be about the same.  Certainly, there is room for more fine-tuning 
here, but the all-important question "Has autovacuum ever run 
(recently)" can now be easily answered in 8.2, so we're good.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Autovacuum on by default?

От
Peter Eisentraut
Дата:
Am Donnerstag, 17. August 2006 18:40 schrieb Josh Berkus:
> I'm in favor of this, but do we want to turn on vacuum_delay by default
> as well?

People might complain that suddenly their vacuum runs take four times as long 
(or whatever).  Of course, if we turn on autovacuum and advocate a more or 
less hands-off vacuum policy, they won't have to care either way.  All of 
this would certainly be release-notes material.

Does anyone, for that matter, want to propose possible default parameters for 
vacuum_delay?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
Peter Eisentraut wrote:
> Am Donnerstag, 17. August 2006 18:40 schrieb Josh Berkus:
>> I'm in favor of this, but do we want to turn on vacuum_delay by default
>> as well?
> 
> People might complain that suddenly their vacuum runs take four times as long 
> (or whatever).  Of course, if we turn on autovacuum and advocate a more or 
> less hands-off vacuum policy, they won't have to care either way.  All of 
> this would certainly be release-notes material.
> 
> Does anyone, for that matter, want to propose possible default parameters for 
> vacuum_delay?

I said vacuum_delay but I should have been more specific, there are 
autovacuum GUC variables which is what we should be talking about.  This 
way manually run, or nighly run by cron vacuums are still as fast as 
they ever were.




Re: Autovacuum on by default?

От
Tom Lane
Дата:
Peter Eisentraut <peter_e@gmx.net> writes:
> Does anyone, for that matter, want to propose possible default parameters for
> vacuum_delay?

I haven't seen any sign that anyone's done any serious testing of delay
parameters, so I don't think we have the data needed to select some
defaults ...
        regards, tom lane


Re: Autovacuum on by default?

От
"Jim C. Nasby"
Дата:
Going back on-list...

On Tue, Aug 22, 2006 at 08:47:04AM -0400, Alvaro Herrera wrote:
> Jim Nasby wrote:
> > On Aug 17, 2006, at 3:19 PM, Alvaro Herrera wrote:
> > >Nevermind -- it's just that if you vacuum a table which you haven't
> > >touched (insert, update, delete) since the last stats reset, then the
> > >vacuum info isn't recorded because we refuse to create the pgstat  
> > >entry
> > >for the table.
> > 
> > Have you changed this?
> 
> No ...
> 
> > ISTM that it should go ahead and create the  pgstat entry...
> 
> What for?

While on the surface it makes sense not to have a stat entry for a table
"with no activity" (since no activity means no need to vacuum), there's
2 problems:

This doesn't exactly meet the test of 'least surprise'. If the table's
vacuumed for any reason (even manually), we should record the info.

If there's a bunch of activity on a table but stats are reset before a
vacuum is run on it and then a vacuum is run, the user will still be
left thinking that the table needs to be vacuumed.
-- 
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net


Re: Autovacuum on by default?

От
Tom Lane
Дата:
"Jim C. Nasby" <jim@nasby.net> writes:
> If there's a bunch of activity on a table but stats are reset before a
> vacuum is run on it and then a vacuum is run, the user will still be
> left thinking that the table needs to be vacuumed.

Except that autovac *won't* vacuum it if the stats have been reset.
So I'm not seeing that there's really a problem in practice.
        regards, tom lane


Re: Autovacuum on by default?

От
ITAGAKI Takahiro
Дата:
"Jim C. Nasby" <jnasby@pervasive.com> wrote:

> 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).

The default fillfactors for index btree is 90%. So if we want to avoid
spliting of the leaf pages, vacuum scale factors should be less than 0.1
in cases where tuples are only updated randomly. I think threshoulds should
be less than PCTFREEs(=1-fillfactors) except ever-increasing tables.

This is a too simplified policy, but we probably need documentation for
the linkages between autovacuum and fillfactors. 

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center




Re: Autovacuum on by default?

От
"Jim C. Nasby"
Дата:
On Tue, Aug 22, 2006 at 11:08:49AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.net> writes:
> > If there's a bunch of activity on a table but stats are reset before a
> > vacuum is run on it and then a vacuum is run, the user will still be
> > left thinking that the table needs to be vacuumed.
> 
> Except that autovac *won't* vacuum it if the stats have been reset.
> So I'm not seeing that there's really a problem in practice.

IIRC the stats also include info about regular (manual) vacuums, so the
above scenario still applies.
-- 
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


Re: Autovacuum on by default?

От
"Jim C. Nasby"
Дата:
On Wed, Aug 23, 2006 at 01:45:43PM +0900, ITAGAKI Takahiro wrote:
> 
> "Jim C. Nasby" <jnasby@pervasive.com> wrote:
> 
> > 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).
> 
> The default fillfactors for index btree is 90%. So if we want to avoid
> spliting of the leaf pages, vacuum scale factors should be less than 0.1
> in cases where tuples are only updated randomly. I think threshoulds should
> be less than PCTFREEs(=1-fillfactors) except ever-increasing tables.

Very good point, though at least for indexes the new code that tries to
reclaim space on a page before splitting it will help. Doesn't help for
the heap, though.

So maybe the default should be 0.08?
-- 
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


Re: Autovacuum on by default?

От
"Larry Rosenman"
Дата:
Jim C. Nasby wrote:
> On Tue, Aug 22, 2006 at 11:08:49AM -0400, Tom Lane wrote:
>> "Jim C. Nasby" <jim@nasby.net> writes:
>>> If there's a bunch of activity on a table but stats are reset
>>> before a vacuum is run on it and then a vacuum is run, the user
>>> will still be left thinking that the table needs to be vacuumed.
>> 
>> Except that autovac *won't* vacuum it if the stats have been reset.
>> So I'm not seeing that there's really a problem in practice.
> 
> IIRC the stats also include info about regular (manual) vacuums, so
> the above scenario still applies.

They do.  The stats patch as applied captures both autovacuum and manual
vacuum
as well as analyze (both from the Autovacuum daemon and manual).



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



Re: Autovacuum on by default?

От
Alvaro Herrera
Дата:
Larry Rosenman wrote:
> Jim C. Nasby wrote:
> > On Tue, Aug 22, 2006 at 11:08:49AM -0400, Tom Lane wrote:
> >> "Jim C. Nasby" <jim@nasby.net> writes:
> >>> If there's a bunch of activity on a table but stats are reset
> >>> before a vacuum is run on it and then a vacuum is run, the user
> >>> will still be left thinking that the table needs to be vacuumed.
> >> 
> >> Except that autovac *won't* vacuum it if the stats have been reset.
> >> So I'm not seeing that there's really a problem in practice.
> > 
> > IIRC the stats also include info about regular (manual) vacuums, so
> > the above scenario still applies.
> 
> They do.  The stats patch as applied captures both autovacuum and manual
> vacuum
> as well as analyze (both from the Autovacuum daemon and manual).

But the original point still remains: if you manually VACUUM a table
that does not have a pgstat entry, the pgstat system will drop the
"vacuum timestamp" message on the floor without recreating the entry.

I think there is a reasonable case for saying that a manual vacuum could
hint pgstat to create the entry instead.  On the other hand, if
autovacuum never vacuums a table with no pgstat entry, then you could
just create the pgstat entry in both cases and it would be the same
anyway.

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


Re: Autovacuum on by default?

От
Tom Lane
Дата:
Alvaro Herrera <alvherre@commandprompt.com> writes:
> I think there is a reasonable case for saying that a manual vacuum could
> hint pgstat to create the entry instead.

The problem with that is that a simple "VACUUM;" would force pgstat to
populate its entire hashtable.  Which more or less defeats the idea of
not wasting table space on inactive tables --- and given the way the
reporting-file mechanism works, there's definitely an incentive to not
make the table bigger than it has to be.

It wouldn't be so bad if pgstat had a mechanism for aging out unused
table entries ...
        regards, tom lane


Re: Autovacuum on by default?

От
"Jim C. Nasby"
Дата:
On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > I think there is a reasonable case for saying that a manual vacuum could
> > hint pgstat to create the entry instead.
> 
> The problem with that is that a simple "VACUUM;" would force pgstat to
> populate its entire hashtable.  Which more or less defeats the idea of
> not wasting table space on inactive tables --- and given the way the
> reporting-file mechanism works, there's definitely an incentive to not
> make the table bigger than it has to be.
> 
> It wouldn't be so bad if pgstat had a mechanism for aging out unused
> table entries ...

Maybe a good compromise would be only populating info for tables that
had dead tuples... that would eliminate any static tables, and most DBAs
should know that those tables are static.
-- 
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


Re: Autovacuum on by default?

От
Tom Lane
Дата:
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote:
>> Alvaro Herrera <alvherre@commandprompt.com> writes:
>>> I think there is a reasonable case for saying that a manual vacuum could
>>> hint pgstat to create the entry instead.
>> 
>> The problem with that is that a simple "VACUUM;" would force pgstat to
>> populate its entire hashtable.

> Maybe a good compromise would be only populating info for tables that
> had dead tuples... that would eliminate any static tables, and most DBAs
> should know that those tables are static.

Hm, that definitely seems like an idea.  Does the current pgstat message
from vacuum tell how many rows it deleted?
        regards, tom lane


Re: Autovacuum on by default?

От
Alvaro Herrera
Дата:
Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote:
> >> Alvaro Herrera <alvherre@commandprompt.com> writes:
> >>> I think there is a reasonable case for saying that a manual vacuum could
> >>> hint pgstat to create the entry instead.
> >> 
> >> The problem with that is that a simple "VACUUM;" would force pgstat to
> >> populate its entire hashtable.
> 
> > Maybe a good compromise would be only populating info for tables that
> > had dead tuples... that would eliminate any static tables, and most DBAs
> > should know that those tables are static.
> 
> Hm, that definitely seems like an idea.  Does the current pgstat message
> from vacuum tell how many rows it deleted?

Hum, no.

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


Re: Autovacuum on by default?

От
"Jim C. Nasby"
Дата:
On Thu, Aug 24, 2006 at 01:48:50PM -0400, Alvaro Herrera wrote:
> Tom Lane wrote:
> > "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > > On Thu, Aug 24, 2006 at 09:58:10AM -0400, Tom Lane wrote:
> > >> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > >>> I think there is a reasonable case for saying that a manual vacuum could
> > >>> hint pgstat to create the entry instead.
> > >> 
> > >> The problem with that is that a simple "VACUUM;" would force pgstat to
> > >> populate its entire hashtable.
> > 
> > > Maybe a good compromise would be only populating info for tables that
> > > had dead tuples... that would eliminate any static tables, and most DBAs
> > > should know that those tables are static.
> > 
> > Hm, that definitely seems like an idea.  Does the current pgstat message
> > from vacuum tell how many rows it deleted?
> 
> Hum, no.

ISTM that wouldn't be bad info to track either... how many dead tuples
the last [auto]vacuum encountered.
-- 
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


Re: Autovacuum on by default?

От
Peter Eisentraut
Дата:
Summarizing this thread, I see support for the following:

- autovacuum set to on by default in 8.2.

- stats_row_level also defaults to on.

(Perhaps stats_block_level should also default to on so it's not inconsistent, 
seeing that everything else in on, too.)

- Delayed vacuum and delayed autovacuum will stay disabled.

- Scale factor set to 0.08 (vacuum) and 0.04 (analyze) (?)  (formerly 0.4 and 
0.2)

- Leave base thresholds alone (pending further analysis that might remove them 
altogether?)

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
Peter Eisentraut wrote:
> Summarizing this thread, I see support for the following:
> 
> - autovacuum set to on by default in 8.2.

Yes.

> - stats_row_level also defaults to on.

Yes.

> (Perhaps stats_block_level should also default to on so it's not inconsistent, 
> seeing that everything else in on, too.)

I haven't followed this closely, but are all the other stats commands 
other than block_level now on by default?  In general I would vote to 
keep it off if not needed just for performance reasons, though I haven't 
measured the effect of block_level turned on.  Anyone measured this?

> - Delayed vacuum and delayed autovacuum will stay disabled.

Unfortunately.

> - Scale factor set to 0.08 (vacuum) and 0.04 (analyze) (?)  (formerly 0.4 and 
> 0.2)

That seems a big jump.  BTW, I know .08 and .04 were suggested, but I 
didn't see confirmation that it was a good idea.  I know my initial 
values were grossly over-conservative, but I am concerned about bogging 
down the server with lots of vacuums, especially since we don't have the 
delay settings on by default, nor do we have a maintenance windows yet.

> - Leave base thresholds alone (pending further analysis that might remove them 
> altogether?)

While there is talk of removing this all together, I think it was also 
agreed that as long as these values are there, they should be reduced. 
I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested.





Re: Autovacuum on by default?

От
Peter Eisentraut
Дата:
Am Freitag, 25. August 2006 17:32 schrieb Matthew T. O'Connor:
> While there is talk of removing this all together, I think it was also
> agreed that as long as these values are there, they should be reduced.
> I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested.

I'm thinking leaving them higher would offset the jump in the scale factor a 
bit.  But if the idea is to get rid of the base threshold, then that's not 
really a forward-looking strategy, I suppose.

I don't mind if we go down with the scale factor a little less and move the 
threshold down more, but at least tying the scale factor to the fill factor 
doesn't make all those numbers too random.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Autovacuum on by default?

От
Tom Lane
Дата:
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> Peter Eisentraut wrote:
>> - Leave base thresholds alone (pending further analysis that might remove them 
>> altogether?)

> While there is talk of removing this all together, I think it was also 
> agreed that as long as these values are there, they should be reduced. 
> I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested.

ISTM that if we don't want to remove the thresholds immediately,
we should make them default to zero for a release or two and see how
well it works.

At the moment I can't find the thread that discussed removing them,
but IIRC there were some good arguments why the thresholds should always
be zero.
        regards, tom lane


Re: Autovacuum on by default?

От
"Matthew T. O'Connor"
Дата:
Tom Lane wrote:
> "Matthew T. O'Connor" <matthew@zeut.net> writes:
>   
>> While there is talk of removing this all together, I think it was also 
>> agreed that as long as these values are there, they should be reduced. 
>> I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested.
>>     
>
> ISTM that if we don't want to remove the thresholds immediately,
> we should make them default to zero for a release or two and see how
> well it works.
>
> At the moment I can't find the thread that discussed removing them,
> but IIRC there were some good arguments why the thresholds should always
> be zero.

I don't have any significant objection to removing them, it just seemed 
to me that we are late in release cycle and that might be more than we 
want to do at the moment.  If others think it's OK, then it's OK with me.



[Open Item] Re: Autovacuum on by default?

От
Alvaro Herrera
Дата:
Tom Lane wrote:
> "Matthew T. O'Connor" <matthew@zeut.net> writes:
> > Peter Eisentraut wrote:
> >> - Leave base thresholds alone (pending further analysis that might remove them 
> >> altogether?)
> 
> > While there is talk of removing this all together, I think it was also 
> > agreed that as long as these values are there, they should be reduced. 
> > I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested.
> 
> ISTM that if we don't want to remove the thresholds immediately,
> we should make them default to zero for a release or two and see how
> well it works.
> 
> At the moment I can't find the thread that discussed removing them,
> but IIRC there were some good arguments why the thresholds should always
> be zero.

I can't find it either, but I think the bug reported here is related:

http://archives.postgresql.org/pgsql-general/2006-06/thrd2.php#00951

On the other hand, I don't think we completely resolved this, so I
proposed this be added to the "Open Items" list.

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


Re: [Open Item] Re: Autovacuum on by default?

От
"Jim C. Nasby"
Дата:
On Fri, Aug 25, 2006 at 12:16:33PM -0400, Alvaro Herrera wrote:
> Tom Lane wrote:
> > "Matthew T. O'Connor" <matthew@zeut.net> writes:
> > > Peter Eisentraut wrote:
> > >> - Leave base thresholds alone (pending further analysis that might remove them 
> > >> altogether?)
> > 
> > > While there is talk of removing this all together, I think it was also 
> > > agreed that as long as these values are there, they should be reduced. 
> > > I think the defaults in 8.1 are 1000/500, I think 200/100 was suggested.
> > 
> > ISTM that if we don't want to remove the thresholds immediately,
> > we should make them default to zero for a release or two and see how
> > well it works.
> > 
> > At the moment I can't find the thread that discussed removing them,
> > but IIRC there were some good arguments why the thresholds should always
> > be zero.
> 
> I can't find it either, but I think the bug reported here is related:
> 
> http://archives.postgresql.org/pgsql-general/2006-06/thrd2.php#00951
> 
> On the other hand, I don't think we completely resolved this, so I
> proposed this be added to the "Open Items" list.

Yeah, I think there's reasons we can't go to zero. 200/100 or even 20/10
would probably be a good compromise.

I agree that droping to 0.08 might be a bit much, but it would be good
if we started recommending that value to folks to see how well it works.

I thought we had agreed it would be a good idea to turn autovac_delay
on? I know there was question as to what a good value would be, but
5-10ms seems pretty reasonable. I think it'd also be good to up the cost
threshold and the dirty_page cost, though I don't have much data to back
that up (I did testing at one customer on a drive array and found 300
and 30 were good values).

If we've got command stats turned on by default now, I'll have a hard
time buying performance as any reason to turn the others off. I think we
should turn them all on and let those who are trying to eek the last few
percent of performance out of a system turn them off.
-- 
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


Re: [Open Item] Re: Autovacuum on by default?

От
Peter Eisentraut
Дата:
Jim C. Nasby wrote:
> I thought we had agreed it would be a good idea to turn autovac_delay
> on?

We had not, because there was no experience available about where to put 
the default numbers.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Autovacuum on by default?

От
"Guillaume Smet"
Дата:
On 8/25/06, Peter Eisentraut <peter_e@gmx.net> wrote:
> Summarizing this thread, I see support for the following:
> - autovacuum set to on by default in 8.2.
> - stats_row_level also defaults to on.
> - Delayed vacuum and delayed autovacuum will stay disabled.
> - Scale factor set to 0.08 (vacuum) and 0.04 (analyze) (?)  (formerly 0.4 and
> 0.2)

IMHO, we shoud also change superuser_reserved_connections from 2 to 3
because one of the connections will be used by autovacuum.

--
Guillaume


Re: [Open Item] Re: Autovacuum on by default?

От
Christopher Browne
Дата:
Centuries ago, Nostradamus foresaw when peter_e@gmx.net (Peter Eisentraut) would write:
> Jim C. Nasby wrote:
>> I thought we had agreed it would be a good idea to turn autovac_delay
>> on?
>
> We had not, because there was no experience available about where to put 
> the default numbers.

I would also not because for Very Large Tables, the delay may make
vacuums run really inordinately long.  And that becomes an "evil big
long-running transaction" to worsen things.
-- 
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://cbbrowne.com/info/internet.html
Signs of  a Klingon Programmer - 2.  "Specifications are  for the weak
and timid!"


Re: Autovacuum on by default?

От
Peter Eisentraut
Дата:
Guillaume Smet wrote:
> IMHO, we shoud also change superuser_reserved_connections from 2 to 3
> because one of the connections will be used by autovacuum.

Yes, good point.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Autovacuum on by default?

От
Peter Eisentraut
Дата:
Matthew T. O'Connor wrote:
> That seems a big jump.  BTW, I know .08 and .04 were suggested, but I
> didn't see confirmation that it was a good idea.  I know my initial
> values were grossly over-conservative, but I am concerned about
> bogging down the server with lots of vacuums, especially since we
> don't have the delay settings on by default, nor do we have a
> maintenance windows yet.

OK, it seems that while everyone wants autovacuum be more aggressive by 
default, no one has any good data to support one setting or another.  I 
so I suggest that we just cut scale factor and base threshold in half 
right now (so it'd be 0.2, 0.1, 500, 250) and see about a 
better-researched setting for the next release.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: [Open Item] Re: Autovacuum on by default?

От
Tom Lane
Дата:
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> If we've got command stats turned on by default now, I'll have a hard
> time buying performance as any reason to turn the others off.

That's a mistaken argument, because the reason stats_command_string
is now on is that it was reimplemented in a way that has basically
nothing to do with the stats subsystem ...
        regards, tom lane


Re: Autovacuum on by default?

От
Josh Berkus
Дата:
Peter,

> OK, it seems that while everyone wants autovacuum be more aggressive by
> default, no one has any good data to support one setting or another.  I
> so I suggest that we just cut scale factor and base threshold in half
> right now (so it'd be 0.2, 0.1, 500, 250) and see about a
> better-researched setting for the next release.

I'd recommend actually 0.4 and 0.2 and 200 and 100.  I think that 20% and 10% 
are too aggresive.  0.4 and 0.2 are what I've been using in production on 
many machines.  On the other hand, I think that the thresholds are much too 
high -- that means that many small tables may never get vacuumed at all, even 
after 100% row replacement.

I'll admit, however, that I don't have test data to support this.  
Unfortunately we never got to good Autovac tests on the STP before it went 
down.

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco


Re: Autovacuum on by default?

От
Josh Berkus
Дата:
Folks,

> all, even after 100% row replacement.

Er, "even after 1000% row replacement."

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco


Re: Autovacuum on by default?

От
"Jim C. Nasby"
Дата:
On Tue, Aug 29, 2006 at 09:23:53PM -0700, Josh Berkus wrote:
> Peter,
> 
> > OK, it seems that while everyone wants autovacuum be more aggressive by
> > default, no one has any good data to support one setting or another.  I
> > so I suggest that we just cut scale factor and base threshold in half
> > right now (so it'd be 0.2, 0.1, 500, 250) and see about a
> > better-researched setting for the next release.
> 
> I'd recommend actually 0.4 and 0.2 and 200 and 100.  I think that 20% and 10% 
> are too aggresive.  0.4 and 0.2 are what I've been using in production on 
> many machines.  On the other hand, I think that the thresholds are much too 
> high -- that means that many small tables may never get vacuumed at all, even 
> after 100% row replacement.
Do you think .2 and .1 (or even .08 and .04, as suggested by the default
page fill percentage) are too aggressive *on small systems*? IMO, these
defaults are meant more for less experienced folks, which are much more
likely to be running a smaller database than a large one.

FWIW, I've been using .2 and .1 (as well as cutting the thresholds
down; typically to between 200 and 400 and 100 and 200) without issue,
though I did tweak the delay costs at one customer.

> I'll admit, however, that I don't have test data to support this.  
> Unfortunately we never got to good Autovac tests on the STP before it went 
> down.
-- 
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


Re: Autovacuum on by default?

От
Bruce Momjian
Дата:
Peter Eisentraut wrote:
> Guillaume Smet wrote:
> > IMHO, we shoud also change superuser_reserved_connections from 2 to 3
> > because one of the connections will be used by autovacuum.
> 
> Yes, good point.

Done, because most people will turn autovacuum on, even if it isn't on
by default.

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Autovacuum on by default?

От
Andreas Pflug
Дата:
Bruce Momjian wrote:
>
> Done, because most people will turn autovacuum on, even if it isn't on
> by default.
>   
I wonder how many distros will turn on autovacuum as well, making it the
de-facto standard anyway.

Regards,



Re: Autovacuum on by default?

От
Bruce Momjian
Дата:
Andreas Pflug wrote:
> Bruce Momjian wrote:
> >
> > Done, because most people will turn autovacuum on, even if it isn't on
> > by default.
> >   
> I wonder how many distros will turn on autovacuum as well, making it the
> de-facto standard anyway.

Win32 already does.

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +