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 on the commandline?
/Magnus
Import Notes
Resolved by subject fallback
* 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 on the 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 up on 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 guess this one would break the default on US systems...
/Magnus
Import Notes
Resolved by subject fallback
"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
Attachments:
regression.diffstext/plain; charset=us-asciiDownload
*** ./expected/select.out 2007-07-11 10:53:23.478371800 +0200
--- ./results/select.out 2007-10-19 09:22:33.042274100 +0200
***************
*** 391,398 ****
chris | 78
sharon | 78
susan | 78
- vera | 78
wendy | 78
julie | 68
leah | 68
paula | 68
--- 391,398 ----
chris | 78
sharon | 78
susan | 78
wendy | 78
+ vera | 78
julie | 68
leah | 68
paula | 68
======================================================================
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
Import Notes
Resolved by subject fallback