FreeBSD 5.2.1, postgresql 7.4.5 and shared memory settings

Greetings,

I am trying to increase the number of max_connections to 200 and found
out I couldn't even start postgresql. So I tried to increase
max_connections gradually (currently at 50 from 40 default) and adjust
shared memory settings along the way. Even at 50 max_connections, I
still cannot get postgresql to start.

The following is the error message I receive:

pgsql FATAL: could not create shared memory segment: Invalid argument
DETAIL: Failed system call was shmget(key=5432001, size=35323904,
03600). HINT: This error usually means that PostgreSQL's request for
a shared memory segment exceeded your kernel's SHMMAX parameter. You
can either reduce the request size or reconfigure the kernel with
larger SHMMAX. To reduce the request size (currently 35323904 bytes),
reduce PostgreSQL's shared_buffers parameter (curre
ntly 4096) and/or its max_connections parameter (currently 50). If the
request size is already small, it's possible that it is less than your
kernel's SHMMIN parameter, in which case raising the request size or
reconfiguring SHMMIN is called for. The PostgreSQL documentation
contains more information about shared memory configuration.
pg_ctl: postmaster does not start

This is confusing because the requested size is both higher than
SHMMIN and lower than SHMMAX as you can see below:

[ root(at)www ] $ sysctl -a | grep kern.ipc
kern.ipc.maxsockbuf: 262144
kern.ipc.sockbuf_waste_factor: 8
kern.ipc.somaxconn: 128
kern.ipc.max_linkhdr: 16
kern.ipc.max_protohdr: 60
kern.ipc.max_hdr: 76
kern.ipc.max_datalen: 132
kern.ipc.nmbclusters: 25600
kern.ipc.nmbufs: 51200
kern.ipc.nmbcnt: 32256
kern.ipc.nsfbufs: 6656
kern.ipc.mbuf_wait: 64
kern.ipc.mbuf_hiwm: 512
kern.ipc.mbuf_lowm: 128
kern.ipc.clust_hiwm: 128
kern.ipc.clust_lowm: 16
kern.ipc.maxpipekva: 10485760
kern.ipc.maxpipekvawired: 5242880
kern.ipc.pipes: 6
kern.ipc.bigpipes: 0
kern.ipc.pipekva: 98304
kern.ipc.pipekvawired: 0
kern.ipc.msgmax: 16384
kern.ipc.msgmni: 40
kern.ipc.msgmnb: 2048
kern.ipc.msgtql: 40
kern.ipc.msgssz: 8
kern.ipc.msgseg: 2048
kern.ipc.semmap: 30
kern.ipc.semmni: 10
kern.ipc.semmns: 60
kern.ipc.semmnu: 30
kern.ipc.semmsl: 60
kern.ipc.semopm: 100
kern.ipc.semume: 10
kern.ipc.semusz: 92
kern.ipc.semvmx: 32767
kern.ipc.semaem: 16384
kern.ipc.shmmax: 33554432
kern.ipc.shmmin: 1
kern.ipc.shmmni: 192
kern.ipc.shmseg: 128
kern.ipc.shmall: 8192
kern.ipc.shm_use_phys: 1
kern.ipc.shm_allow_removed: 0
kern.ipc.numopensockets: 27
kern.ipc.maxsockets: 25600
<118>kern.ipc.shm_use_phys:
<118>kern.ipc.shm_use_phys:

My kernel config options are:

options SYSVSHM
options SYSVMSG
options SYSVSEM
options SHMMAXPGS=262144
options SHMSEG=512
options SHMMNI=512
options SEMMNI=512
options SEMMNS=1024
options SEMMNU=512
options SEMMAP=512
options NMBCLUSTERS=32768

and my postgresql.conf contains:

max_connections = 50
shared_buffers = 4096
sort_mem = 4096
max_fsm_pages = 20000
max_fsm_relations = 1000

and here is a partial dmesg:

