Re: 7.4RC1 planned for Monday

Barring the discovery of any major new bugs, the core committee has
agreed to release 7.4RC1 on Monday. Time to get those last-minute
fixes in place.

I currently have the following issues on my radar screen:

Force GRANT/REVOKE by superuser to act as though owner of object?
Change libpgtcl to use ByteArray for lo_read/lo_write (ljb's patch)
Add option for parallelism limit in make check (Andrew Dunstan's patch)
rule/foreign key interaction reported by Michele Bendazzoli

plus various minor documentation fixes. Not much there...

BTW, 7.4 final will be as early as the following Monday if no problems
are detected. We will decide on a week-to-week basis whether we are
ready to release final or not.

regards, tom lane

--On Thursday, October 30, 2003 18:43:25 -0500 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:

> Barring the discovery of any major new bugs, the core committee has
> agreed to release 7.4RC1 on Monday. Time to get those last-minute
> fixes in place.
>
> I currently have the following issues on my radar screen:
>
> Force GRANT/REVOKE by superuser to act as though owner of object?
> Change libpgtcl to use ByteArray for lo_read/lo_write (ljb's patch)
> Add option for parallelism limit in make check (Andrew Dunstan's patch)
> rule/foreign key interaction reported by Michele Bendazzoli
>
> plus various minor documentation fixes. Not much there...
>
> BTW, 7.4 final will be as early as the following Monday if no problems
> are detected. We will decide on a week-to-week basis whether we are
> ready to release final or not.
Is there any possibility of getting some help to detect the SCO UnixWare
7.1.3UP3 compiler to allow us to conditionally remove the -Kno_host for
the fixed compiler into 7.4?

It would be a good thing. (UP3 came out yesterday).

LER

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Hello,

I know I will probably be flamed into oblivion for this but I would
like to make a suggestion about
the upcoming release.

What if we delayed until the end of the year?

The two reasons that I can come up with are:

1. The irritating (but work around capable) bigint index issue.
2. More importantly the recent potential discovery by Jan on vacuum.

I have several high end users that are really beating their heads
against the wall with even lazy vacuum
because of how brutal it can be on the system. If we could make vacuum a
little less harsh it could be
a large boon.

If I am totally off my rocker, so be it but if we were to hit the
streets with 7.4 and a vacuum that was 70% (ex)
less brutal on the machine it would be a pretty significant statement.

Yes all the other fixes are great and cool.

Sincerely,

Joshua D. Drake

Tom Lane wrote:

>Barring the discovery of any major new bugs, the core committee has
>agreed to release 7.4RC1 on Monday. Time to get those last-minute
>fixes in place.
>
>I currently have the following issues on my radar screen:
>
>Force GRANT/REVOKE by superuser to act as though owner of object?
>Change libpgtcl to use ByteArray for lo_read/lo_write (ljb's patch)
>Add option for parallelism limit in make check (Andrew Dunstan's patch)
>rule/foreign key interaction reported by Michele Bendazzoli
>
>plus various minor documentation fixes. Not much there...
>
>BTW, 7.4 final will be as early as the following Monday if no problems
>are detected. We will decide on a week-to-week basis whether we are
>ready to release final or not.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org

On Thu, 30 Oct 2003, Joshua D. Drake wrote:

> Hello,
>
> I know I will probably be flamed into oblivion for this but I would
> like to make a suggestion about
> the upcoming release.
>
> What if we delayed until the end of the year?
>
> The two reasons that I can come up with are:
>
> 1. The irritating (but work around capable) bigint index issue.
> 2. More importantly the recent potential discovery by Jan on vacuum.
>
> I have several high end users that are really beating their heads
> against the wall with even lazy vacuum
> because of how brutal it can be on the system. If we could make vacuum a
> little less harsh it could be
> a large boon.

Are these folks for whom the autovacuum daemon provides no relief?

I think it's too late in the release cycle to put everything on hold,
especially with no known fix in sight (for bigint at least.)

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> What if we delayed until the end of the year?

Nope, not for those items. There is still some thought of a very short
release cycle (a few months) for 7.5, and we could possibly address the
vacuum issue in that timeframe, if the recent ideas about it prove out.
But there is no consensus on how to fix the integer-index issues, and
I'm not willing to hold 7.4 (or even 7.5) hostage to finding one.

Sooner or later you have to say "this release is done, let's ship it".
It's way too late to go back into invention mode for 7.4.

regards, tom lane

>Sooner or later you have to say "this release is done, let's ship it".
>It's way too late to go back into invention mode for 7.4.
>
>
>
I agree with the argument. It is just that the Vacuum one... well is
very tempting.
On the 7.5 cycle though... I thought 7.5 was basically for win32?

Sincerely,

Joshua Drake

> regards, tom lane
>
>

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org

"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> writes:
> Are these folks for whom the autovacuum daemon provides no relief?

If I understood correctly, Josh was complaining about VACUUM sucking too
much of his disk bandwidth. autovacuum wouldn't help that --- in fact
would likely make it worse, since a cron-driven vacuum script can at
least be scheduled for low-load times of day. autovacuum is likely to
kick in at the least convenient times.

However, we have at this point got only speculative solutions to the
too-much-bandwidth problem. If we had something ready to go today,
I'd be as willing as the next guy to cram it into 7.4. I'm not willing
to go back into development mode for 7.4, though.

regards, tom lane

If I understood correctly, Josh was complaining about VACUUM sucking too

>much of his disk bandwidth. autovacuum wouldn't help that --- in fact
>would likely make it worse, since a cron-driven vacuum script can at
>least be scheduled for low-load times of day. autovacuum is likely to
>kick in at the least convenient times.
>
>
>
Exactly!

>However, we have at this point got only speculative solutions to the
>too-much-bandwidth problem. If we had something ready to go today,
>I'd be as willing as the next guy to cram it into 7.4. I'm not willing
>to go back into development mode for 7.4, though.
>
> regards, tom lane
>
>

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org

> Nope, not for those items. There is still some thought of a very short
> release cycle (a few months) for 7.5, and we could possibly address the
> vacuum issue in that timeframe, if the recent ideas about it prove out.
> But there is no consensus on how to fix the integer-index issues, and
> I'm not willing to hold 7.4 (or even 7.5) hostage to finding one.

The idea of very short release cycle for 7.5 is interesting. What is
the core's decision for point-in-time-recovery? Maybe the decision is
7.5 does not include point-in-time-recovery?
--
Tatsuo Ishii

Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> writes:
> The idea of very short release cycle for 7.5 is interesting. What is
> the core's decision for point-in-time-recovery? Maybe the decision is
> 7.5 does not include point-in-time-recovery?

We'd like to have it in 7.5. Whether it will get done in time is
impossible to predict at this point...

regards, tom lane

On Thu, 30 Oct 2003, Tom Lane wrote:

> rule/foreign key interaction reported by Michele Bendazzoli

In the interests of disclosure, if the case in question for the rule
fails, almost certainly deferred fk constraints will as well which I
think makes this a must fix for 7.4 and is another push to getting a
7.3.5.

On Thu, 30 Oct 2003, Joshua D. Drake wrote:

>
> >Sooner or later you have to say "this release is done, let's ship it".
> >It's way too late to go back into invention mode for 7.4.
> >
> >
> >
> I agree with the argument. It is just that the Vacuum one... well is
> very tempting.
> On the 7.5 cycle though... I thought 7.5 was basically for win32?

Nope, it is *hoped* that v7.5 will include win32 ...

On Fri, 31 Oct 2003, Tatsuo Ishii wrote:

> > Nope, not for those items. There is still some thought of a very short
> > release cycle (a few months) for 7.5, and we could possibly address the
> > vacuum issue in that timeframe, if the recent ideas about it prove out.
> > But there is no consensus on how to fix the integer-index issues, and
> > I'm not willing to hold 7.4 (or even 7.5) hostage to finding one.
>
> The idea of very short release cycle for 7.5 is interesting. What is
> the core's decision for point-in-time-recovery? Maybe the decision is
> 7.5 does not include point-in-time-recovery?

Does anyone have anything ready to put into CVS as soon as we start v7.5,
or shortly afterwards?

Marc G. Fournier wrote:
>
>
> On Fri, 31 Oct 2003, Tatsuo Ishii wrote:
>
> > > Nope, not for those items. There is still some thought of a very short
> > > release cycle (a few months) for 7.5, and we could possibly address the
> > > vacuum issue in that timeframe, if the recent ideas about it prove out.
> > > But there is no consensus on how to fix the integer-index issues, and
> > > I'm not willing to hold 7.4 (or even 7.5) hostage to finding one.
> >
> > The idea of very short release cycle for 7.5 is interesting. What is
> > the core's decision for point-in-time-recovery? Maybe the decision is
> > 7.5 does not include point-in-time-recovery?
>
> Does anyone have anything ready to put into CVS as soon as we start v7.5,
> or shortly afterwards?

Well, I have patches2:

http:/momjian.postgresql.org/cgi-bin/pgpatches2

There are a few things ready for application in there, plus other items
to start work on.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

I meant related to PITR? :)

On Thu, 30 Oct 2003, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> >
> >
> > On Fri, 31 Oct 2003, Tatsuo Ishii wrote:
> >
> > > > Nope, not for those items. There is still some thought of a very short
> > > > release cycle (a few months) for 7.5, and we could possibly address the
> > > > vacuum issue in that timeframe, if the recent ideas about it prove out.
> > > > But there is no consensus on how to fix the integer-index issues, and
> > > > I'm not willing to hold 7.4 (or even 7.5) hostage to finding one.
> > >
> > > The idea of very short release cycle for 7.5 is interesting. What is
> > > the core's decision for point-in-time-recovery? Maybe the decision is
> > > 7.5 does not include point-in-time-recovery?
> >
> > Does anyone have anything ready to put into CVS as soon as we start v7.5,
> > or shortly afterwards?
>
> Well, I have patches2:
>
> http:/momjian.postgresql.org/cgi-bin/pgpatches2
>
> There are a few things ready for application in there, plus other items
> to start work on.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>

> Does anyone have anything ready to put into CVS as soon as we start v7.5,
> or shortly afterwards?

Check bruce's 7.5 patches list (can't remember the address though :) )

I have this COMMENT ON thing ready to go, except for this darn taking in
unsigned ints from the parser business that I haven't had any suggests
for :P

Chris

Oh, sorry, only read your part --- I have not heard anything about PITR
from Patrick. I talked to him about a month ago and he hadn't made much
headway.

---------------------------------------------------------------------------

Marc G. Fournier wrote:
>
>
> I meant related to PITR? :)
>
> On Thu, 30 Oct 2003, Bruce Momjian wrote:
>
> > Marc G. Fournier wrote:
> > >
> > >
> > > On Fri, 31 Oct 2003, Tatsuo Ishii wrote:
> > >
> > > > > Nope, not for those items. There is still some thought of a very short
> > > > > release cycle (a few months) for 7.5, and we could possibly address the
> > > > > vacuum issue in that timeframe, if the recent ideas about it prove out.
> > > > > But there is no consensus on how to fix the integer-index issues, and
> > > > > I'm not willing to hold 7.4 (or even 7.5) hostage to finding one.
> > > >
> > > > The idea of very short release cycle for 7.5 is interesting. What is
> > > > the core's decision for point-in-time-recovery? Maybe the decision is
> > > > 7.5 does not include point-in-time-recovery?
> > >
> > > Does anyone have anything ready to put into CVS as soon as we start v7.5,
> > > or shortly afterwards?
> >
> > Well, I have patches2:
> >
> > http:/momjian.postgresql.org/cgi-bin/pgpatches2
> >
> > There are a few things ready for application in there, plus other items
> > to start work on.
> >
> > --
> > Bruce Momjian | http://candle.pha.pa.us
> > pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> > + If your life is a hard drive, | 13 Roberts Road
> > + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> >
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

"Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> Does anyone have anything ready to put into CVS as soon as we start v7.5,
> or shortly afterwards?

That brings up another question, which is when to create the
REL7_4_STABLE branch in CVS. Offhand I think it would be good to do it
when we make RC1; any thoughts?

regards, tom lane

The world rejoiced as jd(at)commandprompt(dot)com ("Joshua D. Drake") wrote:
> 2. More importantly the recent potential discovery by Jan on vacuum.
>
> I have several high end users that are really beating their heads
> against the wall with even lazy vacuum because of how brutal it can
> be on the system. If we could make vacuum a little less harsh it
> could be a large boon.

Boon it would be, I agree.

But from what I can tell, Jan has only just gotten to the point of
being able to replicate the behaviour, with some initial attempts to
address it. He only mentioned it a few days ago. That doesn't
establish that there is a comprehensive answer that's ready to deploy.
Perhaps there will be something next week, but it may very well take
longer.

We have been living with the current conditions for several versions;
if it is tempting enough, perhaps it will argue for a quick 7.4.1.
Indeed, since the functionality has affected various versions, it is
not unthinkable that a solution might even be amenable to backporting.

But there is a point in time at which to say, "Shoot the engineers,
and release the product." :-)

I rather think it would be a risky endeavour to hold things off on the
_possibility_ that something might happen soon on this, particularly
when this was not an expected enhancement.

I'm certainly not arguing against the improvement; in separate
non-news, I'm still lobbying for my suggestion, of a "VACUUM CACHE",
which would go after the 'low hanging fruit' of going after pages that
are currently in memory. No going after whole tables; just the bits
that require no I/O (save for indexes) because they're already known
to be in memory.
--
http://www3.sympatico.ca/cbbrowne/oses.html
Rules of the Evil Overlord #121. "If I come into possession of an
artifact which can only be used by the pure of heart, I will not
attempt to use it regardless." <http://www.eviloverlord.com/>

Tatsuo Ishii wrote:
> > Nope, not for those items. There is still some thought of a very short
> > release cycle (a few months) for 7.5, and we could possibly address the
> > vacuum issue in that timeframe, if the recent ideas about it prove out.
> > But there is no consensus on how to fix the integer-index issues, and
> > I'm not willing to hold 7.4 (or even 7.5) hostage to finding one.
>
> The idea of very short release cycle for 7.5 is interesting. What is
> the core's decision for point-in-time-recovery? Maybe the decision is
> 7.5 does not include point-in-time-recovery?

I believe we were thinking PITR or Win32, or both could trigger a short
7.5 release cycle. However, it doesn't seem either is ready.

If we do a short cycle, will we have enough features to justify a
release? We could try to get PITR and Win32 done by January 1 and see
if that can happen.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Joshua D. Drake wrote:

>>Sooner or later you have to say "this release is done, let's ship it".
>>It's way too late to go back into invention mode for 7.4.
>>
>>
>>
> I agree with the argument. It is just that the Vacuum one... well is
> very tempting.

Since improving the buffer cache policy will not change any "visible"
functionality other than performance ... maybe you want to convince some
people that if we find a substantial improvement for the cache policy
soon to put it into a 7.4.x release.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> Since improving the buffer cache policy will not change any "visible"
> functionality other than performance ... maybe you want to convince some
> people that if we find a substantial improvement for the cache policy
> soon to put it into a 7.4.x release.

It's way premature to argue about this when we have no patch in hand
to consider.

regards, tom lane

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Thu, 30 Oct 2003, Tom Lane wrote:
>> rule/foreign key interaction reported by Michele Bendazzoli

> In the interests of disclosure, if the case in question for the rule
> fails, almost certainly deferred fk constraints will as well which I
> think makes this a must fix for 7.4 and is another push to getting a
> 7.3.5.

Hm, does Jan's just-committed fix address the concern you had?

regards, tom lane

On Thu, 30 Oct 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > On Thu, 30 Oct 2003, Tom Lane wrote:
> >> rule/foreign key interaction reported by Michele Bendazzoli
>
> > In the interests of disclosure, if the case in question for the rule
> > fails, almost certainly deferred fk constraints will as well which I
> > think makes this a must fix for 7.4 and is another push to getting a
> > 7.3.5.
>
> Hm, does Jan's just-committed fix address the concern you had?

Head now passes the case I'd thought of:

create table ta1(a int primary key);
create table ta2(a int references ta1 initially deferred);
begin;
insert into ta2 values (3);
update ta2 set a=3 where a=3;
-- should error, but might not if the update isn't checked
end;

I'm thinking that this is another test that probably belongs in
the foreign key regression. Does anyone object to me sending a
patch to add this and a couple of related cases?

Christopher Kings-Lynne wrote:
>
> > Does anyone have anything ready to put into CVS as soon as we start v7.5,
> > or shortly afterwards?
>
> Check bruce's 7.5 patches list (can't remember the address though :) )
>
> I have this COMMENT ON thing ready to go, except for this darn taking in
> unsigned ints from the parser business that I haven't had any suggests
> for :P

