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(at)VSServices(dot)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
>