Copyright (c) 1992-2004 The FreeBSD Project.
Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994
The Regents of the University of California. All rights reserved.
FreeBSD 5.2.1-RELEASE #1: Sun Jan 30 02:11:33 EST 2005
rapichai(at)www(dot)logicmerc(dot)com:/usr/obj/usr/src/sys/GENERIC
Preloaded elf kernel "/boot/kernel/kernel" at 0xc0a19000.
Preloaded elf module "/boot/kernel/hpt374.ko" at 0xc0a1921c.
Preloaded elf module "/boot/kernel/acpi.ko" at 0xc0a192c8.
ACPI APIC Table: <A M I OEMAPIC >
Timecounter "i8254" frequency 1193182 Hz quality 0
CPU: Intel(R) Pentium(R) 4 CPU 3.20GHz (3207.29-MHz 686-class CPU)
Origin = "GenuineIntel" Id = 0xf34 Stepping = 4
Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE>
Hyperthreading: 2 logical CPUs
real memory = 2146631680 (2047 MB)
avail memory = 2079891456 (1983 MB)
FreeBSD/SMP: Multiprocessor System Detected: 2 CPUs
cpu0 (BSP): APIC ID: 0
cpu1 (AP): APIC ID: 1
ioapic0 <Version 2.0> irqs 0-23 on motherboard
Pentium Pro MTRR support enabled

as the postgres user i ran ulimit and it said "unlimited".

I've read all the tuning information that I can find, and still can't
seem to get postgresql to start with more than 40 max_connections.

Rick

Greetings,

I am trying to increase the number of max_connections to 200 and found
out I couldn't even start postgresql. So I tried to increase
max_connections gradually (currently at 50 from 40 default) and adjust
shared memory settings along the way. Even at 50 max_connections, I
still cannot get postgresql to start.

The following is the error message I receive:

pgsql FATAL: could not create shared memory segment: Invalid argument
DETAIL: Failed system call was shmget(key=5432001, size=35323904,
03600). HINT: This error usually means that PostgreSQL's request for
a shared memory segment exceeded your kernel's SHMMAX parameter. You
can either reduce the request size or reconfigure the kernel with
larger SHMMAX. To reduce the request size (currently 35323904 bytes),
reduce PostgreSQL's shared_buffers parameter (curre
ntly 4096) and/or its max_connections parameter (currently 50). If the
request size is already small, it's possible that it is less than your
kernel's SHMMIN parameter, in which case raising the request size or
reconfiguring SHMMIN is called for. The PostgreSQL documentation
contains more information about shared memory configuration.
pg_ctl: postmaster does not start

This is confusing because the requested size is both higher than
SHMMIN and lower than SHMMAX as you can see below:

[ root(at)www ] $ sysctl -a | grep kern.ipc
kern.ipc.maxsockbuf: 262144
kern.ipc.sockbuf_waste_factor: 8
kern.ipc.somaxconn: 128
kern.ipc.max_linkhdr: 16
kern.ipc.max_protohdr: 60
kern.ipc.max_hdr: 76
kern.ipc.max_datalen: 132
kern.ipc.nmbclusters: 25600
kern.ipc.nmbufs: 51200
kern.ipc.nmbcnt: 32256
kern.ipc.nsfbufs: 6656
kern.ipc.mbuf_wait: 64
kern.ipc.mbuf_hiwm: 512
kern.ipc.mbuf_lowm: 128
kern.ipc.clust_hiwm: 128
kern.ipc.clust_lowm: 16
kern.ipc.maxpipekva: 10485760
kern.ipc.maxpipekvawired: 5242880
kern.ipc.pipes: 6
kern.ipc.bigpipes: 0
kern.ipc.pipekva: 98304
kern.ipc.pipekvawired: 0
kern.ipc.msgmax: 16384
kern.ipc.msgmni: 40
kern.ipc.msgmnb: 2048
kern.ipc.msgtql: 40
kern.ipc.msgssz: 8
kern.ipc.msgseg: 2048
kern.ipc.semmap: 30
kern.ipc.semmni: 10
kern.ipc.semmns: 60
kern.ipc.semmnu: 30
kern.ipc.semmsl: 60
kern.ipc.semopm: 100
kern.ipc.semume: 10
kern.ipc.semusz: 92
kern.ipc.semvmx: 32767
kern.ipc.semaem: 16384
kern.ipc.shmmax: 33554432
kern.ipc.shmmin: 1
kern.ipc.shmmni: 192
kern.ipc.shmseg: 128
kern.ipc.shmall: 8192
kern.ipc.shm_use_phys: 1
kern.ipc.shm_allow_removed: 0
kern.ipc.numopensockets: 27
kern.ipc.maxsockets: 25600
<118>kern.ipc.shm_use_phys:
<118>kern.ipc.shm_use_phys:

