Обсуждение: max_connections limit

> I have a server running FreeBSD 4.7 and postgres 7.2.4 and when I
> set "max_connections" to a value greater than 47, I am unable to
> connect to the database server.  I need to be able to raise this
> number much higher because every site on this server runs postgres
> and at peak times this limit is reached and causes problems.  The
> server has 2 gigs of memory.  I would appreciate any suggestion.

What's the output in /var/log/pgsql ?  -sc

--
Sean Chittenden

I have a freeBSD with postgres too and I believe I had to tune the
kernel for this question



On Mon, 17 Mar 2003, Sean Chittenden wrote:

> > I have a server running FreeBSD 4.7 and postgres 7.2.4 and when I
> > set "max_connections" to a value greater than 47, I am unable to
> > connect to the database server.  I need to be able to raise this
> > number much higher because every site on this server runs postgres
> > and at peak times this limit is reached and causes problems.  The
> > server has 2 gigs of memory.  I would appreciate any suggestion.
>
> What's the output in /var/log/pgsql ?  -sc
>
> --
> Sean Chittenden
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

> > > I have a server running FreeBSD 4.7 and postgres 7.2.4 and when I
> > > set "max_connections" to a value greater than 47, I am unable to
> > > connect to the database server.  I need to be able to raise this
> > > number much higher because every site on this server runs postgres
> > > and at peak times this limit is reached and causes problems.  The
> > > server has 2 gigs of memory.  I would appreciate any suggestion.
> >
> > What's the output in /var/log/pgsql ?  -sc
>
> I have a freeBSD with postgres too and I believe I had to tune the
> kernel for this question

That's what I was getting at actually.  When you install PostgreSQL
via the ports, a file is both sent to the user for visual review, as
well being copied to a file:

     /usr/local/pgsql/post-install-notes

Please review this and see if this doesn't solve your problem.
FreeBSDs SHM settings are low by default and need to be increased for
any kind of production use.

### Begin
To allow many simultaneous connections to your PostgreSQL server, you
should raise the SystemV shared memory limits in your kernel. Here are
example values for allowing up to 180 clients (tinkering in
postgresql.conf also needed, of course):
  options         SYSVSHM
  options         SYSVSEM
  options         SYSVMSG
  options         SHMMAXPGS=65536
  options         SEMMNI=40
  options         SEMMNS=240
  options         SEMUME=40
  options         SEMMNU=120
### End

Please let me know if anyone thinks these numbers should be revised or
could be broken down into a formula for calculation and I'll
incorporate them.  -sc

--
Sean Chittenden

> Thank you for your suggestion, I recompiled the kernel and was able to
> increase the number of maximum connections.  Is there a formula that
> relates parameters such as SEMMNI or SEMMNS to the desired SHMMAXPGS?

I don't know, but I'd be curious to see some kind of formula as well.
I wonder how correct the following is:

SHMMAXPGS = ((max_connections * shared_buffers * 16K) + (wal_buffers *
    8K) + (max_fsm_relations * 40B) + (max_fsm_pages * 6B)) /
    page_size
SEMMNI = 2 * max_connections (????)
SEMMSL = SEMMNI
SHMSEG = 10    # (???? this is a per SysV process setting)
SEMMNS = 1.5 * max_connections (????)
SEMMNU = 0.75 * max_connections (????)
SEMUME = 10 (????)

Page size is 4086 on x86 hardware, I'm not sure about 64bit platforms,
but getpagesize(3) will return the right size no matter what.
Regardless, with the default PostgreSQL config, you'd have to run with
8232 SHM pages by default.... which is higher than the default GENERIC
kernel.  I suspect that the above formula isn't correct or that the
application doesn't hit the theoretical maxes that the formula
suggests, but I don't think that those recommendations are that far
off.  Does someone with a more authoritative knowledge of this have an
opinion on the above?  If these seem right, I'd love to commit some
kind of documentation on this topic to the handbook.  I get asked this
kind of a question quite often by FreeBSD users.

-sc


PS Here are the defaults, pretty conservative, IMHO, but there are
some definitions included for what the various SysV parameters are.

# Maximum number of entries in a semaphore map.
options         SEMMAP=31

# Maximum number of System V semaphores that can be used on the system at
# one time.
options         SEMMNI=11

# Total number of semaphores system wide
options         SEMMNS=61

# Total number of undo structures in system
options         SEMMNU=31

# Maximum number of System V semaphores that can be used by a single process
# at one time.
options         SEMMSL=61

# Maximum number of operations that can be outstanding on a single System V
# semaphore at one time.
options         SEMOPM=101

# Maximum number of undo operations that can be outstanding on a single
# System V semaphore at one time.
options         SEMUME=11

# Maximum number of shared memory pages system wide.
options         SHMALL=1025

# Maximum size, in bytes, of a single System V shared memory region.
options         SHMMAX=(SHMMAXPGS*PAGE_SIZE+1)
options         SHMMAXPGS=1025

# Minimum size, in bytes, of a single System V shared memory region.
options         SHMMIN=2

# Maximum number of shared memory regions that can be used on the system
# at one time.
options         SHMMNI=33

# Maximum number of System V shared memory regions that can be attached to
# a single process at one time.
options         SHMSEG=9


