Thread: 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
Attachment
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