Обсуждение: 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@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@www.logicmerc.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@gmail.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@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[@]dcc.uchile.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@guests.deus.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@decibel.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@gmail.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@postgresql.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@dmv.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@dmv.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