URL is:

http:/momjian.postgresql.org/cgi-bin/pgpatches2

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Stephan Szabo wrote:
> On Thu, 30 Oct 2003, Tom Lane wrote:
>
>> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
>> > On Thu, 30 Oct 2003, Tom Lane wrote:
>> >> rule/foreign key interaction reported by Michele Bendazzoli
>>
>> > In the interests of disclosure, if the case in question for the rule
>> > fails, almost certainly deferred fk constraints will as well which I
>> > think makes this a must fix for 7.4 and is another push to getting a
>> > 7.3.5.
>>
>> Hm, does Jan's just-committed fix address the concern you had?
>
> Head now passes the case I'd thought of:
>
> create table ta1(a int primary key);
> create table ta2(a int references ta1 initially deferred);
> begin;
> insert into ta2 values (3);
> update ta2 set a=3 where a=3;
> -- should error, but might not if the update isn't checked
> end;

That is basically the same what happened due to Michele's rule.
Deferring of the constraint was done there implicitly since both queries
resulted from the same statement through rule expansion and the update
touched the just inserted row. HEAD and REL7_3_STABLE are safe against
this now.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

On Fri, 31 Oct 2003, Jan Wieck wrote:

> Stephan Szabo wrote:
> > On Thu, 30 Oct 2003, Tom Lane wrote:
> >
> >> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> >> > On Thu, 30 Oct 2003, Tom Lane wrote:
> >> >> rule/foreign key interaction reported by Michele Bendazzoli
> >>
> >> > In the interests of disclosure, if the case in question for the rule
> >> > fails, almost certainly deferred fk constraints will as well which I
> >> > think makes this a must fix for 7.4 and is another push to getting a
> >> > 7.3.5.
> >>
> >> Hm, does Jan's just-committed fix address the concern you had?
> >
> > Head now passes the case I'd thought of:
> >
> > create table ta1(a int primary key);
> > create table ta2(a int references ta1 initially deferred);
> > begin;
> > insert into ta2 values (3);
> > update ta2 set a=3 where a=3;
> > -- should error, but might not if the update isn't checked
> > end;
>
> That is basically the same what happened due to Michele's rule.
> Deferring of the constraint was done there implicitly since both queries
> resulted from the same statement through rule expansion and the update
> touched the just inserted row. HEAD and REL7_3_STABLE are safe against
> this now.

