Обсуждение: max_prepared_transactions default ... why 5?

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

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

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

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

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

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

* 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

"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

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

"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

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

"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

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

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

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

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

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