Обсуждение: 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