Yeah. I was worried that it might not carry as much weight especially
towards 7.3 if it was thought to be an odd rule/key interaction rather
than something that can happen very simply with a deferred constraint.

On Thu, 30 Oct 2003, Joshua D. Drake wrote:

> If I understood correctly, Josh was complaining about VACUUM sucking too
>
> >much of his disk bandwidth. autovacuum wouldn't help that --- in fact
> >would likely make it worse, since a cron-driven vacuum script can at
> >least be scheduled for low-load times of day. autovacuum is likely to
> >kick in at the least convenient times.
> >
> >
> >
> Exactly!

Wait a minute, I thought the problem was that vacuums were happening too
far apart, therefore taking too long, and may have been full, no?

If the autovacuum daemon causes a lazy vacuum to run on only the busiest
(i.e. most updated) tables, then it is likely to only take a few minutes
to run, instead of hours, plus you can try to keep things steady state.
I.e. no more than x% or so dead tuples in a table at any given time, and
they all get reused by fsm / lazy vacuum.

So, have you TESTED the autovacuum daemon with your load, and set it to
run every 5 minutes? Keep in mind, it won't actually vacuum every table
every 5 minutes, it'll just check the stats to see which ones have updated
a fair bit and vacuum those, and they're lazy vacuums. I've found it to
be a win. If you haven't tested it and dismissed it out of hand, then you
should really give it a try to see if it can be configured to provide good
performance and behavior.

