Thread: max_prepared_transactions default ... why 5?

max_prepared_transactions default ... why 5?

From
Josh Berkus
Date:
Folks,

I'm writing up the new GUCs, and noticed that max_prepared_transactions 
defaults to 5.  This is too many for most applications (which don't use them 
at all) and far too few for applications which use them regularly.  

It seems like we should either set the value to 0, or to something higher, 
like 50.

It would also be nice to be able to just set the value to be equal to 
max_connections automatically, but I'm sure I brought that point up too late. 

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco


Re: max_prepared_transactions default ... why 5?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> I'm writing up the new GUCs, and noticed that max_prepared_transactions 
> defaults to 5.  This is too many for most applications (which don't use them 
> at all) and far too few for applications which use them regularly.  

I think the intention was to have enough so you could test 'em (in
particular, run the regression tests) without eating resources for
the majority of installations that aren't using them.

Certainly an installation that *is* using 'em would want a higher
setting.
        regards, tom lane


Re: max_prepared_transactions default ... why 5?

From
Josh Berkus
Date:
On Wednesday 17 October 2007 21:35, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> > I'm writing up the new GUCs, and noticed that max_prepared_transactions
> > defaults to 5.  This is too many for most applications (which don't use
> > them at all) and far too few for applications which use them regularly.
>
> I think the intention was to have enough so you could test 'em (in
> particular, run the regression tests) without eating resources for
> the majority of installations that aren't using them.
>
> Certainly an installation that *is* using 'em would want a higher
> setting.

Yeah, given the amount of memory per xact, I guess we can't actually set the 
default higher.  I just hate to see a setting that is liable to bite someone 
on the tuchas so easily.

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco


Re: max_prepared_transactions default ... why 5?

From
Tom Lane
Date:
Josh Berkus <josh@agliodbs.com> writes:
> Yeah, given the amount of memory per xact, I guess we can't actually set the 
> default higher.  I just hate to see a setting that is liable to bite someone 
> on the tuchas so easily.

I seem to recall thinking about replacing the setting with a
"prepared_transactions = on/off" boolean parameter, where "off" could be
defined as still allowing enough to run the regression tests.  The
problem is to choose the "on" setting --- it's not too hard to think of
application behaviors where you need *more* than max_connections
entries.
        regards, tom lane


Re: max_prepared_transactions default ... why 5?

From
Bruce Momjian
Date:
Josh Berkus wrote:
> On Wednesday 17 October 2007 21:35, Tom Lane wrote:
> > Josh Berkus <josh@agliodbs.com> writes:
> > > I'm writing up the new GUCs, and noticed that max_prepared_transactions
> > > defaults to 5.  This is too many for most applications (which don't use
> > > them at all) and far too few for applications which use them regularly.
> >
> > I think the intention was to have enough so you could test 'em (in
> > particular, run the regression tests) without eating resources for
> > the majority of installations that aren't using them.
> >
> > Certainly an installation that *is* using 'em would want a higher
> > setting.
> 
> Yeah, given the amount of memory per xact, I guess we can't actually set the 
> default higher.  I just hate to see a setting that is liable to bite someone 
> on the tuchas so easily.

They will see the failure at 5 faster and adjust it accordingly.  If it
was higher they might hit the limit only under heavy load and it would
surprise them.

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


Re: max_prepared_transactions default ... why 5?

From
"Magnus Hagander"
Date:
> > I'm writing up the new GUCs, and noticed that max_prepared_transactions 
> > defaults to 5.  This is too many for most applications (which don't use them 
> > at all) and far too few for applications which use them regularly.  
> 
> I think the intention was to have enough so you could test 'em (in
> particular, run the regression tests) without eating resources for
> the majority of installations that aren't using them.
> 
> Certainly an installation that *is* using 'em would want a higher
> setting.

Can' we make the default 0, which is what the majority should want, and have the regression test explicitly set it up
onthe commandline?
 

/Magnus



Re: max_prepared_transactions default ... why 5?

From
Stephen Frost
Date:
* Magnus Hagander (magnus@hagander.net) wrote:
> > Certainly an installation that *is* using 'em would want a higher
> > setting.
>
> Can' we make the default 0, which is what the majority should want,
> and have the regression test explicitly set it up on the commandline?

I'm with Magnus on this one.  Having it set to "just enough to test
with" is just plain terrible.  A value of '5' seems high enough that it
might be fine while on a development system and maybe for a short time
on a production system which means it might not get noticed early enough
to get picked up on and fixed by an admin before a user notices.

I also like the on/off option, with the default being 'off' (where that
really means *off*, not "on, but with a small number"- that would be
even worse than the current situation).
Thanks,
    Stephen

Re: max_prepared_transactions default ... why 5?

From
Tom Lane
Date:
"Magnus Hagander" <magnus@hagander.net> writes:
> Can' we make the default 0, which is what the majority should want, and have the regression test explicitly set it up
onthe commandline?
 

No.  It's a postmaster-start-time-only option, which means that your
proposal breaks "make installcheck".
        regards, tom lane


Re: max_prepared_transactions default ... why 5?

From
"Magnus Hagander"
Date:
> > Can' we make the default 0, which is what the majority should want, and have the regression test explicitly set it
upon the commandline?
 
> 
> No.  It's a postmaster-start-time-only option, which means that your
> proposal breaks "make installcheck".

Bummer. There are lots of ways to break installcheck though - locale being one I get biten by all the time... But I
guessthis one would break the default on US systems...
 

/Magnus


Re: max_prepared_transactions default ... why 5?

