Re: max_connections limit

> 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