My kernel config options are:

options SYSVSHM
options SYSVMSG
options SYSVSEM
options SHMMAXPGS=262144
options SHMSEG=512
options SHMMNI=512
options SEMMNI=512
options SEMMNS=1024
options SEMMNU=512
options SEMMAP=512
options NMBCLUSTERS=32768

and my postgresql.conf contains:

max_connections = 50
shared_buffers = 4096
sort_mem = 4096
max_fsm_pages = 20000
max_fsm_relations = 1000

and here is a partial dmesg:

Copyright (c) 1992-2004 The FreeBSD Project.
Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994
The Regents of the University of California. All rights reserved.
FreeBSD 5.2.1-RELEASE #1: Sun Jan 30 02:11:33 EST 2005
rapichai(at)www(dot)logicmerc(dot)com:/usr/obj/usr/src/sys/GENERIC
Preloaded elf kernel "/boot/kernel/kernel" at 0xc0a19000.
Preloaded elf module "/boot/kernel/hpt374.ko" at 0xc0a1921c.
Preloaded elf module "/boot/kernel/acpi.ko" at 0xc0a192c8.
ACPI APIC Table: <A M I OEMAPIC >
Timecounter "i8254" frequency 1193182 Hz quality 0
CPU: Intel(R) Pentium(R) 4 CPU 3.20GHz (3207.29-MHz 686-class CPU)
Origin = "GenuineIntel" Id = 0xf34 Stepping = 4
Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE>
Hyperthreading: 2 logical CPUs
real memory = 2146631680 (2047 MB)
avail memory = 2079891456 (1983 MB)
FreeBSD/SMP: Multiprocessor System Detected: 2 CPUs
cpu0 (BSP): APIC ID: 0
cpu1 (AP): APIC ID: 1
ioapic0 <Version 2.0> irqs 0-23 on motherboard
Pentium Pro MTRR support enabled

as the postgres user i ran ulimit and it said "unlimited".

I've read all the tuning information that I can find, and still can't
seem to get postgresql to start with more than 40 max_connections.

Rick

On Sun, Jan 30, 2005 at 03:21:25PM -0500, Rick Apichairuk wrote:

> I am trying to increase the number of max_connections to 200 and found
> out I couldn't even start postgresql. So I tried to increase
> max_connections gradually (currently at 50 from 40 default) and adjust
> shared memory settings along the way. Even at 50 max_connections, I
> still cannot get postgresql to start.
>
> The following is the error message I receive:
>
> pgsql FATAL: could not create shared memory segment: Invalid argument
> DETAIL: Failed system call was shmget(key=5432001, size=35323904,
> 03600).

> To reduce the request size (currently 35323904 bytes),

> kern.ipc.shmmax: 33554432

Looks like the request size is just higher than shmmax. The majority of
the request is for the shared buffers, so if you can't increase shmmax
any more you can reduce your shared buffers downwards from 4096 and this
will give you some room for more connections.

Richard

