FreeBSD 5.2.1, postgresql 7.4.5 and shared memory settings

From: Rick Apichairuk <rapichai(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: FreeBSD 5.2.1, postgresql 7.4.5 and shared memory settings
Date: 2005-01-30 20:21:25
Message-ID: 9776bb66050130122122a3e342@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Rick Apichairuk 2005-01-30 20:37:42 Fwd: FreeBSD 5.2.1, postgresql 7.4.5 and shared memory settings
Previous Message Uwe C. Schroeder 2005-01-30 06:53:46 Re: Does anyone have a script to monitor if FSM settings are sufficient?

Browse pgsql-general by date

  From Date Subject
Next Message Rick Apichairuk 2005-01-30 20:37:42 Fwd: FreeBSD 5.2.1, postgresql 7.4.5 and shared memory settings
Previous Message Edward Macnaghten 2005-01-30 19:53:33 ANNOUNCE: ShellSQL 0.7