Re: How to increase shared mem for PostgreSQL on FreeBSD

You might try dropping any indexes on the tables affected while doing the
inserts and then re-creating it when you're done...

-p

On Fri, 14 Dec 2001, Joe Koenig wrote:

> 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?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>