Обсуждение: max_connections, solaris semaphores and initdb
Hi pgsql experts; I've been a little surprised at the high knowledge level of the "novices" on this list, so if this isn't a novice question, let me know and I'll send it to the regular list. I'm running postgresql 7.4 on Solaris 9 and want to increase the value of postgresql.conf's max_connections for an existing, running install. I've edited /etc/system to what I think are more appropriate, but definitely higher, values. Then I changed the value of max_connections to 768; however, by watching the pg_stat_activity view and the serverlog, connections are rejected when the number of connections approaches 200 (with the infamous: "FATAL: connection limit exceeded for non-superusers" error). I ran across a mention of initdb determining the greatest possible number of connections according to kernerl settings. So presumably, when postgresql is first setup, the highest value of max_connections is decided then. A couple questions: 0) Is this correct? Does initdb set an unchaged value that quietly limits the high end of max_connections? 1) Is there a way to get postgresql to reset this value that determines the maximum value of max_connections, without dumping, re-running initdb and loading? Is there a way to see what this value is? 2) Are there any easy postgresql load testing tools? I ran across Tsung, but haven't had time to compile erlang. What do others do for load testing? Thanks, Isaac Vetter I had trouble finding recent example Solaris /etc/system numbers, so I've included mine below. I'm still testing these are am not a solaris sysadmin - ymmv. * On an 8 gig sun4u running solaris 9 set shmsys:shminfo_shmmax=4294967295 set shmsys:shminfo_shmmin=1 set shmsys:shminfo_shmmni=512 set shmsys:shminfo_shmseg=512 *semaphores *max # of semaphores set semsys:seminfo_semmns=2048 *max # of semaphore sets set semsys:seminfo_semmni=1024 *max # of semaphores per set set semsys:seminfo_semmsl=128
Вложения
Isaac Vetter <ivetter@math.purdue.edu> writes: > I'm running postgresql 7.4 on Solaris 9 and want to increase the value > of postgresql.conf's max_connections for an existing, running install. > I've edited /etc/system to what I think are more appropriate, but > definitely higher, values. Then I changed the value of max_connections > to 768; however, by watching the pg_stat_activity view and the > serverlog, connections are rejected when the number of connections > approaches 200 (with the infamous: "FATAL: connection limit exceeded > for non-superusers" error). Um, did you restart the postmaster? This is a can't-change-after-startup parameter. regards, tom lane
Tom Lane wrote: > Isaac Vetter <ivetter@math.purdue.edu> writes: >> I'm running postgresql 7.4 on Solaris 9 and want to increase the value >> of postgresql.conf's max_connections for an existing, running install. >> I've edited /etc/system to what I think are more appropriate, but >> definitely higher, values. Then I changed the value of max_connections >> to 768; however, by watching the pg_stat_activity view and the >> serverlog, connections are rejected when the number of connections >> approaches 200 (with the infamous: "FATAL: connection limit exceeded >> for non-superusers" error). Hi Tom; > Um, did you restart the postmaster? This is a > can't-change-after-startup parameter. Yes. I've restarted. Even rebooted to have the /etc/system changes take effect. My concern is that there's a value somewhere that quietly sets an upper limit on what max_connections can be, that is determined from kernel settings when initdb is run. This page: http://www.postgresql.org/docs/7.4/static/runtime-config.html contains this line describing the max_connections parameter: "The default is typically 100, but may be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start. " 0) Is this correct? Does initdb set an unchangeable value that quietly limits the high end of max_connections? 1) Is there a way to get postgresql to reset this value, which determines the maximum value of max_connections, without dumping, re-running initdb and loading? Is there a way to see what this value is? 2) Are there any easy postgresql load testing tools? I ran across Tsung, but haven't had time to compile erlang. What do others do for load testing? Thanks, Isaac Vetter
Вложения
Isaac Vetter <ivetter@math.purdue.edu> writes: > Yes. I've restarted. Even rebooted to have the /etc/system changes take > effect. My concern is that there's a value somewhere that quietly sets > an upper limit on what max_connections can be, that is determined from > kernel settings when initdb is run. Well, you're mistaken: if the system can't support the specified max_connections then it will fail at postmaster start, not silently reduce the parameter value. It's certainly possible to fall foul of a kernel process-count restriction at runtime, but the message would look like "fork failed", not the one you're reporting. I think you've messed up changing the effective setting of max_connections somehow. Are you sure you edited the right copy of postgresql.conf? > 0) Is this correct? Does initdb set an unchangeable value that quietly > limits the high end of max_connections? The only thing that initdb does is put a value for max_connections into the initial postgresql.conf file, which it chooses by experimenting to see whether the postmaster will start with various settings. No hidden magic. regards, tom lane
Tom Lane wrote: > Isaac Vetter <ivetter@math.purdue.edu> writes: >> Yes. I've restarted. Even rebooted to have the /etc/system changes take >> effect. My concern is that there's a value somewhere that quietly sets >> an upper limit on what max_connections can be, that is determined from >> kernel settings when initdb is run. > > Well, you're mistaken: if the system can't support the specified > max_connections then it will fail at postmaster start, not silently > reduce the parameter value. > > It's certainly possible to fall foul of a kernel process-count > restriction at runtime, but the message would look like "fork failed", > not the one you're reporting. Hi Tom; That's exactly the answer I hoped for. Thank you. > I think you've messed up changing the effective setting of > max_connections somehow. Are you sure you edited the right copy of > postgresql.conf? I considered this too, but I've made other changes to the same postgresql.conf file that have taken effect (specifically, set stats_command_string = true and now the pg_stat_activity query fields contain data). I had some tabs in front of some comments following the max_connections definition. ? The method I've been using to check the number of connections is by running apache's benchmarking tool (ab) against a db heavy php page and then watching serverlog for connection failures. Is there a way to check the current value of max_connections from a running instance? (Or a better way to load test postgresql?) >> 0) Is this correct? Does initdb set an unchangeable value that quietly >> limits the high end of max_connections? > > The only thing that initdb does is put a value for max_connections into > the initial postgresql.conf file, which it chooses by experimenting to > see whether the postmaster will start with various settings. No hidden > magic. Isaac Vetter
Вложения
Isaac Vetter <ivetter@math.purdue.edu> writes: > The method I've been using to check the number of connections is by > running apache's benchmarking tool (ab) against a db heavy php page and > then watching serverlog for connection failures. Is there a way to check > the current value of max_connections from a running instance? "show max_connections" regards, tom lane
Kudos to Tom Lane for solving my problems. Once I knew what the running instance's max_connections value was, finding it hardcoded in the init script was easy. :) I've embedded answers to my own questions below for future searchers. Isaac Vetter Isaac Vetter wrote: > I'm running postgresql 7.4 on Solaris 9 and want to increase the value > of postgresql.conf's max_connections for an existing, running install. > I've edited /etc/system to what I think are more appropriate, but > definitely higher, values. Then I changed the value of max_connections > to 768; however, by watching the pg_stat_activity view and the > serverlog, connections are rejected when the number of connections > approaches 200 (with the infamous: "FATAL: connection limit exceeded > for non-superusers" error). > > I ran across a mention of initdb determining the greatest possible > number of connections according to kernerl settings. So presumably, > when postgresql is first setup, the highest value of max_connections > is decided then. > > A couple questions: > 0) Is this correct? Does initdb set an unchaged value that quietly > limits the high end of max_connections? This is not correct. > 1) Is there a way to get postgresql to reset this value that > determines the maximum value of max_connections, without dumping, > re-running initdb and loading? Is there a way to see what this value is? Runing 'show max_connections;' in pgsql will display this value. > 2) Are there any easy postgresql load testing tools? I ran across > Tsung, but haven't had time to compile erlang. What do others do for > load testing? My impression is that people write their own and use the logging settings in postgresql.conf > I had trouble finding recent example Solaris /etc/system numbers, so > I've included mine below. I'm still testing these are am not a solaris > sysadmin - ymmv. > > * On an 8 gig sun4u running solaris 9 > set shmsys:shminfo_shmmax=4294967295 > set shmsys:shminfo_shmmin=1 > set shmsys:shminfo_shmmni=512 > set shmsys:shminfo_shmseg=512 > *semaphores > *max # of semaphores > set semsys:seminfo_semmns=2048 > *max # of semaphore sets > set semsys:seminfo_semmni=1024 > *max # of semaphores per set > set semsys:seminfo_semmsl=128
Isaac Vetter <ivetter@math.purdue.edu> writes: > Once I knew what the running instance's max_connections value was, > finding it hardcoded in the init script was easy. :) How annoying :-(. The common theory among PG admins these days is that anything you can reasonably set in postgresql.conf should be set there, and not via alternatives such as command-line options to the postmaster. I'm afraid it didn't occur to me that you might be getting bitten that way. Glad you worked it out. regards, tom lane