Обсуждение: 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
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
[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
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
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
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