From
Tom Lane
Date:
"Magnus Hagander" <magnus@hagander.net> writes:
> Bummer. There are lots of ways to break installcheck though - locale
> being one I get biten by all the time...

Hmm, which locale do you use and what breakage do you see?
        regards, tom lane


Re: max_prepared_transactions default ... why 5?

From
Decibel!
Date:
On Oct 18, 2007, at 12:07 AM, Bruce Momjian wrote:
> Josh Berkus wrote:
>> On Wednesday 17 October 2007 21:35, Tom Lane wrote:
>>> Josh Berkus <josh@agliodbs.com> writes:
>>>> I'm writing up the new GUCs, and noticed that  
>>>> max_prepared_transactions
>>>> defaults to 5.  This is too many for most applications (which  
>>>> don't use
>>>> them at all) and far too few for applications which use them  
>>>> regularly.
>>>
>>> I think the intention was to have enough so you could test 'em (in
>>> particular, run the regression tests) without eating resources for
>>> the majority of installations that aren't using them.
>>>
>>> Certainly an installation that *is* using 'em would want a higher
>>> setting.
>>
>> Yeah, given the amount of memory per xact, I guess we can't  
>> actually set the
>> default higher.  I just hate to see a setting that is liable to  
>> bite someone
>> on the tuchas so easily.
>
> They will see the failure at 5 faster and adjust it accordingly.   
> If it
> was higher they might hit the limit only under heavy load and it would
> surprise them.

Actually, the amount of memory is a reason to default to 0, or change  
the name, or put a big comment in the config, because I very often  
saw databases where people had set this to a very high value under  
the impression that it impacted prepared statements.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828




Re: max_prepared_transactions default ... why 5?

From
Gregory Stark
Date:
"Decibel!" <decibel@decibel.org> writes:

> Actually, the amount of memory is a reason to default to 0, or change  the
> name, or put a big comment in the config, because I very often  saw databases
> where people had set this to a very high value under  the impression that it
> impacted prepared statements.

There's another cost associated with prepared transactions. If it's set to 0
then there's no real reason we need to wal log lock operations.


--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com


Re: max_prepared_transactions default ... why 5?

From
"Kevin Grittner"
Date:
>>> On Thu, Oct 18, 2007 at 11:23 AM, in message
<87ejfswfvw.fsf@oxford.xeocode.com>, Gregory Stark <stark@enterprisedb.com>
wrote:
> If it's set to 0
> then there's no real reason we need to wal log lock operations.
Do we currently take advantage of that fact, or log them anyway?
-Kevin




Re: max_prepared_transactions default ... why 5?

From
Heikki Linnakangas
Date:
Kevin Grittner wrote:
>>>> On Thu, Oct 18, 2007 at 11:23 AM, in message
> <87ejfswfvw.fsf@oxford.xeocode.com>, Gregory Stark <stark@enterprisedb.com>
> wrote: 
>  
>> If it's set to 0
>> then there's no real reason we need to wal log lock operations.
>  
> Do we currently take advantage of that fact, or log them anyway?

No, we log them anyway.

There's a few other reasons to WAL log lock operations, see comments in
heap_lock_tuple:

> /*
>  * XLOG stuff.    You might think that we don't need an XLOG record because
>  * there is no state change worth restoring after a crash.    You would be
>  * wrong however: we have just written either a TransactionId or a
>  * MultiXactId that may never have been seen on disk before, and we need
>  * to make sure that there are XLOG entries covering those ID numbers.
>  * Else the same IDs might be re-used after a crash, which would be
>  * disastrous if this page made it to disk before the crash.  Essentially
>  * we have to enforce the WAL log-before-data rule even in this case.
>  * (Also, in a PITR log-shipping or 2PC environment, we have to have XLOG
>  * entries for everything anyway.)
>  */

There's also the risk of torn pages. We set the xmax and the XMAX_*_LOCK
flag in heap_lock_tuple, and it's possible that only one of those
changes is written to disk before crash.

--  Heikki Linnakangas EnterpriseDB   http://www.enterprisedb.com


Re: max_prepared_transactions default ... why 5?

From
Magnus Hagander
Date:
On Thu, Oct 18, 2007 at 10:45:29AM -0400, Tom Lane wrote:
> "Magnus Hagander" <magnus@hagander.net> writes:
> > Bummer. There are lots of ways to break installcheck though - locale
> > being one I get biten by all the time...
>
> Hmm, which locale do you use and what breakage do you see?

Swedish_Sweden.1252. Diffs attached. The reason is that in swedish sort
order, "w" and "v" are considered equal.

//Magnus


Attachment

Re: max_prepared_transactions default ... why 5?

From
Tom Lane
Date:
Magnus Hagander <magnus@hagander.net> writes:
> On Thu, Oct 18, 2007 at 10:45:29AM -0400, Tom Lane wrote:
>> Hmm, which locale do you use and what breakage do you see?

> Swedish_Sweden.1252. Diffs attached. The reason is that in swedish sort
> order, "w" and "v" are considered equal.

If that's all, I see no objection to adding a select_1.out expected
file that matches your results.
        regards, tom lane


Re: max_prepared_transactions default ... why 5?

From
"Magnus Hagander"
Date:
> >> Hmm, which locale do you use and what breakage do you see?
> 
> > Swedish_Sweden.1252. Diffs attached. The reason is that in swedish sort
> > order, "w" and "v" are considered equal.
> 
> If that's all, I see no objection to adding a select_1.out expected
> file that matches your results.
>
I've just assumed there are similar problems in other locales... If not I can just ad that...

/Magnus