Re: max_connections limit - Mailing list pgsql-admin

> 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