scott(dot)marlowe(at)ihs(dot)com ("scott.marlowe") writes:
> On Thu, 30 Oct 2003, Joshua D. Drake wrote:
>> If I understood correctly, Josh was complaining about VACUUM sucking too
>> >much of his disk bandwidth. autovacuum wouldn't help that --- in fact
>> >would likely make it worse, since a cron-driven vacuum script can at
>> >least be scheduled for low-load times of day. autovacuum is likely to
>> >kick in at the least convenient times.

>> Exactly!

> Wait a minute, I thought the problem was that vacuums were happening
> too far apart, therefore taking too long, and may have been full,
> no?

No, that is a different issue.

> If the autovacuum daemon causes a lazy vacuum to run on only the
> busiest (i.e. most updated) tables, then it is likely to only take a
> few minutes to run, instead of hours, plus you can try to keep
> things steady state. I.e. no more than x% or so dead tuples in a
> table at any given time, and they all get reused by fsm / lazy
> vacuum.

That is fine, for a system that isn't already "pretty much pegged"
with transaction load.

The "disk bandwidth" problem occurs when the system is already so busy
that doing a VACUUM on a big table adds a huge I/O load, killing
cache, and slowing the other activity.

> So, have you TESTED the autovacuum daemon with your load, and set it
> to run every 5 minutes? Keep in mind, it won't actually vacuum
> every table every 5 minutes, it'll just check the stats to see which
> ones have updated a fair bit and vacuum those, and they're lazy
> vacuums. I've found it to be a win. If you haven't tested it and
> dismissed it out of hand, then you should really give it a try to
> see if it can be configured to provide good performance and
> behavior.