Rick Apichairuk <rapichai(at)gmail(dot)com> writes:
> This is confusing because the requested size is both higher than
> SHMMIN and lower than SHMMAX as you can see below:

SHMALL is the other limit that might cause this failure.

regards, tom lane

On Sun, Jan 30, 2005 at 03:21:25PM -0500, Rick Apichairuk wrote:

> This is confusing because the requested size is both higher than
> SHMMIN and lower than SHMMAX as you can see below:

Maybe your problem is shmall:

> [ root(at)www ] $ sysctl -a | grep kern.ipc
> kern.ipc.shmmax: 33554432
> kern.ipc.shmmin: 1
> kern.ipc.shmmni: 192
> kern.ipc.shmseg: 128
> kern.ipc.shmall: 8192

Not sure if that is measured in the same units on your platform though.

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"La persona que no quería pecar / estaba obligada a sentarse
en duras y empinadas sillas / desprovistas, por cierto
de blandos atenuantes" (Patricio Vogel)

On Sun, 30 Jan 2005 20:50:21 +0000, Richard Poole <rp(at)guests(dot)deus(dot)net> wrote:
> On Sun, Jan 30, 2005 at 03:21:25PM -0500, Rick Apichairuk wrote:
>
> > I am trying to increase the number of max_connections to 200 and found
> > out I couldn't even start postgresql. So I tried to increase
> > max_connections gradually (currently at 50 from 40 default) and adjust
> > shared memory settings along the way. Even at 50 max_connections, I
> > still cannot get postgresql to start.
> >
> > The following is the error message I receive:
> >
> > pgsql FATAL: could not create shared memory segment: Invalid argument
> > DETAIL: Failed system call was shmget(key=5432001, size=35323904,
> > 03600).
>
> > To reduce the request size (currently 35323904 bytes),
>
> > kern.ipc.shmmax: 33554432
>
> Looks like the request size is just higher than shmmax. The majority of
> the request is for the shared buffers, so if you can't increase shmmax
> any more you can reduce your shared buffers downwards from 4096 and this
> will give you some room for more connections.
>
>
> Richard
>

Thanks... I missed that completely (need sleep!). I will try to bump
up my SHMMAXPGS from 262144 to 400000.

Is the shared_buffers per process? The reason I ask is the formula
given on http://www.postgresql.org/docs/7.4/static/kernel-resources.html
for calculating how much SHMMAX you need is:

SHMMAX = 250 kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections
up to infinity

If it were per process (connection) shouldn't it be:

SHMMAX = (250 kB + 8.2 kB * shared_buffers + 14.2 kB) *
max_connections up to infinity

Also the examples for FreeBSD are:

options SYSVSHM
options SHMMAXPGS=4096
options SHMSEG=256

options SYSVSEM
options SEMMNI=256
options SEMMNS=512
options SEMMNU=256
options SEMMAP=256

How many connections is this supposed to support?

Thanks in advance.

On Sun, Jan 30, 2005 at 04:26:29PM -0500, Rick Apichairuk wrote:
> Is the shared_buffers per process? The reason I ask is the formula
> given on http://www.postgresql.org/docs/7.4/static/kernel-resources.html
> for calculating how much SHMMAX you need is:
>
> SHMMAX = 250 kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections
> up to infinity

No, it is system-wide. shared_buffers is used for a number of things,
but any excess is used by PostgreSQL to buffer data it reads. Before 8.0
the consensus was that (unlike most databases) it's better to let the OS
cache the data instead of PostgreSQL. 8.0 has a much more advanced cache
management algorithm, so it might now be better to let PostgreSQL be
your primary cache, but AFAIK no testing has been done to show that's
the case.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

I've tested this up to 250 connections on 5.3:

options SHMMAXPGS=131072
options SEMMNI=128
options SEMMNS=512
options SEMUME=100
options SEMMNU=256

I tried the settings you gave and they worked (with some caveats).

After I recompiled the FreeBSD kernel with the options:

