default resource limits
The discussion was a bit inconclusive last time, so I would like to
renew my proposal for more generous default resource limits.
Nearly everyone seems to agree that the default for max_fsm_pages is
woefully low, so I would like to have the default for this set
unconditionally to 200,000 rather than 20,000. The cost would be just
over 1Mb of shared memory, if the docs are correct. Alternatively, we
could put this into the mix that is calculated by initdb, scaling it
linearly with shared_buffers (but with the default still at 200,000).
I would also like to propose a more modest increase in max_connections
and shared_buffers by a factor of 3. This number is picked with some
caution, and is intended to ensure that a) we don't eat all the
resources on a modestly resourced machine, and b) we can accomodate 1
connection per apache child in a typical (unix) apache configuration. If
we were slightly less conservative, we'd at least take default
max_connections to 400, which is the maximum number of apache threads on
Windows, in the default configuration.)
Since the time when these defaults were set, the price of memory has
plummeted, and the typical memory population of an entry level machine
has roughly quadrupled, from my observation, so it seems to me that by
revising these limits we are just adjusting to the new reality.
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
Nearly everyone seems to agree that the default for max_fsm_pages is
woefully low, so I would like to have the default for this set
unconditionally to 200,000 rather than 20,000. The cost would be just
over 1Mb of shared memory, if the docs are correct. Alternatively, we
could put this into the mix that is calculated by initdb, scaling it
linearly with shared_buffers (but with the default still at 200,000).
I would also like to propose a more modest increase in max_connections
and shared_buffers by a factor of 3.
I don't mind having initdb try larger values to see if they work, but
if you are suggesting that we try to force adoption of larger settings
I'll resist it.
"Factor of three" seems mighty weird. The existing numbers (100 and 1000)
at least have the defensibility of being round.
regards, tom lane
Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
Nearly everyone seems to agree that the default for max_fsm_pages is
woefully low, so I would like to have the default for this set
unconditionally to 200,000 rather than 20,000. The cost would be just
over 1Mb of shared memory, if the docs are correct. Alternatively, we
could put this into the mix that is calculated by initdb, scaling it
linearly with shared_buffers (but with the default still at 200,000).I would also like to propose a more modest increase in max_connections
and shared_buffers by a factor of 3.I don't mind having initdb try larger values to see if they work, but
if you are suggesting that we try to force adoption of larger settings
I'll resist it.
OK, works for me. The only thing I suggested might be set in stone was
max_fsm_pages; I always envisioned the others being tested as now by initdb.
"Factor of three" seems mighty weird. The existing numbers (100 and 1000)
at least have the defensibility of being round.
What numbers would you like? If what I suggested seems odd, how about
targets of 400 connections, 4000 shared_buffers and 200,000 max_fsm_pages?
cheers
andrew
I wrote:
Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
Nearly everyone seems to agree that the default for max_fsm_pages is
woefully low, so I would like to have the default for this set
unconditionally to 200,000 rather than 20,000. The cost would be
just over 1Mb of shared memory, if the docs are correct.
Alternatively, we could put this into the mix that is calculated by
initdb, scaling it linearly with shared_buffers (but with the
default still at 200,000).I would also like to propose a more modest increase in
max_connections and shared_buffers by a factor of 3.I don't mind having initdb try larger values to see if they work, but
if you are suggesting that we try to force adoption of larger settings
I'll resist it.OK, works for me. The only thing I suggested might be set in stone was
max_fsm_pages; I always envisioned the others being tested as now by
initdb."Factor of three" seems mighty weird. The existing numbers (100 and
1000)
at least have the defensibility of being round.What numbers would you like? If what I suggested seems odd, how about
targets of 400 connections, 4000 shared_buffers and 200,000
max_fsm_pages?
Here's a patch that does what I had in mind. On my modest workstation it
tops out at 400 connections and 2500/125000
shared_buffers/max_fsm_pages. An idle postmaster with these settings
consumed less than 4% of the 380Mb of memory, according to top, making
it still dwarfed by X, mozilla, apache and amavisd among other memory hogs.
Comments welcome.
cheers
andrew
er patch attached this time
Andrew Dunstan wrote:
Show quoted text
I wrote:
Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
Nearly everyone seems to agree that the default for max_fsm_pages
is woefully low, so I would like to have the default for this set
unconditionally to 200,000 rather than 20,000. The cost would be
just over 1Mb of shared memory, if the docs are correct.
Alternatively, we could put this into the mix that is calculated by
initdb, scaling it linearly with shared_buffers (but with the
default still at 200,000).I would also like to propose a more modest increase in
max_connections and shared_buffers by a factor of 3.I don't mind having initdb try larger values to see if they work, but
if you are suggesting that we try to force adoption of larger settings
I'll resist it.OK, works for me. The only thing I suggested might be set in stone
was max_fsm_pages; I always envisioned the others being tested as now
by initdb."Factor of three" seems mighty weird. The existing numbers (100 and
1000)
at least have the defensibility of being round.What numbers would you like? If what I suggested seems odd, how about
targets of 400 connections, 4000 shared_buffers and 200,000
max_fsm_pages?Here's a patch that does what I had in mind. On my modest workstation
it tops out at 400 connections and 2500/125000
shared_buffers/max_fsm_pages. An idle postmaster with these settings
consumed less than 4% of the 380Mb of memory, according to top, making
it still dwarfed by X, mozilla, apache and amavisd among other memory
hogs.Comments welcome.
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Attachments:
revlimits.difftext/x-patch; name=revlimits.diffDownload
Index: src/bin/initdb/initdb.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.101
diff -c -r1.101 initdb.c
*** src/bin/initdb/initdb.c 9 Dec 2005 15:51:14 -0000 1.101
--- src/bin/initdb/initdb.c 23 Dec 2005 20:29:15 -0000
***************
*** 120,125 ****
--- 120,126 ----
/* defaults */
static int n_connections = 10;
static int n_buffers = 50;
+ static int n_fsm_pages = 20000;
/*
* Warning messages for authentication methods
***************
*** 1090,1096 ****
test_connections(void)
{
char cmd[MAXPGPATH];
! static const int conns[] = {100, 50, 40, 30, 20, 10};
static const int len = sizeof(conns) / sizeof(int);
int i,
status;
--- 1091,1098 ----
test_connections(void)
{
char cmd[MAXPGPATH];
! static const int conns[] = {400, 300, 250, 200, 150,
! 100, 50, 40, 30, 20, 10};
static const int len = sizeof(conns) / sizeof(int);
int i,
status;
***************
*** 1125,1146 ****
test_buffers(void)
{
char cmd[MAXPGPATH];
! static const int bufs[] = {1000, 900, 800, 700, 600, 500,
! 400, 300, 200, 100, 50};
static const int len = sizeof(bufs) / sizeof(int);
int i,
! status;
! printf(_("selecting default shared_buffers ... "));
fflush(stdout);
for (i = 0; i < len; i++)
{
snprintf(cmd, sizeof(cmd),
"%s\"%s\" -boot -x0 %s "
"-c shared_buffers=%d -c max_connections=%d template1 "
"< \"%s\" > \"%s\" 2>&1%s",
SYSTEMQUOTE, backend_exec, boot_options,
bufs[i], n_connections,
DEVNULL, DEVNULL, SYSTEMQUOTE);
status = system(cmd);
--- 1127,1155 ----
test_buffers(void)
{
char cmd[MAXPGPATH];
! static const int bufs[] = {
! 4000, 3500, 3000, 2500, 2000, 1500,
! 1000, 900, 800, 700, 600, 500,
! 400, 300, 200, 100, 50
! };
static const int len = sizeof(bufs) / sizeof(int);
int i,
! status,
! test_max_fsm_pages;
! printf(_("selecting default shared_buffers/max_fsm_pages ... "));
fflush(stdout);
for (i = 0; i < len; i++)
{
+ test_max_fsm_pages = bufs[i] > 1000 ? 50 * bufs[i] : 20000;
snprintf(cmd, sizeof(cmd),
"%s\"%s\" -boot -x0 %s "
+ "-c max_fsm_pages=%d "
"-c shared_buffers=%d -c max_connections=%d template1 "
"< \"%s\" > \"%s\" 2>&1%s",
SYSTEMQUOTE, backend_exec, boot_options,
+ test_max_fsm_pages,
bufs[i], n_connections,
DEVNULL, DEVNULL, SYSTEMQUOTE);
status = system(cmd);
***************
*** 1150,1157 ****
if (i >= len)
i = len - 1;
n_buffers = bufs[i];
! printf("%d\n", n_buffers);
}
/*
--- 1159,1167 ----
if (i >= len)
i = len - 1;
n_buffers = bufs[i];
+ n_fsm_pages = test_max_fsm_pages;
! printf("%d/%d\n", n_buffers, n_fsm_pages);
}
/*
***************
*** 1177,1182 ****
--- 1187,1195 ----
snprintf(repltok, sizeof(repltok), "shared_buffers = %d", n_buffers);
conflines = replace_token(conflines, "#shared_buffers = 1000", repltok);
+ snprintf(repltok, sizeof(repltok), "max_fsm_pages = %d", n_fsm_pages);
+ conflines = replace_token(conflines, "#max_fsm_pages = 20000", repltok);
+
#if DEF_PGPORT != 5432
snprintf(repltok, sizeof(repltok), "#port = %d", DEF_PGPORT);
conflines = replace_token(conflines, "#port = 5432", repltok);
On Fri, Dec 23, 2005 at 03:38:56PM -0500, Andrew Dunstan wrote:
What numbers would you like? If what I suggested seems odd, how about
targets of 400 connections, 4000 shared_buffers and 200,000
max_fsm_pages?Here's a patch that does what I had in mind. On my modest workstation it
tops out at 400 connections and 2500/125000
shared_buffers/max_fsm_pages. An idle postmaster with these settings
consumed less than 4% of the 380Mb of memory, according to top, making
it still dwarfed by X, mozilla, apache and amavisd among other memory hogs.
I don't understand the motivation for so many connections by default, it
seems wasteful in most cases.
-dg
--
David Gould daveg@sonic.net
If simplicity worked, the world would be overrun with insects.
daveg wrote:
On Fri, Dec 23, 2005 at 03:38:56PM -0500, Andrew Dunstan wrote:
What numbers would you like? If what I suggested seems odd, how about
targets of 400 connections, 4000 shared_buffers and 200,000
max_fsm_pages?Here's a patch that does what I had in mind. On my modest workstation it
tops out at 400 connections and 2500/125000
shared_buffers/max_fsm_pages. An idle postmaster with these settings
consumed less than 4% of the 380Mb of memory, according to top, making
it still dwarfed by X, mozilla, apache and amavisd among other memory hogs.I don't understand the motivation for so many connections by default, it
seems wasteful in most cases.
The rationale is one connection per apache thread (which on Windows
defaults to 400). If people think this is too many I could live with
winding it back a bit - the defaults number of apache workers on Unix is
250, IIRC.
Here's why iot matters: during Hurricane Katrina, one web site that was
collecting details on people missing etc. found its application failing
because apache/php wanted more connections (out of the box) than the out
of the box postgres default. Luckily we were able to advise the operator
on how to fix it very quickly, but having these in some sort of sync
seems reasonable.
Of course, if you use connection pooling you can probably wind the
number back quite a lot.
There's no magic right answer - but on even entry level retail desktop
machines the extra used from this is now quite a small drop in the
memory bucket - the very lowest come with 256Mb of memory, and all but
the very lowest come with 512Mb or 1Gb. So why should we argue over a
handful of megabytes in raising limits last set 3 or 4 years ago?
cheers
andrew
daveg <daveg@sonic.net> writes:
I don't understand the motivation for so many connections by default, it
seems wasteful in most cases.
I think Andrew is thinking about database-backed Apache servers ...
Some quick checks say that CVS tip's demand for shared memory increases
by about 26kB per max_connection slot. (Almost all of this is lock table
space; very possibly we could afford to decrease max_locks_per_connection
when max_connections is large, to buy back some of that.) So boosting
the default from 100 to 400 would eat an additional 7.8mB of shared
memory if we don't do anything with max_locks_per_connection. This is
probably not a lot on modern machines.
A bigger concern is the increase in semaphores or whatever the local
platform uses instead. I'd be *real* strongly tempted to bound the
default at 100 on Darwin, for example, because on that platform each
"semaphore" is an open file that has to be passed down to every backend.
Uselessly large max_connections therefore slows backend launch and
risks running the whole machine out of filetable slots. I don't know
what the story is on Windows but it might have some problems with large
numbers of semas too --- anyone know?
Also, some more thought needs to be given to the tradeoff between
shared_buffers and max_connections. Given a constrained SHMMAX value,
I think the patch as-is will expend too much space on connections and
not enough on buffers --- the "* 5" in test_connections() probably needs
a second look.
regards, tom lane
Tom Lane wrote:
daveg <daveg@sonic.net> writes:
I don't understand the motivation for so many connections by default, it
seems wasteful in most cases.I think Andrew is thinking about database-backed Apache servers ...
Some quick checks say that CVS tip's demand for shared memory increases
by about 26kB per max_connection slot. (Almost all of this is lock table
space; very possibly we could afford to decrease max_locks_per_connection
when max_connections is large, to buy back some of that.) So boosting
the default from 100 to 400 would eat an additional 7.8mB of shared
memory if we don't do anything with max_locks_per_connection. This is
probably not a lot on modern machines.A bigger concern is the increase in semaphores or whatever the local
platform uses instead. I'd be *real* strongly tempted to bound the
default at 100 on Darwin, for example, because on that platform each
"semaphore" is an open file that has to be passed down to every backend.
Uselessly large max_connections therefore slows backend launch and
risks running the whole machine out of filetable slots. I don't know
what the story is on Windows but it might have some problems with large
numbers of semas too --- anyone know?Also, some more thought needs to be given to the tradeoff between
shared_buffers and max_connections. Given a constrained SHMMAX value,
I think the patch as-is will expend too much space on connections and
not enough on buffers --- the "* 5" in test_connections() probably needs
a second look.
All very good points. I didn't try to change the existing logic much.
I think we need to take this back to -hackers to get discussion on
tuning the way initdb selects the defaults.
Here are some questions:
. Do we want/need to make the behaviour platform specific?
. The patch put max_fsm_pages into the mix, and above it's suggested we
look at max_locks_per_connection. Is there anything else we should add?
My goal here is to pick reasonable defaults, not to tune the
installation highly. I think we tend to err on the side of being too
conservative today, but certainly it's possible to err the other way too.
cheers
andrew
Am Samstag, 24. Dezember 2005 00:20 schrieb Andrew Dunstan:
The rationale is one connection per apache thread (which on Windows
defaults to 400). If people think this is too many I could live with
winding it back a bit - the defaults number of apache workers on Unix is
250, IIRC.
It's 150. I don't mind increasing the current 100 to 150, although I find
tying this to apache pretty bogus.
I really don't like the prospect of making the defaults platform specific,
especially if the only rationale for that would be "apache does it". Why
does apache allocate more connections on Windows anyway?
On Saturday 24 December 2005 06:22, Peter Eisentraut wrote:
Am Samstag, 24. Dezember 2005 00:20 schrieb Andrew Dunstan:
The rationale is one connection per apache thread (which on Windows
defaults to 400). If people think this is too many I could live with
winding it back a bit - the defaults number of apache workers on Unix is
250, IIRC.It's 150. I don't mind increasing the current 100 to 150, although I find
tying this to apache pretty bogus.I really don't like the prospect of making the defaults platform specific,
especially if the only rationale for that would be "apache does it". Why
does apache allocate more connections on Windows anyway?
Maybe we should write something in to check if apache is installed if we're so
concerned about that usage... I already know that I set the connection limits
lower on most of the installations I do (given than most installations are
not production webservers). There is also the argument to be made that just
because systems these days have more memory doesn't mean we have to use it.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
[moving to -hackers]
Peter Eisentraut wrote:
Am Samstag, 24. Dezember 2005 00:20 schrieb Andrew Dunstan:
The rationale is one connection per apache thread (which on Windows
defaults to 400). If people think this is too many I could live with
winding it back a bit - the defaults number of apache workers on Unix is
250, IIRC.It's 150. I don't mind increasing the current 100 to 150, although I find
tying this to apache pretty bogus.
According to
http://httpd.apache.org/docs/2.0/mod/mpm_common.html#maxclients the
default for the prefork MPM, which is the default on Unix, is 256. 400
appears to be what is used for hybrid MPMs like worker, which is not the
default for any platform. The default Windows MPM (mpm_winnt) is
apparently governed by the ThreadsPerChild setting, which defaults to
64, not 400 as I previously stated.
I really don't like the prospect of making the defaults platform specific,
especially if the only rationale for that would be "apache does it". Why
does apache allocate more connections on Windows anyway?
It uses a *very* different engine.
Maybe referring to apache is not ideal, although playing nicely with a
very common client doesn't strike me as totally bogus either.
But what is the rationale for the current settings, or for anything else
that might be proposed? I have yet to hear any. Is there anyone who
thinks that 1000/20000 for shared_buffers/max_fsm_pages is a good set of
defaults?
Maybe we need to split this into two pieces, given Tom's legitimate
concern about semaphore use. How about we increase the allowed range for
shared_buffers and max_fsm_pages, as proposed in my patch, and leave the
max_connections issue on the table? I also wondered if instead of first
setting max_connections and then shared_buffers/max_fsm_pages, we should
try to scale them in synch somehow.
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
Maybe we need to split this into two pieces, given Tom's legitimate
concern about semaphore use. How about we increase the allowed range for
shared_buffers and max_fsm_pages, as proposed in my patch, and leave the
max_connections issue on the table? I also wondered if instead of first
setting max_connections and then shared_buffers/max_fsm_pages, we should
try to scale them in synch somehow.
The existing initdb code actually does try to scale them in sync to some
extent --- take a closer look at the arguments being passed during the
max-connections test phase. It won't choose a large max_connections
unless it can simultaneously get 5 times that many shared_buffers.
I think this probably needs to be more aggressive though. In a
situation of limited SHMMAX it's probably more important to keep
shared_buffers as high as we can than to get a high max_connections.
We could think about increasing the 5x multiplier, adding Min and/or Max
limits, or some combination.
BTW, I fat-fingered the calculations I was doing last night --- the
actual shmem consumption in CVS tip seems to be more like 17K per
max_connection increment, assuming max_locks_per_connection = 64.
regards, tom lane
Tom Lane wrote:
BTW, I fat-fingered the calculations I was doing last night --- the
actual shmem consumption in CVS tip seems to be more like 17K per
max_connection increment, assuming max_locks_per_connection = 64.
ITYM max_locks_per_transaction (which as the docs say is confusingly named).
So if we went to 256, say, as an upper limit on max_connections, that
would account for an extra 2.6Mb of memory use - a pretty modest
increase, really.
cheers
andrew
Robert Treat wrote:
Maybe we should write something in to check if apache is installed if we're so
concerned about that usage...
Er, yeah, I'll get right on that. (Don't hold your breath.)
I already know that I set the connection limits
lower on most of the installations I do (given than most installations are
not production webservers).
So do I. In fact, even on production web servers I usually use
connection pooling and can rarely get an app to approach saturating a
pool size of around 20 let alone 100. But then you and I know something
about tuning Postgres. What I am aiming for is something that is closer
to the norm on out of the box configuration.
There is also the argument to be made that just
because systems these days have more memory doesn't mean we have to use it.
Just because we can run with very little memory doesn't mean we have to.
What is the point of having lots of memory if you don't use it? We are
talking defaults here. initdb will still scale down on resource-starved
machines.
cheers
andrew
[moved to -hackers]
Petr Jelinek said:
Andrew Dunstan wrote:
Just because we can run with very little memory doesn't mean we have to.
What is the point of having lots of memory if you don't use it? We are
talking defaults here. initdb will still scale down on
resource-starved machines.
Why not just give user few configs tuned to different things like mysql
does ? Or better, make it initdb option so it will try to set
higher/lower limits depending on type of config.
And what settings will be tried by distros that automatically run initdb on
first startup?
I don't mind having an initdb option that tunes the settings tried, but that
doesn't remove the need to choose some defaults.
I'm not sure that I think mysql's setup is a good example to follow.
cheers
andrew
Import Notes
Resolved by subject fallback
Tom Lane said:
Andrew Dunstan <andrew@dunslane.net> writes:
Maybe we need to split this into two pieces, given Tom's legitimate
concern about semaphore use. How about we increase the allowed range
for shared_buffers and max_fsm_pages, as proposed in my patch, and
leave the max_connections issue on the table? I also wondered if
instead of first setting max_connections and then
shared_buffers/max_fsm_pages, we should try to scale them in synch
somehow.The existing initdb code actually does try to scale them in sync to
some extent --- take a closer look at the arguments being passed during
the max-connections test phase. It won't choose a large
max_connections unless it can simultaneously get 5 times that many
shared_buffers.
Yes, I know. What I meant was that we could try using one phase
rather than two. But that's only one possible approach.
I think this probably needs to be more aggressive
though. In a
situation of limited SHMMAX it's probably more important to keep
shared_buffers as high as we can than to get a high max_connections. We
could think about increasing the 5x multiplier, adding Min and/or Max
limits, or some combination.
Yes. If we were to base it on the current maxima (1000/100), we could use a
factor of 10, or if on the maxima I am now proposing (4000/250) a factor of
16. Something in that range is about right I suspect.
cheers
andrew
"Andrew Dunstan" <andrew@dunslane.net> writes:
Tom Lane said:
The existing initdb code actually does try to scale them in sync to
some extent ---
Yes, I know. What I meant was that we could try using one phase
rather than two. But that's only one possible approach.
I think that's a bad idea, mainly because max_connections is constrained
by more things than just SHMMAX. In a scenario where the number of
semaphores constrains max_connections, you'd probably end up failing to
push shared_buffers up as high as it could be.
regards, tom lane
I wrote:
Tom Lane said:
I think this probably needs to be more aggressive
though. In a
situation of limited SHMMAX it's probably more important to keep
shared_buffers as high as we can than to get a high max_connections. We
could think about increasing the 5x multiplier, adding Min and/or Max
limits, or some combination.Yes. If we were to base it on the current maxima (1000/100), we could use a
factor of 10, or if on the maxima I am now proposing (4000/250) a factor of
16. Something in that range is about right I suspect.
In experimenting I needed to set this at 20 for it to bite much. If we
wanted to fine tune it I'd be inclined to say that we wanted
20*connections buffers for the first, say, 50 or 100 connections and 10
or 16 times for each connection over that. But that might be getting a
little too clever - something we should leave to a specialised tuning
tool. After all, we try these in fairly discrete jumps anyway. Maybe a
simple factor around 20 would be sufficient.
Leaving aside the question of max_connections, which seems to be the
most controversial, is there any objection to the proposal to increase
the settings tried for shared_buffers (up to 4000) and max_fsm_pages (up
to 200000) ? If not, I'll apply a patch for those changes shortly.
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
In experimenting I needed to set this at 20 for it to bite much. If we
wanted to fine tune it I'd be inclined to say that we wanted
20*connections buffers for the first, say, 50 or 100 connections and 10
or 16 times for each connection over that. But that might be getting a
little too clever - something we should leave to a specialised tuning
tool. After all, we try these in fairly discrete jumps anyway. Maybe a
simple factor around 20 would be sufficient.
I was thinking of a linear factor plus clamps to minimum and maximum
values --- does that make it work any better?
Leaving aside the question of max_connections, which seems to be the
most controversial, is there any objection to the proposal to increase
the settings tried for shared_buffers (up to 4000) and max_fsm_pages (up
to 200000) ? If not, I'll apply a patch for those changes shortly.
You probably need to fix the max-connections pass so that it applies the
same changes to max_fsm_pages as the second pass does --- otherwise, its
assumption that shared_buffers can really be set that way will be wrong.
Other than that I didn't see any problem with the shared_buffers part of
the patch.
regards, tom lane
Tom Lane wrote:
I was thinking of a linear factor plus clamps to minimum and maximum
values --- does that make it work any better?
Can you suggest some factor/clamp values? Obviously it would be
reasonable to set the max clamp at the max shared_buffers size we would
test in the next step, but I'm not sure I see a need for a minimum - all
the factors I'm thinking of (or any factor above 10) would make us
exceed our current minumum (100) in all cases anyway.
You probably need to fix the max-connections pass so that it applies the
same changes to max_fsm_pages as the second pass does --- otherwise, its
assumption that shared_buffers can really be set that way will be wrong.
Other than that I didn't see any problem with the shared_buffers part of
the patch.
OK, will do.
cheers
andrew
Tom Lane wrote:
Leaving aside the question of max_connections, which seems to be the
most controversial, is there any objection to the proposal to increase
the settings tried for shared_buffers (up to 4000) and max_fsm_pages (up
to 200000) ? If not, I'll apply a patch for those changes shortly.You probably need to fix the max-connections pass so that it applies the
same changes to max_fsm_pages as the second pass does --- otherwise, its
assumption that shared_buffers can really be set that way will be wrong.
Other than that I didn't see any problem with the shared_buffers part of
the patch.
revised patch attached, leaving max_connections alone except as above.
I'll apply this in a day or two, barring objection.
cheers
andrew
Attachments:
shbuf.difftext/x-patch; name=shbuf.diffDownload
Index: src/bin/initdb/initdb.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.101
diff -c -r1.101 initdb.c
*** src/bin/initdb/initdb.c 9 Dec 2005 15:51:14 -0000 1.101
--- src/bin/initdb/initdb.c 26 Dec 2005 22:44:09 -0000
***************
*** 120,125 ****
--- 120,126 ----
/* defaults */
static int n_connections = 10;
static int n_buffers = 50;
+ static int n_fsm_pages = 20000;
/*
* Warning messages for authentication methods
***************
*** 1084,1089 ****
--- 1085,1097 ----
}
/*
+ * max_fsm_pages setting used in both the shared_buffers and max_connections
+ * tests.
+ */
+
+ #define TEST_FSM(x) ( (x) > 1000 ? 50 * (x) : 20000 )
+
+ /*
* check how many connections we can sustain
*/
static void
***************
*** 1100,1111 ****
for (i = 0; i < len; i++)
{
snprintf(cmd, sizeof(cmd),
"%s\"%s\" -boot -x0 %s "
"-c shared_buffers=%d -c max_connections=%d template1 "
"< \"%s\" > \"%s\" 2>&1%s",
SYSTEMQUOTE, backend_exec, boot_options,
! conns[i] * 5, conns[i],
DEVNULL, DEVNULL, SYSTEMQUOTE);
status = system(cmd);
if (status == 0)
--- 1108,1124 ----
for (i = 0; i < len; i++)
{
+ int test_buffs = conns[i] * 5;
+ int test_max_fsm = TEST_FSM(test_buffs);
+
snprintf(cmd, sizeof(cmd),
"%s\"%s\" -boot -x0 %s "
+ "-c max_fsm_pages=%d "
"-c shared_buffers=%d -c max_connections=%d template1 "
"< \"%s\" > \"%s\" 2>&1%s",
SYSTEMQUOTE, backend_exec, boot_options,
! test_max_fsm,
! test_buffs, conns[i],
DEVNULL, DEVNULL, SYSTEMQUOTE);
status = system(cmd);
if (status == 0)
***************
*** 1125,1146 ****
test_buffers(void)
{
char cmd[MAXPGPATH];
! static const int bufs[] = {1000, 900, 800, 700, 600, 500,
! 400, 300, 200, 100, 50};
static const int len = sizeof(bufs) / sizeof(int);
int i,
! status;
! printf(_("selecting default shared_buffers ... "));
fflush(stdout);
for (i = 0; i < len; i++)
{
snprintf(cmd, sizeof(cmd),
"%s\"%s\" -boot -x0 %s "
"-c shared_buffers=%d -c max_connections=%d template1 "
"< \"%s\" > \"%s\" 2>&1%s",
SYSTEMQUOTE, backend_exec, boot_options,
bufs[i], n_connections,
DEVNULL, DEVNULL, SYSTEMQUOTE);
status = system(cmd);
--- 1138,1167 ----
test_buffers(void)
{
char cmd[MAXPGPATH];
! static const int bufs[] = {
! 4000, 3500, 3000, 2500, 2000, 1500,
! 1000, 900, 800, 700, 600, 500,
! 400, 300, 200, 100, 50
! };
static const int len = sizeof(bufs) / sizeof(int);
int i,
! status,
! test_max_fsm_pages;
! printf(_("selecting default shared_buffers/max_fsm_pages ... "));
fflush(stdout);
for (i = 0; i < len; i++)
{
+ test_max_fsm_pages = TEST_FSM(bufs[i]);
+
snprintf(cmd, sizeof(cmd),
"%s\"%s\" -boot -x0 %s "
+ "-c max_fsm_pages=%d "
"-c shared_buffers=%d -c max_connections=%d template1 "
"< \"%s\" > \"%s\" 2>&1%s",
SYSTEMQUOTE, backend_exec, boot_options,
+ test_max_fsm_pages,
bufs[i], n_connections,
DEVNULL, DEVNULL, SYSTEMQUOTE);
status = system(cmd);
***************
*** 1150,1157 ****
if (i >= len)
i = len - 1;
n_buffers = bufs[i];
! printf("%d\n", n_buffers);
}
/*
--- 1171,1179 ----
if (i >= len)
i = len - 1;
n_buffers = bufs[i];
+ n_fsm_pages = test_max_fsm_pages;
! printf("%d/%d\n", n_buffers, n_fsm_pages);
}
/*
***************
*** 1177,1182 ****
--- 1199,1207 ----
snprintf(repltok, sizeof(repltok), "shared_buffers = %d", n_buffers);
conflines = replace_token(conflines, "#shared_buffers = 1000", repltok);
+ snprintf(repltok, sizeof(repltok), "max_fsm_pages = %d", n_fsm_pages);
+ conflines = replace_token(conflines, "#max_fsm_pages = 20000", repltok);
+
#if DEF_PGPORT != 5432
snprintf(repltok, sizeof(repltok), "#port = %d", DEF_PGPORT);
conflines = replace_token(conflines, "#port = 5432", repltok);
I wrote:
You probably need to fix the max-connections pass so that it applies the
same changes to max_fsm_pages as the second pass does --- otherwise, its
assumption that shared_buffers can really be set that way will be wrong.
Other than that I didn't see any problem with the shared_buffers part of
the patch.revised patch attached, leaving max_connections alone except as above.
committed, along with minor docs change.
The open question is whether to try more connections, on some or all
platforms.
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
In experimenting I needed to set this at 20 for it to bite much. If we
wanted to fine tune it I'd be inclined to say that we wanted
20*connections buffers for the first, say, 50 or 100 connections and 10
or 16 times for each connection over that. But that might be getting a
little too clever - something we should leave to a specialised tuning
tool. After all, we try these in fairly discrete jumps anyway. Maybe a
simple factor around 20 would be sufficient.
I think 10 is probably a good compromise value. If we set it to 20
we'll find "make check" failing on Darwin because Apple's standard
SHMMAX value doesn't allow more than about 300 buffers ... and the
regression tests want max_connections to be at least 20.
I noticed while fooling with this on my laptop that initdb was selecting
a shared_buffers value less than the value it had just proved would work
:-(. This is because the list of shared_buffers values it was probing
did not include all the values corresponding to values tried during the
max_connections scan. I've added documentation about that gotcha.
regards, tom lane
Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
In experimenting I needed to set this at 20 for it to bite much. If we
wanted to fine tune it I'd be inclined to say that we wanted
20*connections buffers for the first, say, 50 or 100 connections and 10
or 16 times for each connection over that. But that might be getting a
little too clever - something we should leave to a specialised tuning
tool. After all, we try these in fairly discrete jumps anyway. Maybe a
simple factor around 20 would be sufficient.I think 10 is probably a good compromise value. If we set it to 20
we'll find "make check" failing on Darwin because Apple's standard
SHMMAX value doesn't allow more than about 300 buffers ... and the
regression tests want max_connections to be at least 20.
Well, we could do something like:
#define MIN_BUFS_FOR_CONNS(nconns) ((nconns) <= 20 ? (nconns) * 10 : 200
+ (((nconns) - 20) * 20))
But I'm happy just to live with 10 :-)
I noticed while fooling with this on my laptop that initdb was selecting
a shared_buffers value less than the value it had just proved would work
:-(. This is because the list of shared_buffers values it was probing
did not include all the values corresponding to values tried during the
max_connections scan. I've added documentation about that gotcha.
That's easily fixed, I think. We just need to remember what we have
proved works.
I can apply the attached patch if you think that's worth doing.
Thanks for cleaning this up. The remaining question is whether to leave
the max connections tried at 100 on all platforms or bump it for those
that won't hurt from extra semaphore use. I can see arguments both ways
- I'm less concerned about this than the shared buffers numbers.
cheers
andrew
Attachments:
buf-fix.patchtext/x-patch; name=buf-fix.patchDownload
Index: initdb.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/initdb/initdb.c,v
retrieving revision 1.103
diff -c -r1.103 initdb.c
*** initdb.c 31 Dec 2005 23:50:59 -0000 1.103
--- initdb.c 2 Jan 2006 00:52:58 -0000
***************
*** 1122,1128 ****
status,
test_conns,
test_buffs,
! test_max_fsm;
printf(_("selecting default max_connections ... "));
fflush(stdout);
--- 1122,1130 ----
status,
test_conns,
test_buffs,
! test_max_fsm,
! ok_buffers = 0;
!
printf(_("selecting default max_connections ... "));
fflush(stdout);
***************
*** 1144,1150 ****
--- 1146,1155 ----
DEVNULL, DEVNULL, SYSTEMQUOTE);
status = system(cmd);
if (status == 0)
+ {
+ ok_buffers = test_buffs;
break;
+ }
}
if (i >= connslen)
i = connslen - 1;
***************
*** 1158,1163 ****
--- 1163,1173 ----
for (i = 0; i < bufslen; i++)
{
test_buffs = trial_bufs[i];
+ if (test_buffs <= ok_buffers)
+ {
+ test_buffs = ok_buffers;
+ break;
+ }
test_max_fsm = FSM_FOR_BUFS(test_buffs);
snprintf(cmd, sizeof(cmd),
***************
*** 1173,1181 ****
if (status == 0)
break;
}
! if (i >= bufslen)
! i = bufslen - 1;
! n_buffers = trial_bufs[i];
n_fsm_pages = FSM_FOR_BUFS(n_buffers);
printf("%d/%d\n", n_buffers, n_fsm_pages);
--- 1183,1189 ----
if (status == 0)
break;
}
! n_buffers = test_buffs;
n_fsm_pages = FSM_FOR_BUFS(n_buffers);
printf("%d/%d\n", n_buffers, n_fsm_pages);
Andrew Dunstan <andrew@dunslane.net> writes:
That's easily fixed, I think. We just need to remember what we have
proved works.
I can apply the attached patch if you think that's worth doing.
If you like; but if so, remove the comment saying that there's a
connection between the required list entries.
regards, tom lane
Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
That's easily fixed, I think. We just need to remember what we have
proved works.I can apply the attached patch if you think that's worth doing.
If you like; but if so, remove the comment saying that there's a
connection between the required list entries.
done.
cheers
andrew