If the I/O bus is saturated, and you are doing a lot of updates to big
tables, then the vacuums _are_ "performance killers."

The result of running pg_autovacuum on those tables would be that
there would be a near-continuous system slowdown. Not a win. Two
things are prime causes for this:

1. VACUUM rips through the page cache, loading the pages of tables
being vacuumed, and throwing away other data being frequently
accessed.

2. VACUUM has to compete with other processing for I/O.

Neither of those factors can be alleviated by vacuuming more often.

Jan has seen this phenomenon; I have seen this phenomenon; I have no
reason to think that Jason is not describing the very same phenomenon.

pg_autovacuum is well and useful, and I hesitate to try to count how
many systems I have installed it on. Probably a dozen. I have added
about as many patches to it as has Matthew O'Connor; I have a fair
idea of what it does. It is a godsend in test systems or low traffic
environments, by virtue of cutting down on the need to manually do
vacuums or to script up cron jobs.

It's exactly what is needed to make PostgreSQL usable in the long term
for hosting small web apps, or to make PostgreSQL work well as a host
for desktop applications. I'd like to see GnuCash use PostgreSQL by
default, instead of its custom XML data format, and pg_autovacuum
would be part of what would make that mix work.

But it isn't a magical solution to all ills, and the scenarios that
Jan Wieck and Jason Drake have been describing represent the
pathological cases where pg_autovacuum can cause performance problems
of its own.
--
output = reverse("ofni.smrytrebil" "@" "enworbbc")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

