Re: 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(at)agliodbs(dot)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(at)agliodbs(dot)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(at)agliodbs(dot)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(at)agliodbs(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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(at)agliodbs(dot)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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

"Decibel!" <decibel(at)decibel(dot)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(dot)fsf(at)oxford(dot)xeocode(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)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(dot)fsf(at)oxford(dot)xeocode(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)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