Re: How to increase shared mem for PostgreSQL on FreeBSD

First of all I would like to thank GB and everyone else who helped. I
recompiled my kernel yesterday. I was able to increase my shared memory,
but I've still yet to see any performance increase in the script I'm
concerned with. The whole script takes about 27 minutes to run and
inserts somewhere around 700,000 rows. I read somewhere to turn off
fsync, but I also see that that is not a real safe move and won't save
me much time in 7.1. When the script is solely doing inserts, it is able
to insert around 200 rows per second. The inserts are wrapped in
transactions in groups of 5000. It seems that I should be able to insert
faster than that with my system (1GHz, 1GB RAM, RAID 5 w/10K 18GB
drives). Are there other things I need to be concerned with that will
help the speed? my shared_buffers is 15200 and sort_mem is at 8096
currently. Also, the script doing the inserts is written in PHP 4.1.0 -
could that be slowing me, as well? Thanks,

Joe

GB Clark II wrote:
>
> 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?