Thread: pgsql-server: Vacuum delay activated by default.
Log Message:
-----------
Vacuum delay activated by default.
Jan
Modified Files:
--------------
pgsql-server/src/backend/utils/misc:
guc.c (r1.227 -> r1.228)
(http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/utils/misc/guc.c.diff?r1=1.227&r2=1.228)
postgresql.conf.sample (r1.121 -> r1.122)
(http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/utils/misc/postgresql.conf.sample.diff?r1=1.121&r2=1.122)
wieck@svr1.postgresql.org (Jan Wieck) writes:
> Vacuum delay activated by default.
What? If there was consensus to do this, I missed it. If there was
even any *discussion* of doing this, I missed it.
regards, tom lane
Jan Wieck <JanWieck@Yahoo.com> writes:
> On 8/7/2004 12:47 AM, Tom Lane wrote:
>> What? If there was consensus to do this, I missed it. If there was
>> even any *discussion* of doing this, I missed it.
> How many questions about vacuum still grabbing all available bandwidth,
> vacuum slowing down the whole system, vacuum being all evil do you want
> to answer for 8.0? Over and over again we are defending reasonable
> default configuration values against gazillions of little switches, and
> this is a reasonable default that will be a relief for large databases
> and makes more or less no difference for small ones.
What basis do you have for saying that this is a reasonable default?
Does anyone else agree?
Again, it's the lack of discussion that is bothering me.
regards, tom lane
On Sat, 7 Aug 2004, Tom Lane wrote: > Jan Wieck <JanWieck@Yahoo.com> writes: >> On 8/7/2004 12:47 AM, Tom Lane wrote: >>> What? If there was consensus to do this, I missed it. If there was >>> even any *discussion* of doing this, I missed it. > >> How many questions about vacuum still grabbing all available bandwidth, >> vacuum slowing down the whole system, vacuum being all evil do you want >> to answer for 8.0? Over and over again we are defending reasonable >> default configuration values against gazillions of little switches, and >> this is a reasonable default that will be a relief for large databases >> and makes more or less no difference for small ones. > > What basis do you have for saying that this is a reasonable default? > Does anyone else agree? Just curious, but isn't this one of the key points about pg_autovacuum in the first place? So that you vacuum what needs to be vacuum'd, and not *everything* ... ? Shouldn't the answer to the 'bandwidth issue' change to 'you should install/use pg_autovacuum'? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Marc G. Fournier wrote: > On Sat, 7 Aug 2004, Tom Lane wrote: > > > Jan Wieck <JanWieck@Yahoo.com> writes: > >> On 8/7/2004 12:47 AM, Tom Lane wrote: > >>> What? If there was consensus to do this, I missed it. If there was > >>> even any *discussion* of doing this, I missed it. > > > >> How many questions about vacuum still grabbing all available bandwidth, > >> vacuum slowing down the whole system, vacuum being all evil do you want > >> to answer for 8.0? Over and over again we are defending reasonable > >> default configuration values against gazillions of little switches, and > >> this is a reasonable default that will be a relief for large databases > >> and makes more or less no difference for small ones. > > > > What basis do you have for saying that this is a reasonable default? > > Does anyone else agree? > > Just curious, but isn't this one of the key points about pg_autovacuum in > the first place? So that you vacuum what needs to be vacuum'd, and not > *everything* ... ? Shouldn't the answer to the 'bandwidth issue' change > to 'you should install/use pg_autovacuum'? We are talking about the vacuum delay feature, not pg_autovacuum. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Sat, 7 Aug 2004, Bruce Momjian wrote: > Marc G. Fournier wrote: >> On Sat, 7 Aug 2004, Tom Lane wrote: >> >>> Jan Wieck <JanWieck@Yahoo.com> writes: >>>> On 8/7/2004 12:47 AM, Tom Lane wrote: >>>>> What? If there was consensus to do this, I missed it. If there was >>>>> even any *discussion* of doing this, I missed it. >>> >>>> How many questions about vacuum still grabbing all available bandwidth, >>>> vacuum slowing down the whole system, vacuum being all evil do you want >>>> to answer for 8.0? Over and over again we are defending reasonable >>>> default configuration values against gazillions of little switches, and >>>> this is a reasonable default that will be a relief for large databases >>>> and makes more or less no difference for small ones. >>> >>> What basis do you have for saying that this is a reasonable default? >>> Does anyone else agree? >> >> Just curious, but isn't this one of the key points about pg_autovacuum in >> the first place? So that you vacuum what needs to be vacuum'd, and not >> *everything* ... ? Shouldn't the answer to the 'bandwidth issue' change >> to 'you should install/use pg_autovacuum'? > > We are talking about the vacuum delay feature, not pg_autovacuum. Right, and your point? That doesn't answer my question, only clarifies for everyone what we already know we're talking about, thank you ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> Just curious, but isn't this one of the key points about pg_autovacuum in
> the first place? So that you vacuum what needs to be vacuum'd, and not
> *everything* ... ? Shouldn't the answer to the 'bandwidth issue' change
> to 'you should install/use pg_autovacuum'?
No, not really, but I think it's much more likely that you'd want to
enable vacuum delay for autovacuum-commanded vacuums than vacuums
commanded interactively. Or, if you still prefer the old-tech way of
performing routine vacuums from a cron script, you'd probably turn on
vacuum delay in that cron script.
I think we *should* add to autovacuum a parameter to let it set
vacuum_delay for its vacuums, and maybe even default to having it on.
But I'm unconvinced we want any delay as the global default.
regards, tom lane
On 8/7/2004 12:47 AM, Tom Lane wrote: > wieck@svr1.postgresql.org (Jan Wieck) writes: >> Vacuum delay activated by default. > > What? If there was consensus to do this, I missed it. If there was > even any *discussion* of doing this, I missed it. > > regards, tom lane How many questions about vacuum still grabbing all available bandwidth, vacuum slowing down the whole system, vacuum being all evil do you want to answer for 8.0? Over and over again we are defending reasonable default configuration values against gazillions of little switches, and this is a reasonable default that will be a relief for large databases and makes more or less no difference for small ones. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: >>Just curious, but isn't this one of the key points about pg_autovacuum in >>the first place? So that you vacuum what needs to be vacuum'd, and not >>*everything* ... ? Shouldn't the answer to the 'bandwidth issue' change >>to 'you should install/use pg_autovacuum'? > > No, not really, but I think it's much more likely that you'd want to > enable vacuum delay for autovacuum-commanded vacuums than vacuums > commanded interactively. Or, if you still prefer the old-tech way of > performing routine vacuums from a cron script, you'd probably turn on > vacuum delay in that cron script. > > I think we *should* add to autovacuum a parameter to let it set > vacuum_delay for its vacuums, and maybe even default to having it on. > But I'm unconvinced we want any delay as the global default. I don't think vacuum delay should be on by default. In many use scenarios, it is undesireable. And in the ones where you'd want to use it, in my experience it requires tuning based on your actual load profile. When I was experimenting with vacuum delay a few months ago (real application, not contrived), I found that too low a setting did not lower load noticeably, and too high a setting caused vaccum to steadily fall behind and never catch up. Joe
On Sat, 7 Aug 2004, Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: >> Just curious, but isn't this one of the key points about pg_autovacuum in >> the first place? So that you vacuum what needs to be vacuum'd, and not >> *everything* ... ? Shouldn't the answer to the 'bandwidth issue' change >> to 'you should install/use pg_autovacuum'? > > No, not really, but I think it's much more likely that you'd want to > enable vacuum delay for autovacuum-commanded vacuums than vacuums > commanded interactively. Or, if you still prefer the old-tech way of > performing routine vacuums from a cron script, you'd probably turn on > vacuum delay in that cron script. > > I think we *should* add to autovacuum a parameter to let it set > vacuum_delay for its vacuums, and maybe even default to having it on. > But I'm unconvinced we want any delay as the global default. how about having it as part of the SQL? VACUUM ANALYZE DELAY; ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> how about having it as part of the SQL?
> VACUUM ANALYZE DELAY;
Offhand I don't see any advantage to doing it that way...
regards, tom lane
Jan Wieck wrote: > On 8/7/2004 12:47 AM, Tom Lane wrote: > > > wieck@svr1.postgresql.org (Jan Wieck) writes: > >> Vacuum delay activated by default. > > > > What? If there was consensus to do this, I missed it. If there was > > even any *discussion* of doing this, I missed it. > > > > regards, tom lane > > How many questions about vacuum still grabbing all available bandwidth, > vacuum slowing down the whole system, vacuum being all evil do you want > to answer for 8.0? Over and over again we are defending reasonable > default configuration values against gazillions of little switches, and > this is a reasonable default that will be a relief for large databases > and makes more or less no difference for small ones. I haven't seen tons of complaints about vacuum myself. In addition it has already been pointed out that there is no one magic value that fixes it for everyone so some tuning is going to be needed anyway. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On 8/7/2004 2:06 PM, Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: >> Just curious, but isn't this one of the key points about pg_autovacuum in >> the first place? So that you vacuum what needs to be vacuum'd, and not >> *everything* ... ? Shouldn't the answer to the 'bandwidth issue' change >> to 'you should install/use pg_autovacuum'? > > No, not really, but I think it's much more likely that you'd want to > enable vacuum delay for autovacuum-commanded vacuums than vacuums > commanded interactively. Or, if you still prefer the old-tech way of > performing routine vacuums from a cron script, you'd probably turn on > vacuum delay in that cron script. > > I think we *should* add to autovacuum a parameter to let it set > vacuum_delay for its vacuums, and maybe even default to having it on. > But I'm unconvinced we want any delay as the global default. That sounds like a good idea. But then again, based on this entire discussion and the fact that unvoluntary vacuum runs during a production servers peak time are the worst thing that can happen, I take it that it was never intended to enable pg_autovacuum by default either and that one has to enable it explicitly in the configuration, right? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck <JanWieck@Yahoo.com> writes:
> But then again, based on this entire discussion and the fact that
> unvoluntary vacuum runs during a production servers peak time are the
> worst thing that can happen, I take it that it was never intended to
> enable pg_autovacuum by default either and that one has to enable it
> explicitly in the configuration, right?
I would certainly never have voted to enable it by default in 8.0
(or in 8.1, if that's where it first hits the streets). I'd want
it to be out in the field for at least one release cycle first.
My opinion about vacuum delay is about the same --- I want to get some
field experience before it becomes default, not after.
regards, tom lane