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