options SYSVSHM
options SYSVMSG
options SYSVSEM

options SHMMAXPGS=131072
options SEMMNI=128
options SEMMNS=512
options SEMUME=100
options SEMMNU=256

I rebooted and attempted to start postgresql and it failed again. I
then ran sysctl -a to verify that the settings were correct. They were
not. Regardless of what's configured in the kernel conf file, the
default values were loaded. I had to edit my /etc/sysctl.conf and
/boot/loader.conf file to get it to work. Which makes me wonder why
the settings are even in the kernel conf file.... but that's not
relevant to this list. But the values you provided did allow me to
start up postgresql with max_connections set up to 479 (480 is too
high and causes error).

On Sun, 30 Jan 2005 13:55:35 -0800, Chris <pglist(at)gmail(dot)com> wrote:
> I've tested this up to 250 connections on 5.3:
>
> options SHMMAXPGS=131072
> options SEMMNI=128
> options SEMMNS=512
> options SEMUME=100
> options SEMMNU=256
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

Rick Apichairuk wrote:
> I tried the settings you gave and they worked (with some caveats).
>
> After I recompiled the FreeBSD kernel with the options:
>
> options SYSVSHM
> options SYSVMSG
> options SYSVSEM
>
> options SHMMAXPGS=131072
> options SEMMNI=128
> options SEMMNS=512
> options SEMUME=100
> options SEMMNU=256
>
> I rebooted and attempted to start postgresql and it failed again. I
> then ran sysctl -a to verify that the settings were correct. They were
> not. Regardless of what's configured in the kernel conf file, the
> default values were loaded.

I assume you added these variables to either the GENERIC or a custom
kernel in /usr/src/sys/i386/conf. If you created a custom kernel called
CUSTOM, then you would:

cd /usr/src
make buildkernel KERNCONF=CUSTOM
make installkernel KERNCONF=CUSTOM

Just wanted to verify that you built and installed the kernel using the
config file that has the settings you listed above (your sysctl output
would indicate otherwise).

Sven

On Sun, 30 Jan 2005 21:12:08 -0500, Sven Willenberger <sven(at)dmv(dot)com> wrote:

> I assume you added these variables to either the GENERIC or a custom
> kernel in /usr/src/sys/i386/conf. If you created a custom kernel called
> CUSTOM, then you would:

That's what I did.

> cd /usr/src
> make buildkernel KERNCONF=CUSTOM
> make installkernel KERNCONF=CUSTOM

The exact commands I used were:

KERN_CONF=CUSTOM make buildkernel > make.buildkernel.log 2>&1 & tail
-f make.buildkernel.log

KERN_CONF=CUSTOM make installkernel > make.installkernel.log 2>&1 &
tail -f make.installkernel.log

> Just wanted to verify that you built and installed the kernel using the
> config file that has the settings you listed above (your sysctl output
> would indicate otherwise).

Rick Apichairuk wrote:
> On Sun, 30 Jan 2005 21:12:08 -0500, Sven Willenberger <sven(at)dmv(dot)com> wrote:
>
>
>>I assume you added these variables to either the GENERIC or a custom
>>kernel in /usr/src/sys/i386/conf. If you created a custom kernel called
>>CUSTOM, then you would:
>
>
> That's what I did.
>
>
>>cd /usr/src
>>make buildkernel KERNCONF=CUSTOM
>>make installkernel KERNCONF=CUSTOM
>
>
> The exact commands I used were:
>
> KERN_CONF=CUSTOM make buildkernel > make.buildkernel.log 2>&1 & tail
> -f make.buildkernel.log
>
> KERN_CONF=CUSTOM make installkernel > make.installkernel.log 2>&1 &
> tail -f make.installkernel.log
>
KERNCONF not KERN_CONF ... you are simply rebuilding GENERIC which is
why your settings are not "sticking" (see the /usr/src/UPDATING file).

Sven