On Thu, 2003-10-30 at 23:13, Bruce Momjian wrote:
> If we do a short cycle, will we have enough features to justify a
> release? We could try to get PITR and Win32 done by January 1 and see
> if that can happen.

It's worth noting that we've thought about doing "quick" major releases
in the past, without much success: originally, 7.4 was going to be
"Win32 + PITR, released in a few months", and look how that turned out
:-)

Since the cost of migrating to a new major release is more-or-less
constant (you need a complete initdb+reload whether the release took 3
weeks or 3 years), I'm still not in favour of a short release cycle. But
in any case, the whole debate is somewhat academic unless someone does
the work to get PITR and Win32 done very quickly.

-Neil

----- Original Message -----
From: "Neil Conway" <neilc(at)samurai(dot)com>
To: "Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: "Tatsuo Ishii" <t-ishii(at)sra(dot)co(dot)jp>; "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>;
"Joshua Drake" <jd(at)commandprompt(dot)com>; "PostgreSQL Hackers"
<pgsql-hackers(at)postgresql(dot)org>
Sent: Friday, October 31, 2003 6:27 PM
Subject: Re: [HACKERS] 7.4RC1 planned for Monday

> On Thu, 2003-10-30 at 23:13, Bruce Momjian wrote:
> > If we do a short cycle, will we have enough features to justify a
> > release? We could try to get PITR and Win32 done by January 1 and see
> > if that can happen.
>
> It's worth noting that we've thought about doing "quick" major releases
> in the past, without much success: originally, 7.4 was going to be
> "Win32 + PITR, released in a few months", and look how that turned out
> :-)
>
> Since the cost of migrating to a new major release is more-or-less
> constant (you need a complete initdb+reload whether the release took 3
> weeks or 3 years), I'm still not in favour of a short release cycle. But
> in any case, the whole debate is somewhat academic unless someone does
> the work to get PITR and Win32 done very quickly.
>

You don't have to upgrade to every new release.

Maybe win32 needs to be done against the 7.4 codebase whenever that is
branched, and could be released out of cycle.

PITR doesn't seem to be going anywhere very fast from the messages I've seen
here.

cheers

andrew