> > > > I have a server running FreeBSD 4.7 and postgres 7.2.4 and when I
> > > > set "max_connections" to a value greater than 47, I am unable to
> > > > connect to the database server.  I need to be able to raise this
> > > > number much higher because every site on this server runs postgres
> > > > and at peak times this limit is reached and causes problems.  The
> > > > server has 2 gigs of memory.  I would appreciate any suggestion.
> > >
> > > What's the output in /var/log/pgsql ?  -sc
> >
> > I have a freeBSD with postgres too and I believe I had to tune the
> > kernel for this question
>
> That's what I was getting at actually.  When you install PostgreSQL
> via the ports, a file is both sent to the user for visual review, as
> well being copied to a file:
>
>      /usr/local/pgsql/post-install-notes
>
> Please review this and see if this doesn't solve your problem.
> FreeBSDs SHM settings are low by default and need to be increased for
> any kind of production use.
>
> ### Begin
> To allow many simultaneous connections to your PostgreSQL server, you
> should raise the SystemV shared memory limits in your kernel. Here are
> example values for allowing up to 180 clients (tinkering in
> postgresql.conf also needed, of course):
>   options         SYSVSHM
>   options         SYSVSEM
>   options         SYSVMSG
>   options         SHMMAXPGS=65536
>   options         SEMMNI=40
>   options         SEMMNS=240
>   options         SEMUME=40
>   options         SEMMNU=120
> ### End
>
> Please let me know if anyone thinks these numbers should be revised or
> could be broken down into a formula for calculation and I'll
> incorporate them.  -sc
>
> --
> Sean Chittenden
>

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:
> I wonder how correct the following is:

> SHMMAXPGS = ((max_connections * shared_buffers * 16K) + (wal_buffers *
>     8K) + (max_fsm_relations * 40B) + (max_fsm_pages * 6B)) /
>     page_size

Not very.  shared_buffers are 8K apiece, not 16K, and you don't multiply
them by max_connections.  There *is* a multiplier for max_connections
but I'm not sure what it is --- less than 1K, probably.  Also you should
allow for the lock table, which will be some small multiplier times
max_connections * max_locks_per_transaction.

> SEMMNI = 2 * max_connections (????)
> SEMMSL = SEMMNI
> SHMSEG = 10    # (???? this is a per SysV process setting)
> SEMMNS = 1.5 * max_connections (????)
> SEMMNU = 0.75 * max_connections (????)
> SEMUME = 10 (????)

Dunno where you got these semaphore numbers from, either.

            regards, tom lane

> > I wonder how correct the following is:
>
> > SHMMAXPGS = ((max_connections * shared_buffers * 16K) + (wal_buffers *
> >     8K) + (max_fsm_relations * 40B) + (max_fsm_pages * 6B)) /
> >     page_size
>
> Not very.  shared_buffers are 8K apiece, not 16K, and you don't multiply
> them by max_connections.  There *is* a multiplier for max_connections
> but I'm not sure what it is --- less than 1K, probably.

Alright, the output wasn't quite right in terms of matching up with
real world data so this doens't surprise me....

I know they're 8K, but going off of the hint from postgresql.conf, I
changed it to 16K instead of 2 * 8K:

#shared_buffers = 64            # 2*max_connections, min 16, typically 8KB each

Each shared buffer is 8K, need two shared buffers per connection is
how I read that comment.

> Also you should allow for the lock table, which will be some small
> multiplier times max_connections * max_locks_per_transaction.

I don't know what data structure is used for the locking... I'm
guessing it's not a pthread's mutex.  :) sizeof(void *) or some
struct?

> > SEMMNI = 2 * max_connections (????)
> > SEMMSL = SEMMNI
> > SHMSEG = 10    # (???? this is a per SysV process setting)
> > SEMMNS = 1.5 * max_connections (????)
> > SEMMNU = 0.75 * max_connections (????)
> > SEMUME = 10 (????)
>
> Dunno where you got these semaphore numbers from, either.

Eh, a cross between the approximations in post-install-notes from
FreeBSD and the databases that I manage.  I suspect someone will have
to audit the code to come up with an official recommendation.

--
Sean Chittenden

Sean Chittenden <sean@chittenden.org> writes:
> I know they're 8K, but going off of the hint from postgresql.conf, I
> changed it to 16K instead of 2 * 8K:

> #shared_buffers = 64            # 2*max_connections, min 16, typically 8KB each

> Each shared buffer is 8K, need two shared buffers per connection is
> how I read that comment.

It's not very well phrased then.  I just updated the comments to look
like this:


#shared_buffers = 64        # min 16, at least max_connections*2, 8KB each
#max_locks_per_transaction = 64    # min 10, ~260*max_connections bytes each
#wal_buffers = 8        # min 4, 8KB each
# fsm = free space map
#max_fsm_relations = 1000    # min 100, ~50 bytes each
#max_fsm_pages = 20000        # min max_fsm_relations*16, 6 bytes each
# note: increasing max_connections also costs about 500 bytes each,
# in addition to costs from shared_buffers and max_locks_per_transaction.


The multipliers for max_connections and max_locks_per_transaction are
based on some experiments I just did (alter parameter, note change in
size of shared memory block...).  They're probably somewhat
architecture-dependent, but these should be close enough for the purpose.

            regards, tom lane