Re: How to increase shared mem for PostgreSQL on FreeBSD

On Thursday 13 December 2001 10:42, Joe Koenig wrote:
> My system is FreeBSD 4.3 with 1GHz P3, 1GB RAM, SCSI RAID 5. I read an
> article on PHPBuilder about optimizing PostgreSQL - one big thing was
> increasing shared memory. I asked on the FreeBSD mailing list and got
> different suggestions as to how to increase the shared memory - my main
> question is - do I need to rebuild my kernel? I used sysctl -w
> shmall=131072 to up my shared mem, then edited postgresql.conf to show:
>
> shared_buffers = 15200
> sort_mem = 32168

That sort mem is REAL high.  Have you read Bruce's tuning pages?
He gives a real good description on the tradeoffs involved.

http://www.ca.postgresql.org/docs/momjian/hw_performance/

> and postgres wouldn't start. I assume that means the shared buffers is
> actually larger than the amount of shared mem, so it appears the sysctl
> didn't really do anything. If I comment out the shared_buffers line,
> postgres will start, but upping the sort mem doesn't help my insert
> speed. Can anyone help me out with how to get the shared mem increased
> so I can up the shared_buffers in postgres? Thanks,
>
> Joe
>
Hi,

Please post the output from the following command:

sysctl -a | grep shm

Here is information from my primary server:

FreeBSD 4.4-STABLE #1: Thu Nov  8 05:58:44 CST 2001
CPU: Pentium III/Pentium III Xeon/Celeron (845.64-MHz 686-class CPU)
  Origin = "GenuineIntel"  Id = 0x683  Stepping = 3

real memory  = 1073676288 (1048512K bytes)
avail memory = 1040752640 (1016360K bytes)
--

tms:postgres# sysctl -a | grep shm
kern.ipc.shmmax: 128004097
kern.ipc.shmmin: 1
kern.ipc.shmmni: 512
kern.ipc.shmseg: 1024
kern.ipc.shmall: 31251
kern.ipc.shm_use_phys: 1
tms:postgres#
--

Also, I just edited my kernel config file and recompiled.
Please note that I've got ALOT of extra in a couple of places.  This is for
other applications than just PostgreSQL.
Here is a section from my kernel config file.

--SNIP--
# SysV stuff -- GB Mods
options         SYSVSHM                 #SYSV-style shared memory
options         SHMMAXPGS=31251
options         SHMALL=31251
options         SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)"
options         SHMSEG=1024
options         SHMMNI=512
options         SHMMIN=1

options         SYSVMSG                 #SYSV-style message queues

options         SYSVSEM                 #SYSV-style semaphores
options         SEMMNI=256
options         SEMMNS=512
options         SEMMNU=256
options         SEMMAP=256
--SNIP--

And here is the section from my PG config file:
--SNIP--
shared_buffers = 14336
sort_mem = 8096
--SNIP--


I hope some of this helps.

GB

--
GB Clark II - N5VMF - Roaming FreeBSD Admin
gclarkii@VSServices.COM - Looking for extra work FreeBSD and PostgreSQL
           CTHULU for President - Why choose the lesser of two evils?